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

    XMLWordPrintable

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

          duplicates

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19163 When you have a COMMENT and a CHECK on a column, the SHOW CREATE TABLE creates an invalid query

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-18473 DB restore fails when CHECK and COMMENT are used

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31191 mysqldump bad format when using CHECK and COMMENT

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            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.