summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/join_outer.result16
-rw-r--r--mysql-test/r/join_outer_jcl6.result16
-rw-r--r--mysql-test/r/select.result21
-rw-r--r--mysql-test/r/select_jcl6.result21
-rw-r--r--mysql-test/r/select_pkeycache.result21
-rw-r--r--mysql-test/r/subselect.result2
-rw-r--r--mysql-test/r/subselect4.result2
-rw-r--r--mysql-test/r/subselect_no_mat.result2
-rw-r--r--mysql-test/r/subselect_no_opts.result2
-rw-r--r--mysql-test/r/subselect_no_scache.result2
-rw-r--r--mysql-test/r/subselect_no_semijoin.result2
-rw-r--r--mysql-test/r/table_elim.result6
-rw-r--r--mysql-test/t/join_outer.test18
-rw-r--r--mysql-test/t/select.test19
-rw-r--r--sql/sql_select.cc42
-rw-r--r--sql/sql_select.h5
16 files changed, 172 insertions, 25 deletions
diff --git a/mysql-test/r/join_outer.result b/mysql-test/r/join_outer.result
index 8b426c9a26f..79ae845cacf 100644
--- a/mysql-test/r/join_outer.result
+++ b/mysql-test/r/join_outer.result
@@ -1930,7 +1930,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
-Note 1003 select NULL AS `a` from `test`.`t2`
+Note 1003 select NULL AS `a` from `test`.`t2` where 1
DROP TABLE t1,t2,t3;
#
# LP bug #817384 Wrong result with outer join + subquery in ON
@@ -2150,4 +2150,18 @@ INSERT INTO t2 VALUES (3,'2000-03-06'), (4,'2007-09-25');
SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE d1 IS NULL AND d2 IS NULL;
i1 d1 i2 d2
DROP TABLE t1,t2;
+#
+# Bug mdev-4952: LEFT JOIN with disjunctive
+# <non-nullable datetime field> IS NULL in WHERE
+# causes an assert failure
+#
+CREATE TABLE t1 (a1 int, b1 int NOT NULL) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1, 10), (2, 11);
+CREATE TABLE t2 (dt datetime NOT NULL, a2 int, b2 int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+('2006-10-08 09:34:54', 1, 100), ('2001-01-19 01:04:43', 2, 200);
+SELECT * FROM t1 LEFT JOIN t2 ON a1 = a2
+WHERE ( dt IS NULL OR FALSE ) AND b2 IS NULL;
+a1 b1 dt a2 b2
+DROP TABLE t1,t2;
SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/join_outer_jcl6.result b/mysql-test/r/join_outer_jcl6.result
index 7ce7eb2eec7..c38fdc66974 100644
--- a/mysql-test/r/join_outer_jcl6.result
+++ b/mysql-test/r/join_outer_jcl6.result
@@ -1941,7 +1941,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00
2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 Using where
Warnings:
-Note 1003 select NULL AS `a` from `test`.`t2`
+Note 1003 select NULL AS `a` from `test`.`t2` where 1
DROP TABLE t1,t2,t3;
#
# LP bug #817384 Wrong result with outer join + subquery in ON
@@ -2161,6 +2161,20 @@ INSERT INTO t2 VALUES (3,'2000-03-06'), (4,'2007-09-25');
SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE d1 IS NULL AND d2 IS NULL;
i1 d1 i2 d2
DROP TABLE t1,t2;
+#
+# Bug mdev-4952: LEFT JOIN with disjunctive
+# <non-nullable datetime field> IS NULL in WHERE
+# causes an assert failure
+#
+CREATE TABLE t1 (a1 int, b1 int NOT NULL) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1, 10), (2, 11);
+CREATE TABLE t2 (dt datetime NOT NULL, a2 int, b2 int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+('2006-10-08 09:34:54', 1, 100), ('2001-01-19 01:04:43', 2, 200);
+SELECT * FROM t1 LEFT JOIN t2 ON a1 = a2
+WHERE ( dt IS NULL OR FALSE ) AND b2 IS NULL;
+a1 b1 dt a2 b2
+DROP TABLE t1,t2;
SET optimizer_switch=@save_optimizer_switch;
set join_cache_level=default;
show variables like 'join_cache_level';
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 4f3a2f58d7d..5e98f2852da 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -4786,7 +4786,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where
Warnings:
-Note 1003 select 2 AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1`
+Note 1003 select 2 AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1` where 1
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00');
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where
@@ -5425,4 +5425,23 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,'k' AS `c`,'k' AS
SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b));
a b c d
DROP TABLE t1,t2;
+#
+# Bug mdev-4944: range conditition in OR formula with fields
+# belonging to multiple equalities
+#
+CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,8);
+CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8), (9);
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2
+WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+Warnings:
+Note 1003 select 1 AS `i1`,8 AS `j1`,`test`.`t2`.`i2` AS `i2` from `test`.`t2` where 0
+SELECT * FROM t1, t2
+WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+i1 j1 i2
+DROP TABLE t1,t2;
End of 5.3 tests
diff --git a/mysql-test/r/select_jcl6.result b/mysql-test/r/select_jcl6.result
index 907514669aa..e6f5ab566c0 100644
--- a/mysql-test/r/select_jcl6.result
+++ b/mysql-test/r/select_jcl6.result
@@ -4797,7 +4797,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where
Warnings:
-Note 1003 select 2 AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1`
+Note 1003 select 2 AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1` where 1
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00');
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where
@@ -5436,6 +5436,25 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,'k' AS `c`,'k' AS
SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b));
a b c d
DROP TABLE t1,t2;
+#
+# Bug mdev-4944: range conditition in OR formula with fields
+# belonging to multiple equalities
+#
+CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,8);
+CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8), (9);
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2
+WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+Warnings:
+Note 1003 select 1 AS `i1`,8 AS `j1`,`test`.`t2`.`i2` AS `i2` from `test`.`t2` where 0
+SELECT * FROM t1, t2
+WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+i1 j1 i2
+DROP TABLE t1,t2;
End of 5.3 tests
set join_cache_level=default;
show variables like 'join_cache_level';
diff --git a/mysql-test/r/select_pkeycache.result b/mysql-test/r/select_pkeycache.result
index 4f3a2f58d7d..5e98f2852da 100644
--- a/mysql-test/r/select_pkeycache.result
+++ b/mysql-test/r/select_pkeycache.result
@@ -4786,7 +4786,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where
Warnings:
-Note 1003 select 2 AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1`
+Note 1003 select 2 AS `b`,`test`.`t1`.`a` AS `a` from `test`.`t1` where 1
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a > UNIX_TIMESTAMP('2009-03-10 00:00:00');
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 10 100.00 Using where
@@ -5425,4 +5425,23 @@ Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,'k' AS `c`,'k' AS
SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b));
a b c d
DROP TABLE t1,t2;
+#
+# Bug mdev-4944: range conditition in OR formula with fields
+# belonging to multiple equalities
+#
+CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,8);
+CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8), (9);
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2
+WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+Warnings:
+Note 1003 select 1 AS `i1`,8 AS `j1`,`test`.`t2`.`i2` AS `i2` from `test`.`t2` where 0
+SELECT * FROM t1, t2
+WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+i1 j1 i2
+DROP TABLE t1,t2;
End of 5.3 tests
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index de4c93e2527..3b5b838b56a 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -1319,7 +1319,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result
index 219dec58629..11b27e76782 100644
--- a/mysql-test/r/subselect4.result
+++ b/mysql-test/r/subselect4.result
@@ -1750,7 +1750,7 @@ SET @@optimizer_switch = 'in_to_exists=on,materialization=off,semijoin=off';
EXPLAIN SELECT * FROM t1 WHERE a1 IN (SELECT b1 FROM t2 WHERE b1 = b2);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
-2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
SELECT * FROM t1 WHERE a1 IN (SELECT b1 FROM t2 WHERE b1 = b2);
a1 a2
set @@optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index dc579912565..a14e08b0aac 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -1326,7 +1326,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 0650250db2b..7c1a7f67670 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -1322,7 +1322,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index 45443eabe71..2b85a66c124 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -1325,7 +1325,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index 9960dd97f05..5a7e303f4b9 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -1322,7 +1322,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
Warnings:
-Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)`
+Note 1003 select <in_optimizer>(0,<exists>(select 1 from dual where (0 = 1))) AS `0 IN (SELECT 1 FROM t1 a)`
INSERT INTO t1 (pseudo) VALUES ('test1');
SELECT 0 IN (SELECT 1 FROM t1 a);
0 IN (SELECT 1 FROM t1 a)
diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result
index 8bb0bdda4e4..0b19b6b4eaf 100644
--- a/mysql-test/r/table_elim.result
+++ b/mysql-test/r/table_elim.result
@@ -17,7 +17,7 @@ explain extended select t1.a from t1 left join t2 on t2.a=t1.a;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00
Warnings:
-Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1`
+Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where 1
select t1.a from t1 left join t2 on t2.a=t1.a;
a
0
@@ -62,7 +62,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t0 ALL NULL NULL NULL NULL 4 100.00
1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where
Warnings:
-Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on((`test`.`t1`.`a` = `test`.`t0`.`a`))
+Note 1003 select `test`.`t0`.`a` AS `a` from `test`.`t0` left join (`test`.`t1`) on((`test`.`t1`.`a` = `test`.`t0`.`a`)) where 1
# Elimination with aggregate functions
explain select count(*) from t1 left join t2 on t2.a=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
@@ -626,7 +626,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t2 index NULL b 5 NULL 2 100.00 Using where; Using index
2 DEPENDENT SUBQUERY t1 system NULL NULL NULL NULL 1 100.00
Warnings:
-Note 1003 select `test`.`t2`.`b` AS `b` from `test`.`t2` where <expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(select sum(1) from dual having (<cache>(`test`.`t2`.`b`) = <ref_null_helper>(sum(1))))))
+Note 1003 select `test`.`t2`.`b` AS `b` from `test`.`t2` where <expr_cache><`test`.`t2`.`b`>(<in_optimizer>(`test`.`t2`.`b`,<exists>(select sum(1) from dual where 1 having (<cache>(`test`.`t2`.`b`) = <ref_null_helper>(sum(1))))))
DROP TABLE t1,t2;
#
# MDEV-4840: Wrong result (missing rows) on LEFT JOIN with InnoDB tables
diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index f48120bf63b..04bf09b7484 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -1703,4 +1703,22 @@ SELECT * FROM t1 LEFT JOIN t2 ON i1 = i2 WHERE d1 IS NULL AND d2 IS NULL;
DROP TABLE t1,t2;
+--echo #
+--echo # Bug mdev-4952: LEFT JOIN with disjunctive
+--echo # <non-nullable datetime field> IS NULL in WHERE
+--echo # causes an assert failure
+--echo #
+
+CREATE TABLE t1 (a1 int, b1 int NOT NULL) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1, 10), (2, 11);
+
+CREATE TABLE t2 (dt datetime NOT NULL, a2 int, b2 int) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+('2006-10-08 09:34:54', 1, 100), ('2001-01-19 01:04:43', 2, 200);
+
+SELECT * FROM t1 LEFT JOIN t2 ON a1 = a2
+ WHERE ( dt IS NULL OR FALSE ) AND b2 IS NULL;
+
+DROP TABLE t1,t2;
+
SET optimizer_switch=@save_optimizer_switch;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 0c28afafdd9..47cd46f209e 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -4563,5 +4563,24 @@ SELECT * FROM t1, t2 WHERE c=b AND (1=2 OR ((b='h' OR a=136) AND d=b));
DROP TABLE t1,t2;
+--echo #
+--echo # Bug mdev-4944: range conditition in OR formula with fields
+--echo # belonging to multiple equalities
+--echo #
+
+CREATE TABLE t1 (i1 int, j1 int) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1,8);
+
+CREATE TABLE t2 (i2 int, INDEX idx (i2)) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8), (9);
+
+EXPLAIN EXTENDED
+SELECT * FROM t1, t2
+ WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+SELECT * FROM t1, t2
+ WHERE i1 = i2 AND ( FALSE OR ( j1 > 27 AND j1 < 100 OR j1 <= 3 ) AND j1 = i2 );
+
+DROP TABLE t1,t2;
+
--echo End of 5.3 tests
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index d4062f5e0c9..fa77db45e81 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1262,17 +1262,12 @@ JOIN::optimize()
conds=new Item_int((longlong) 1,1); // Always true
}
- if (const_tables && conds)
+ if (impossible_where)
{
- conds= remove_eq_conds(thd, conds, &cond_value);
- if (cond_value == Item::COND_FALSE)
- {
- zero_result_cause=
- "Impossible WHERE noticed after reading const tables";
- select_lex->mark_const_derived(zero_result_cause);
- conds=new Item_int((longlong) 0,1);
- goto setup_subq_exit;
- }
+ zero_result_cause=
+ "Impossible WHERE noticed after reading const tables";
+ select_lex->mark_const_derived(zero_result_cause);
+ goto setup_subq_exit;
}
select= make_select(*table, const_table_map,
@@ -3598,6 +3593,18 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
}
}
+ join->impossible_where= false;
+ if (conds && const_count)
+ {
+ conds= remove_eq_conds(join->thd, conds, &join->cond_value);
+ if (join->cond_value == Item::COND_FALSE)
+ {
+ join->impossible_where= true;
+ conds=new Item_int((longlong) 0,1);
+ }
+ join->conds= conds;
+ }
+
/* Calc how many (possible) matched records in each table */
for (s=stat ; s < stat_end ; s++)
@@ -13692,7 +13699,20 @@ internal_remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
}
else
{
- li.replace(new_item);
+ if (new_item->type() == Item::COND_ITEM &&
+ ((Item_cond*) new_item)->functype() ==
+ ((Item_cond*) cond)->functype())
+ {
+ List<Item> *new_item_arg_list=
+ ((Item_cond *) new_item)->argument_list();
+ uint cnt= new_item_arg_list->elements;
+ li.replace(*new_item_arg_list);
+ /* Make iterator li ignore new items */
+ for (cnt--; cnt; cnt--)
+ li++;
+ }
+ else
+ li.replace(new_item);
should_fix_fields= 1;
}
}
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 478eede7108..af51c7dad48 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1160,6 +1160,11 @@ public:
bool cleaned;
DYNAMIC_ARRAY keyuse;
Item::cond_result cond_value, having_value;
+ /**
+ Impossible where after reading const tables
+ (set in make_join_statistics())
+ */
+ bool impossible_where;
List<Item> all_fields; ///< to store all fields that used in query
///Above list changed to use temporary table
List<Item> tmp_all_fields1, tmp_all_fields2, tmp_all_fields3;
View Lorry Controller Status