Bug #35952 Regression: 5.1.24 Results not returned for users with select privs as expected
Submitted: 10 Apr 2008 1:20 Modified: 25 Apr 2008 20:24
Reporter: Omer Barnir (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.1.24 OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any
Triage: D2 (Serious) / R3 (Medium) / E3 (Medium)

[10 Apr 2008 1:20] Omer Barnir
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
[10 Apr 2008 14:03] Omer Barnir
Test files

Attachment: bug35952.tar (application/x-tar, text), 20.00 KiB.

[18 Apr 2008 13:42] Evgeny Potemkin
Can't repeat it on the 5.1.25-rc.
But I can't repeat it on the version tagged as 5.1.24-build.
All results are same as provided for 5.1.23.
[25 Apr 2008 20:24] Omer Barnir
I was not able to reproduce the bug with the latest 5.1 (main tree). Also seems the bug reproduces itself with 5.1.24 binaries only on my VM (was not able to reproduce it on a different machine. 

Given it does not show in latest 5.1 tree, closing the bug