Bug #48294 assertion when creating a view based on some row() construct in select query
Submitted: 25 Oct 2009 20:49 Modified: 21 Jun 2010 1:03
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S1 (Critical)
Version:5.0.88-debug, 5.1.41-debug,5.5.1-m2-debug OS:Any
Assigned to: Sergei Glukhov CPU Architecture:Any

[25 Oct 2009 20:49] Shane Bester
Description:
Version: '5.1.40-enterprise-gpl-advanced-debug'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Edition Debug (GPL)
Assertion failed: 0, file .\item_subselect.cc, line 811

 mysqld-debug.exe!my_sigabrt_handler()[mysqld.cc:2048]
 mysqld-debug.exe!raise()[winsig.c:597]
 mysqld-debug.exe!abort()[abort.c:78]
 mysqld-debug.exe!_wassert()[assert.c:212]
 mysqld-debug.exe!Item_in_subselect::val_int()[item_subselect.cc:811]
 mysqld-debug.exe!Item_func_nop_all::val_int()[item_cmpfunc.cc:354]
 mysqld-debug.exe!eval_const_cond()[item_func.cc:63]
 mysqld-debug.exe!remove_eq_conds()[sql_select.cc:9137]
 mysqld-debug.exe!optimize_cond()[sql_select.cc:8985]
 mysqld-debug.exe!JOIN::optimize()[sql_select.cc:830]
 mysqld-debug.exe!subselect_single_select_engine::exec()[item_subselect.cc:1882]
 mysqld-debug.exe!Item_subselect::exec()[item_subselect.cc:261]
 mysqld-debug.exe!Item_singlerow_subselect::val_int()[item_subselect.cc:565]
 mysqld-debug.exe!Item::update_null_value()[item.h:820]
 mysqld-debug.exe!Item_subselect::is_null()[item_subselect.h:94]
 mysqld-debug.exe!Item_row::fix_fields()[item_row.cc:80]
 mysqld-debug.exe!Item_func::fix_fields()[item_func.cc:171]
 mysqld-debug.exe!setup_conds()[sql_base.cc:7998]
 mysqld-debug.exe!setup_without_group()[sql_select.cc:412]
 mysqld-debug.exe!JOIN::prepare()[sql_select.cc:501]
 mysqld-debug.exe!st_select_lex_unit::prepare()[sql_union.cc:264]
 mysqld-debug.exe!mysql_create_view()[sql_view.cc:523]
 mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:4644]
 mysqld-debug.exe!mysql_parse()[sql_parse.cc:5967]
 mysqld-debug.exe!dispatch_command()[sql_parse.cc:1226]
 mysqld-debug.exe!do_command()[sql_parse.cc:865]
 mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1127]
 mysqld-debug.exe!pthread_start()[my_winthread.c:85]
 mysqld-debug.exe!_callthreadstart()[thread.c:295]
 mysqld-debug.exe!_threadstart()[thread.c:277]

How to repeat:
on a debug build, run the following:

drop table if exists `t1`;
create table `t1`(`a` int);
insert into `t1` values ();
create or replace view `v1` as select 1
from `t1` where  
row(1,1)>= row(1,( select 1 from `t1` where  `a` >= any ( select '1' )));
[25 Oct 2009 22:16] MySQL Verification Team
Thank you for the bug report.

c:\dbs>c:\dbs\5.1\bin\mysqld --defaults-file=c:\dbs\5.1\my.ini --standalone --console
091025 20:12:49 [Note] Plugin 'FEDERATED' is disabled.
091025 20:12:49  InnoDB: Started; log sequence number 0 44233
091025 20:12:49 [Note] Event Scheduler: Loaded 0 events
091025 20:12:49 [Note] c:\dbs\5.1\bin\mysqld: ready for connections.
Version: '5.1.41-Win X64-debug'  socket: ''  port: 3510  Source distribution
Assertion failed: 0, file .\item_subselect.cc, line 811

This application has requested the Runtime to terminate it in an unusual way.
Please contact the application's support team for more information.

This application has requested the Runtime to terminate it in an unusual way.
Please contact the application's support team for more information.
091025 20:13:22 - mysqld got exception 0x80000003 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8384512
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338112 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x3adbad8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
00000001400579E5    mysqld.exe!my_sigabrt_handler()[mysqld.cc:2048]
000000014000BB61    mysqld.exe!raise()[winsig.c:597]
0000000140014133    mysqld.exe!abort()[abort.c:78]
00000001405C9574    mysqld.exe!_wassert()[assert.c:212]
0000000140314AF2    mysqld.exe!Item_in_subselect::val_int()[item_subselect.cc:811]
000000014009CFCD    mysqld.exe!Item_func_nop_all::val_int()[item_cmpfunc.cc:354]
0000000140143A16    mysqld.exe!eval_const_cond()[item_func.cc:63]
00000001402BED86    mysqld.exe!remove_eq_conds()[sql_select.cc:9137]
00000001402BC3BE    mysqld.exe!optimize_cond()[sql_select.cc:8985]
00000001402A3EC3    mysqld.exe!JOIN::optimize()[sql_select.cc:830]
0000000140319758    mysqld.exe!subselect_single_select_engine::exec()[item_subselect.cc:1882]
000000014031294A    mysqld.exe!Item_subselect::exec()[item_subselect.cc:261]
0000000140313BCF    mysqld.exe!Item_singlerow_subselect::val_int()[item_subselect.cc:565]
00000001400889A6    mysqld.exe!Item::update_null_value()[item.h:820]
000000014031BB66    mysqld.exe!Item_subselect::is_null()[item_subselect.h:94]
000000014042ADF3    mysqld.exe!Item_row::fix_fields()[item_row.cc:80]
0000000140143EE6    mysqld.exe!Item_func::fix_fields()[item_func.cc:171]
0000000140176ACF    mysqld.exe!setup_conds()[sql_base.cc:7998]
00000001402D98EA    mysqld.exe!setup_without_group()[sql_select.cc:412]
00000001402A2A76    mysqld.exe!JOIN::prepare()[sql_select.cc:501]
000000014036D794    mysqld.exe!st_select_lex_unit::prepare()[sql_union.cc:264]
00000001402E2CB7    mysqld.exe!mysql_create_view()[sql_view.cc:523]
000000014020A25E    mysqld.exe!mysql_execute_command()[sql_parse.cc:4644]
000000014020F34D    mysqld.exe!mysql_parse()[sql_parse.cc:5967]
00000001401FFCCC    mysqld.exe!dispatch_command()[sql_parse.cc:1226]
00000001401FEFDA    mysqld.exe!do_command()[sql_parse.cc:865]
00000001400C48B5    mysqld.exe!handle_one_connection()[sql_connect.cc:1127]
00000001405FCEB5    mysqld.exe!pthread_start()[my_winthread.c:85]
00000001405D30F5    mysqld.exe!_callthreadstart()[thread.c:295]
00000001405D30C7    mysqld.exe!_threadstart()[thread.c:277]
00000000770EC3BD    kernel32.dll!BaseThreadInitThunk()
0000000077714581    ntdll.dll!RtlUserThreadStart()
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 000000000437F408=create or replace view `v1` as select 1
from `t1` where
row(1,1)>= row(1,( select 1 from `t1` where  `a` >= any ( select '1' )))
thd->thread_id=1
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.

c:\dbs>
[5 Feb 2010 12:15] 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/99402

3345 Sergey Glukhov	2010-02-05
      Bug#48294 assertion when creating a view based on some row() construct in select query
      In case of 'CREATE VIEW' subselect transformation does not happen(see JOIN::prepare).
      During fix_fields Item_row may call is_null() method for its arugmens which
      leads to item calculation(wrong subselect in our case as
      transformation did not happen before). This is_null() call
      does not make sence for 'CREATE VIEW'.
      So the fix is skip is_null() call in this case.
     @ mysql-test/r/view.result
        test case
     @ mysql-test/t/view.test
        test case
     @ sql/item_row.cc
        skip is_null() call in case of 'CREATE VIEW' as unnecessary.
[12 Feb 2010 9: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/100088

3380 Sergey Glukhov	2010-02-12
      Bug#48294 assertion when creating a view based on some row() construct in select query
      In case of 'CREATE VIEW' subselect transformation does not happen(see JOIN::prepare).
      During fix_fields Item_row may call is_null() method for its arugmens which
      leads to item calculation(wrong subselect in our case as
      transformation did not happen before). This is_null() call
      does not make sence for 'CREATE VIEW'.
      Note:
      Only Item_row is affected because other items don't call is_null() 
      during fix_fields() for arguments.
     @ mysql-test/r/view.result
        test case
     @ mysql-test/t/view.test
        test case
     @ sql/item_row.cc
        skip is_null() call in case of 'CREATE VIEW' as unnecessary.
[1 Mar 2010 8:42] Bugs System
Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:joerg@mysql.com-20100212173307-ph563zr4wmoklgwd) (merge vers: 5.1.45) (pib:16)
[2 Mar 2010 14:32] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100225090938-2j5ybqoau570mytu) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 14:38] Bugs System
Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alexey.kopytov@sun.com-20100221213311-xf5nyv391dsw9v6j) (merge vers: 5.5.2-m2) (pib:16)
[2 Mar 2010 14:43] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100224135227-rcqs9pe9b2in80pf) (pib:16)
[8 Apr 2010 18:14] Paul DuBois
Noted in 5.1.45, 5.5.3, 6.0.14 changelogs.

For debug builds, creating a view containing a row constructor caused
an assertion to be raised.
[17 Jun 2010 11:48] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:25] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:13] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)