MySQL Bugs: #51523: inconsistent reporting of warnings on re-execution of a query against csv tables

Bug #51523	inconsistent reporting of warnings on re-execution of a query against csv tables
Submitted:	25 Feb 2010 18:31	Modified:	9 Aug 2013 8:19
Reporter:	Shane Bester (Platinum Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.45, 5.5.99-m3	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
warning generation seems to be affected by whether or not the table is in the table cache or not.
some queries generate warnings on first execution but not subsequent executions:
testcase outputs:

mysql> select sql_no_cache 1 from t2
    -> join  t1 on 1=1 where t1.a <> '*'
    -> and t2.a >= t1.a and not t2.a!='1';
Empty set (0.05 sec)

mysql> show warnings; #no warnings!
Empty set (0.01 sec)

mysql> select sql_no_cache 1 from t2
    -> join  t1 on 1=1 where t1.a <> '*'
    -> and t2.a >= t1.a and not t2.a!='1';
Empty set, 1 warning (0.03 sec)  <--------

How to repeat:
drop table if exists t1,t2;
create table t1(a int not null)engine=csv;
insert into t1 values (1),(2);
create table t2(a int not null)engine=csv;
insert into t2 values (-20758),(23578),(20479),(-31073);
     
set sql_mode='';
flush tables;

select sql_no_cache 1 from t2
join  t1 on 1=1 where t1.a <> '*' 
and t2.a >= t1.a and not t2.a!='1';
show warnings; #no warnings!

select sql_no_cache 1 from t2
join  t1 on 1=1 where t1.a <> '*' 
and t2.a >= t1.a and not t2.a!='1';
show warnings; #1 warning!

I do not see thius problem with recent 5.1.45 from bzr:

openxs@suse:/home2/openxs/dbs/5.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.45-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

mysql> create table t1(a int not null)engine=csv;
Query OK, 0 rows affected (0.03 sec)

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

mysql> create table t2(a int not null)engine=csv;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t2 values (-20758),(23578),(20479),(-31073);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

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

mysql> flush tables;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> select sql_no_cache 1 from t2
    -> join  t1 on 1=1 where t1.a <> '*'
    -> and t2.a >= t1.a and not t2.a!='1';
Empty set (0.00 sec)

mysql> show warnings; #no warnings!
Empty set (0.00 sec)

mysql>
mysql> select sql_no_cache 1 from t2
    -> join  t1 on 1=1 where t1.a <> '*'
    -> and t2.a >= t1.a and not t2.a!='1';
Empty set (0.00 sec)

What am I doing wrong?

good question. can you shutdown server completely then start it up cleanly. run mysql client with -A option to skip loading tables into cache?

Verified just as described in the last comment:

openxs@ubuntu:/home2/openxs/dbs/next-mr$ bin/mysql --no-defaults -A -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.99-m3-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists t1,t2;
Query OK, 0 rows affected, 2 warnings (0.18 sec)

mysql> create table t1(a int not null)engine=csv;
Query OK, 0 rows affected (0.29 sec)

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

mysql> create table t2(a int not null)engine=csv;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t2 values (-20758),(23578),(20479),(-31073);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

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

mysql> flush tables;
Query OK, 0 rows affected (0.14 sec)

mysql> 
mysql> select sql_no_cache 1 from t2
    -> join  t1 on 1=1 where t1.a <> '*' 
    -> and t2.a >= t1.a and not t2.a!='1';
Empty set (0.63 sec)

mysql> select sql_no_cache 1 from t2
    -> join  t1 on 1=1 where t1.a <> '*' 
    -> and t2.a >= t1.a and not t2.a!='1';
Empty set, 1 warning (0.05 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1292
Message: Truncated incorrect DOUBLE value: '*'
1 row in set (0.04 sec)

[7 Aug 2013 1:40] Neeraj X Bisht

This is not a bug.

When we run the query first time.Its explain shows

mysql> explain select 1 from t2 join  t1 on 1=1 where t1.a <> '*'
and t2.a >= t1.a and not t2.a!='1'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where; Using join buffer (Block Nested Loop)

which means, first time it is checking for the condition on t2.

When we run the same query second time. The explain is

mysql> explain select 1 from t2 join  t1 on 1=1 where t1.a <> '*' and t2.a >=
t1.a and not t2.a!='1'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set (0.00 sec)

result in checking t1 condition second time, which cause warning.

-------------------------------------------------------

As there is change in the plan because of the statistics change, which cause
warning
when we execute second time.

We can get the same warning with any other table(belong to some other
engine).
if we choose the same join order used in second execution.
create table t1(a int not null)engine=myisam;
insert into t1 values (1),(2);
create table t2(a int not null)engine=myisam;
insert into t2 values (-20758),(23578),(20479),(-31073);

mysql> select 1 from t1 straight_join t2 on 1=1 where t1.a <> '*' and t2.a >=
t1.a and not t2.a!='1';
Empty set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+---------------------------------------+
| Level   | Code | Message                               |
+---------+------+---------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '*' |
+---------+------+---------------------------------------+
1 row in set (0.00 sec)

where as
mysql>  select 1 from t2 straight_join t1 on 1=1 where t1.a <> '*' and t2.a
>= t1.a and not t2.a!='1';
Empty set (0.00 sec)

So we think this is not a bug, as there is just a plan change because of the
peculiarity of CSV tables.