MySQL Bugs: #20291: SELECT with no tables but a derived table resets warning list, contrary to docs

Bug #20291	SELECT with no tables but a derived table resets warning list, contrary to docs
Submitted:	6 Jun 2006 12:37	Modified:	12 Nov 2013 8:36
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.0.44, 5.0.23-BK, 4.1.20 and 5.0.22	OS:	Any (*)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
queries that do not involve a table (select from DUAL) are not issuing warnings on there own. Also they do not reset the warning of previous queries.

selecting from a subquery, even if not involving tables, behaves fine.

see how to repeat

create table ti (a smallint);
 insert into ti select 2200000; show warnings;
 select 1;  show warnings;
 insert into ti select 2200000; show warnings;
 select 1 FROM DUAL;  show warnings;
 insert into ti select 2200000; show warnings;
 select 1 from ti;  show warnings;
 insert into ti select 2200000; show warnings;
 select 1 from (select 1)x;  show warnings;
 drop table ti;

On MYSQL 4.1 only
 select cast(789999999999999999999999999999 as unsigned) x; show warnings;
 select cast(789999999999999999999999999999 as unsigned) from (select 1) x; show warnings;

How to repeat:
# mysql 5.0 and 4.1
create table ti (a smallint);
Query OK, 0 rows affected (0.01 sec)

### cause a warning on a table
mysql>  insert into ti select 2200000; show warnings;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'a' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

### new select, old warning
mysql>  select 1;  show warnings;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'a' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.01 sec)

### cause a warning on a table
mysql>  insert into ti select 2200000; show warnings;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'a' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

### new select, explicit from dual, old warning
mysql>  select 1 FROM DUAL;  show warnings;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'a' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.01 sec)

### cause a warning on a table
mysql>  insert into ti select 2200000; show warnings;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'a' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

### select from table, does not show the old warning
mysql>  select 1 from ti;  show warnings;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
+---+
3 rows in set (0.00 sec)

Empty set (0.01 sec)

### cause a warning on a table

mysql>  insert into ti select 2200000; show warnings;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'a' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

### select with no tables, but sub-query in from
### no warning
mysql>  select 1 from (select 1)x;  show warnings;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Empty set (0.00 sec)

mysql>  drop table ti;
Query OK, 0 rows affected (0.01 sec)

###on Mysql 4.1 only
### first query does not give a warning

 select cast(789999999999999999999999999999 as unsigned) x; show warnings;
+---------------------+
| x                   |
+---------------------+
| 9223372036854775807 |
+---------------------+
1 row in set (0.01 sec)

Empty set (0.00 sec)

mysql>  select cast(789999999999999999999999999999 as unsigned) from (select 1) x; show warnings;
+--------------------------------------------------+
| cast(789999999999999999999999999999 as unsigned) |
+--------------------------------------------------+
|                              9223372036854775807 |
+--------------------------------------------------+

## this works on 5.0
## but The value is given as empty in the warning

mysql> select cast(789999999999999999999999999999 as unsigned) x; show warnings;
+--------------+
| x            |
+--------------+
| 789999999999 |
+--------------+
1 row in set, 1 warning (0.00 sec)

+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1292 | Truncated incorrect DECIMAL value: '' |
+-------+------+---------------------------------------+
1 row in set (0.00 sec)

mysql>  select cast(789999999999999999999999999999 as unsigned) from (select 1) x; show warnings;
+--------------------------------------------------+
| cast(789999999999999999999999999999 as unsigned) |
+--------------------------------------------------+
|                                     789999999999 |
+--------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

+-------+------+---------------------------------------+
| Level | Code | Message                               |
+-------+------+---------------------------------------+
| Error | 1292 | Truncated incorrect DECIMAL value: '' |
+-------+------+---------------------------------------+
1 row in set (0.01 sec)

Suggested fix:
-

Thank you for a problem report. Verified just as described with 5.0.23-BK () on Linux:

openxs@suse:~/dbs/5.0> 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 to server version: 5.0.23

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table ti (a smallint);
Query OK, 0 rows affected (0.01 sec)

mysql>  insert into ti select 2200000; show warnings;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'a' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select 1;  show warnings;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'a' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql>  insert into ti select 2200000; show warnings;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'a' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select 1 FROM DUAL;  show warnings;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'a' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql>  insert into ti select 2200000; show warnings;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'a' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql>  select 1 from ti;  show warnings;
+---+
| 1 |
+---+
| 1 |
| 1 |
| 1 |
+---+
3 rows in set (0.00 sec)

Empty set (0.00 sec)

mysql>  insert into ti select 2200000; show warnings;
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 1

+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1264 | Out of range value adjusted for column 'a' at row 1 |
+---------+------+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select 1 from (select 1) x;  show warnings;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

Empty set (0.00 sec)

Although, according to the manual (http://dev.mysql.com/doc/refman/5.0/en/show-warnings.html):

"SHOW WARNINGS shows the error, warning, and note messages that resulted from the last statement that generated messages, or nothing _if the last statement that used a table generated no messages_."

So, this can be called not a bug. But inconsistent behaviour with "FROM (select 1) t" and "FROM DUAL" should be at least explicitely explained. So, it is at least a documentation request.

Would like to seen an explicit note stating that SELECTs that do not reference any tables may not reset warnings.

Selecting from a derived table is not the same as a SELECT that references no table, apparently, so those cases are not covered by this caveat.

Kolbe, the section already says: "The list of messages is reset for each new statement that uses a table."

Same results in 5.0.44.

Taking over, since Davi is already looking at the problem in context of Bug#23032

This was fixed in MySQL 5.7.2.