[MDEV-6289] Unexpected results when querying information_schema Created: 2014-06-02  Updated: 2014-07-24  Resolved: 2014-07-23

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.3.12, 5.5.38, 10.0.11
Fix Version/s: 5.5.39, 10.0.13

Type: Bug Priority: Major
Reporter: Marten Jacobs Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: upstream
Environment:

Arch Linux (up-to-date 2014/06/02) under Virtuozzo. Issue occurs when connected through ssh tunnel with Sequel Pro and when using mysqli in php (at least).


 Description   

This issue is similar (but not exactly the same) to an issue I've reported earlier under MDEV-4335.

I'm using information_schema.SCHEMATA to make sure that a database exists before switching to it in my PHP app.

The issue:

The following query unexpectedly returns an empty result set on this version (I've had no issues on previous versions, except the one in the issue I've mentioned before):

SELECT `id`, `db`, `appv`, `group`, `name` FROM `web_dbs` WHERE `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;

This is unexpected, because the following query returns multiple rows, while it should be more restrictive:

SELECT `id`, `db`, `appv`, `group`, `name` FROM `web_dbs` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username` = '[REDACTED]') AND `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;

Upon further research I found out that removing the ORDER BY clause from the first query helps, so for now I will change the first query into the following, which does work (but will probably have decreased performance):

SELECT * FROM (SELECT `id`, `db`, `appv`, `group`, `name` FROM `web_dbs` WHERE `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA)) tbl ORDER BY `db`;



 Comments   
Comment by Elena Stepanova [ 2014-06-08 ]

Hi Marten,

I presume the structure of `web_dbs` is the same as in MDEV-4335?
Could you please provide the same info as you did in MDEV-4335 – EXPLAIN EXTENDED followed by SHOW WARNINGS, and if possible the [censored] data that web_dbs and I_S.SCHEMATA contain?

Thanks

Comment by Marten Jacobs [ 2014-06-08 ]

Dear Elena,

After doing some testing, I found that other tables did seem to work. I tried to isolate the problem, and was able to find it had something to do with the unique key on the db column. To test this, I created two tables with these commands:

DROP TABLE IF EXISTS `test1`;
 
CREATE TABLE `test1` (
 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  `db` varchar(254) NOT NULL DEFAULT '',
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `db` (`db`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `test1` (`db`) VALUES ('test1'),('test2'),('test3'),('test4');
 
 
 
DROP TABLE IF EXISTS `test2`;
 
CREATE TABLE `test2` (
 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  `db` varchar(254) NOT NULL DEFAULT '',
 
  PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
INSERT INTO `test2` (`db`) VALUES ('test1'),('test2'),('test3'),('test4');

After this, and making sure that the same server has at least one database named test(1-4), running

SELECT `db` FROM `test1` WHERE `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;

returns an empty set on my install, while these all return the same (correct) result:

SELECT `db` FROM `test2` WHERE `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;
 
SELECT * FROM (SELECT `db` FROM `test1` WHERE `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA)) tbl ORDER BY `db`;
 
SELECT * FROM (SELECT `db` FROM `test2` WHERE `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA)) tbl ORDER BY `db`;

The unique key obviously has some effect on the command handling, as EXPLAIN EXTENDED returns different results:

EXPLAIN EXTENDED SELECT `db` FROM `test1` WHERE `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 Using temporary; Using filesort
1 PRIMARY test1 eq_ref db db 764 information_schema. SCHEMATA. SCHEMA_NAME 1 100.00 Using where; Using index
2 MATERIALIZED SCHEMATA ALL NULL NULL NULL NULL NULL NULL   

EXPLAIN EXTENDED SELECT `db` FROM `test2` WHERE `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;

id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY test2 ALL NULL NULL NULL NULL 4 100.00 Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 195 func 1 100.00 Using where
2 MATERIALIZED SCHEMATA ALL NULL NULL NULL NULL NULL NULL  

I hope this helps you!
Regards,
Marten

Comment by Elena Stepanova [ 2014-06-08 ]

Hi Marten,

Thanks a lot for the test case. It is reproducible as described on MariaDB 5.3-10.0, and also on MySQL 5.6.
Charset UTF8 seems to be important.

Test case (same as above, just all together with schema creation):

DROP TABLE IF EXISTS `test1`;
 
CREATE TABLE `test1` (
 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  `db` varchar(254) NOT NULL DEFAULT '',
 
  PRIMARY KEY (`id`),
 
  UNIQUE KEY `db` (`db`)
 
) DEFAULT CHARSET=utf8;
 
INSERT INTO `test1` (`db`) VALUES ('test1'),('test2'),('test3'),('test4');
 
 
 
create database test1;
 
create database test2;
 
create database test3;
 
create database test4;
 
 
 
SELECT `db` FROM `test1` WHERE `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;
 
 
 
EXPLAIN EXTENDED
 
SELECT `db` FROM `test1` WHERE `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;

Comment by Elena Stepanova [ 2014-07-22 ]

Re-filed at bugs.mysql.com as http://bugs.mysql.com/bug.php?id=73357

Comment by Sergei Petrunia [ 2014-07-22 ]

Analysis:

There is something wrong going on here:

  #0  get_schema_tables_result (join=0x7fffb4988650, executed_place=PROCESSED_BY_JOIN_EXEC) at /home/psergey/dev2/5.5/sql/sql_show.cc:7801
 
  #1  0x000000000064834a in JOIN::exec (this=0x7fffb4988650) at /home/psergey/dev2/5.5/sql/sql_select.cc:2357
 
  #2  0x000000000064ad39 in mysql_select (thd=0x7fffbba3c060, rref_pointer_array=0x7fffbba3fcc8, tables=0x7fffb4845320, wild_num=0, fields=..., conds=0x7fffb4846ba8, og_num=1, order=0x7fffb4846e90, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fffb4988630, unit=0x7fffbba3f380, select_lex=0x7fffbba3fa58) at /home/psergey/dev2/5.5/sql/sql_select.cc:3090
 
  #3  0x00000000006415c0 in handle_select (thd=0x7fffbba3c060, lex=0x7fffbba3f2d0, result=0x7fffb4988630, setup_tables_done_option=0) at /home/psergey/dev2/5.5/sql/sql_select.cc:319

Here, I_S tables are pre-filled with data. In the query without ORDER BY: get_schema_tables_result() is called once, and execution inside the function reaches this call:

      if (table_list->schema_table->fill_table(thd, table_list,
 
                                               tab->select_cond))

In the query with ORDER BY, get_schema_tables_result() is called twice: the first time with executed_place=PROCESSED_BY_JOIN_EXEC, the second time with executed_place=PROCESSED_BY_CREATE_SORT_INDEX. However, fill_table() is never called.

I suspect it's not ORDER BY that is the problem, it is the join order.

Comment by Sergei Petrunia [ 2014-07-23 ]

Fix pushed into 5.5 tree. martenjacobs, thanks for taking time to report this!

Generated at Thu Feb 08 07:10:46 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.