MySQL Bugs: #31633: Information schema = NULL queries crash the server

Bug #31633	Information schema = NULL queries crash the server
Submitted:	16 Oct 2007 10:17	Modified:	30 Oct 2007 0:54
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S1 (Critical)
Version:	5.1.22-RC1, 5.1, 5.2 BK	OS:	Linux (Kubuntu)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	regression

Description:
Simple information_schema queries that include a WHERE expression of the form 

<column-name> = NULL

or 

<column-name> = <function-name>()

where function-name returns NULL

crash the server. 

This behaviour can be reproduced with several information_schema tables and several columns. However, it has been suggested by others that these queries do not crash a 5.1.19 server.

How to repeat:
This query has been reported to crash by several people on linux and Mac, but only when no default database is set (i.e., SCHEMA() returns NULL)

SELECT e.ENGINE, t.TABLE_NAME
FROM information_schema.TABLES t
INNER JOIN information_schema.ENGINES e
ON t.ENGINE = e.ENGINE
WHERE t.TABLE_SCHEMA = SCHEMA()

By elimination it was found that the following queries crash the 5.1.22 server:

select * from information_schema.columns where table_schema = NULL;
select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL;

select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;

select * from `information_schema`.`PARTITIONS` where `TABLE_SCHEMA` = NULL;
select * from `information_schema`.`PARTITIONS` where `TABLE_NAME` = NULL;

select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL;
select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL;

select * from information_schema.schemata where schema_name = NULL;

select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL;
select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL;

select * from information_schema.tables where table_schema = NULL;
select * from information_schema.tables where table_catalog = NULL;
select * from information_schema.tables where table_name = NULL;
select * from `information_schema`.`TABLES` where `TABLE_TYPE` = NULL;
select * from `information_schema`.`TABLES` where `ENGINE` = NULL;
select * from `information_schema`.`TABLES` where `VERSION` = NULL;
select * from `information_schema`.`TABLES` where `ROW_FORMAT` = NULL;
select * from `information_schema`.`TABLES` where `TABLE_ROWS` = NULL;
select * from `information_schema`.`TABLES` where `AVG_ROW_LENGTH` = NULL;
select * from `information_schema`.`TABLES` where `DATA_LENGTH` = NULL;
select * from `information_schema`.`TABLES` where `MAX_DATA_LENGTH` = NULL;
select * from `information_schema`.`TABLES` where `INDEX_LENGTH` = NULL;
select * from `information_schema`.`TABLES` where `DATA_FREE` = NULL;
select * from `information_schema`.`TABLES` where `AUTO_INCREMENT` = NULL;
select * from `information_schema`.`TABLES` where `CREATE_TIME` = NULL;
select * from `information_schema`.`TABLES` where `UPDATE_TIME` = NULL;
select * from `information_schema`.`TABLES` where `CHECK_TIME` = NULL;
select * from `information_schema`.`TABLES` where `TABLE_COLLATION` = NULL;
select * from `information_schema`.`TABLES` where `CHECKSUM` = NULL;
select * from `information_schema`.`TABLES` where `CREATE_OPTIONS` = NULL;
select * from `information_schema`.`TABLES` where `TABLE_COMMENT` = NULL;

select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;
select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;

select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_SCHEMA` = NULL;
select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_TABLE` = NULL;

select * from `information_schema`.`VIEWS` where `TABLE_SCHEMA` = NULL;
select * from `information_schema`.`VIEWS` where `TABLE_NAME` = NULL;

Suggested fix:
I suspect this might have to do with the recent patch to improve the I_S performance as all of the TABLES columns are affected, and in general TABLE_SCHEMA and TABLE_NAME columns.

Thank you for the report.

Verified as described, except with queries

select * from information_schema.tables where table_catalog = NULL;
select * from `information_schema`.`TABLES` where `TABLE_TYPE` = NULL;
select * from `information_schema`.`TABLES` where `ENGINE` = NULL;
select * from `information_schema`.`TABLES` where `VERSION` = NULL;
select * from `information_schema`.`TABLES` where `ROW_FORMAT` = NULL;
select * from `information_schema`.`TABLES` where `TABLE_ROWS` = NULL;
select * from `information_schema`.`TABLES` where `AVG_ROW_LENGTH` = NULL;
select * from `information_schema`.`TABLES` where `DATA_LENGTH` = NULL;
select * from `information_schema`.`TABLES` where `MAX_DATA_LENGTH` = NULL;
select * from `information_schema`.`TABLES` where `INDEX_LENGTH` = NULL;
select * from `information_schema`.`TABLES` where `DATA_FREE` = NULL;
select * from `information_schema`.`TABLES` where `AUTO_INCREMENT` = NULL;
select * from `information_schema`.`TABLES` where `CREATE_TIME` = NULL;
select * from `information_schema`.`TABLES` where `UPDATE_TIME` = NULL;
select * from `information_schema`.`TABLES` where `CHECK_TIME` = NULL;
select * from `information_schema`.`TABLES` where `TABLE_COLLATION` = NULL;
select * from `information_schema`.`TABLES` where `CHECKSUM` = NULL;
select * from `information_schema`.`TABLES` where `CREATE_OPTIONS` = NULL;
select * from `information_schema`.`TABLES` where `TABLE_COMMENT` = NULL;

MySQL server doesn't fails for me.

Seems to be introduced by:

ChangeSet@1.2528.99.1, 2007-08-03 03:14:05+05:00, gluh@mysql.com +18 -0
  WL#3732 Information schema optimization

Passing 

Backtrace:

#0  0x081bdbaa in String::length() const (this=0xb3095968) at sql_string.h:90
#1  0x083c9533 in get_lookup_value(THD*, Item_func*, TABLE_LIST*, st_lookup_field_values*) (thd=0xb30959a8, item_func=0x83c96a7, table=0x8fb3f50, lookup_field_vals=0x8fc8b10) at sql_show.cc:2330
#2  0x083c96a7 in calc_lookup_values_from_cond(THD*, Item*, TABLE_LIST*, st_lookup_field_values*) (thd=0xb30959e8, cond=0x83c9819, table=0x8fb3f50, lookup_field_vals=0x8fc8b10) at sql_show.cc:2375
#3  0x083c9819 in get_lookup_field_values(THD*, Item*, TABLE_LIST*, st_lookup_field_values*) (thd=0xb3095ce8, cond=0x83da2c3, tables=0x8fb3f50, lookup_field_values=0x8fc8b10) at sql_show.cc:2525
#4  0x083da2c3 in get_all_tables(THD*, TABLE_LIST*, Item*) (thd=0xb3095d48, tables=0x83c8ed9, cond=0x8fb3f50) at sql_show.cc:3116
#5  0x083c8ed9 in get_schema_tables_result(JOIN*, enum_schema_table_state) (join=0xb3095e68, executed_place=137426630) at sql_show.cc:5838
#6  0x0830f6c6 in JOIN::exec() (this=0xb3095ee8) at sql_select.cc:1670
#7  0x0830c7db in mysql_select(THD*, Item***, TABLE_LIST*, unsigned, List<Item>&, Item*, unsigned, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*) (thd=0xb3095f88, rref_pointer_array=0x831148d, tables=0x8fb3f50, wild_num=150688012, fields=@0x8fc8848, conds=0x1, og_num=150687912, order=0x8fc8b10, group=0x0, having=0x0, proc_param=0x0, select_options=150768640, result=0xa0044a00, unit=0x0, select_lex=0x8fc93c8) at sql_select.cc:2313
#8  0x0831148d in handle_select(THD*, st_lex*, select_result*, unsigned long) (thd=0xb30963f8, lex=0x8290248, result=0x8fb3f50, setup_tables_done_option=150687056) at sql_select.cc:263
#9  0x08290248 in execute_sqlcom_select (thd=0x8fc93c8, all_tables=0x8fc93c8) at sql_parse.cc:4549
#10 0x08291b3a in mysql_execute_command(THD*) (thd=0xb3096d48) at sql_parse.cc:1886
#11 0x0829a531 in mysql_parse(THD*, char const*, unsigned, char const**) (thd=0xb3097348, inBuf=0x829b05c "éQ\001", length=150683472, found_semicolon=0x8fc86a0) at sql_parse.cc:5462
#12 0x0829b05c in dispatch_command(enum_server_command, THD*, char*, unsigned) (command=3003741080, thd=0x829c1cb, packet=0x3 <Address 0x3 out of bounds>, packet_length=150683472) at sql_parse.cc:958
#13 0x0829c1cb in do_command(THD*) (thd=0xb30973c8) at sql_parse.cc:717
#14 0x0828a003 in handle_one_connection (arg=0xb30974b8) at sql_connect.cc:1099
#15 0x4298844b in start_thread () from /lib/libpthread.so.0
#16 0x4279380e in clone () from /lib/libc.so.6

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

ChangeSet@1.2575, 2007-10-19 11:47:37+05:00, gluh@mysql.com +3 -0
  Bug#31633 Information schema = NULL queries crash the server
  added correct handling of NULL values for lookup fields

Review feedback provided via email, ok to push after addressed.

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

ChangeSet@1.2592, 2007-10-23 13:20:51+05:00, gluh@mysql.com +3 -0
  Bug#31633 Information schema = NULL queries crash the server
  added correct handling of NULL values for lookup fields

Pushed into 5.1.23-beta

Pushed into 6.0.4-alpha

Noted in 5.1.23, 6.0.4 changelogs.

Queries that include a comparison of an INFORMATION_SCHEMA table
column to NULL caused a server crash.