Bug #9414 Warnings not properly reported if the query is in the cache
Submitted: 26 Mar 2005 17:51 Modified: 10 Jul 2005 16:02
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.10a OS:Linux (Linux)
Assigned to: Oleksandr Byelkin

[26 Mar 2005 17:51] jocelyn fournier
Description:
Hi,

When executing a query which is cached by the query cache, SHOW WARNINGS; doesn't display properly the warnings.

Regards,
  Jocelyn

How to repeat:
CREATE TABLE t1 (
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  KEY `date` (`date`)
) ENGINE=MyISAM;

INSERT INTO t1 VALUES ('20050326');
INSERT INTO t1 VALUES ('20050325');

SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 0:0:0';
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set, 2 warnings (0.00 sec)

SHOW WARNINGS;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: '20050327 0:0:0' |
| Warning | 1292 | Truncated incorrect datetime value: '20050327 0:0:0' |
+---------+------+------------------------------------------------------+
2 rows in set (0.00 sec)

SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050328 0:0:0';
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set, 2 warnings (0.00 sec)

SHOW WARNINGS;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: '20050328 0:0:0' |
| Warning | 1292 | Truncated incorrect datetime value: '20050328 0:0:0' |
+---------+------+------------------------------------------------------+
2 rows in set (0.00 sec)

SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 0:0:0';
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set, 2 warnings (0.00 sec)

SHOW WARNINGS;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: '20050328 0:0:0' |
| Warning | 1292 | Truncated incorrect datetime value: '20050328 0:0:0' |
+---------+------+------------------------------------------------------+
2 rows in set (0.00 sec)

Wrong result here, should be '20050327 0:0:0'

RESET QUERY CACHE;
Query OK, 0 rows affected (0.04 sec)

SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 0:0:0';
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set, 2 warnings (0.00 sec)

SHOW WARNINGS;
+---------+------+------------------------------------------------------+
| Level   | Code | Message                                              |
+---------+------+------------------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: '20050327 0:0:0' |
| Warning | 1292 | Truncated incorrect datetime value: '20050327 0:0:0' |
+---------+------+------------------------------------------------------+
2 rows in set (0.00 sec)

BTW, why is this warning displayed two times ?
[26 Mar 2005 21:26] Jorge del Conde
Thanks for your bug report.  I verified this bug with 4.1.11 from bk
[27 Jun 2005 21:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26461
[27 Jun 2005 21:55] jocelyn fournier
Hi Sanja,

About this bug, do you know why the warning is displayed two times ?

Thanks,
  Jocelyn
[28 Jun 2005 6:47] Oleksandr Byelkin
It displayed old warning just because QC do not store/restore them and it show warnings from last really executed query.

After some thinking we decide that query with warnings is (or should) something unusual in production. Also it is possible to have warnings depends on some external to query causes, so when causes will have gone warning will be not actual any more so it is better do not store warnings in QC but just do not cache such queries.
[28 Jun 2005 6:54] jocelyn fournier
I agree with this. But what is strange (although not really a problem for me) is that with see two times the same warning displayed when using SHOW WARNING;
[28 Jun 2005 12:48] Oleksandr Byelkin
there are two rows in t1, so where will be evaluated for both of them, during each evaluation we got the warning, so we have two warnings.
[28 Jun 2005 13:04] jocelyn fournier
Ok, but in this case, if I add a new row in the table, why are there still 2 warnings and not 3 displayed ? ;)
[28 Jun 2005 14:03] Oleksandr Byelkin
Ah, yes, you are right.

First message issued during preparation WHERE condition (evaluation constant part).
Second issued during calculation approximation of lines which fit to condition by index you create.
[4 Jul 2005 6:43] Oleksandr Byelkin
pushed to 4.1.13
[10 Jul 2005 16:02] Jon Stephens
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented in 4.1.13 change history; closed.