[MDEV-24254] CHECK (ISNULL(code)=ISNULL(class)) syntax error Created: 2020-11-20  Updated: 2021-01-22  Resolved: 2020-11-20

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.3.27
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Brooke Tsui Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Environment:

Linux 5.4.0-53-generic #59~18.04.1-Ubuntu
Server version: 10.3.27-MariaDB-1:10.3.27+maria~bionic-log


Issue Links:
Duplicate
duplicates MDEV-24194 View definition corruption Closed
duplicates MDEV-24226 10.3.26 rejects syntax that 10.3.25 a... Closed
is duplicated by MDEV-24641 Check constraint parser incorrectly i... Closed

 Description   

It was working before until this version. The clause "CHECK (ISNULL(code)=ISNULL(class))" now causes syntax error mistakenly. Please refer to screen capture for problem explanation and replication.

root@fusion:~# uname -a
Linux fusion 5.4.0-53-generic #59~18.04.1-Ubuntu SMP Wed Oct 21 12:14:56 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux
root@fusion:~#
root@fusion:~# mysql fusion
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 39
Server version: 10.3.27-MariaDB-1:10.3.27+maria~bionic-log mariadb.org binary distribution

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

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

MariaDB [fusion]> CREATE TABLE IF NOT EXISTS `test` (
-> `jmaster` varchar(18) COLLATE utf8_bin NOT NULL,
-> `class` varchar(2) COLLATE utf8_bin DEFAULT NULL,
-> `code` varchar(5) COLLATE utf8_bin DEFAULT NULL CHECK (ISNULL(code)=ISNULL(class)),
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '= (`class` is null)' at line 1
MariaDB [fusion]>



 Comments   
Comment by Alice Sherepa [ 2020-11-20 ]

Thanks! MDEV-24194 was also reported for the same bug.
If you'd like to follow the progress, please, watch MDEV-24194

Generated at Thu Feb 08 09:28:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.