summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/explain.result10
-rw-r--r--mysql-test/r/join_outer.result4
-rw-r--r--mysql-test/r/join_outer_jcl6.result4
-rw-r--r--mysql-test/r/negation_elimination.result6
-rw-r--r--mysql-test/r/subselect4.result6
-rw-r--r--mysql-test/r/subselect_mat_cost_bugs.result4
-rw-r--r--mysql-test/r/subselect_sj.result2
-rw-r--r--mysql-test/r/subselect_sj_jcl6.result2
-rw-r--r--mysql-test/r/view.result19
-rw-r--r--mysql-test/suite/innodb/r/innodb_mysql.result40
-rw-r--r--mysql-test/suite/innodb/t/innodb_mysql.test44
-rw-r--r--mysql-test/suite/innodb_plugin/r/innodb_mysql.result37
-rw-r--r--mysql-test/suite/innodb_plugin/t/innodb_mysql.test44
-rw-r--r--mysql-test/suite/pbxt/r/negation_elimination.result6
-rw-r--r--mysql-test/t/view.test20
-rw-r--r--sql/mysql_priv.h1
-rw-r--r--sql/opt_range.h4
-rw-r--r--sql/records.cc9
-rw-r--r--sql/sql_acl.cc21
-rw-r--r--sql/sql_parse.cc23
-rw-r--r--sql/sql_select.cc12
-rw-r--r--sql/sql_yacc.yy6
22 files changed, 100 insertions, 224 deletions
diff --git a/mysql-test/r/explain.result b/mysql-test/r/explain.result
index 7bfb090f659..1c55ff4edb5 100644
--- a/mysql-test/r/explain.result
+++ b/mysql-test/r/explain.result
@@ -267,7 +267,7 @@ WHERE t1.f1 GROUP BY t1.f1));
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
2 SUBQUERY a system NULL NULL NULL NULL 1
-2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
+2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where
PREPARE stmt FROM
'EXPLAIN SELECT 1 FROM t1
WHERE 1 > ALL((SELECT 1 FROM t1 RIGHT OUTER JOIN t1 a
@@ -277,12 +277,12 @@ EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
2 SUBQUERY a system NULL NULL NULL NULL 1
-2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
+2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
2 SUBQUERY a system NULL NULL NULL NULL 1
-2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
+2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where
DEALLOCATE PREPARE stmt;
PREPARE stmt FROM
'EXPLAIN SELECT 1 FROM t1
@@ -293,12 +293,12 @@ EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
2 SUBQUERY a system NULL NULL NULL NULL 1
-2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
+2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where
EXECUTE stmt;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
2 SUBQUERY a system NULL NULL NULL NULL 1
-2 SUBQUERY t1 fulltext f1 f1 0 1 Using where
+2 SUBQUERY t1 fulltext f1_2,f1 f1 0 1 Using where
DEALLOCATE PREPARE stmt;
DROP TABLE t1;
End of 5.1 tests.
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 0f20b0909b7..b5d26970d3a 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1378,7 +1378,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index
1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index
Warnings:
-Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt1` left join (`test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1))) on(1) where 1
+Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt1` left join (`test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on(((`test`.`jt6`.`f1` <> 0) and 1))) on(1) where 1
EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1
RIGHT JOIN t1 AS jt2
RIGHT JOIN t1 AS jt3
@@ -1395,7 +1395,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index
1 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index
Warnings:
-Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1)) left join `test`.`t1` `jt1` on(1) where 1
+Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on(((`test`.`jt6`.`f1` <> 0) and 1)) left join `test`.`t1` `jt1` on(1) where 1
DROP TABLE t1;
#
# Bug#49600: outer join of two single-row tables with joining attributes
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 8184d391d03..3f3f1543999 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -1387,7 +1387,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE jt5 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer (incremental, BNL join)
1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer (incremental, BNL join)
Warnings:
-Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt1` left join (`test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1))) on(1) where 1
+Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt1` left join (`test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on(((`test`.`jt6`.`f1` <> 0) and 1))) on(1) where 1
EXPLAIN EXTENDED SELECT STRAIGHT_JOIN jt1.f1 FROM t1 AS jt1
RIGHT JOIN t1 AS jt2
RIGHT JOIN t1 AS jt3
@@ -1404,7 +1404,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE jt2 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer (incremental, BNL join)
1 SIMPLE jt1 index NULL PRIMARY 4 NULL 2 100.00 Using where; Using index; Using join buffer (incremental, BNL join)
Warnings:
-Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on((`test`.`jt6`.`f1` and 1)) left join `test`.`t1` `jt1` on(1) where 1
+Note 1003 select straight_join `test`.`jt1`.`f1` AS `f1` from `test`.`t1` `jt6` left join (`test`.`t1` `jt3` join `test`.`t1` `jt4` left join `test`.`t1` `jt5` on(1) left join `test`.`t1` `jt2` on(1)) on(((`test`.`jt6`.`f1` <> 0) and 1)) left join `test`.`t1` `jt1` on(1) where 1
DROP TABLE t1;
#
# Bug#49600: outer join of two single-row tables with joining attributes
diff --git a/mysql-test/r/negation_elimination.result b/mysql-test/r/negation_elimination.result
index acb0fd18214..5b09b0fc511 100644
--- a/mysql-test/r/negation_elimination.result
+++ b/mysql-test/r/negation_elimination.result
@@ -4,7 +4,7 @@ insert into t1 values (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
explain select * from t1 where not(not(a));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL a 5 NULL 21 Using where; Using index
+1 SIMPLE t1 range a a 5 NULL 20 Using where; Using index
select * from t1 where not(not(a));
a
1
@@ -500,7 +500,7 @@ NULL NULL
3 1
explain extended select a, not(not(a)), not(a <= 2 and not(a)), not(a not like "1"), not (a not in (1,2)), not(a != 2) from t1 where not(not(a)) having not(not(a));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 index NULL a 5 NULL 5 100.00 Using where; Using index
+1 SIMPLE t1 range a a 5 NULL 4 100.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a` <> 0) AS `not(not(a))`,((`test`.`t1`.`a` > 2) or `test`.`t1`.`a`) AS `not(a <= 2 and not(a))`,(`test`.`t1`.`a` like '1') AS `not(a not like "1")`,(`test`.`t1`.`a` in (1,2)) AS `not (a not in (1,2))`,(`test`.`t1`.`a` = 2) AS `not(a != 2)` from `test`.`t1` where `test`.`t1`.`a` having `test`.`t1`.`a`
+Note 1003 select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a` <> 0) AS `not(not(a))`,((`test`.`t1`.`a` > 2) or `test`.`t1`.`a`) AS `not(a <= 2 and not(a))`,(`test`.`t1`.`a` like '1') AS `not(a not like "1")`,(`test`.`t1`.`a` in (1,2)) AS `not (a not in (1,2))`,(`test`.`t1`.`a` = 2) AS `not(a != 2)` from `test`.`t1` where (`test`.`t1`.`a` <> 0) having (`test`.`t1`.`a` <> 0)
drop table t1;
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index bfd5872acf2..686c8df0ea6 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -1354,7 +1354,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 system NULL NULL NULL NULL 1
2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
3 SUBQUERY SQ1_t1 index NULL f4 5 NULL 2 Using index; Using temporary
-3 SUBQUERY SQ1_t3 index NULL f4 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
+3 SUBQUERY SQ1_t3 index f4 f4 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
drop table t1, t2, t3;
#
# BUG#52317: Assertion failing in Field_varstring::store()
@@ -1898,7 +1898,7 @@ WHERE SUBQUERY2_t2.col_varchar_nokey IN
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 index NULL col_int_key 5 NULL 2 Using index
2 SUBQUERY SUBQUERY2_t1 index NULL col_int_key 5 NULL 2 Using index
-2 SUBQUERY SUBQUERY2_t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+2 SUBQUERY SUBQUERY2_t2 ALL col_varchar_key NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary
SELECT col_int_key
FROM t2
@@ -1920,7 +1920,7 @@ WHERE SUBQUERY2_t2.col_varchar_nokey IN
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 index NULL col_int_key 5 NULL 2 Using index
2 SUBQUERY SUBQUERY2_t1 index NULL col_int_key 5 NULL 2 Using index
-2 SUBQUERY SUBQUERY2_t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
+2 SUBQUERY SUBQUERY2_t2 ALL col_varchar_key NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join)
3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary
SELECT col_int_key
FROM t2
diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result
index ed86ae5c7b9..698a68116b3 100644
--- a/mysql-test/r/subselect_mat_cost_bugs.result
+++ b/mysql-test/r/subselect_mat_cost_bugs.result
@@ -94,7 +94,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
2 DEPENDENT SUBQUERY t1a ref c2 c2 5 test.t1b.pk 2 100.00 Using where
Warnings:
Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1
-Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on((2 and (`test`.`t1a`.`c2` = `test`.`t1b`.`pk`))) where (`test`.`t1`.`pk` and (<cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1`) and (`test`.`t1b`.`c4` = `test`.`t2`.`c3`))))
+Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <in_optimizer>(`test`.`t1`.`c1`,<exists>(select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on((2 and (`test`.`t1a`.`c2` = `test`.`t1b`.`pk`))) where ((`test`.`t1`.`pk` <> 0) and (<cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1`) and (`test`.`t1b`.`c4` = `test`.`t2`.`c3`))))
SELECT pk
FROM t1
WHERE c1 IN
@@ -184,7 +184,7 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using filesort
1 PRIMARY alias1 eq_ref PRIMARY PRIMARY 4 test.t2.f3 1 Using index
3 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2
-3 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
+3 DEPENDENT SUBQUERY t1 index PRIMARY PRIMARY 4 NULL 2 Using where; Using index; Using join buffer (flat, BNL join)
SELECT alias2.f2 AS field1
FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1
WHERE (
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index 23fe9540924..eba163f994c 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -726,7 +726,7 @@ FROM it1 LEFT JOIN it2 ON it2.datetime_key);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY it1 index NULL int_key 4 NULL 2 Using index; Start temporary
1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join)
-1 PRIMARY it2 ref int_key int_key 4 test.ot1.int_nokey 3 Using where; End temporary
+1 PRIMARY it2 ALL int_key,datetime_key NULL NULL NULL 20 Using where; End temporary; Using join buffer (flat, BNL join)
DROP TABLE ot1, it1, it2;
# End of BUG#38075
#
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index cc2748c2a56..4a831718ca5 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -737,7 +737,7 @@ FROM it1 LEFT JOIN it2 ON it2.datetime_key);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY it1 index NULL int_key 4 NULL 2 Using index; Start temporary
1 PRIMARY ot1 ALL NULL NULL NULL NULL 20 Using join buffer (flat, BNL join)
-1 PRIMARY it2 ref int_key int_key 4 test.ot1.int_nokey 3 Using where; End temporary; Using join buffer (incremental, BKA join); Key-ordered Rowid-ordered scan
+1 PRIMARY it2 ALL int_key,datetime_key NULL NULL NULL 20 Using where; End temporary; Using join buffer (incremental, BNL join)
DROP TABLE ot1, it1, it2;
# End of BUG#38075
#
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result
index b09c00a883c..241d4f392f8 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -3711,7 +3711,7 @@ CREATE TABLE t1 (c INT);
CREATE VIEW v1 (view_column) AS SELECT c AS alias FROM t1 HAVING alias;
SHOW CREATE VIEW v1;
View Create View character_set_client collation_connection
-v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`c` AS `view_column` from `t1` having `view_column` latin1 latin1_swedish_ci
+v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`c` AS `view_column` from `t1` having (`view_column` <> 0) latin1 latin1_swedish_ci
SELECT * FROM v1;
view_column
@@ -3906,6 +3906,23 @@ SELECT * FROM v1;
a
DROP VIEW v1;
DROP TABLE t1;
+#
+# LP BUG#777809 (a retrograded condition for view ON)
+#
+CREATE TABLE t1 ( f1 int NOT NULL , f6 int NOT NULL ) ;
+INSERT IGNORE INTO t1 VALUES (20, 2);
+CREATE TABLE t2 ( f3 int NOT NULL ) ;
+INSERT IGNORE INTO t2 VALUES (7);
+CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2;
+PREPARE prep_stmt FROM 'SELECT t1.f6 FROM t1 RIGHT JOIN v2 ON v2.f3 WHERE t1.f1 != 0';
+EXECUTE prep_stmt;
+f6
+2
+EXECUTE prep_stmt;
+f6
+2
+drop view v2;
+drop table t1,t2;
# -----------------------------------------------------------------
# -- End of 5.1 tests.
# -----------------------------------------------------------------
diff --git a/mysql-test/suite/innodb/r/innodb_mysql.result b/mysql-test/suite/innodb/r/innodb_mysql.result
index 9acc1f424d4..1bd572d7231 100644
--- a/mysql-test/suite/innodb/r/innodb_mysql.result
+++ b/mysql-test/suite/innodb/r/innodb_mysql.result
@@ -2624,46 +2624,6 @@ create table t1 (a int primary key, b int) engine = innodb;
insert into t1 values (1,1),(2,1);
alter ignore table t1 add unique `main` (b);
drop table t1;
-#
-# Bug#56862 Execution of a query that uses index merge returns a wrong result
-#
-CREATE TABLE t1 (
-pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-a int,
-b int,
-INDEX idx(a))
-ENGINE=INNODB;
-INSERT INTO t1(a,b) VALUES
-(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
-(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
-(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
-(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
-INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
-INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
-INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1 VALUES (1000000, 0, 0);
-SET SESSION sort_buffer_size = 1024*36;
-EXPLAIN
-SELECT COUNT(*) FROM
-(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
-WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 3537 Using sort_union(idx,PRIMARY); Using where
-SELECT COUNT(*) FROM
-(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
-WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
-COUNT(*)
-1537
-SET SESSION sort_buffer_size = DEFAULT;
-DROP TABLE t1;
End of 5.1 tests
#
# Test for bug #39932 "create table fails if column for FK is in different
diff --git a/mysql-test/suite/innodb/t/innodb_mysql.test b/mysql-test/suite/innodb/t/innodb_mysql.test
index 850e6b1a0c3..c17b9465aa6 100644
--- a/mysql-test/suite/innodb/t/innodb_mysql.test
+++ b/mysql-test/suite/innodb/t/innodb_mysql.test
@@ -849,50 +849,6 @@ insert into t1 values (1,1),(2,1);
alter ignore table t1 add unique `main` (b);
drop table t1;
---echo #
---echo # Bug#56862 Execution of a query that uses index merge returns a wrong result
---echo #
-
-CREATE TABLE t1 (
- pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
- a int,
- b int,
- INDEX idx(a))
-ENGINE=INNODB;
-
-INSERT INTO t1(a,b) VALUES
- (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
- (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
- (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
- (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
-INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
-INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
-INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1 VALUES (1000000, 0, 0);
-
-SET SESSION sort_buffer_size = 1024*36;
-
-EXPLAIN
-SELECT COUNT(*) FROM
- (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
- WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
-
-SELECT COUNT(*) FROM
- (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
- WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
-
-SET SESSION sort_buffer_size = DEFAULT;
-
-DROP TABLE t1;
-
--echo End of 5.1 tests
--echo #
diff --git a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result
index df23048f834..e9d636baf2e 100644
--- a/mysql-test/suite/innodb_plugin/r/innodb_mysql.result
+++ b/mysql-test/suite/innodb_plugin/r/innodb_mysql.result
@@ -2411,41 +2411,4 @@ PACK_KEYS=0;
CREATE INDEX a ON t1 (a);
CREATE INDEX c on t1 (c);
DROP TABLE t1;
-CREATE TABLE t1 (
-pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
-a int,
-b int,
-INDEX idx(a))
-ENGINE=INNODB;
-INSERT INTO t1(a,b) VALUES
-(11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
-(3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
-(6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
-(13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
-INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
-INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
-INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1 VALUES (1000000, 0, 0);
-SET SESSION sort_buffer_size = 1024*36;
-EXPLAIN
-SELECT COUNT(*) FROM
-(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
-WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
-id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index_merge PRIMARY,idx idx,PRIMARY 5,4 NULL 3537 Using sort_union(idx,PRIMARY); Using where
-SELECT COUNT(*) FROM
-(SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
-WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
-COUNT(*)
-1537
-SET SESSION sort_buffer_size = DEFAULT;
-DROP TABLE t1;
End of 5.1 tests
diff --git a/mysql-test/suite/innodb_plugin/t/innodb_mysql.test b/mysql-test/suite/innodb_plugin/t/innodb_mysql.test
index 279802fcdb4..32bbdfa10b4 100644
--- a/mysql-test/suite/innodb_plugin/t/innodb_mysql.test
+++ b/mysql-test/suite/innodb_plugin/t/innodb_mysql.test
@@ -647,48 +647,4 @@ CREATE INDEX c on t1 (c);
DROP TABLE t1;
-#
-# Bug#56862 Execution of a query that uses index merge returns a wrong result
-#
-
-CREATE TABLE t1 (
- pk int NOT NULL AUTO_INCREMENT PRIMARY KEY,
- a int,
- b int,
- INDEX idx(a))
-ENGINE=INNODB;
-
-INSERT INTO t1(a,b) VALUES
- (11, 1100), (2, 200), (1, 100), (14, 1400), (5, 500),
- (3, 300), (17, 1700), (4, 400), (12, 1200), (8, 800),
- (6, 600), (18, 1800), (9, 900), (10, 1000), (7, 700),
- (13, 1300), (15, 1500), (19, 1900), (16, 1600), (20, 2000);
-INSERT INTO t1(a,b) SELECT a+20, b+2000 FROM t1;
-INSERT INTO t1(a,b) SELECT a+40, b+4000 FROM t1;
-INSERT INTO t1(a,b) SELECT a+80, b+8000 FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1(a,b) SELECT a,b FROM t1;
-INSERT INTO t1 VALUES (1000000, 0, 0);
-
-SET SESSION sort_buffer_size = 1024*36;
-
-EXPLAIN
-SELECT COUNT(*) FROM
- (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
- WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
-
-SELECT COUNT(*) FROM
- (SELECT * FROM t1 FORCE INDEX (idx,PRIMARY)
- WHERE a BETWEEN 2 AND 7 OR pk=1000000) AS t;
-
-SET SESSION sort_buffer_size = DEFAULT;
-
-DROP TABLE t1;
-
--echo End of 5.1 tests
diff --git a/mysql-test/suite/pbxt/r/negation_elimination.result b/mysql-test/suite/pbxt/r/negation_elimination.result
index 61889824865..1aaa256abcc 100644
--- a/mysql-test/suite/pbxt/r/negation_elimination.result
+++ b/mysql-test/suite/pbxt/r/negation_elimination.result
@@ -4,7 +4,7 @@ insert into t1 values (NULL), (0), (1), (2), (3), (4), (5), (6), (7), (8), (9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19);
explain select * from t1 where not(not(a));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 index NULL a 5 NULL 21 Using where; Using index
+1 SIMPLE t1 range a a 5 NULL 2 Using where; Using index
select * from t1 where not(not(a));
a
1
@@ -388,7 +388,7 @@ Table Op Msg_type Msg_text
test.t1 analyze status OK
explain extended select a, not(not(a)), not(a <= 2 and not(a)), not(a not like "1"), not (a not in (1,2)), not(a != 2) from t1 where not(not(a)) having not(not(a));
id select_type table type possible_keys key key_len ref rows filtered Extra
-1 SIMPLE t1 index NULL a 5 NULL 5 100.00 Using where; Using index
+1 SIMPLE t1 index a a 5 NULL 5 40.00 Using where; Using index
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a` <> 0) AS `not(not(a))`,((`test`.`t1`.`a` > 2) or `test`.`t1`.`a`) AS `not(a <= 2 and not(a))`,(`test`.`t1`.`a` like '1') AS `not(a not like "1")`,(`test`.`t1`.`a` in (1,2)) AS `not (a not in (1,2))`,(`test`.`t1`.`a` = 2) AS `not(a != 2)` from `test`.`t1` where `test`.`t1`.`a` having `test`.`t1`.`a`
+Note 1003 select `test`.`t1`.`a` AS `a`,(`test`.`t1`.`a` <> 0) AS `not(not(a))`,((`test`.`t1`.`a` > 2) or `test`.`t1`.`a`) AS `not(a <= 2 and not(a))`,(`test`.`t1`.`a` like '1') AS `not(a not like "1")`,(`test`.`t1`.`a` in (1,2)) AS `not (a not in (1,2))`,(`test`.`t1`.`a` = 2) AS `not(a != 2)` from `test`.`t1` where (`test`.`t1`.`a` <> 0) having (`test`.`t1`.`a` <> 0)
drop table t1;
diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test
index 9eafae1729f..fd7ef02353e 100644
--- a/mysql-test/t/view.test
+++ b/mysql-test/t/view.test
@@ -3954,6 +3954,26 @@ SELECT * FROM v1;
DROP VIEW v1;
DROP TABLE t1;
+--echo #
+--echo # LP BUG#777809 (a retrograded condition for view ON)
+--echo #
+
+CREATE TABLE t1 ( f1 int NOT NULL , f6 int NOT NULL ) ;
+INSERT IGNORE INTO t1 VALUES (20, 2);
+
+CREATE TABLE t2 ( f3 int NOT NULL ) ;
+INSERT IGNORE INTO t2 VALUES (7);
+
+CREATE OR REPLACE VIEW v2 AS SELECT * FROM t2;
+
+PREPARE prep_stmt FROM 'SELECT t1.f6 FROM t1 RIGHT JOIN v2 ON v2.f3 WHERE t1.f1 != 0';
+
+EXECUTE prep_stmt;
+EXECUTE prep_stmt;
+
+drop view v2;
+drop table t1,t2;
+
--echo # -----------------------------------------------------------------
--echo # -- End of 5.1 tests.
--echo # -----------------------------------------------------------------
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 68184a94666..7284be312e3 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -1640,6 +1640,7 @@ bool add_to_list(THD *thd, SQL_I_List<ORDER> &list, Item *group,bool asc);
bool push_new_name_resolution_context(THD *thd,
TABLE_LIST *left_op,
TABLE_LIST *right_op);
+Item *normalize_cond(Item *cond);
void add_join_on(TABLE_LIST *b,Item *expr);
void add_join_natural(TABLE_LIST *a,TABLE_LIST *b,List<String> *using_fields,
SELECT_LEX *lex);
diff --git a/sql/opt_range.h b/sql/opt_range.h
index d7a0c1e2f8f..0ad2b7242f2 100644
--- a/sql/opt_range.h
+++ b/sql/opt_range.h
@@ -274,7 +274,6 @@ public:
virtual bool reverse_sorted() = 0;
virtual bool unique_key_range() { return false; }
- virtual bool clustered_pk_range() { return false; }
/*
Request that this quick select produces sorted output. Not all quick
@@ -593,9 +592,6 @@ public:
MEM_ROOT alloc;
THD *thd;
virtual int read_keys_and_merge()= 0;
-
- bool clustered_pk_range() { return test(pk_quick_select); }
-
/* used to get rows collected in Unique */
READ_RECORD read_record;
};
diff --git a/sql/records.cc b/sql/records.cc
index 5ce7d8660e9..60c801f8977 100644
--- a/sql/records.cc
+++ b/sql/records.cc
@@ -195,15 +195,6 @@ bool init_read_record(READ_RECORD *info,THD *thd, TABLE *table,
if (select && my_b_inited(&select->file))
tempfile= &select->file;
- else if (select && select->quick && select->quick->clustered_pk_range())
- {
- /*
- In case of QUICK_INDEX_MERGE_SELECT with clustered pk range we have to
- use its own access method(i.e QUICK_INDEX_MERGE_SELECT::get_next()) as
- sort file does not contain rowids which satisfy clustered pk range.
- */
- tempfile= 0;
- }
else
tempfile= table->sort.io_cache;
if (tempfile && my_b_inited(tempfile) &&
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index 742a20ab4f3..dc1405a00a4 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -7565,21 +7565,15 @@ static ulong parse_client_handshake_packet(MPVIO_EXT *mpvio,
uint passwd_len= thd->client_capabilities & CLIENT_SECURE_CONNECTION ?
(uchar)(*passwd++) : strlen(passwd);
- if (thd->client_capabilities & CLIENT_CONNECT_WITH_DB)
- {
- db= db + passwd_len + 1;
- /* strlen() can't be easily deleted without changing protocol */
- db_len= strlen(db);
- }
- else
- {
- db= 0;
- db_len= 0;
- }
+ db= thd->client_capabilities & CLIENT_CONNECT_WITH_DB ?
+ db + passwd_len + 1 : 0;
- if (passwd + passwd_len + db_len > (char *)net->read_pos + pkt_len)
+ if (passwd + passwd_len + test(db) > (char *)net->read_pos + pkt_len)
return packet_error;
+ /* strlen() can't be easily deleted without changing protocol */
+ db_len= db ? strlen(db) : 0;
+
char *client_plugin= passwd + passwd_len + (db ? db_len + 1 : 0);
/* Since 4.1 all database names are stored in utf8 */
@@ -7646,8 +7640,7 @@ static ulong parse_client_handshake_packet(MPVIO_EXT *mpvio,
if (thd->client_capabilities & CLIENT_PLUGIN_AUTH)
{
- if ((client_plugin + strlen(client_plugin)) >
- (char *)net->read_pos + pkt_len)
+ if (client_plugin >= (char *)net->read_pos + pkt_len)
return packet_error;
client_plugin= fix_plugin_ptr(client_plugin);
}
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index f10430b022c..18dad6cfff0 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -6795,6 +6795,28 @@ push_new_name_resolution_context(THD *thd,
/**
+ Fix condition which contains only field (f turns to f <> 0 )
+
+ @param cond The condition to fix
+
+ @return fixed condition
+*/
+
+Item *normalize_cond(Item *cond)
+{
+ if (cond)
+ {
+ Item::Type type= cond->type();
+ if (type == Item::FIELD_ITEM || type == Item::REF_ITEM)
+ {
+ cond= new Item_func_ne(cond, new Item_int(0));
+ }
+ }
+ return cond;
+}
+
+
+/**
Add an ON condition to the second operand of a JOIN ... ON.
Add an ON condition to the right operand of a JOIN ... ON clause.
@@ -6812,6 +6834,7 @@ void add_join_on(TABLE_LIST *b, Item *expr)
{
if (expr)
{
+ expr= normalize_cond(expr);
if (!b->on_expr)
b->on_expr= expr;
else
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index ee0ad5571d0..deb1fed4249 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -13082,7 +13082,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields,
bool using_unique_constraint= 0;
bool use_packed_rows= 0;
bool not_all_columns= !(select_options & TMP_TABLE_ALL_COLUMNS);
- char *tmpname,path[FN_REFLEN], tmp_table_name[50];
+ char *tmpname,path[FN_REFLEN];
uchar *pos, *group_buff, *bitmaps;
uchar *null_flags;
Field **reg_field, **from_field, **default_field;
@@ -13113,12 +13113,12 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields,
temp_pool_slot = bitmap_lock_set_next(&temp_pool);
if (temp_pool_slot != MY_BIT_NONE) // we got a slot
- sprintf(tmp_table_name, "%s_%lx_%i", tmp_file_prefix,
+ sprintf(path, "%s_%lx_%i", tmp_file_prefix,
current_pid, temp_pool_slot);
else
{
/* if we run out of slots or we are not using tempool */
- sprintf(tmp_table_name, "%s%lx_%lx_%x", tmp_file_prefix,current_pid,
+ sprintf(path, "%s%lx_%lx_%x", tmp_file_prefix,current_pid,
thd->thread_id, thd->tmp_table++);
}
@@ -13126,7 +13126,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields,
No need to change table name to lower case as we are only creating
MyISAM, Aria or HEAP tables here
*/
- fn_format(path, tmp_table_name, mysql_tmpdir, "",
+ fn_format(path, path, mysql_tmpdir, "",
MY_REPLACE_EXT|MY_UNPACK_FILENAME);
if (group)
@@ -13179,7 +13179,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields,
sizeof(*key_part_info)*(param->group_parts+1),
&param->start_recinfo,
sizeof(*param->recinfo)*(field_count*2+4),
- &tmpname, (uint) strlen(tmp_table_name)+1,
+ &tmpname, (uint) strlen(path)+1,
&group_buff, (group && ! using_unique_constraint ?
param->group_length : 0),
&bitmaps, bitmap_buffer_size(field_count)*4,
@@ -13198,7 +13198,7 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields,
DBUG_RETURN(NULL); /* purecov: inspected */
}
param->items_to_copy= copy_func;
- strmov(tmpname, tmp_table_name);
+ strmov(tmpname, path);
/* make table according to fields */
bzero((char*) table,sizeof(*table));
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index d2cef29cb08..c2fea985aa1 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -9426,7 +9426,7 @@ where_clause:
expr
{
SELECT_LEX *select= Select;
- select->where= $3;
+ select->where= normalize_cond($3);
select->parsing_place= NO_MATTER;
if ($3)
$3->top_level_item();
@@ -9442,7 +9442,7 @@ having_clause:
expr
{
SELECT_LEX *sel= Select;
- sel->having= $3;
+ sel->having= normalize_cond($3);
sel->parsing_place= NO_MATTER;
if ($3)
$3->top_level_item();
@@ -10909,7 +10909,7 @@ wild_and_where:
}
| WHERE expr
{
- Select->where= $2;
+ Select->where= normalize_cond($2);
if ($2)
$2->top_level_item();
}
View Lorry Controller Status