Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5.38, 10.0.11
-
None
-
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`; |
Attachments
Issue Links
Activity
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
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; |
Re-filed at bugs.mysql.com as http://bugs.mysql.com/bug.php?id=73357
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.
Fix pushed into 5.5 tree. martenjacobs, thanks for taking time to report this!
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