Description:
When I use a double tick ('') in a string literal to specify a single tick (') it does not work when having NO_BACKSLASH_ESCAPES enabled and trying in the check constraint of a CREATE TABLE.
It does work in CREATE TABLE with NO_BACKSLASH_ESCAPES disabled. And it does work with NO_BACKSLASH_ESCAPES enabled in a simple SELECT.
How to repeat:
I do
CREATE TABLE tab(col varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, CONSTRAINT cons CHECK(col REGEXP '''+')) ENGINE=InnoDB;
Query OK, 0 rows affected (0,05 sec)
and it works. Then I do enable NO_BACKSLASH_ESCAPES:
set @@sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_BACKSLASH_ESCAPES';
mysql> DROP TABLE tab;
Query OK, 0 rows affected (0,02 sec)
mysql> CREATE TABLE tab(col varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, CONSTRAINT cons CHECK(col REGEXP '''+')) ENGINE=InnoDB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''))' at line 1
When I check my regexp (with NO_BACKSLASH_ESCAPES still enabled) it works:
mysql> SELECT 'aaa' REGEXP '''+';
+---------------------+
| 'aaa' REGEXP '''+' |
+---------------------+
| 0 |
+---------------------+
1 row in set (0,00 sec)
mysql> SELECT 'a''aa' REGEXP '''+';
+-----------------------+
| 'a''aa' REGEXP '''+' |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0,00 sec)
Description: When I use a double tick ('') in a string literal to specify a single tick (') it does not work when having NO_BACKSLASH_ESCAPES enabled and trying in the check constraint of a CREATE TABLE. It does work in CREATE TABLE with NO_BACKSLASH_ESCAPES disabled. And it does work with NO_BACKSLASH_ESCAPES enabled in a simple SELECT. How to repeat: I do CREATE TABLE tab(col varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, CONSTRAINT cons CHECK(col REGEXP '''+')) ENGINE=InnoDB; Query OK, 0 rows affected (0,05 sec) and it works. Then I do enable NO_BACKSLASH_ESCAPES: set @@sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_BACKSLASH_ESCAPES'; mysql> DROP TABLE tab; Query OK, 0 rows affected (0,02 sec) mysql> CREATE TABLE tab(col varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, CONSTRAINT cons CHECK(col REGEXP '''+')) ENGINE=InnoDB; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''))' at line 1 When I check my regexp (with NO_BACKSLASH_ESCAPES still enabled) it works: mysql> SELECT 'aaa' REGEXP '''+'; +---------------------+ | 'aaa' REGEXP '''+' | +---------------------+ | 0 | +---------------------+ 1 row in set (0,00 sec) mysql> SELECT 'a''aa' REGEXP '''+'; +-----------------------+ | 'a''aa' REGEXP '''+' | +-----------------------+ | 1 | +-----------------------+ 1 row in set (0,00 sec)