[MDEV-17654] Incorrect syntax returned for column with CHECK constraint in the "SHOW CREATE TABLE ..." result Created: 2018-11-09  Updated: 2023-05-05  Resolved: 2019-05-02

Status: Closed
Project: MariaDB Server
Component/s: Admin statements, Parser
Affects Version/s: 10.2.17, 10.3.9
Fix Version/s: 10.2.24, 10.3.15, 10.4.5

Type: Bug Priority: Blocker
Reporter: Sibin Assignee: Anel Husakovic
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-19163 When you have a COMMENT and a CHECK o... Closed
is duplicated by MDEV-18473 DB restore fails when CHECK and COMME... Closed
is duplicated by MDEV-31191 mysqldump bad format when using CHECK... Closed

 Description   

Consider the below example,

1)Create the below table,

CREATE TABLE `t1` (`name` VARCHAR(30) CHECK (CHAR_LENGTH(`name`)>2), `start_date` DATE, `end_date` DATE COMMENT 'DATE COLUMN' CHECK (`start_date` is NULL OR `end_date` is NULL OR `start_date`<`end_date`));

2) Execute "SHOW CREATE TABLE `t1`"

3) Copy the result returned by the query executed in Step-2.

4) Execute the copied query in Step-3 after renaming the table name

5) The query execution will fail due to incorrect syntax. The issue is "COMMENT" column_definition comes after the 'CHECK' constraint_definition. Refer the MariaDB create table syntax >> https://mariadb.com/kb/en/library/create-table/

 Comments   
Comment by Elena Stepanova [ 2018-11-09 ]

Thanks for the report. Reproducible easily as described.

While I thought we had another one about it, I cannot find it in JIRA, so let's use this one as primary. If somebody ever stumbles upon a similar existing report, please link them.

Comment by Elena Stepanova [ 2019-02-04 ]

I"m raising the priority because there is another external report, MDEV-18473; and in general, it might well disrupt mysqldump-based backup/restoration.

Comment by Jonathan M. Wilbur [ 2019-02-20 ]

I don't know if you guys are aware, but there is a pull request out that fixes this already. It's just waiting to be merged. https://github.com/MariaDB/server/pull/924

Comment by Sergei Golubchik [ 2019-04-05 ]

jonathanwilbur1993, yes, jira links to it automatically (see the right side of the page).

It will be in the next 10.2 release.

Comment by Sergei Golubchik [ 2019-04-07 ]

overdue PR

Comment by Sergey Vojtovich [ 2019-04-30 ]

serg, could you review https://github.com/MariaDB/server/pull/924? I wonder if our parser has good reasons to require this order. Also are there other attributes that need to be properly ordered wrt CHECK.

Comment by Sergei Golubchik [ 2019-04-30 ]

That's what the standard says. The order is column type [default] [constraint] [collate]. We still have collate on the wrong place, but it'd break lots of applications if we move it.

Generated at Thu Feb 08 08:38:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.