MariaDB Bugs? LIKE Escaping Broken

Question

Server version: 10.1.21-MariaDB-1~jessie

select 'a%b' like '%\%\%';
+--------------------+
| 'a%b' like '%\%\%' |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

the like clause represents 'wildcard + literal % + literal %' but it matches 'a%b'.

or

select 'a%b' like '%\%\%\%\%\%';
+--------------------------+
| 'a%b' like '%\%\%\%\%\%' |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.00 sec)

MySQL 5.5.38 returns 0 for both statements. Is the syntax of MariaDB different?

Add

@rahul pointed that the syntax is wrong, so I created a dummy table and ran

SELECT * FROM `table1` where 'a%b' like '%\%\%';

which matches every row in the table.

However, a row with field1='a%b' doesn't match when I ran

SELECT * from `table` where field1 like '%\%\%';

Now going to test on 10.1.22.

MariaDB by default uses backslash character for escaping `%` (unless overridden by the `escape` clause within the `like` expression). — Shadow, Mar 21 '17 at 10:48
I can't reproduce this on 10.1.22 so I'm guessing a bug that was fixed? — apokryfos, Mar 21 '17 at 11:10

score 0 · Accepted Answer · answered Mar 22 '17 at 04:04

It seems this is fixed in 10.1.22.

Server version: 10.1.22-MariaDB-1~xenial mariadb.org binary distribution

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select 'a%b' like '%\%\%';
+--------------------+
| 'a%b' like '%\%\%' |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

MariaDB Bugs? LIKE Escaping Broken

1 Answers1

Linked