r/bug35952.result0000640000175000001440000000565410777264320012643 0ustar omerusersCREATE 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'); connecting to user 'view_create' connected CREATE VIEW initial_v53 (col1,col2) AS SELECT col1,col2 FROM initial_t14; connecting to user 'view_select' connected 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); col1 col2 s1 p1 s1 p2 s1 p4 s2 p1 s3 p2 s4 p4 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); 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 SELECT DISTINCT first.col2 FROM initial_v53 first WHERE first.col2 IN (SELECT second.col2 FROM initial_t14 second WHERE second.col1<>first.col1); col2 p1 p2 p4 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); 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 connecting to user root connected 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); col1 col2 s1 p1 s1 p2 s1 p4 s2 p1 s3 p2 s4 p4 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); 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 SELECT DISTINCT first.col2 FROM initial_v53 first WHERE first.col2 IN (SELECT second.col2 FROM initial_t14 second WHERE second.col1<>first.col1); col2 p1 p2 p4 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); 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 r/bug35952.result_51230000640000175000001440000000565410777264320013315 0ustar omerusersCREATE 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'); connecting to user 'view_create' connected CREATE VIEW initial_v53 (col1,col2) AS SELECT col1,col2 FROM initial_t14; connecting to user 'view_select' connected 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); col1 col2 s1 p1 s1 p2 s1 p4 s2 p1 s3 p2 s4 p4 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); 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 SELECT DISTINCT first.col2 FROM initial_v53 first WHERE first.col2 IN (SELECT second.col2 FROM initial_t14 second WHERE second.col1<>first.col1); col2 p1 p2 p4 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); 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 connecting to user root connected 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); col1 col2 s1 p1 s1 p2 s1 p4 s2 p1 s3 p2 s4 p4 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); 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 SELECT DISTINCT first.col2 FROM initial_v53 first WHERE first.col2 IN (SELECT second.col2 FROM initial_t14 second WHERE second.col1<>first.col1); col2 p1 p2 p4 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); 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 r/bug35952.result_51240000640000175000001440000000576310777265606013327 0ustar omerusersCREATE 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'); connecting to user 'view_create' connected CREATE VIEW initial_v53 (col1,col2) AS SELECT col1,col2 FROM initial_t14; connecting to user 'view_select' connected 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); col1 col2 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); 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); col2 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); 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 second type system possible_keys NULL key NULL key_len NULL ref NULL rows 0 Extra const row not found connecting to user root connected 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); col1 col2 s1 p1 s1 p2 s1 p4 s2 p1 s3 p2 s4 p4 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); 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 SELECT DISTINCT first.col2 FROM initial_v53 first WHERE first.col2 IN (SELECT second.col2 FROM initial_t14 second WHERE second.col1<>first.col1); col2 p1 p2 p4 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); 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 t/bug35952.test0000644000175000001440000000456310777264315012314 0ustar omerusers connect (root, localhost, 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'; connect (view_create, localhost, view_create, view_create,viewdb); connect (view_select, localhost, view_select, view_select,viewdb); connection root; 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'); --echo connecting to user 'view_create' connection view_create; --echo connected CREATE VIEW initial_v53 (col1,col2) AS SELECT col1,col2 FROM initial_t14; --echo connecting to user 'view_select' connection view_select; --echo connected 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); --vertical_results 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); --horizontal_results SELECT DISTINCT first.col2 FROM initial_v53 first WHERE first.col2 IN (SELECT second.col2 FROM initial_t14 second WHERE second.col1<>first.col1); --vertical_results 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); --horizontal_results --echo connecting to user root connection root; --echo connected 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); --vertical_results 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); --horizontal_results SELECT DISTINCT first.col2 FROM initial_v53 first WHERE first.col2 IN (SELECT second.col2 FROM initial_t14 second WHERE second.col1<>first.col1); --vertical_results 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); --horizontal_results