Bug #24670 Events: crash with subquery
Submitted: 28 Nov 2006 19:48 Modified: 1 Feb 2007 1:14
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.14-beta-debug, 4.1.23-debug OS:Linux (SUSE 10.0 / 64-bit)
Assigned to: Igor Babaev

[28 Nov 2006 19:48] Peter Gulutzan
Description:
I try to create an event with a subquery for the scheduling interval.
The subquery has an impossible where clause.
Crash.

I fear that this is a result of the fix for bug#22830, where I read:
"Usage of subqueries is less restricted. It was forbidden
before ..."
Notice that the bug#22830 description doesn't ask for subquery support.

How to repeat:
mysql> create event e0 on schedule every (select 1 from dual where 1=2) second do set @a=5;
ERROR 2013 (HY000): Lost connection to MySQL server during query
[28 Nov 2006 20:06] Andrey Hristov
[New Thread 1107393456 (LWP 13729)]
mysqld: item_subselect.cc:420: virtual Item_subselect::trans_res Item_singlerow_subselect::select_transformer(JOIN*): Assertion `join->conds == 0 && join->having == 0' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread 1107393456 (LWP 13729)]
0xffffe410 in __kernel_vsyscall ()
(gdb) bt
#0  0xffffe410 in __kernel_vsyscall ()
#1  0x400f22c1 in raise () from /lib/tls/libc.so.6
#2  0x400f3b75 in abort () from /lib/tls/libc.so.6
#3  0x400eb903 in __assert_fail () from /lib/tls/libc.so.6
#4  0x081db3e4 in Item_singlerow_subselect::select_transformer (this=0x8e01818, join=0x8df3c08) at item_subselect.cc:420
#5  0x08263646 in JOIN::prepare (this=0x8df3c08, rref_pointer_array=0x8dfb184, tables_init=0x0, wild_num=0, conds_init=0x0, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x8dfb038, unit_arg=0x8df8cc0) at sql_select.cc:385
#6  0x081de67b in subselect_single_select_engine::prepare (this=0x8dfa3a8) at item_subselect.cc:1517
#7  0x081da9a8 in Item_subselect::fix_fields (this=0x8e01818, thd_param=0x8dfcac0, ref=0x8dfb010) at item_subselect.cc:146
#8  0x0838541d in Event_parse_data::init_interval (this=0x8dfaf58, thd=0x8dfcac0) at event_data_objects.cc:289
#9  0x08385cc1 in Event_parse_data::check_parse_data (this=0x8dfaf58, thd=0x8dfcac0) at event_data_objects.cc:546
#10 0x0838b370 in check_parse_params (thd=0x8dfcac0, parse_data=0x8dfaf58) at event_db_repository.cc:477
#11 0x0838b534 in Event_db_repository::create_event (this=0x87981b8, thd=0x8dfcac0, parse_data=0x8dfaf58, create_if_not=0 '\0') at event_db_repository.cc:530
#12 0x0838cc4a in Events::create_event (this=0x8797fe0, thd=0x8dfcac0, parse_data=0x8dfaf58, if_not_exists=false) at events.cc:353
#13 0x0822eea7 in mysql_execute_command (thd=0x8dfcac0) at sql_parse.cc:3939
#14 0x08233653 in mysql_parse (thd=0x8dfcac0, inBuf=0x8dfaeb8 "create event e0 on schedule every (select 1 from dual where 1=2) second\ndo set @a=5", length=83) at sql_parse.cc:6051
#15 0x0822a400 in dispatch_command (command=COM_QUERY, thd=0x8dfcac0, packet=0x8df9b31 "create event e0 on schedule every (select 1 from dual where 1=2) second\ndo set @a=5", packet_length=84) at sql_parse.cc:1835
#16 0x08229d4d in do_command (thd=0x8dfcac0) at sql_parse.cc:1619
#17 0x08228fde in handle_one_connection (arg=0x0) at sql_parse.cc:1234
#18 0x40053aa7 in start_thread () from /lib/tls/libpthread.so.0
#19 0x40184c2e in clone () from /lib/tls/libc.so.6
(gdb)
[28 Nov 2006 20:16] Andrey Hristov
kill (select 1 from dual where 1=2)
also fails with this assert

HA_READ probably too
[29 Nov 2006 12:04] Andrey Hristov
Seems to be optimizer related :
mysql> create table t123 (a int);
Query OK, 0 rows affected (0.18 sec)

mysql> select * from t123 where (SELECT 1 FROM DUAL WHERE 1 > 2);
mysqld: item_subselect.cc:420: virtual Item_subselect::trans_res Item_singlerow_subselect::select_transformer(JOIN*): Assertion `join->conds == 0 && join->having == 0' failed.
mysqld got signal 6;
[1 Dec 2006 6:04] Igor Babaev
The same problem can be demonstrated for 4.1.23-debug:

mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 4.1.23-debug |
+--------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.47 sec)

mysql> INSERT INTO t1 VALUES (1), (2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;
ERROR 2013 (HY000): Lost connection to MySQL server during query

At this point the server reports the following info:

 Version: '4.1.23-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread -1220400224 (LWP 13416)]
mysqld: item_subselect.cc:378: virtual Item_subselect::trans_res Item_singlerow_subselect::select_transformer(JOIN*): Assertion `join->conds == 0 && join->having == 0' failed.

Program received signal SIGABRT, Aborted.
[Switching to Thread -1220400224 (LWP 13416)]
0xffffe410 in __kernel_vsyscall ()
[11 Dec 2006 23:45] Igor Babaev
Now if I block the invalid optimization mentioned in the previous comment I get the right output for the reported query:

mysql> SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) > 0;
Empty set (0.00 sec)

Yet I have another problem with the following query:

mysql> SELECT a FROM t1 WHERE (SELECT 1 FROM DUAL WHERE 1=0) IS NULL;
Empty set (0.00 sec)

It happens because the result of the subquery without tables erroneously always is considered as not nullable. This is another invalid optimization applied
to subqueries without tables.
[12 Dec 2006 2:57] 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/16814

ChangeSet@1.2583, 2006-12-11 18:57:23-08:00, igor@olga.mysql.com +4 -0
  Fixed bug #24670: optimizations that are legal only for subqueries without tables
  and no WHERE condition were applied for any subquery without tables.
[11 Jan 2007 10:05] Alexey Botchkov
fixed in 4.1.23, 5.0.34, 5.1.15
[11 Jan 2007 23:01] Peter Gulutzan
I tested with MySQL 5.1.15 pulled today.
Last changeset was: ChangeSet@1.2384, 2007-01-11 23:05:13+01:00.

I see:

mysql> create event e0 on schedule every (select 1 from dual where 1=2) second
    -> do set @a=5;
ERROR 2013 (HY000): Lost connection to MySQL server during query

I'm setting status back to "patch queued".
[11 Jan 2007 23:13] Andrey Hristov
It's even simpler :

mysql> set @a=(select 1 from dual where 1=2);
ERROR 2013 (HY000): Lost connection to MySQL server during query
[16 Jan 2007 6:14] Jon Stephens
Returning to patch pending. Please confirm that the patch has been pushed. Thanks.
[16 Jan 2007 7:06] Igor Babaev
I confirm that the patch is now in the 4.1.23, 5.0.34, 5.1.15 main trees.
[1 Feb 2007 1:14] 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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Documented bugfix in 4.1.23, 5.0.34, and 5.1.15 changelogs.