Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17654

Incorrect syntax returned for column with CHECK constraint in the "SHOW CREATE TABLE ..." result

Details

    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/

      Attachments

        Issue Links

          Activity

            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.

            elenst Elena Stepanova added a comment - 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.

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

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

            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

            jonathanwilbur1993 Jonathan M. Wilbur added a comment - 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

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

            It will be in the next 10.2 release.

            serg Sergei Golubchik added a comment - jonathanwilbur1993 , yes, jira links to it automatically (see the right side of the page). It will be in the next 10.2 release.

            overdue PR

            serg Sergei Golubchik added a comment - overdue PR

            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.

            svoj Sergey Vojtovich added a comment - 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 .

            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.

            serg Sergei Golubchik added a comment - 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.

            People

              anel Anel Husakovic
              Sibin Sibin
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.