MySQL Bugs: #112179: Backslash in string literals does not work for CREATE TABLE with NO_BACKSLASH

Bug #112179	Backslash in string literals does not work for CREATE TABLE with NO_BACKSLASH_
Submitted:	25 Aug 2023 8:23	Modified:	28 Aug 2023 10:07
Reporter:	Ralf Wiebicke	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.34-0ubuntu0.22.04.1	OS:	Ubuntu (Linux Mint)
Assigned to:		CPU Architecture:	x86

Description:
NO_BACKSLASH_ESCAPES does not work when creating tables with check constraints with regexp.

How to repeat:
I start with NO_BACKSLASH_ESCAPES disabled:

mysql> SELECT @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

I create a table with a check constraint requiring at least a single digit:

CREATE TABLE tab ( col varchar(80), CHECK (col REGEXP '\\d'));

Note, that the backslash for \d is correctly escaped by an additional backslash.

The check constraint works as expected:

mysql> INSERT tab VALUES ('ab');
ERROR 3819 (HY000): Check constraint 'tab_chk_1' is violated.
mysql> INSERT tab VALUES ('a1b');
Query OK, 1 row affected (0,00 sec)

Then I add NO_BACKSLASH_ESCAPES to my sql_mode:

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';

and create an equivalent table:

CREATE TABLE tab2 ( col varchar(80), CHECK (col REGEXP '\d'));

Note, that the backslash for \d is correctly no longer escaped.

Then the check constraint does NOT work as expected, the second INSERT should not fail:

mysql> INSERT tab2 VALUES ('ab');
ERROR 3819 (HY000): Check constraint 'tab2_chk_1' is violated.
mysql> INSERT tab2 VALUES ('a1b');
ERROR 3819 (HY000): Check constraint 'tab2_chk_1' is violated.

The same regexp in a simple SELECT (with still NO_BACKSLASH_ESCAPES enabled) shows the correct behaviour:

mysql> SELECT 'ab' REGEXP '\d', 'a1b' REGEXP '\d';
+------------------+-------------------+
| 'ab' REGEXP '\d' | 'a1b' REGEXP '\d' |
+------------------+-------------------+
|                0 |                 1 |
+------------------+-------------------+

This problem is not specific to \d, I have seen it with \A and \z as well.

Seems to be related to #111737

A bit more investigation: a reconnect "heals" the problem. So after what was done in "How to repeat:" I disconnect and connect again without dropping or otherwise touching the tables. Then both tables are fine without NO_BACKSLASH_ESCAPES:

mysql> SELECT @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

mysql> INSERT tab VALUES ('ab');
ERROR 3819 (HY000): Check constraint 'tab_chk_1' is violated.
mysql> INSERT tab VALUES ('a1b');
Query OK, 1 row affected (0,00 sec)

mysql> INSERT tab2 VALUES ('ab');
ERROR 3819 (HY000): Check constraint 'tab2_chk_1' is violated.
mysql> INSERT tab2 VALUES ('a1b');
Query OK, 1 row affected (0,00 sec)

and with NO_BACKSLASH_ESCAPES as well:

mysql> 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';
Query OK, 0 rows affected (0,00 sec)

mysql> INSERT tab VALUES ('ab');
ERROR 3819 (HY000): Check constraint 'tab_chk_1' is violated.
mysql> INSERT tab VALUES ('a1b');
Query OK, 1 row affected (0,00 sec)

mysql> INSERT tab2 VALUES ('ab');
ERROR 3819 (HY000): Check constraint 'tab2_chk_1' is violated.
mysql> INSERT tab2 VALUES ('a1b');
Query OK, 1 row affected (0,00 sec)

Hello Ralf Wiebicke,

Thank you for the report and test case.
Verified as described.

regards,
Umesh