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: | |
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
[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