Bug #48769 inconsistent warnings/errors depending on index usage and engine
Submitted: 13 Nov 2009 22:46 Modified: 14 Nov 2009 9:06
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.88, 5.1.40 OS:Any
Assigned to: CPU Architecture:Any

[13 Nov 2009 22:46] Shane Bester
Description:
there are some inconsistent results with the testcase.

1) warnings (aka errors in strict mode) received when using an index, but not without using an index.

2) innodb tables cause "Incorrect decimal value: '' for column '' at row -1"
   myisam tables cause "Incorrect decimal value: '' for column 'b' at row 0"

so, depending on whether optimizer chooses to use an index, the resulting tables may or may not get created! for purpose of demonstration, i used force/index index hints to show the problem.

mysql>
mysql> create temporary table tmp1 select 1 from t1 natural join t2 force  index(idx) where t2.a <> 1;
ERROR 1366 (HY000): Incorrect decimal value: '' for column '' at row -1
mysql> create temporary table tmp2 select 1 from t1 natural join t2 ignore index(idx) where t2.a <> 1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> alter table t1 engine=myisam;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table t2 engine=myisam;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql>
mysql> create temporary table tmp3 select 1 from t1 natural join t2 force  index(idx) where t2.a <> 1;
ERROR 1366 (HY000): Incorrect decimal value: '' for column 'b' at row 0
mysql> create temporary table tmp4 select 1 from t1 natural join t2 ignore index(idx) where t2.a <> 1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

How to repeat:
set sql_mode='strict_all_tables';
drop temporary table if exists tmp1,tmp2,tmp3,tmp4;
drop table if exists `t1`;

create table `t1` (`b` char(1)) engine=innodb;
insert into `t1` values ('');
drop table if exists `t2`;
create table `t2` (`a` int,`b` decimal(10,0), key `idx` (`b`)) engine=innodb;
insert into `t2` values (1,1),(1,1);

create temporary table tmp1 select 1 from t1 natural join t2 force  index(idx) where t2.a <> 1;
create temporary table tmp2 select 1 from t1 natural join t2 ignore index(idx) where t2.a <> 1;

alter table t1 engine=myisam;
alter table t2 engine=myisam;

create temporary table tmp3 select 1 from t1 natural join t2 force  index(idx) where t2.a <> 1;
create temporary table tmp4 select 1 from t1 natural join t2 ignore index(idx) where t2.a <> 1;

Suggested fix:
i expected creation of tables tmp1,tmp2,tmp3,tmp4 to all fail with identical errors.
[14 Nov 2009 9:06] Valeriy Kravchuk
Verified just as described also with recent 5.0.88 on Mac OS X:

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.88-debug | 
+--------------+
1 row in set (0.00 sec)

mysql> set sql_mode='strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> drop temporary table if exists tmp1,tmp2,tmp3,tmp4;
Query OK, 0 rows affected, 4 warnings (0.00 sec)

mysql> drop table if exists `t1`;
Query OK, 0 rows affected (0.08 sec)

mysql> create table `t1` (`b` char(1)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into `t1` values ('');
Query OK, 1 row affected (0.00 sec)

mysql> drop table if exists `t2`;
Query OK, 0 rows affected (0.13 sec)

mysql> create table `t2` (`a` int,`b` decimal(10,0), key `idx` (`b`)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into `t2` values (1,1),(1,1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create temporary table tmp1 select 1 from t1 natural join t2 force  index(idx) where t2.a
    -> <> 1;
ERROR 1366 (HY000): Incorrect decimal value: '' for column '' at row -1
mysql> create temporary table tmp2 select 1 from t1 natural join t2 ignore index(idx) where t2.a
    -> <> 1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t1 engine=myisam;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table t2 engine=myisam;
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> create temporary table tmp3 select 1 from t1 natural join t2 force  index(idx) where t2.a
    -> <> 1;
ERROR 1366 (HY000): Incorrect decimal value: '' for column 'b' at row 0
mysql> create temporary table tmp4 select 1 from t1 natural join t2 ignore index(idx) where t2.a
    -> <> 1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
[23 Sep 2010 11:12] MySQL Verification Team
related: bug #56962
[13 Jan 2011 20:36] MySQL Verification Team
related: bug #59482