MySQL Bugs: #40747: Duplicate datetime truncation warning if a range optimizer is used.

Bug #40747	Duplicate datetime truncation warning if a range optimizer is used.
Submitted:	14 Nov 2008 15:03	Modified:	14 Nov 2008 15:41
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.70, 5.1.29, 6.0.7	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
If the range optimizer calls test_quick_select() for a datetime range, and one of the values in the range is incorrect, a duplicate truncation warning is reported:
one when we evaluate range optimizer applicability, and another when we execute the statement.
test_quick_select calls Item::save_in_field_no_warnings(), but it has no effect on  Field_datetime functionality.

Test case:

mysql> CREATE TABLE t1 (
    -> `date` datetime NOT NULL default '0000-00-00 00:00:00',
    -> KEY `date` (`date`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES ('20050326');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES ('20050325');
Query OK, 1 row affected (0.01 sec)

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

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------+
| Level   | Code | Message                                                                 |
+---------+------+-------------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 | 
| Warning | 1292 | Incorrect datetime value: '20050327 invalid' for column 'date' at row 1 | 
+---------+------+-------------------------------------------------------------------------+

Backtrace:
#0  push_warning (thd=0xa39a040, level=MYSQL_ERROR::WARN_LEVEL_WARN, 
    code=1292, 
    msg=0x9bc24a8c "Incorrect datetime value: '20050327 invalid' for column 'date' at row 1") at sql_error.cc:93
#1  0x0844f867 in make_truncated_value_warning (thd=0xa39a040, 
    level=MYSQL_ERROR::WARN_LEVEL_WARN, str_val=0xa429bc0 "20050327 invalid", 
    str_length=16, time_type=MYSQL_TIMESTAMP_DATETIME, 
    field_name=0xa4278c5 "date") at time.cc:763
#2  0x083026d6 in Field::set_datetime_warning (this=0xa4283b0, 
    level=MYSQL_ERROR::WARN_LEVEL_WARN, code=1264, 
    str=0xa429bc0 "20050327 invalid", str_length=16, 
    ts_type=MYSQL_TIMESTAMP_DATETIME, cuted_increment=1) at field.cc:10144
#3  0x0830f258 in Field_datetime::store (this=0xa4283b0, 
    from=0xa429bc0 "20050327 invalid", len=16, cs=0x8d48760) at field.cc:5856
#4  0x08270397 in Item::save_str_value_in_field (this=0xa429be8, 
    field=0xa4283b0, result=0xa429bf4) at item.cc:371
#5  0x082703ea in Item_string::save_in_field (this=0xa429be8, field=0xa4283b0, 
    no_conversions=true) at item.cc:5076
#6  0x082642da in Item::save_in_field_no_warnings (this=0xa429be8, 
    field=0xa4283b0, no_conversions=true) at item.cc:975
#7  0x0845f664 in get_mm_leaf (param=0x9bc257cc, conf_func=0xa429ca8, 
    field=0xa4283b0, key_part=0xa42bec0, type=Item_func::LE_FUNC, 
    value=0xa429be8) at opt_range.cc:5753
#8  0x0845ff9a in get_mm_parts (param=0x9bc257cc, cond_func=0xa429ca8, 
    field=0xa4283b0, type=Item_func::LE_FUNC, value=0xa429be8, 
    cmp_type=INT_RESULT) at opt_range.cc:5560
#9  0x08460310 in get_func_mm_tree (param=0x9bc257cc, cond_func=0xa429ca8, 
    field=0xa4283b0, value=0x0, cmp_type=INT_RESULT, inv=false)
    at opt_range.cc:5054
#10 0x084609f3 in get_full_func_mm_tree (param=0x9bc257cc, 
    cond_func=0xa429ca8, field_item=0xa429ac0, value=0x0, inv=false)
    at opt_range.cc:5338
#11 0x084611d5 in get_mm_tree (param=0x9bc257cc, cond=0xa429ca8)
    at opt_range.cc:5455
#12 0x08467a20 in SQL_SELECT::test_quick_select (this=0xa42bd98, 
    thd=0xa39a040, keys_to_use={map = 1}, prev_tables=0, 
    limit=18446744073709551615, force_quick_range=false, ordered_output=false)
    at opt_range.cc:2381
#13 0x083cb984 in get_quick_record_count (thd=0xa39a040, select=0xa42bd98, 
    table=0xa427b30, keys=0xa42b6b0, limit=18446744073709551615)
    at sql_select.cc:3782
#14 0x083d04ef in make_join_statistics (join=0xa429f30, tables=0x0, 
    conds=0xa429ca8, keyuse_array=0xa42b428) at sql_select.cc:4208
#15 0x083d3126 in JOIN::optimize (this=0xa429f30) at sql_select.cc:1573
#16 0x083d6d4f in mysql_select (thd=0xa39a040, rref_pointer_array=0xa39b6d8, 
    tables=0xa429660, wild_num=0, fields=@0xa39b654, conds=0xa429ca8, 
    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, 
---Type <return> to continue, or q <return> to quit---
    select_options=2147764736, result=0xa429f08, unit=0xa39b31c, 
    select_lex=0xa39b5bc) at sql_select.cc:3011
#17 0x083dc6a4 in handle_select (thd=0xa39a040, lex=0xa39b2c0, 
    result=0xa429f08, setup_tables_done_option=0) at sql_select.cc:301
#18 0x08348128 in execute_sqlcom_select (thd=0xa39a040, all_tables=0xa429660)
    at sql_parse.cc:4637
#19 0x0834922c in mysql_execute_command (thd=0xa39a040) at sql_parse.cc:2061
#20 0x08351ba1 in mysql_parse (thd=0xa39a040, 
    inBuf=0xa4294e0 "SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 invalid'", length=76, found_semicolon=0x9bc29bbc) at sql_parse.cc:5625
#21 0x08352c8c in dispatch_command (command=COM_QUERY, thd=0xa39a040, 
    packet=0xa418041 "SELECT COUNT(*) FROM t1 WHERE date BETWEEN '20050326' AND '20050327 invalid'", packet_length=76) at sql_parse.cc:1006
#22 0x083541cf in do_command (thd=0xa39a040) at sql_parse.cc:689
#23 0x08340cbf in handle_one_connection (arg=0xa39a040) at sql_connect.cc:1156
#24 0xb7f7b46b in start_thread () from /lib/tls/i686/cmov/libpthread.so.0
#25 0xb7d816de in clone () from /lib/tls/i686/cmov/libc.so.6

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 invalid';
show warnings;

Suggested fix:
Have a way to *really, irrevocably, unquestionably* suppress all warnings during execution in the server, not lame kludges with count_cuted_fields/etc.

Thank you for a problem report. Verified on all recent versions.

One way to achieve the goal of silencing all warnings is to install a silencer (Internal_error_handler) in save_in_field_no_warnings().
Another (starting from 6.0), is to replace thd->warning_info with a backup for the duration of save_in_field_no_warnings(), and then throw the "dirty" structure away and restore the original structure.