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

Bundling insert with create statement for table with unsigned Decimal primary key issues warning 1194

Details

    • 5.5.51 & 10.2.2, 5.5.54

    Description

      Creating a temporary table with an unsigned decimal as it's primary key (or part of a primary key) in the same statement that populates the table, incorrectly returns an 1194 warning. The same code, executed as two separate statements issues no such warning.

      # Works
      drop temporary table if exists bugExample;
      create temporary table bugExample (
          ID decimal(2,1) unsigned NOT NULL DEFAULT '0.0'
          ,   PRIMARY KEY (ID)
      ) engine=memory;
      insert into bugExample
      select  2.1 ID;
      

      #Works - but returns a warning - 1194 Found wrong key definition in bugExample; Please do "ALTER TABLE 'bugExample' FORCE" to fix it!
      drop temporary table if exists bugExample;
      create temporary table bugExample (
          ID decimal(2,1) unsigned NOT NULL DEFAULT '0.0'
          ,   PRIMARY KEY (ID)
      ) engine=memory
      select  2.1 ID;
      

      #Works - Warning eliminated By dropping unsigned from definition
      drop temporary table if exists bugExample;
      create temporary table bugExample (
          ID decimal(2,1) NOT NULL DEFAULT '0.0'
          ,   PRIMARY KEY (ID)
      ) engine=memory
      select  2.1 ID;
      

      #Works - Warning eliminated By changing data type to double
      drop temporary table if exists bugExample;
      create temporary table bugExample (
          ID double(2,1) unsigned NOT NULL DEFAULT '0.0'
          ,   PRIMARY KEY (ID)
      ) engine=memory
      select  2.1 ID;
      

      #Fails - appends a column to the table definition
      drop temporary table if exists bugExample;
      create temporary table bugExample (
          ID decimal(2,1)  UNSIGNED NOT NULL DEFAULT '0.0'
          ,   PRIMARY KEY (ID)
      ) engine=memory
      select  2.1 MYVAL;
      

      Attachments

        Activity

          elenst Elena Stepanova added a comment - - edited

          The last one does not fail, it works as specified (see MySQL manual and MariaDB KB).

          Warnings in the second case look like a bug indeed.

          elenst Elena Stepanova added a comment - - edited The last one does not fail, it works as specified (see MySQL manual and MariaDB KB ). Warnings in the second case look like a bug indeed.

          Temporary table and MEMORY are not important, reproducible with a regular table and other engines as well.

          MariaDB [test]> create table bugExample (
              ->     ID decimal(2,1) unsigned NOT NULL DEFAULT '0.0'
              ->     ,   PRIMARY KEY (ID)
              -> ) engine=InnoDB
              -> select  2.1 ID;
          Query OK, 1 row affected, 2 warnings (0.21 sec)
          Records: 1  Duplicates: 0  Warnings: 2
           
          MariaDB [test]> show warnings;
          +---------+------+-------------------------------------------------------------------------------------------------+
          | Level   | Code | Message                                                                                         |
          +---------+------+-------------------------------------------------------------------------------------------------+
          | Warning | 1194 | Found wrong key definition in bugExample; Please do "ALTER TABLE 'bugExample' FORCE" to fix it! |
          | Warning | 1194 | Found wrong key definition in bugExample; Please do "ALTER TABLE 'bugExample' FORCE" to fix it! |
          +---------+------+-------------------------------------------------------------------------------------------------+
          2 rows in set (0.00 sec)
          

          Reproducible with MariaDB 5.5-10.2, probably on earlier versions as well.
          Reproducible with MySQL 5.6.
          Not reproducible with MySQL 5.7.

          Assigned to holyfoot because he fixed a similar issue only 9 years ago:
          https://bugs.mysql.com/bug.php?id=25426
          Please reassign if needed.

          elenst Elena Stepanova added a comment - Temporary table and MEMORY are not important, reproducible with a regular table and other engines as well. MariaDB [test]> create table bugExample ( -> ID decimal (2,1) unsigned NOT NULL DEFAULT '0.0' -> , PRIMARY KEY (ID) -> ) engine=InnoDB -> select 2.1 ID; Query OK, 1 row affected, 2 warnings (0.21 sec) Records: 1 Duplicates: 0 Warnings: 2   MariaDB [test]> show warnings; + ---------+------+-------------------------------------------------------------------------------------------------+ | Level | Code | Message | + ---------+------+-------------------------------------------------------------------------------------------------+ | Warning | 1194 | Found wrong key definition in bugExample; Please do "ALTER TABLE 'bugExample' FORCE" to fix it! | | Warning | 1194 | Found wrong key definition in bugExample; Please do "ALTER TABLE 'bugExample' FORCE" to fix it! | + ---------+------+-------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) Reproducible with MariaDB 5.5-10.2, probably on earlier versions as well. Reproducible with MySQL 5.6. Not reproducible with MySQL 5.7. Assigned to holyfoot because he fixed a similar issue only 9 years ago: https://bugs.mysql.com/bug.php?id=25426 Please reassign if needed.
          holyfoot Alexey Botchkov added a comment - Proposed fix: http://lists.askmonty.org/pipermail/commits/2016-December/010300.html

          People

            holyfoot Alexey Botchkov
            MarkP Mark Punak
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.