| Bug #29104 | crash when selecting from a view | ||
|---|---|---|---|
| Submitted: | 14 Jun 2007 12:41 | Modified: | 6 Jul 2007 3:31 |
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 5.0.42, 5.0.44BK, 5.1.20BK | OS: | Any |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
| Tags: | crash | ||
[14 Jun 2007 12:41]
Shane Bester
[14 Jun 2007 12:43]
MySQL Verification Team
debug binary fails assertion: Assertion failed: (*ref)->real_item()->type() == item_ref_ref->real_item()->type(), file .\item.cc, line 5627
[14 Jun 2007 13:01]
MySQL Verification Team
SELECT 1 FROM <view> GROUP BY <some field>; another variation of query causes huge memory consumption and crash with no assert: stack trace: mysqld-debug.exe!update_ref_and_keys mysqld-debug.exe!make_join_statistics mysqld-debug.exe!JOIN::optimize mysqld-debug.exe!subselect_single_select_engine::exec mysqld-debug.exe!Item_subselect::exec mysqld-debug.exe!Item_exists_subselect::val_bool mysqld-debug.exe!Item_func_not::val_int mysqld-debug.exe!Item::val_bool mysqld-debug.exe!Item_cond_and::val_int mysqld-debug.exe!evaluate_join_record mysqld-debug.exe!sub_select mysqld-debug.exe!evaluate_null_complemented_join_record mysqld-debug.exe!sub_select mysqld-debug.exe!evaluate_null_complemented_join_record mysqld-debug.exe!sub_select mysqld-debug.exe!evaluate_null_complemented_join_record mysqld-debug.exe!sub_select mysqld-debug.exe!evaluate_join_record mysqld-debug.exe!sub_select mysqld-debug.exe!evaluate_join_record mysqld-debug.exe!sub_select mysqld-debug.exe!evaluate_join_record mysqld-debug.exe!sub_select mysqld-debug.exe!evaluate_join_record mysqld-debug.exe!sub_select mysqld-debug.exe!evaluate_join_record mysqld-debug.exe!sub_select mysqld-debug.exe!evaluate_join_record mysqld-debug.exe!sub_select mysqld-debug.exe!evaluate_join_record mysqld-debug.exe!sub_select mysqld-debug.exe!evaluate_join_record mysqld-debug.exe!sub_select mysqld-debug.exe!evaluate_join_record mysqld-debug.exe!sub_select mysqld-debug.exe!do_select mysqld-debug.exe!JOIN::exec mysqld-debug.exe!mysql_select mysqld-debug.exe!handle_select mysqld-debug.exe!mysql_execute_command mysqld-debug.exe!mysql_parse mysqld-debug.exe!dispatch_command mysqld-debug.exe!do_command mysqld-debug.exe!handle_one_connection mysqld-debug.exe!pthread_start mysqld-debug.exe!_threadstart kernel32.dll!BaseThreadInitThunk ntdll.dll!LdrInitializeThunk
[14 Jun 2007 13:05]
MySQL Verification Team
EXPLAIN <select> also crashes...
[18 Jun 2007 0:53]
MySQL Verification Team
-------------- SMALL TESTCASE -------------- drop table if exists `t1`; drop table if exists `t2`; drop view if exists `v1`; drop view if exists `v2`; create table `t1` (`b` char(1))engine=innodb; create table `t2` (`a` char(1))engine=innodb; create view `v2` as select (case when (`a` = 'v') then 1 else null end) as `a`from `t2`; create view `v1` as select `b`,`a` from `v2`,`t1`; select 1 from v1 where (`a` = 1) group by `b`;
[20 Jun 2007 5:18]
Igor Babaev
This problem can be demonstrated with a simpler example: mysql> CREATE TABLE t1 (a int, b int); Query OK, 0 rows affected (0.46 sec) mysql> INSERT INTO t1 VALUES (1,2), (2,2), (1,3), (1,2); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> CREATE VIEW v1 AS SELECT a, b+1 as b FROM t1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT a, SUM(b) FROM v1 WHERE b=3 GROUP BY a; ERROR 2013 (HY000): Lost connection to MySQL server during query It's interesting that a slightly different query works: mysql> SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b; +------+--------+ | b | SUM(a) | +------+--------+ | 3 | 4 | +------+--------+ 1 row in set (0.01 sec) It's easy to see why the assertion in Item_direct_view_ref::eq that fails for the first query in the does not fail for the second query. The both cases the method Item_direct_view_ref::eq is called in the function const_expression_in_where that finds out whether a group by element gr can be removed because the where condition contains a conjunctive predicate of the form gr=const. For the first query eq is called for the item representing a with an item that represents b as a parameter. For the second query eq is called for the item representing a with the same item as a parameter. It's obvious that when the items are different the assertion may fail. In general, of course, the assertion can not be valid and should be removed.
[20 Jun 2007 5:29]
Igor Babaev
Removal of the failing assertion reveals another problem. For the query 'SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b' GROUP BY is eliminated: mysql> EXPLAIN SELECT b, SUM(a) FROM v1 WHERE b=3 GROUP BY b +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 4 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) while for a similar query 'SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a' GROUP BY IS not eliminated: mysql> EXPLAIN SELECT a, SUM(b) FROM v1 WHERE a=1 GROUP BY a; +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+ | 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using temporary; Using filesort | +----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+ 1 row in set (0.00 sec) This is another bug.
[20 Jun 2007 19:39]
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/29215 ChangeSet@1.2493, 2007-06-20 12:43:14-07:00, igor@olga.mysql.com +3 -0 Fixed bug #29104: assertion abort for grouping queries using views. The abort happened when a query contained a conjunctive predicate of the form 'view column = constant' in the WHERE condition and the grouping list also contained a reference to a view column yet a different one. Removed the failing assertion as invalid in a general case. Also fixed a bug that prevented applying some optimization for grouping queries using views. If the WHERE condition of such a query contains a conjunctive condition of the form 'view column = constant' and this view column is used in the grouping list then grouping by this column can be eliminated. The bug blocked performing this elimination.
[25 Jun 2007 21:49]
Bugs System
Pushed into 5.1.21-beta
[25 Jun 2007 21:51]
Bugs System
Pushed into 5.0.46
[6 Jul 2007 3:31]
Paul DuBois
Noted in 5.0.46, 5.1.21 changelogs. An assertion failure occurred if a query contained a conjunctive predicate of the form view_column = constant in the WHERE clause and the GROUP BY clause contained a reference to a different view column. The fix also enables application of an optimization that was being skipped if a query contained a conjunctive predicate of the form view_column = constant in the WHERE clause and the GROUP BY clause contained a reference to the same view column.
