Bug #42014 Crash, name_const with collate
Submitted: 10 Jan 2009 14:46 Modified: 16 Feb 2009 2:15
Reporter: Adam Erickson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S1 (Critical)
Version:5.0.72, 5.0.76, 5.1.29, 5.1.30, 5.1.31, 6.0.10 OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: collate, crash, name_const, regression

[10 Jan 2009 14:46] Adam Erickson
Description:
Comparisons of date columns using name_const with collate to set 'binary' character sets causes a server crash.

The origin of the problem comes from a stored procedure we're using.  The full binary log entry is below.  Some of the session variables look odd but they're not necessary to produce a crash.

SET TIMESTAMP=1231593724;
SET @@session.foreign_key_checks=1,
    @@session.sql_auto_is_null=1,
    @@session.unique_checks=1;
SET @@session.sql_mode=2097152;
/*!\C latin1 */;
SET @@session.character_set_client=8,
    @@session.collation_connection=8,
    @@session.collation_server=8;

delete from batch_job_history
 where run_date =  NAME_CONST('reportDate',_binary'2009-01-09' COLLATE 'binary')
   and run_period = 'BATCH';

How to repeat:
mysql> create table foo ( a date );
Query OK, 0 rows affected (0.00 sec)

mysql> select * from foo where a = NAME_CONST('reportDate',_binary'2009-01-09' COLLATE 'binary');
ERROR 2013 (HY000): Lost connection to MySQL server during query

Suggested fix:
Not crash, compare properly or do not write the comparison to the binary log like this.
[10 Jan 2009 16:43] MySQL Verification Team
thanks for the bug report!

mysqld.exe!Arg_comparator::can_compare_as_dates()[item_cmpfunc.cc:760]
mysqld.exe!Arg_comparator::set_cmp_func()[item_cmpfunc.cc:853]
mysqld.exe!Item_bool_func2::set_cmp_func()[item_cmpfunc.h:337]
mysqld.exe!Item_bool_func2::fix_length_and_dec()[item_cmpfunc.cc:525]
mysqld.exe!Item_func::fix_fields()[item_func.cc:199]
mysqld.exe!setup_conds()[sql_base.cc:7923]
mysqld.exe!setup_without_group()[sql_select.cc:410]
mysqld.exe!JOIN::prepare()[sql_select.cc:492]
mysqld.exe!mysql_select()[sql_select.cc:2341]
mysqld.exe!handle_select()[sql_select.cc:269]
mysqld.exe!execute_sqlcom_select()[sql_parse.cc:4889]
mysqld.exe!mysql_execute_command()[sql_parse.cc:2184]
mysqld.exe!mysql_parse()[sql_parse.cc:5791]
mysqld.exe!dispatch_command()[sql_parse.cc:1202]
mysqld.exe!do_command()[sql_parse.cc:857]
mysqld.exe!handle_one_connection()[sql_connect.cc:1115]
mysqld.exe!pthread_start()[my_winthread.c:85]
mysqld.exe!_callthreadstart()[thread.c:295]
mysqld.exe!_threadstart()[thread.c:275]
kernel32.dll!BaseThreadStart()
[10 Jan 2009 16:49] MySQL Verification Team
bug was introduced in 5.0.72 and 5.1.29, so this is a regression.  Perhaps fix for bug #39182 made some problems?
[20 Jan 2009 14:19] 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/commits/63611

2734 Ramil Kalimullin	2009-01-20
      Fix for bug#42014: Crash, name_const with collate
      
      Problem: some queries using NAME_CONST(.. COLLATE ...)
      lead to server crash due to failed type cast.
      
      Fix: return the underlying item's type in case of
      NAME_CONST(.. COLLATE ...) to avoid wrong casting.
[2 Feb 2009 20:43] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=42553 was marked as duplicate of this one.
[4 Feb 2009 18:18] 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/commits/65223

2737 Ramil Kalimullin	2009-02-04
      Fix for bug#42014: Crash, name_const with collate
      
      Problem: some queries using NAME_CONST(.. COLLATE ...)
      lead to server crash due to failed type cast.
      
      Fix: return the underlying item's type in case of
      NAME_CONST(.. COLLATE ...) to avoid wrong casting.
[5 Feb 2009 7:44] 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/commits/65288

2738 Ramil Kalimullin	2009-02-05
      Fix for bug#42014: Crash, name_const with collate
      
      Problem: some queries using NAME_CONST(.. COLLATE ...)
      lead to server crash due to failed type cast.
      
      Fix: return the underlying item's type in case of
      NAME_CONST(.. COLLATE ...) to avoid wrong casting.
[5 Feb 2009 7:51] Ramil Kalimullin
Pushed to 5.0-bugteam, 5.1-bugteam and 6.0-bugteam.
[6 Feb 2009 13:42] Bugs System
Pushed into 5.0.78 (revid:joro@sun.com-20090206133929-ybpbkkox9wkptopb) (version source revid:ramil@mysql.com-20090205074339-honmhibgyygyzym7) (merge vers: 5.0.78) (pib:6)
[9 Feb 2009 22:33] Bugs System
Pushed into 5.1.32 (revid:davi.arnaut@sun.com-20090209214102-gj3sb3ujpnvpiy4c) (version source revid:davi.arnaut@sun.com-20090209214102-gj3sb3ujpnvpiy4c) (merge vers: 5.1.32) (pib:6)
[14 Feb 2009 13:00] Bugs System
Pushed into 6.0.10-alpha (revid:matthias.leich@sun.com-20090212211028-y72faag15q3z3szy) (version source revid:holyfoot@mysql.com-20090205070736-2zg3ou71pmp35q62) (merge vers: 6.0.10-alpha) (pib:6)
[16 Feb 2009 2:15] Paul DuBois
Noted in 5.0.78, 5.1.32, 6.0.10 changelogs.

Some queries using NAME_CONST(.. COLLATE ...) led to a server crash
due to a failed type cast.
[17 Feb 2009 15:00] Bugs System
Pushed into 5.1.32-ndb-6.3.23 (revid:tomas.ulin@sun.com-20090217131017-6u8qz1edkjfiobef) (version source revid:tomas.ulin@sun.com-20090216083408-rmvyaxjt6mk8sg1y) (merge vers: 5.1.32-ndb-6.3.23) (pib:6)
[17 Feb 2009 16:47] Bugs System
Pushed into 5.1.32-ndb-6.4.3 (revid:tomas.ulin@sun.com-20090217134419-5ha6xg4dpedrbmau) (version source revid:tomas.ulin@sun.com-20090216083646-m8st11oj1hhfuuh5) (merge vers: 5.1.32-ndb-6.4.3) (pib:6)
[17 Feb 2009 18:23] Bugs System
Pushed into 5.1.32-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090217134216-5699eq74ws4oxa0j) (version source revid:tomas.ulin@sun.com-20090211111208-wf0acl7c1vl5653e) (merge vers: 5.1.32-ndb-6.2.17) (pib:6)
[27 Feb 2009 7:07] Valeriy Kravchuk
Bug #43245 was marked as a duplicate of this one.
[27 Feb 2009 15:57] Valeriy Kravchuk
Bug #43261 was marked as a duplicate of this one.
[14 Apr 2009 12:02] MySQL Verification Team
Bug: http://bugs.mysql.com/bug.php?id=44251 marked as duplicate of this one.
[9 Jun 2009 19:04] Paul DuBois
Noted in 5.0.74sp1 changelog.
[16 Jun 2009 7:35] Valeriy Kravchuk
Bug #45448 was marked as a duplicate of this one.
[15 Jul 2009 5:25] MySQL Verification Team
bug #46187 was marked as a duplicate of this one.
[14 Jun 2023 8:17] Nikhil Ninawe
This is still happening 5.7.38-log

2023-06-14T06:15:11.305153Z 6462899 [ERROR] Slave SQL for channel '': Error 'Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation '='' on query. Default database: 'turvo'. Query: 'UPDATE
        sequence_data
      SET
        sequence_cur_value =  NAME_CONST('range_end',14578)
      WHERE
        sequence_name =  NAME_CONST('seq_name',_latin1'sequence_shipment_88' COLLATE 'latin1_swedish_ci')', Error_code: 1267