MySQL Bugs: #6276: A SELECT that does a NATURAL JOIN of view and table crashes server

Bug #6276	A SELECT that does a NATURAL JOIN of view and table crashes server
Submitted:	27 Oct 2004 8:51	Modified:	25 Aug 2005 11:42
Reporter:	Sergey Gluhov
Status:	Closed
Category:	Server	Severity:	S2 (Serious)
Version:	5.0	OS:	Linux (Linux)
Assigned to:	Timour Katchaounov	Target Version:

Description:
The following SELECT crashes server:
select * from v2 natural left join a2;

where v2 - view, a2 - table

How to repeat:
create table a1(a1 int);
insert into a1 values(1),(2),(3),(4),(5),(6);
create table a2(a2 int);
insert into a2 values(1),(2),(3),(4),(5),(6);
create view v2 (c) as select a1 from a1;
select * from v2 natural left join a2;
drop table v2, a1, a2;

The same problem with NATURAL RIGHT.

Verified with 5.0.2-alpha-debug-log

Back trace:
(gdb) bt
#0  0x081b4b23 in make_cond_for_table (cond=0x0, tables=13835058055282163713,
used_table=1)
    at sql_select.cc:9925
#1  0x081ab36e in make_join_select (join=0x87b6780, select=0x87bd5e0, cond=0x0)
    at sql_select.cc:5395
#2  0x081a1774 in JOIN::optimize (this=0x87b6780) at sql_select.cc:705
#3  0x081a49f4 in mysql_select (thd=0x87a8be0, rref_pointer_array=0x87a8ef0,
tables=0x87b59d8,
    wild_num=1, fields=@0x87a8e3c, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0,
    proc_param=0x0, select_options=8669696, result=0x87b6770, unit=0x87a8c24,
    select_lex=0x87a8dd0) at sql_select.cc:2015
#4  0x081a05ea in handle_select (thd=0x87a8be0, lex=0x87a8c18, result=0x87b6770)
    at sql_select.cc:229
#5  0x08175e2d in mysql_execute_command (thd=0x87a8be0) at sql_parse.cc:2069
#6  0x0817bc09 in mysql_parse (thd=0x87a8be0,
    inBuf=0x87b58d8 "select * from v2 natural left join a2", length=37) at
sql_parse.cc:4626
#7  0x08174b02 in dispatch_command (command=COM_QUERY, thd=0x87a8be0,
    packet=0x87a04f9 "select * from v2 natural left join a2", packet_length=38)
    at sql_parse.cc:1518
#8  0x081743e7 in do_command (thd=0x87a8be0) at sql_parse.cc:1298
#9  0x0817394c in handle_one_connection (arg=0x87a8be0) at sql_parse.cc:1034
#10 0xb7e4714b in pthread_start_thread () from /lib/libpthread.so.0
#11 0xb7e471df in pthread_start_thread_event () from /lib/libpthread.so.0
#12 0xb7d7a50a in clone () from /lib/libc.so.6

The problem is that natural outer join turns into outer join with no ON condition when
joined tables don't have any common fields. 
This can be reproduced without views: 

select * from a1 natural left join a2;

Initial fix rejected, working on a new one

This bug requires considerable work to transform such queries into inner joins.
The bug will be fixed when such transformation is implemented in an upcoming
MySQL version.

See also Bug#9978.

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/internals/28701

Pushed into 5.0.12.

The fix is based on WL#2486 - natural and using join according to SQL:2003.

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/internals/28740

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:

Fix documented in 5.0.12 changelog. Closed.