| Bug #54515 | Crash in opt_range.cc::get_best_group_min_max on SELECT from VIEW with GROUP BY | ||
|---|---|---|---|
| Submitted: | 15 Jun 2010 14:14 | Modified: | 20 Nov 2010 23:23 |
| Reporter: | John Embretsen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.5.5-m3-Celosia, 5.6.99-Dahlia, 6.0.14 | OS: | Any |
| Assigned to: | Jørgen Løland | CPU Architecture: | Any |
| Tags: | regression | ||
[15 Jun 2010 14:23]
Valeriy Kravchuk
Verified just as described on Mac OS X. Note that 5.1.49 does NOT crash:
valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot testReading 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 34
Server version: 5.1.49-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.00 sec)
mysql> DROP VIEW IF EXISTS view_t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> CREATE TABLE t1 (
-> col_int_key int DEFAULT NULL,
-> KEY int_key (col_int_key)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.06 sec)
mysql>
mysql> CREATE VIEW view_t1 AS
-> select t1.col_int_key AS col_int_key
-> from t1;
Query OK, 0 rows affected (0.19 sec)
mysql>
mysql> SELECT col_int_key FROM view_t1 GROUP BY col_int_key;
Empty set (0.00 sec)
So, this is a regression.
Actually, -debug version shows that we have assertion failure here:
Version: '5.5.5-m3-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution
Assertion failed: ((*tmp_group->item)->type() == Item::FIELD_ITEM), function get_best_group_min_max, file opt_range.cc, line 9639.
100615 17:21:16 - mysqld got signal 6 ;
[16 Jun 2010 11:44]
John Embretsen
Data point: This bug is similar to Bug#29104, fixed in 2007.
[16 Jun 2010 12:40]
Jørgen Løland
Also crashes for MyISAM if you insert a few rows: DROP TABLE IF EXISTS t1; DROP VIEW IF EXISTS view_t1; CREATE TABLE t1 ( col_int_key int DEFAULT NULL, KEY int_key (col_int_key) ) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(2); CREATE VIEW view_t1 AS SELECT t1.col_int_key AS col_int_key FROM t1; SELECT col_int_key FROM view_t1 GROUP BY col_int_key; DROP VIEW view_t1; DROP TABLE t1;
[17 Jun 2010 7:46]
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/111365 3199 Jorgen Loland 2010-06-17 Bug#54515: Crash in opt_range.cc::get_best_group_min_max on SELECT from VIEW with GROUP BY When handling the grouping items in get_best_group_min_max, the items need to be of type Item_field. In this bug, an ASSERT triggered because the item used for grouping was an Item_direct_view_ref (i.e., the group column is from a view). The fix is to get the real_item since Item_ref* pointing to Item_field is ok. @ mysql-test/r/select.result Add test for BUG#54515 @ mysql-test/r/select_jcl6.result Add test for BUG#54515 @ mysql-test/t/select.test Add test for BUG#54515 @ sql/opt_range.cc Get the real_item() when processing grouping items in get_best_group_min_max.
[18 Jun 2010 9:32]
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/111525 3200 Jorgen Loland 2010-06-18 Bug#54515: Crash in opt_range.cc::get_best_group_min_max on SELECT from VIEW with GROUP BY When handling the grouping items in get_best_group_min_max, the items need to be of type Item_field. In this bug, an ASSERT triggered because the item used for grouping was an Item_direct_view_ref (i.e., the group column is from a view). The fix is to get the real_item since Item_ref* pointing to Item_field is ok. @ mysql-test/r/select.result Add test for BUG#54515 @ mysql-test/r/select_jcl6.result Add test for BUG#54515 @ mysql-test/t/select.test Add test for BUG#54515 @ sql/opt_range.cc Get the real_item() when processing grouping items in get_best_group_min_max.
[18 Jun 2010 12:48]
Jørgen Løland
Pushed to next-mr-opt-backporting
[18 Jun 2010 14:36]
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/111561 3203 Oystein Grovlen 2010-06-18 [merge] Merge test restructuring with Bug#54515. Update all result files affected by new test case.
[8 Jul 2010 13:37]
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/113134 3295 Olav Sandstaa 2010-07-08 [merge] Merging fix for Bug#54515 from mysql-trunk-bugfixing to mysql-next-mr-bugfixing.
[8 Jul 2010 13:48]
Olav Sandstå
Backported patch from mysql-next-mr-opt-backporting to mysql-trunk-bugfixing with revision id: olav@sun.com-20100708131905-2i3p7esvpbb2qjts. Merged this patch to mysql-next-mr-bugfixing.
[23 Jul 2010 12:24]
Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100723121820-jryu2fuw3pc53q9w) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[23 Jul 2010 12:31]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100723121929-90e9zemk3jkr2ocy) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (pib:18)
[2 Aug 2010 19:40]
Paul DuBois
Noted in 5.5.6 changelog. Queries that named view columns in a GROUP BY clause could cause a server crash.
[4 Aug 2010 8:04]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[4 Aug 2010 8:20]
Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:vasil.dimov@oracle.com-20100531152341-x2d4hma644icamh1) (merge vers: 5.5.5-m3) (pib:18)
[4 Aug 2010 23:11]
Paul DuBois
Bug does not appear in any released 5.6.x version.
[16 Aug 2010 6:34]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:24]
Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
Description: mysqld crashes when executing SELECT col_int_key FROM view_t1 GROUP BY col_int_key; when the table upon which view_t1 is built is using InnoDB, and the selected column is indexed. With debug builds an assertion is hit: Assertion failed: (*tmp_group->item)->type() == Item::FIELD_ITEM, file sql/opt_range.cc, line 9643 100615 15:19:59 - mysqld got signal 6 ; Stacktrace from mysql-trunk-bugfixing revid alik@sun.com-20100615083101-btnsovjswczpfcv7, debug build (solaris): [12] _assert(0x0, 0x0, 0x0, 0x0, 0x0, 0x0), at 0xfffffd7fff1fdc95 [13] get_best_group_min_max(param = 0xfffffd7fff076dc8, tree = (nil), read_time = 1.2), line 9643 in "opt_range.cc" [14] SQL_SELECT::test_quick_select(this = 0x6511a00, thd = 0x64c8730, keys_to_use = CLASS, prev_tables = 0, limit = 18446744073709551615ULL, force_quick_range = false), line 2385 in "opt_range.cc" [15] get_quick_record_count(thd = 0x64c8730, select = 0x6511a00, table = 0x6540860, keys = 0x651d058, limit = 18446744073709551615ULL), line 2594 in "sql_select.cc" [16] make_join_statistics(join = 0x6510108, tables_arg = 0x650fc00, conds = (nil), keyuse_array = 0x65116b8), line 3005 in "sql_select.cc" [17] JOIN::optimize(this = 0x6510108), line 1034 in "sql_select.cc" [18] mysql_select(thd = 0x64c8730, rref_pointer_array = 0x64ca788, tables = 0x6542c40, wild_num = 0, fields = STRUCT, conds = (nil), og_num = 1U, order = (nil), group = 0x6543258, having = (nil), proc_param = (nil), select_options = 2147748608ULL, result = 0x65448c0, unit = 0x64c9fb8, select_lex = 0x64ca5b8), line 2535 in "sql_select.cc" [19] handle_select(thd = 0x64c8730, lex = 0x64c9f10, result = 0x65448c0, setup_tables_done_option = 0), line 278 in "sql_select.cc" [20] execute_sqlcom_select(thd = 0x64c8730, all_tables = 0x6542c40), line 4822 in "sql_parse.cc" [21] mysql_execute_command(thd = 0x64c8730), line 2304 in "sql_parse.cc" [22] mysql_parse(thd = 0x64c8730, inBuf = 0x6542a20 "SELECT col_int_key FROM view_t1 GROUP BY col_int_key", length = 52U, parser_state = 0xfffffd7fff07da58), line 5851 in "sql_parse.cc" [23] dispatch_command(command = COM_QUERY, thd = 0x64c8730, packet = 0x6548561 "SELECT col_int_key FROM view_t1 GROUP BY col_int_key", packet_length = 52U), line 1114 in "sql_parse.cc" [24] do_command(thd = 0x64c8730), line 800 in "sql_parse.cc" [25] do_handle_one_connection(thd_arg = 0x64c8730), line 1195 in "sql_connect.cc" [26] handle_one_connection(arg = 0x64c8730), line 1134 in "sql_connect.cc" Stacktrace with optimized build (linux): Thread 1 (process 1896): #0 0x0000003587a0b122 in pthread_kill () from /lib64/libpthread.so.0 #1 0x00000000004f8dcf in handle_segfault (sig=11) at /export/home/pb2/build/sb_0-1931401-1276592853.42/mysql-5.5.5-m3/sql/mysqld.cc:2791 #2 <signal handler called> #3 0x0000000000728c47 in SQL_SELECT::test_quick_select (this=0x144df848, thd=0x1445bd80, keys_to_use=<value optimized out>, prev_tables=<value optimized out>, limit=<value optimized out>, force_quick_range=<value optimized out>) at /export/home/pb2/build/sb_0-1931401-1276592853.42/mysql-5.5.5-m3/sql/opt_range.cc:9645 #4 0x0000000000596526 in make_join_statistics (join=0x144ddf48, tables_arg=0x144dda40, conds=<value optimized out>, keyuse_array=0x144df500) at /export/home/pb2/build/sb_0-1931401-1276592853.42/mysql-5.5.5-m3/sql/sql_select.cc:2594 #5 0x0000000000597155 in JOIN::optimize (this=0x144ddf48) at /export/home/pb2/build/sb_0-1931401-1276592853.42/mysql-5.5.5-m3/sql/sql_select.cc:1034 #6 0x00000000005a0481 in mysql_select (thd=0x1445bd80, rref_pointer_array=0x1445de48, tables=0x144ccb50, wild_num=0, fields=@0x1445dd80, conds=0x0, og_num=1, order=0x0, group=0x144cd168, having=0x0, proc_param=0x0, select_options=2147748608, result=0x144ce7d0, unit=0x1445d678, select_lex=0x1445dc78) at /export/home/pb2/build/sb_0-1931401-1276592853.42/mysql-5.5.5-m3/sql/sql_select.cc:2535 #7 0x00000000005a0e6f in handle_select (thd=0x1445bd80, lex=0x1445d5d0, result=0x144ce7d0, setup_tables_done_option=0) at /export/home/pb2/build/sb_0-1931401-1276592853.42/mysql-5.5.5-m3/sql/sql_select.cc:290 #8 0x0000000000564340 in execute_sqlcom_select (thd=0x1445bd80, all_tables=0x144ccb50) at /export/home/pb2/build/sb_0-1931401-1276592853.42/mysql-5.5.5-m3/sql/sql_parse.cc:4822 #9 0x000000000056558d in mysql_execute_command (thd=0x1445bd80) at /export/home/pb2/build/sb_0-1931401-1276592853.42/mysql-5.5.5-m3/sql/sql_parse.cc:2304 #10 0x000000000056afb5 in mysql_parse (thd=0x1445bd80, inBuf=<value optimized out>, length=<value optimized out>, parser_state=0x46784670) at /export/home/pb2/build/sb_0-1931401-1276592853.42/mysql-5.5.5-m3/sql/sql_parse.cc:5851 #11 0x000000000056ba6e in dispatch_command (command=COM_QUERY, thd=0x1445bd80, packet=0x1450f9f1 "SELECT col_int_key FROM view_t1 GROUP BY col_int_key", packet_length=<value optimized out>) at /export/home/pb2/build/sb_0-1931401-1276592853.42/mysql-5.5.5-m3/sql/sql_parse.cc:1114 #12 0x000000000056c672 in do_command (thd=0x1445bd80) at /export/home/pb2/build/sb_0-1931401-1276592853.42/mysql-5.5.5-m3/sql/sql_parse.cc:800 #13 0x00000000005fd6b0 in do_handle_one_connection (thd_arg=0x1445bd80) at /export/home/pb2/build/sb_0-1931401-1276592853.42/mysql-5.5.5-m3/sql/sql_connect.cc:1195 #14 0x00000000005fdc03 in handle_one_connection (arg=<value optimized out>) at /export/home/pb2/build/sb_0-1931401-1276592853.42/mysql-5.5.5-m3/sql/sql_connect.cc:1134 #15 0x000000000088593a in pfs_spawn_thread (arg=<value optimized out>) at /export/home/pb2/build/sb_0-1931401-1276592853.42/mysql-5.5.5-m3/storage/perfschema/pfs.cc:1015 #16 0x0000003587a062f7 in start_thread () from /lib64/libpthread.so.0 #17 0x0000003586ed1b6d in clone () from /lib64/libc.so.6 (Not repeatable with mysql-5.1-bugteam revid bjorn.munch@sun.com-20100615090352-4ibglh6znnhoje1u.) How to repeat: If using MTR, include; --source include/have_innodb.inc DROP TABLE IF EXISTS t1; DROP VIEW IF EXISTS view_t1; CREATE TABLE t1 ( col_int_key int DEFAULT NULL, KEY int_key (col_int_key) ) ENGINE=InnoDB; CREATE VIEW view_t1 AS select t1.col_int_key AS col_int_key from t1; SELECT col_int_key FROM view_t1 GROUP BY col_int_key; Issue is avoided if: - using MyISAM instead of InnoDB - not using GROUP BY - not using an index on the selected column