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