Bug #16382 SELECT with IF and GROUP BY on VIEW can crash server
Submitted: 11 Jan 2006 18:23 Modified: 28 Feb 2006 2:34
Reporter: Eli Dickinson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.18/5.0.19 BK OS:Windows (Windows/Linux)
Assigned to: Igor Babaev CPU Architecture:Any

[11 Jan 2006 18:23] Eli Dickinson
Description:
It appears that a SELECT statement that uses an IF statement to alter a field in a VIEW consistently crashes the server.

I can verify this bug on 5.0.18-nt and 5.0.15-nt-max. Not sure about any other versions or operating systems.

How to repeat:
drop table if exists crash;
CREATE TABLE `crash` 
	(X varchar(100));

insert into crash values (null),('foo'),('bar'),(null);

drop view if exists crashview;
create view crashview as select * from crash;

# The following queries all work fine
select if(x is null,'blank','not blank') from crashview group by x; 
select if(x is null,'blank','not blank') as x from crash group by x; 
select if(x is null,'blank','not blank') as x from crashview;
select if(x is null,'blank','not blank') as y from crashview group by y; 

# The following query crashes the server!
select if(x is null,'blank','not blank') as x from crashview group by x;

# I get the same result with ifnull()

Suggested fix:
Got me. I am violating an SQL rule by aliasing a field like that?
[11 Jan 2006 20:41] MySQL Verification Team
Thank you for the bug report. I was able to repeat:

mysql> select if(x is null,'blank','not blank') as x from crashview group by x;
ERROR 2013 (HY000): Lost connection to MySQL server during query

030411 18:39:14 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.19-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1131862960 (LWP 7346)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1131862960 (LWP 7346)]
0x0825071e in find_order_in_list (thd=0x8e59910, ref_pointer_array=0x8e7a388, tables=0x8e794e0, order=0x8e79740, fields=@0x8e59c0c, 
    all_fields=@0x8e838d0, is_group_field=true) at sql_select.cc:12302
12302                             current_thd->where);
(gdb) bt full
#0  0x0825071e in find_order_in_list (thd=0x8e59910, ref_pointer_array=0x8e7a388, tables=0x8e794e0, order=0x8e79740, fields=@0x8e59c0c, 
    all_fields=@0x8e838d0, is_group_field=true) at sql_select.cc:12302
        view_ref = (class Item *) 0x8e7a490
        order_item = (class Item *) 0x8e79688
        order_item_type = FIELD_ITEM
        select_item = (class Item **) 0x8e7949c
        from_field = (class Field *) 0x2
        counter = 0
        unaliased = false
        el = 136489781
<cut>
[2 Feb 2006 4: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/2039
[10 Feb 2006 5:22] Igor Babaev
ChangeSet
  1.2026 06/02/01 20:43:43 igor@rurik.mysql.com +3 -0
  Fixed bug #16382.
  When an ambiguous field name is used in a group by clause a warning is issued
  in the find_order_in_list function by a call to push_warning_printf.
  An expression that was not always valid was passed to this call as the field
  name parameter.

  sql/sql_select.cc
    1.390 06/02/01 20:43:37 igor@rurik.mysql.com +2 -1
    Fixed bug #16382.
    When an ambiguous field name is used in a group by clause a warning is issued
    in the find_order_in_list function by a call to push_warning_printf.
    An expression that was not always valid was passed to this call as the field
    name parameter.   

  mysql-test/t/view.test
    1.134 06/02/01 20:43:37 igor@rurik.mysql.com +17 -0
    Added a test case for bug #16382.

  mysql-test/r/view.result
    1.145 06/02/01 20:43:37 igor@rurik.mysql.com +34 -0
    Added a test case for bug #16382.

The fix will appear in 5.0.19 and 5.1.7
[28 Feb 2006 2:34] Paul DuBois
Noted in 5.0.19, 5.1.7 changelogs.

<literal>SELECT</literal> with <literal>GROUP BY</literal> on
a view can cause a server crash. (Bug #16382)