MySQL Bugs: #13595: MySQL server crashes when querying view with group by.. having clause

Bug #13595	MySQL server crashes when querying view with group by.. having clause
Submitted:	29 Sep 2005 6:23	Modified:	29 Sep 2005 12:19
Reporter:	Rita Rao	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.13	OS:	Windows (WIN-NT2003)
Assigned to:		CPU Architecture:	Any

Description:
server crash while issuing group  by having on view.
CREATE TABLE `emp` (
  `EMPNO` int(4) unsigned NOT NULL auto_increment,
  `ENAME` varchar(10) default NULL,
  `JOB` varchar(9) default NULL,
  `MGR` int(4) unsigned default NULL,
  `HIREDATE` date default NULL,
  `SAL` double(7,2) unsigned default NULL,
  `COMM` double(7,2) unsigned default NULL,
  `DEPTNO` int(2) unsigned NOT NULL,
  PRIMARY KEY  (`EMPNO`),
  KEY `EMP_FOREIGN_KEY` (`DEPTNO`),
  KEY `SELF_FOREIGN_KEY` (`MGR`)
);

CREATE TABLE `dept` (
  `deptno` int(2) unsigned NOT NULL auto_increment,
  `DNAME` varchar(13) default NULL,
  `LOC` varchar(14) NOT NULL,
  PRIMARY KEY  (`deptno`)
)

CREATE VIEW emp_testview AS SELECT * FROM emp;

when querying SELECT ename,deptno FROM emp GROUP BY deptno HAVING ename in ('rita','scott');

query return resultset

on querying view 
SELECT ename,deptno FROM emp_testview GROUP BY deptno HAVING ename in ('rita','scott');

the server crashes and on command line we get error The instruction at "0x00431d83" referenced memory at "0x000000000".The memory could not be read.

ERROR 2013(HY000): Lost conection to MySQL server during query.

How to repeat:
CREATE TABLE `emp` (
  `EMPNO` int(4) unsigned NOT NULL auto_increment,
  `ENAME` varchar(10) default NULL,
  `JOB` varchar(9) default NULL,
  `MGR` int(4) unsigned default NULL,
  `HIREDATE` date default NULL,
  `SAL` double(7,2) unsigned default NULL,
  `COMM` double(7,2) unsigned default NULL,
  `DEPTNO` int(2) unsigned NOT NULL,
  PRIMARY KEY  (`EMPNO`),
  KEY `EMP_FOREIGN_KEY` (`DEPTNO`),
  KEY `SELF_FOREIGN_KEY` (`MGR`)
);

CREATE TABLE `dept` (
  `deptno` int(2) unsigned NOT NULL auto_increment,
  `DNAME` varchar(13) default NULL,
  `LOC` varchar(14) NOT NULL,
  PRIMARY KEY  (`deptno`)
)

CREATE VIEW emp_testview AS SELECT * FROM emp;

when querying SELECT ename,deptno FROM emp GROUP BY deptno HAVING ename in ('rita','scott');

query return resultset

on querying view 
SELECT ename,deptno FROM emp_testview GROUP BY deptno HAVING ename in ('rita','scott');

the server crashes and on command line we get error The instruction at "0x00431d83" referenced memory at "0x000000000".The memory could not be read.

ERROR 2013(HY000): Lost conection to MySQL server during query.

Suggested fix:
When the table is queried the resultset is obtained without any errors.In MySQL views are handled as tables. Views are not seperate objects but considered as table object.Why not make views as a seperate object as in oracle.In oracle such query will run smoothly without any problems.

Verified on Fedora Core 4 with 5.0 from BK tree
ChangeSet@1.1991.3.2, 2005-09-27 13:37:20+04:00, kaa@polly.local

Backtrace:

#0  0x08153774 in Item_ref::real_item() (this=0x8dd18f0) at item.h:1621
#1  0x0814d5cb in Item_direct_view_ref::eq(Item const*, bool) const (this=0x8dd30f8, item=0x8dd18f0, binary_cmp=false) at item.cc:4925
#2  0x08220a5c in find_item_in_list(Item*, List<Item>&, unsigned*, find_item_error_report_type, bool*) (find=0x8dd18f0, items=@0x8da6464, counter=0x97063340, report_error=REPORT_EXCEPT_NOT_FOUND, unaliased=0x9706333f) at sql_base.cc:3462
#3  0x08148d83 in resolve_ref_in_select_and_group (thd=0x8da6188, ref=0x8dd18f0, select=0x8da63e4) at item.cc:3094
#4  0x0814c2eb in Item_ref::fix_fields(THD*, Item**) (this=0x8dd18f0, thd=0x8da6188, reference=0x8dd1b30) at item.cc:4419
#5  0x08163fa9 in Item_func::fix_fields(THD*, Item**) (this=0x8dd1aa8, thd=0x8da6188, ref=0x8e0d940) at item_func.cc:158
#6  0x0817df20 in Item_func_in::fix_fields(THD*, Item**) (this=0x8dd1aa8, thd=0x8da6188, ref=0x8e0d940) at item_cmpfunc.cc:2314
#7  0x0824ad7b in JOIN::prepare(Item***, st_table_list*, unsigned, Item*, unsigned, st_order*, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*) (this=0x8e0ccf8, rref_pointer_array=0x8da64fc, tables_init=0x8dd1668, wild_num=0, conds_init=0x0, og_num=1, order_init=0x0, group_init=0x8dd18b8, having_init=0x8dd1aa8, proc_param_init=0x0, select_lex_arg=0x8da63e4, unit_arg=0x8da61d8) at sql_select.cc:359
#8  0x082518d7 in mysql_select(THD*, Item***, st_table_list*, unsigned, List<Item>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long, select_result*, st_select_lex_unit*, st_select_lex*) (thd=0x8da6188, rref_pointer_array=0x8da64fc, tables=0x8dd1668, wild_num=0, fields=@0x8da6464, conds=0x0, og_num=1, order=0x0, group=0x8dd18b8, having=0x8dd1aa8, proc_param=0x0, select_options=2156153344, result=0x8dd2ff0, unit=0x8da61d8, select_lex=0x8da63e4) at sql_select.cc:1811
#9  0x08251c62 in handle_select(THD*, st_lex*, select_result*, unsigned long) (thd=0x8da6188, lex=0x8da61c8, result=0x8dd2ff0, setup_tables_done_option=0) at sql_select.cc:246
#10 0x081f3c21 in mysql_execute_command(THD*) (thd=0x8da6188) at sql_parse.cc:2487
#11 0x081fbdc1 in mysql_parse(THD*, char*, unsigned) (thd=0x8da6188, inBuf=0x8dd1418 "SELECT ename,deptno FROM emp_testview GROUP BY deptno HAVING ename in ('rita','scott')", length=86) at sql_parse.cc:5507
#12 0x081fc7e1 in dispatch_command(enum_server_command, THD*, char*, unsigned) (command=COM_QUERY, thd=0x8da6188, packet=0x8dc93b9 "SELECT ename,deptno FROM emp_testview GROUP BY deptno HAVING ename in ('rita','scott')", packet_length=87) at sql_parse.cc:1685
#13 0x081fdd53 in do_command(THD*) (thd=0x8da6188) at sql_parse.cc:1486
#14 0x081fe147 in handle_one_connection (arg=0x8da6188) at sql_parse.cc:1137
#15 0x49da8b80 in start_thread () from /lib/libpthread.so.0
#16 0x49c199ce in clone () from /lib/libc.so.6

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 bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Already fixed changeset. See below test with 5.0.14 server

miguel@hegel:~/dbs/mysql-5.0> bk changes | head
ChangeSet@1.2006.1.1, 2005-09-28 19:54:55+02:00, pekka@mysql.com
  ndb - printSchemaFile: more checks and options

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.13-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `emp` (
    ->   `EMPNO` int(4) unsigned NOT NULL auto_increment,
    ->   `ENAME` varchar(10) default NULL,
    ->   `JOB` varchar(9) default NULL,
    ->   `MGR` int(4) unsigned default NULL,
    ->   `HIREDATE` date default NULL,
    ->   `SAL` double(7,2) unsigned default NULL,
    ->   `COMM` double(7,2) unsigned default NULL,
    ->   `DEPTNO` int(2) unsigned NOT NULL,
    ->   PRIMARY KEY  (`EMPNO`),
    ->   KEY `EMP_FOREIGN_KEY` (`DEPTNO`),
    ->   KEY `SELF_FOREIGN_KEY` (`MGR`)
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> CREATE TABLE `dept` (
    ->   `deptno` int(2) unsigned NOT NULL auto_increment,
    ->   `DNAME` varchar(13) default NULL,
    ->   `LOC` varchar(14) NOT NULL,
    ->   PRIMARY KEY  (`deptno`)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> CREATE VIEW emp_testview AS SELECT * FROM emp;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT ename,deptno FROM emp_testview GROUP BY deptno HAVING ename in
    -> ('rita','scott');
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.14-rc-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `emp` (
    ->   `EMPNO` int(4) unsigned NOT NULL auto_increment,
    ->   `ENAME` varchar(10) default NULL,
    ->   `JOB` varchar(9) default NULL,
    ->   `MGR` int(4) unsigned default NULL,
    ->   `HIREDATE` date default NULL,
    ->   `SAL` double(7,2) unsigned default NULL,
    ->   `COMM` double(7,2) unsigned default NULL,
    ->   `DEPTNO` int(2) unsigned NOT NULL,
    ->   PRIMARY KEY  (`EMPNO`),
    ->   KEY `EMP_FOREIGN_KEY` (`DEPTNO`),
    ->   KEY `SELF_FOREIGN_KEY` (`MGR`)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql>
mysql> CREATE TABLE `dept` (
    ->   `deptno` int(2) unsigned NOT NULL auto_increment,
    ->   `DNAME` varchar(13) default NULL,
    ->   `LOC` varchar(14) NOT NULL,
    ->   PRIMARY KEY  (`deptno`)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> CREATE VIEW emp_testview AS SELECT * FROM emp;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT ename,deptno FROM emp_testview GROUP BY deptno HAVING ename in
    -> ('rita','scott');
Empty set (0.01 sec)

mysql>