Mysql – 10.3.9-MariaDB can not query equal operator with special character

mariadbmariadb-10.3MySQL

I have a MariaDB database that has a filed TagName with value ~!@#$%^&*()_+|}{":?><./';[]\=-`

I had set mode SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_BACKSLASH_ESCAPES');

When I try to created DB in MySQL 8.0.13, this query work well and return 1 row 

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';

But in 10.3.9-MariaDB, the query 

select * from taginfo1 where TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';

can not return any result.

Is this an MariaDB issue or I missed some configuration?

Best Answer

According to the documentation about sql_mode NO_BACKSLASH_ESCAPES:

Disables using the backslash character \ as an escape character within strings, making it equivalent to an ordinary character.

Your string has a double backslash in it. In the NO_BACKSLASH_ESCAPES sql_mode this will be interpreted as a double backslash whereas in the default sql_mode it's interpreted as a single backslash.

Below are some tests with MariaDB 10.3.13 using the following table: 

CREATE TABLE taginfo1 (id int unsigned PRIMARY KEY, TagName varchar(200));

Default sql_mode

INSERT INTO taginfo1 (id, TagName) VALUES (1, '~!@#$%^&*()_+|}{":?><./'';[]\\=-`');
SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';
+----+---------------------------------+
| id | TagName                         |
+----+---------------------------------+
|  1 | ~!@#$%^&*()_+|}{":?><./';[]\=-` |
+----+---------------------------------+

Note that one backslash has disappeared.

sql_mode with NO_BACKSLASH_ESCAPES

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';
Empty set (0.000 sec)

Let's remove one backslash from the WHERE clause:

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';
+----+---------------------------------+
| id | TagName                         |
+----+---------------------------------+
|  1 | ~!@#$%^&*()_+|}{":?><./';[]\=-` |
+----+---------------------------------+

Then let's insert a second row while we're in NO_BACKSLASH_ESCAPES mode:

INSERT INTO taginfo1 (id, TagName) VALUES (2, '~!@#$%^&*()_+|}{":?><./'';[]\\=-`');

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';
+----+----------------------------------+
| id | TagName                          |
+----+----------------------------------+
|  2 | ~!@#$%^&*()_+|}{":?><./';[]\\=-` |
+----+----------------------------------+

As far as I can tell, this all works as per the documentation. If you really get different results in 10.3.9, then presumably there was a bug that's been fixed.

Related Question