Description:
Users with select privs on a database get empty result sets as follows:
As user root:
CREATE DATABASE viewdb;
GRANT SELECT ON viewdb.* TO 'view_select'@'localhost'
IDENTIFIED BY 'view_select';
GRANT CREATE VIEW, SELECT ON viewdb.* TO
'view_create'@'localhost' IDENTIFIED BY 'view_create';
USE viewdb;
CREATE TABLE initial_t14 (col1 CHAR(5),col2 CHAR(5));
INSERT INTO initial_t14 VALUES ('s1','p1'),('s1','p2'),
('s1','p3'),('s1','p4'),
('s2','p1'),('s3','p2'),
('s4','p4');
== == == ==
As user 'view_create':
USE viewdb;
CREATE VIEW initial_v53 (col1,col2)
AS SELECT col1,col2 FROM initial_t14;
== == == ==
As user 'select_view'
USE viewdb;
SELECT first.col1,first.col2 FROM initial_t14 first WHERE first.col2
IN (SELECT second.col2 FROM initial_t14 second WHERE second.col1<>first.col1);
>>> In 5.1.23 results returned:
col1 col2
s1 p1
s1 p2
s1 p4
s2 p1
s3 p2
s4 p4
>>> In 5.1.24 results returned:
(no rows returned)
explain SELECT first.col1,first.col2 FROM initial_t14 first WHERE first.col2
IN (SELECT second.col2 FROM initial_t14 second WHERE second.col1<>first.col1);
>>> In 5.1.23 results returned:
id 1
select_type PRIMARY
table first
type ALL
possible_keys NULL
key NULL
key_len NULL
ref NULL
rows 7
Extra Using where
id 2
select_type DEPENDENT SUBQUERY
table second
type ALL
possible_keys NULL
key NULL
key_len NULL
ref NULL
rows 7
Extra Using where
>>> In 5.1.24 results returned:
id 1
select_type PRIMARY
table NULL
type NULL
possible_keys NULL
key NULL
key_len NULL
ref NULL
rows NULL
Extra Impossible WHERE noticed after reading const tables
id 2
select_type DEPENDENT SUBQUERY
table NULL
type NULL
possible_keys NULL
key NULL
key_len NULL
ref NULL
rows NULL
Extra Impossible WHERE noticed after reading const tables
SELECT DISTINCT first.col2 FROM initial_v53 first WHERE first.col2
IN (SELECT second.col2 FROM initial_t14 second WHERE second.col1<>first.col1);
>>> In 5.1.23 results returned:
col2
p1
p2
p4
>>> In 5.1.24 results returned:
(no rows returned)
explain SELECT DISTINCT first.col2 FROM initial_v53 first WHERE first.col2
IN (SELECT second.col2 FROM initial_t14 second WHERE second.col1<>first.col1);
>>> In 5.1.23 results returned:
id 1
select_type PRIMARY
table initial_t14
type ALL
possible_keys NULL
key NULL
key_len NULL
ref NULL
rows 7
Extra Using where; Using temporary
id 2
select_type DEPENDENT SUBQUERY
table second
type ALL
possible_keys NULL
key NULL
key_len NULL
ref NULL
rows 7
Extra Using where
>>> In 5.1.24 results returned:
d 1
select_type PRIMARY
table NULL
type NULL
possible_keys NULL
key NULL
key_len NULL
ref NULL
rows NULL
Extra Impossible WHERE noticed after reading const tables
id 2
select_type DEPENDENT SUBQUERY
table second
type system
possible_keys NULL
key NULL
key_len NULL
ref NULL
rows 0
Extra const row not found
Note if the above scenario is all done by the root user results in 5.1.24 are identical to the ones in 5.1.23
How to repeat:
Extract the attached file and run the test using mysql-test-run.pl --do-test
Suggested fix:
Results should be as in 5.1.23