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

    • Sprint:
      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

            People

            • Assignee:
              holyfoot Alexey Botchkov
              Reporter:
              MarkP Mark Punak
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: