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:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0.42, 5.0.44BK, 5.1.20BK OS:Any
Assigned to: Igor Babaev
Tags: crash

[14 Jun 2007 12:41] Shane Bester
Description:
When doing a select from a view, mysqld crashes with following stack trace:

mysqld-debug.exe!_NMSG_WRITE
mysqld-debug.exe!abort
mysqld-debug.exe!_assert
mysqld-debug.exe!Item_direct_view_ref::eq
mysqld-debug.exe!const_expression_in_where
mysqld-debug.exe!const_expression_in_where
mysqld-debug.exe!remove_const
mysqld-debug.exe!JOIN::optimize
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

How to repeat:
will upload a testcase later

Suggested fix:
.
[14 Jun 2007 12:43] Shane Bester
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] Shane Bester
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] Shane Bester
EXPLAIN <select> also crashes...
[18 Jun 2007 0:53] Shane Bester
--------------
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.