Bug #43834 Assertion in Natural_join_column::db_name() on an I_S query
Submitted: 24 Mar 2009 15:19 Modified: 17 Jul 2009 3:11
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S1 (Critical)
Version:6.0-bzr,5.4 OS:Linux
Assigned to: Alexey Botchkov CPU Architecture:Any
Tags: regression

[24 Mar 2009 15:19] Philip Stoev
Description:
The following simple, valid and very useful I_S query

SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
LEFT JOIN INFORMATION_SCHEMA.COLUMNS
USING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
WHERE COLUMNS.TABLE_SCHEMA = 'test'
AND COLUMNS.TABLE_NAME = 't1';

crashes with the following stack trace:

#6  0x000000315a42bec9 in __assert_fail () from /lib64/libc.so.6
#7  0x0000000000737341 in Natural_join_column::db_name (this=0x40597a8) at table.cc:4034
#8  0x00000000007373b8 in Field_iterator_table_ref::get_db_name (this=0x7f41cb76ac30) at table.cc:4246
#9  0x00000000007bb87c in check_grant_all_columns (thd=0x7f41c401b708, want_access_arg=1, fields=0x7f41cb76ac30) at sql_acl.cc:4260
#10 0x00000000007265e7 in insert_fields (thd=0x7f41c401b708, context=0x7f41c401d518, db_name=0x0, table_name=0x0, it=0x7f41cb76add0, any_privileges=false)
    at sql_base.cc:6999
#11 0x00000000007273ef in setup_wild (thd=0x7f41c401b708, tables=0x3dd1e28, fields=@0x7f41c401d5d0, sum_func_list=0x4059058, wild_num=1) at sql_base.cc:6548
#12 0x00000000007731a6 in JOIN::prepare (this=0x40534a0, rref_pointer_array=0x7f41c401d6b0, tables_init=0x3dd1e28, wild_num=1, conds_init=0x3dd34c0,
    og_num=0, order_init=0x0, group_init=0x0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x7f41c401d4c8, unit_arg=0x7f41c401d060)
    at sql_select.cc:543
#13 0x000000000077486f in mysql_select (thd=0x7f41c401b708, rref_pointer_array=0x7f41c401d6b0, tables=0x3dd1e28, wild_num=1, fields=@0x7f41c401d5d0,
    conds=0x3dd34c0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2684635648, result=0x3eb0e98, unit=0x7f41c401d060,
    select_lex=0x7f41c401d4c8) at sql_select.cc:3040
#14 0x000000000077a0d1 in handle_select (thd=0x7f41c401b708, lex=0x7f41c401cfc0, result=0x3eb0e98, setup_tables_done_option=0) at sql_select.cc:314
#15 0x00000000006d67db in execute_sqlcom_select (thd=0x7f41c401b708, all_tables=0x3dd1e28) at sql_parse.cc:4768
#16 0x00000000006d7ddf in mysql_execute_command (thd=0x7f41c401b708) at sql_parse.cc:2069
#17 0x00000000006e01a7 in mysql_parse (thd=0x7f41c401b708,
    inBuf=0x3dd1890 "SELECT *\nFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE\nLEFT JOIN INFORMATION_SCHEMA.COLUMNS\nUSING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)\nWHERE COLUMNS.TABLE_SCHEMA = 'test'\nAND COLUMNS.TABLE_NAME = 't1'", length=198, found_semicolon=0x7f41cb76cf00) at sql_parse.cc:5783
#18 0x00000000006e0d92 in dispatch_command (command=COM_QUERY, thd=0x7f41c401b708,
    packet=0x7f41c41a9a39 "SELECT *\nFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE\nLEFT JOIN INFORMATION_SCHEMA.COLUMNS\nUSING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)\nWHERE COLUMNS.TABLE_SCHEMA = 'test'\nAND COLUMNS.TABLE_NAME = 't1'", packet_length=198) at sql_parse.cc:1009
#19 0x00000000006e22bb in do_command (thd=0x7f41c401b708) at sql_parse.cc:691
#20 0x00000000006d0185 in handle_one_connection (arg=0x7f41c401b708) at sql_connect.cc:1146
#21 0x000000315b0073da in start_thread () from /lib64/libpthread.so.0
#22 0x000000315a4e627d in clone () from /lib64/libc.so.6

mysqld: table.cc:4037: const char* Natural_join_column::db_name(): Assertion `!strcmp(table_ref->db, table_ref->table->s->db.str) || (table_ref->schema_table && table_ref->table->s->db.str[0] == 0)' failed.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`pk`)
);

SELECT *
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
LEFT JOIN INFORMATION_SCHEMA.COLUMNS
USING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
WHERE COLUMNS.TABLE_SCHEMA = 'test'
AND COLUMNS.TABLE_NAME = 't1';

DROP TABLE t1;
[24 Mar 2009 15:24] Philip Stoev
Workaround is to list all desired result set columns individually, like this:

SELECT CONSTRAINT_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
LEFT JOIN INFORMATION_SCHEMA.COLUMNS
USING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
WHERE COLUMNS.TABLE_SCHEMA = 'test'
AND COLUMNS.TABLE_NAME = 't1';
[24 Mar 2009 17:06] MySQL Verification Team
I couldn't repeat too on Windows 64-bit server, debug build.
[24 Mar 2009 20:30] Sveta Smirnova
Thank you for the report.

Verified as described. Versions 6.0.8, 5.0 and 5.1 are not affected.

Backtrace:

Thread 1 (process 14492):
#0  0x002ce402 in __kernel_vsyscall ()
#1  0x0046264f in pthread_kill () from /lib/libpthread.so.0
#2  0x088240fb in my_write_core (sig=6) at stacktrace.c:309
#3  0x082bf3a0 in handle_segfault (sig=6) at mysqld.cc:2689
#4  <signal handler called>
#5  0x002ce402 in __kernel_vsyscall ()
#6  0x00314f90 in raise () from /lib/libc.so.6
#7  0x00316678 in abort () from /lib/libc.so.6
#8  0x0030e269 in __assert_fail () from /lib/libc.so.6
#9  0x08337cd1 in Natural_join_column::db_name (this=0x9f45a50) at table.cc:4034
#10 0x08338464 in Field_iterator_table_ref::get_db_name (this=0xad1da934) at table.cc:4246
#11 0x083b828b in check_grant_all_columns (thd=0x9ec7d88, want_access_arg=1, fields=0xad1da934) at sql_acl.cc:4260
#12 0x08327b4a in insert_fields (thd=0x9ec7d88, context=0x9ec8ff0, db_name=0x0, table_name=0x0, it=0xad1daa18, any_privileges=false) at sql_base.cc:6999
#13 0x0832831f in setup_wild (thd=0x9ec7d88, tables=0x9e477c0, fields=@0x9ec9050, sum_func_list=0x9f452f8, wild_num=1) at sql_base.cc:6548
#14 0x083655d3 in JOIN::prepare (this=0x9f405b0, rref_pointer_array=0x9ec90c0, tables_init=0x9e477c0, wild_num=1, conds_init=0x9e48788, og_num=0, order_init=0x0, group_init=0x0, having_init=0x0, 
    proc_param_init=0x0, select_lex_arg=0x9ec8fbc, unit_arg=0x9ec8d24) at sql_select.cc:543
#15 0x0836f261 in mysql_select (thd=0x9ec7d88, rref_pointer_array=0x9ec90c0, tables=0x9e477c0, wild_num=1, fields=@0x9ec9050, conds=0x9e48788, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, 
    select_options=2684635648, result=0x9e89d40, unit=0x9ec8d24, select_lex=0x9ec8fbc) at sql_select.cc:3040
#16 0x0836f62e in handle_select (thd=0x9ec7d88, lex=0x9ec8cc8, result=0x9e89d40, setup_tables_done_option=0) at sql_select.cc:314
#17 0x082ce4dd in execute_sqlcom_select (thd=0x9ec7d88, all_tables=0x9e477c0) at sql_parse.cc:4768
#18 0x082d4017 in mysql_execute_command (thd=0x9ec7d88) at sql_parse.cc:2069
#19 0x082dced3 in mysql_parse (thd=0x9ec7d88, 
    inBuf=0x9e472c8 "SELECT *\nFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE\nLEFT JOIN INFORMATION_SCHEMA.COLUMNS\nUSING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)\nWHERE COLUMNS.TABLE_SCHEMA = 'test'\nAND COLUMNS.TABLE_NAME = 't1'", length=198, found_semicolon=0xad1dbf20) at sql_parse.cc:5783
#20 0x082dd915 in dispatch_command (command=COM_QUERY, thd=0x9ec7d88, 
    packet=0x9e60641 "SELECT *\nFROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE\nLEFT JOIN INFORMATION_SCHEMA.COLUMNS\nUSING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)\nWHERE COLUMNS.TABLE_SCHEMA = 'test'\nAND COLUMNS.TABLE_NAME = 't1'", packet_length=198) at sql_parse.cc:1009
#21 0x082dec79 in do_command (thd=0x9ec7d88) at sql_parse.cc:691
#22 0x082cca67 in handle_one_connection (arg=0x9ec7d88) at sql_connect.cc:1146
#23 0x0045fbd4 in start_thread () from /lib/libpthread.so.0
#24 0x003b74fe in clone () from /lib/libc.so.6
[21 May 2009 13:49] 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/74705

3311 Alexey Botchkov	2009-05-21
      Bug#43834      Assertion in Natural_join_column::db_name() on an I_S query
         starting with the 6.0 we have 'information_schema' as the db name for
         the information schema tables. So that we need to use is_schema() test
         to check.
      
      per-file comments:
        mysql-test/t/information_schema.test
      Bug#43834      Assertion in Natural_join_column::db_name() on an I_S query
          test case added. Test result should be modified later when this test
          is enabled.
      
        sql/table.cc
      Bug#43834      Assertion in Natural_join_column::db_name() on an I_S query
          use is_schema_db() to check for proper schema table.
[23 Jun 2009 14:14] 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/76927

3311 Alexey Botchkov	2009-06-23
      Bug#43834      Assertion in Natural_join_column::db_name() on an I_S query
         starting with the 6.0 we have 'information_schema' as the db name for
         the information schema tables. So that we need to use is_schema() test
         to check.
      
      per-file comments:
        mysql-test/t/information_schema.test
      Bug#43834      Assertion in Natural_join_column::db_name() on an I_S query
          test case added. Test result should be modified later when this test
          is enabled.
      
        sql/table.cc
      Bug#43834      Assertion in Natural_join_column::db_name() on an I_S query
          use is_schema_db() to check for proper schema table.
[24 Jun 2009 11:09] 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/77019

2804 Alexey Botchkov	2009-06-24
      Bug#43834      Assertion in Natural_join_column::db_name() on an I_S query
          starting with the 6.0 we have 'information_schema' as the db name for
          the information schema tables. So that we need to use is_schema_db() test
          to check.
       
      per-file comments:
        mysql-test/t/information_schema.test
      Bug#43834      Assertion in Natural_join_column::db_name() on an I_S query
          test case added. Test result should be modified later when this test
          is enabled.
      
        sql/table.cc
      Bug#43834      Assertion in Natural_join_column::db_name() on an I_S query
          use is_schema_db() to check for proper schema table.
[24 Jun 2009 11:10] Alexey Botchkov
pushed into mysql-azalea-bugfixing tree
[30 Jun 2009 18:25] 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/77562

2819 Alexander Nozdrin	2009-06-30
      Fix result file (a follow up on Bug#43834).
[3 Jul 2009 6:13] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version source revid:alik@sun.com-20090630182536-708ka7w2zrgz7k08) (merge vers: 5.4.4-alpha) (pib:11)
[9 Jul 2009 7:35] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version source revid:alik@sun.com-20090630182536-708ka7w2zrgz7k08) (merge vers: 5.4.4-alpha) (pib:11)
[17 Jul 2009 3:11] Paul Dubois
Noted in 5.4.4 changelogs.

A natural join of INFORMATION_SCHEMA tables could cause an assertion
failure.
[12 Mar 2010 15:58] Paul Dubois
Noted in 5.5.3 changelog.