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

    XMLWordPrintable

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

          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.