MySQL Bugs: #12977: Crash referencing a joined column by table when a USING follows an ON

Bug #12977	Crash referencing a joined column by table when a USING follows an ON
Submitted:	4 Sep 2005 12:29	Modified:	8 Sep 2005 19:40
Reporter:	Dan Stillman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.0.12-beta-standard/BK source	OS:	Linux (Gentoo Linux)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
In a SELECT statement with two (or more) JOINs, the first with ON and the second with USING, referencing a column from the first join (i.e. in the first or second table) explicitly by table reference crashes the server.

How to repeat:
CREATE TABLE A (`id` TINYINT);
CREATE TABLE B (`id` TINYINT);
CREATE TABLE C (`id` TINYINT);
INSERT INTO A VALUES (1),(2),(3);
INSERT INTO B VALUES (2);
INSERT INTO C VALUES (3);

SELECT A.id,C.id FROM A JOIN B ON (B.id=A.id) LEFT JOIN C USING (id);
ERROR 2006 (HY000): MySQL server has gone away

The following (with a non-existent column name) crashes as well:

SELECT A.id,C.id FROM A JOIN B ON (B.notacolumn=A.id) LEFT JOIN C USING (id);
ERROR 2006 (HY000): MySQL server has gone away

The following (with no explicit table reference on A.id) works:

SELECT id,C.id FROM A JOIN B ON (B.id=A.id) LEFT JOIN C USING (id);
+------+------+
| id   | id   |
+------+------+
|    2 | NULL |
+------+------+

Backtrace

Attachment: stack_trace.txt (text/plain), 1.01 KiB.

Thank you for the bug report.

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.13-beta-debug

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

mysql> CREATE TABLE A (`id` TINYINT);
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE B (`id` TINYINT);
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE C (`id` TINYINT);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO A VALUES (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO B VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO C VALUES (3);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT A.id,C.id FROM A JOIN B ON (B.id=A.id) LEFT JOIN C USING (id);
ERROR 2013 (HY000): Lost connection to MySQL server during query

050904 10:47:24 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.13-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1132456880 (LWP 6474)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1132456880 (LWP 6474)]
0x40253348 in strcmp () from /lib/tls/libc.so.6
(gdb) backtrace full
#0  0x40253348 in strcmp () from /lib/tls/libc.so.6
No symbol table info available.
#1  0x08596be9 in my_strcasecmp_bin (cs=0x879adc0, s=0x0, t=0x8e54280 "A") at ctype-bin.c:224
No locals.
#2  0x08215bb0 in find_field_in_table_ref (thd=0x8e28ca0, table_list=0x8e549c0, name=0x8e54288 "id", item_name=0x8e54288 "id", 
    table_name=0x8e54280 "A", db_name=0x0, length=2, ref=0x8e5434c, check_grants_table=false, check_grants_view=false, allow_rowid=true, 
    cached_field_index_ptr=0x8e542f0, register_tree_change=true, actual_table=0x437fd888) at sql_base.cc:2887
        fld = (class Field *) 0x437fd6e8
        _db_func_ = 0x437febb0 "°ë\177C\\<á\b°ë\177C\001"
        _db_file_ = 0x8e54d38 ""
        _db_level_ = 0
        _db_framep_ = (char **) 0x8e19ee8
#3  0x08215d11 in find_field_in_table_ref (thd=0x8e28ca0, table_list=0x8e54d38, name=0x8e54288 "id", item_name=0x8e54288 "id", 
    table_name=0x8e54280 "A", db_name=0x0, length=2, ref=0x8e5434c, check_grants_table=false, check_grants_view=false, allow_rowid=true, 
    cached_field_index_ptr=0x8e542f0, register_tree_change=true, actual_table=0x437fd888) at sql_base.cc:2913
        it = {<base_list_iterator> = {list = 0x8e54ea0, el = 0x8e54ed0, prev = 0x8e54ec8, current = 0x8e54ed0}, <No data fields>}
        table = (TABLE_LIST *) 0x8e549c0
        fld = (class Field *) 0x0
        _db_func_ = 0x437fd798 "\001"
        _db_file_ = 0x437fd810 "(<a\bÀIå\bÀIå\bÀIå\b(<a\b"
        _db_level_ = 136409204
        _db_framep_ = (char **) 0x7fd858
<cut>

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/29446

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/29481

Dan,

the crash has been fixed, but please notice that all test queries
incorrect because they all contain an ambiguous column reference.

The inner join "A join B" produces a table with two columns with
the same name 'id'. This table serves as one of the tables joined
by "JOIN ... USING (id)". In this case the column "id" in table "C"
is ambiguous because the other join operand contains two different
columns named "id".

Fixed in 5.0.13.

Noted in 5.0.13 changelog.