Bug #54568 create view cause Assertion failed: 0, file .\item_subselect.cc, line 836
Submitted: 16 Jun 2010 21:41 Modified: 24 Mar 2011 22:34
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S1 (Critical)
Version:5.0.91-debug, 5.1.47-debug OS:Any
Assigned to: Martin Hansson CPU Architecture:Any

[16 Jun 2010 21:41] Shane Bester
Description:
Similar to bug #52120 but that testcase doesn't crash anymore.

Version: '5.1.47-enterprise-gpl-advanced-debug'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Edition Debug (GPL)
Assertion failed: 0, file .\item_subselect.cc, line 836

mysqld-debug.exe!my_sigabrt_handler()[mysqld.cc:2049]
mysqld-debug.exe!raise()[winsig.c:590]
mysqld-debug.exe!abort()[abort.c:71]
mysqld-debug.exe!_wassert()[assert.c:212]
mysqld-debug.exe!Item_in_subselect::val_int()[item_subselect.cc:836]
mysqld-debug.exe!eval_const_cond()[item_func.cc:63]
mysqld-debug.exe!remove_eq_conds()[sql_select.cc:9307]
mysqld-debug.exe!optimize_cond()[sql_select.cc:9155]
mysqld-debug.exe!JOIN::optimize()[sql_select.cc:879]
mysqld-debug.exe!subselect_single_select_engine::exec()[item_subselect.cc:1908]
mysqld-debug.exe!Item_subselect::exec()[item_subselect.cc:280]
mysqld-debug.exe!Item_singlerow_subselect::val_str()[item_subselect.cc:604]
mysqld-debug.exe!Item_func_like::fix_fields()[item_cmpfunc.cc:4656]
mysqld-debug.exe!find_order_in_list()[sql_select.cc:14643]
mysqld-debug.exe!setup_group()[sql_select.cc:14720]
mysqld-debug.exe!setup_without_group()[sql_select.cc:444]
mysqld-debug.exe!JOIN::prepare()[sql_select.cc:521]
mysqld-debug.exe!st_select_lex_unit::prepare()[sql_union.cc:264]
mysqld-debug.exe!mysql_create_view()[sql_view.cc:549]
mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:4667]
mysqld-debug.exe!mysql_parse()[sql_parse.cc:5986]
mysqld-debug.exe!dispatch_command()[sql_parse.cc:1233]
mysqld-debug.exe!do_command()[sql_parse.cc:874]
mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1134]
mysqld-debug.exe!pthread_start()[my_winthread.c:85]
mysqld-debug.exe!_callthreadstart()[thread.c:293]
mysqld-debug.exe!_threadstart()[thread.c:277]

How to repeat:
#on debug build

drop table if exists `t1`;
create table `t1`(`a` int);
create or replace view `v1` as
select 1 from `t1` group by (1 like 
(select 1 from `t1`where `a`in(select 1)));
[16 Jun 2010 22:05] MySQL Verification Team
Thank you for the bug report.

[miguel@tikal ~]$ mysql -uroot test --socket=/tmp/mysql.sock 
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.0.92-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

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

mysql> create table `t1`(`a` int);
Query OK, 0 rows affected (0.04 sec)

mysql> create or replace view `v1` as
    -> select 1 from `t1` group by (1 like 
    -> (select 1 from `t1`where `a`in(select 1)));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[19 Jun 2010 5:20] MySQL Verification Team
There is no DoS vector here.  People don't run debug builds in production.
[30 Jun 2010 15:30] 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/112602

3455 Martin Hansson	2010-06-30
      Bug#54568: create view cause Assertion failed: 0, 
      file .\item_subselect.cc, line 836
      
      IN quantified predicates are never executed directly. They are rather wrapped
      inside nodes called IN Optimizers (Item_in_optimizer) which take care of the
      execution. However, this is not done during view preparation. Unfortunately
      the LIKE predicate pre-evaluates constant right-hand side arguments even
      during name resolution within view preparation. Likely this is meant as an
      optimization.
      
      Fixed by not pre-evaluating LIKE arguments in view prepare mode.
[2 Jul 2010 9:00] 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/112719

3456 Martin Hansson	2010-07-02
      Bug#54568: create view cause Assertion failed: 0, 
      file .\item_subselect.cc, line 836
      
      IN quantified predicates are never executed directly. They are rather wrapped
      inside nodes called IN Optimizers (Item_in_optimizer) which take care of the
      execution. However, this is not done during query preparation. Unfortunately
      the LIKE predicate pre-evaluates constant right-hand side arguments even
      during name resolution. Likely this is meant as an optimization.
      
      Fixed by not pre-evaluating LIKE arguments in view prepare mode.
[2 Jul 2010 12: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/112753

3456 Martin Hansson	2010-07-02
      Bug#54568: create view cause Assertion failed: 0, 
      file .\item_subselect.cc, line 836
      
      IN quantified predicates are never executed directly. They are rather wrapped
      inside nodes called IN Optimizers (Item_in_optimizer) which take care of the
      execution. However, this is not done during query preparation. Unfortunately
      the LIKE predicate pre-evaluates constant right-hand side arguments even
      during name resolution. Likely this is meant as an optimization.
      
      Fixed by not pre-evaluating LIKE arguments in view prepare mode.
      
      At the time of bug analysis, there were four cases failing. Only one of these
      remained at the time of fix, but tests of all four are left in the test case
      to protect from regressions.
[2 Jul 2010 13:29] 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/112761

3456 Martin Hansson	2010-07-02
      Bug#54568: create view cause Assertion failed: 0, 
      file .\item_subselect.cc, line 836
      
      IN quantified predicates are never executed directly. They are rather wrapped
      inside nodes called IN Optimizers (Item_in_optimizer) which take care of the
      execution. However, this is not done during query preparation. Unfortunately
      the LIKE predicate pre-evaluates constant right-hand side arguments even
      during name resolution. Likely this is meant as an optimization.
      
      Fixed by not pre-evaluating LIKE arguments in view prepare mode.
[4 Aug 2010 13:23] 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/115016

3473 Martin Hansson	2010-08-04
      Bug#54568: create view cause Assertion failed: 0, 
      file .\item_subselect.cc, line 836
      
      IN quantified predicates are never executed directly. They are rather wrapped
      inside nodes called IN Optimizers (Item_in_optimizer) which take care of the
      execution. However, this is not done during query preparation. Unfortunately
      the LIKE predicate pre-evaluates constant right-hand side arguments even
      during name resolution. Likely this is meant as an optimization.
      
      Fixed by not pre-evaluating LIKE arguments in view prepare mode.
[5 Aug 2010 8: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/commits/115062

3176 Martin Hansson	2010-08-05 [merge]
      Merge of fix for Bug#54568.
[5 Aug 2010 10:42] 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/115076

3474 Martin Hansson	2010-08-05
      Bug#54568: create view cause Assertion failed: 0, 
      file .\item_subselect.cc, line 836
      
      IN quantified predicates are never executed directly. They are rather wrapped
      inside nodes called IN Optimizers (Item_in_optimizer) which take care of the
      execution. However, this is not done during query preparation. Unfortunately
      the LIKE predicate pre-evaluates constant right-hand side arguments even
      during name resolution. Likely this is meant as an optimization.
      
      Fixed by not pre-evaluating LIKE arguments in view prepare mode.
[18 Aug 2010 7:20] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100818071819-2lu46b0mm3cs34rf) (version source revid:alik@sun.com-20100818071732-g682fg1v0nnrrutx) (merge vers: 5.6.1-m4) (pib:20)
[18 Aug 2010 7:21] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100818071923-4ounwbhiium2met1) (version source revid:alik@sun.com-20100818071743-lrzordai06i2crty) (pib:20)
[18 Aug 2010 7:22] Bugs System
Pushed into mysql-5.5 5.5.6-m3 (revid:alik@sun.com-20100818071719-dktnkvt8zvidj0sy) (version source revid:alik@sun.com-20100818071719-dktnkvt8zvidj0sy) (merge vers: 5.5.6-m3) (pib:20)
[8 Sep 2010 1:35] Paul DuBois
Noted in 5.1.51, 5.5.6 changelogs.

Pre-evaluation of LIKE predicates during view preparation could cause
a server crash.
[28 Sep 2010 8:46] Bugs System
Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)
[14 Oct 2010 8:34] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:49] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 9:03] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[15 Oct 2010 13:21] Jon Stephens
Already documented in the 5.1.51 changelog. Reverting to Closed state.
[25 Oct 2010 3:28] MySQL Verification Team
still seeing this with different testcase after this fix. see bug #57703
[3 Nov 2010 19:47] Paul DuBois
CVE-2010-3836
[16 Dec 2010 18:50] Paul DuBois
Noted in 5.0.92 changelog.
[8 Feb 2011 11:57] Bugs System
Pushed into mysql-5.1 5.1.56 (revid:karen.langford@oracle.com-20110208115233-t04e8k0pwhwtbc1u) (version source revid:karen.langford@oracle.com-20110208115233-t04e8k0pwhwtbc1u) (merge vers: 5.1.56) (pib:24)
[8 Feb 2011 13:46] Bugs System
Pushed into mysql-5.0 5.0.93 (revid:jonathan.perkin@oracle.com-20110208134327-62swunlfs2s88jo2) (version source revid:jonathan.perkin@oracle.com-20110208134327-62swunlfs2s88jo2) (merge vers: 5.0.93) (pib:24)
[8 Feb 2011 14:08] Bugs System
Pushed into mysql-5.5 5.5.10 (revid:jonathan.perkin@oracle.com-20110208140736-1173xnoipufbhowh) (version source revid:jonathan.perkin@oracle.com-20110208135903-jhzy6wq16b2fx7pg) (merge vers: 5.5.10) (pib:24)
[8 Feb 2011 16:44] Bugs System
Pushed into mysql-trunk 5.6.2 (revid:georgi.kodinov@oracle.com-20110208155412-tfy4l5hqxi0g7o41) (version source revid:georgi.kodinov@oracle.com-20110208154951-gzqgr74u4bndswi8) (merge vers: 5.6.2) (pib:24)