[MDEV-10274] Bundling insert with create statement for table with unsigned Decimal primary key issues warning 1194 Created: 2016-06-22  Updated: 2016-12-19  Resolved: 2016-12-19

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Temporary
Affects Version/s: 10.0.25, 5.5, 10.0, 10.1
Fix Version/s: 5.5.54

Type: Bug Priority: Major
Reporter: Mark Punak Assignee: Alexey Botchkov
Resolution: Fixed Votes: 0
Labels: upstream-fixed
Environment:

Centos 7.1

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;



 Comments   
Comment by Elena Stepanova [ 2016-06-22 ]

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.

Comment by Elena Stepanova [ 2016-06-22 ]

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.

Comment by Alexey Botchkov [ 2016-12-19 ]

Proposed fix:
http://lists.askmonty.org/pipermail/commits/2016-December/010300.html

Generated at Thu Feb 08 07:40:58 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.