[MDEV-4335] Unexpected results when selecting on information_schema Created: 2013-03-27  Updated: 2013-03-29  Resolved: 2013-03-29

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.1, 5.5.30, 5.3.12
Fix Version/s: 10.0.2, 5.5.31, 5.3.13

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

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


 Description   

I have a metabase which contains references to databases on a user group basis (so every user has a group column, and databases are linked to groups). In my php script, I used to be able to get the allowed tables for a user (under MySQL 5.5) with the following query:

SELECT * FROM `web_dbs` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="some user") AND 
	`db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;

The last check (to the information_schema database) was only included to make sure no non-existent databases are presented to the user. After upgrading to MariaDB 5.5.30, this is not working anymore, it simply returns an empty result set.

Currently, I've removed the extra check on the existence of the database: 

SELECT * FROM `web_dbs` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="some user") ORDER BY `db` DESC;

which works, but does risk showing non-existent databases.
I've also tried to work with an inner join:

SELECT * FROM `web_dbs` INNER JOIN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) AS scm ON `web_dbs`.`db`=scm.`SCHEMA_NAME` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="some user")  ORDER BY `web_dbs`.`db` DESC;

which also works, but is semantically less attractive to me.

I expected MariaDB to work with the same query that worked under MySQL 5.5, so I think this should be fixed.

 Comments   
Comment by Elena Stepanova [ 2013-03-27 ]

Hi Marten,

You mentioned in the Environment field that the issue occurs with ssh + php. Does it mean that it does not occur if you are using the plain MySQL client, or that you didn't try it? In the latter case, would it be possible for you try to run the same problematic query from the client to see whether it works?

Could you please also provide the structure of web_dbs table, the value of optimizer_switch and EXPLAIN for the query in question? In other words, the output of

SHOW CREATE TABLE web_dbs;
SELECT @@optimizer_switch;
EXPLAIN EXTENDED <bad query>;
SHOW WARNINGS;

Thanks.

Comment by Marten Jacobs [ 2013-03-27 ]

It means I didn't try. I have now and it still returns an empty result set. Here is my transscript from running it locally on the server (with some values censored):

[root@vps-vanmarten ~]# mysql -u root -p --database=inname
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 178
Server version: 5.5.30-MariaDB-log Source distribution

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [inname]> SELECT * FROM `web_dbs` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="CENSORED") AND `db` IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) ORDER BY `db` DESC;
Empty set (0.00 sec)

MariaDB [inname]> SELECT * FROM `web_dbs` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="CENSORED") ORDER BY `db` DESC;
-------------------------------------

id db appv group name

-------------------------------------

1 CENSORED_2013 1 CENSORED 2013
2 CENSORED_2012 1 CENSORED 2012

-------------------------------------
2 rows in set (0.01 sec)

MariaDB [inname]> SELECT * FROM `web_dbs` INNER JOIN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA) AS scm ON `web_dbs`.`db`=scm.`SCHEMA_NAME` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="CENSORED") ORDER BY `web_dbs`.`db` DESC;
------------------------------------------------------+

id db appv group name SCHEMA_NAME

------------------------------------------------------+

1 CENSORED_2013 1 CENSORED 2013 CENSORED_2013
2 CENSORED_2012 1 CENSORED 2012 CENSORED_2012

------------------------------------------------------+
2 rows in set (0.00 sec)

EDIT: I tried to improve formatting again, and failed again

Comment by Marten Jacobs [ 2013-03-27 ]

Here's the output on the other questions you asked:

SHOW CREATE TABLE web_dbs;

----------------------------------------------------------------------+

Table Create Table

----------------------------------------------------------------------+

web_dbs CREATE TABLE `web_dbs` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`db` varchar(254) NOT NULL DEFAULT '',
`appv` varchar(254) NOT NULL DEFAULT '',
`group` varchar(30) NOT NULL DEFAULT '',
`name` varchar(50) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
UNIQUE KEY `db` (`db`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

-----------------------------------------------------------------------+

SELECT @@optimizer_switch;

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off

EXPLAIN EXTENDED SELECT * FROM `web_dbs` WHERE `group` IN (SELECT `group` FROM `web_users` WHERE `username`="CENSORED") AND `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 web_users const username username 602 const 1 100.00 Using filesort
1 PRIMARY web_dbs ALL db NULL NULL NULL 2 100.00 Using where
1 PRIMARY <subquery3> eq_ref distinct_key distinct_key 195 func 1 100.00 Using where
3 MATERIALIZED SCHEMATA ALL NULL NULL NULL NULL NULL NULL  

--------------------------------------------------------------------------------------------------
4 rows in set, 1 warning (0.00 sec)

SHOW WARNINGS;

Level Code Message
Note 1003 select `inname`.`web_dbs`.`id` AS `id`,`inname`.`web_dbs`.`db` AS `db`,`inname`.`web_dbs`.`appv` AS `appv`,`inname`.`web_dbs`.`group` AS `group`,`inname`.`web_dbs`.`name` AS `name` from `inname`.`web_users` semi join (`information_schema`.`SCHEMATA`) join `inname`.`web_dbs` where ((`inname`.`web_dbs`.`group` = 'CENSORED') and 1 and (`inname`.`web_dbs`.`db` = `information_schema`.`SCHEMATA`.`SCHEMA_NAME`)) order by `inname`.`web_dbs`.`db` desc
Comment by Elena Stepanova [ 2013-03-27 ]

@Marten:

Thank you, I'm able to reproduce the problem.
If you need a temporary workaround till the bug is fixed, you can try to run
SET optimizer_switch='semijoin=off';
in the session before the query, or add
optimizer_switch=semijoin=off
to your cnf file.

Comment by Elena Stepanova [ 2013-03-27 ]

@Sergei:

Minimal optimizer_switch: 'in_to_exists=on,semijoin=on

Test case:

SET optimizer_switch = 'in_to_exists=on,semijoin=on';

CREATE TABLE t1 (db VARCHAR(64) DEFAULT NULL);
INSERT INTO t1 VALUES ('mysql'),('information_schema');
SELECT * FROM t1 WHERE db IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA);

  1. End of test case

Also reproducible on MySQL 5.6.10.

Comment by Sergei Petrunia [ 2013-03-29 ]

EXPLAINs:

MariaDB [j61]> explain SELECT * FROM t1 WHERE db IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA);
--------------------------------------------------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

--------------------------------------------------------------------------------------------------------------------------+

1 PRIMARY t1 ALL NULL NULL NULL NULL 2  
1 PRIMARY SCHEMATA ALL NULL NULL NULL NULL NULL Using where; FirstMatch(t1); Using join buffer (flat, BNL join)

--------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Comment by Sergei Petrunia [ 2013-03-29 ]

Join buffer is not a problem:

MariaDB [j61]> set join_cache_level=0;
Query OK, 0 rows affected (0.01 sec)

MariaDB [j61]> explain SELECT * FROM t1 WHERE db IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA);
--------------------------------------------------------------------------------------+

id select_type table type possible_keys key key_len ref rows Extra

--------------------------------------------------------------------------------------+

1 PRIMARY t1 ALL NULL NULL NULL NULL 2  
1 PRIMARY SCHEMATA ALL NULL NULL NULL NULL NULL Using where; FirstMatch(t1)

--------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

MariaDB [j61]> SELECT * FROM t1 WHERE db IN (SELECT `SCHEMA_NAME` FROM information_schema.SCHEMATA);
Empty set (0.00 sec)

Comment by Sergei Petrunia [ 2013-03-29 ]

Pushed to 5.3

Generated at Thu Feb 08 06:55:38 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.