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

          Ascending order - Click to sort in descending order
          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.
          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.

          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

          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.