summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--mysql-test/r/select.result85
-rw-r--r--mysql-test/t/select.test50
-rw-r--r--sql/item_cmpfunc.cc46
-rw-r--r--sql/item_cmpfunc.h4
-rw-r--r--sql/sql_select.cc2
5 files changed, 180 insertions, 7 deletions
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index d0b2a575a32..fa030670d6e 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -4441,6 +4441,91 @@ SELECT 1 FROM t2 JOIN t1 ON 1=1
WHERE a != '1' AND NOT a >= b OR NOT ROW(b,a )<> ROW(a,a);
1
DROP TABLE t1,t2;
+#
+# Bug #49199: Optimizer handles incorrectly:
+# field='const1' AND field='const2' in some cases
+
+CREATE TABLE t1(a DATETIME NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01');
+SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+a
+2001-01-01 00:00:00
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
+Warnings:
+Note 1003 select '2001-01-01 00:00:00' AS `a` from `test`.`t1` where 1
+DROP TABLE t1;
+CREATE TABLE t1(a DATE NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01');
+SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+a
+2001-01-01
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
+Warnings:
+Note 1003 select '2001-01-01' AS `a` from `test`.`t1` where 1
+DROP TABLE t1;
+CREATE TABLE t1(a TIMESTAMP NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01');
+SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+a
+2001-01-01 00:00:00
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
+Warnings:
+Note 1003 select '2001-01-01 00:00:00' AS `a` from `test`.`t1` where 1
+DROP TABLE t1;
+CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
+SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
+a b
+2001-01-01 00:00:00 2001-01-01
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
+Warnings:
+Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from `test`.`t1` where 1
+DROP TABLE t1;
+CREATE TABLE t1(a DATETIME NOT NULL, b VARCHAR(20) NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
+SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
+a b
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
+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 '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from `test`.`t1` where 0
+SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01';
+a b
+2001-01-01 00:00:00 2001-01-01
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01';
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
+Warnings:
+Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01' AS `b` from `test`.`t1` where 1
+DROP TABLE t1;
+CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
+SELECT x.a, y.a, z.a FROM t1 x
+JOIN t1 y ON x.a=y.a
+JOIN t1 z ON y.a=z.a
+WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00';
+a a a
+2001-01-01 00:00:00 2001-01-01 00:00:00 2001-01-01 00:00:00
+EXPLAIN EXTENDED SELECT x.a, y.a, z.a FROM t1 x
+JOIN t1 y ON x.a=y.a
+JOIN t1 z ON y.a=z.a
+WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00';
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 SIMPLE x system NULL NULL NULL NULL 1 100.00
+1 SIMPLE y system NULL NULL NULL NULL 1 100.00
+1 SIMPLE z system NULL NULL NULL NULL 1 100.00
+Warnings:
+Note 1003 select '2001-01-01 00:00:00' AS `a`,'2001-01-01 00:00:00' AS `a`,'2001-01-01 00:00:00' AS `a` from `test`.`t1` `x` join `test`.`t1` `y` join `test`.`t1` `z` where 1
+DROP TABLE t1;
End of 5.0 tests
create table t1(a INT, KEY (a));
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index ac65e5cbaf5..116a09f5d00 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -3786,6 +3786,56 @@ SELECT 1 FROM t2 JOIN t1 ON 1=1
DROP TABLE t1,t2;
+--echo #
+--echo # Bug #49199: Optimizer handles incorrectly:
+--echo # field='const1' AND field='const2' in some cases
+--echo
+CREATE TABLE t1(a DATETIME NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01');
+SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+DROP TABLE t1;
+
+CREATE TABLE t1(a DATE NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01');
+SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+DROP TABLE t1;
+
+CREATE TABLE t1(a TIMESTAMP NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01');
+SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a='2001-01-01 00:00:00';
+DROP TABLE t1;
+
+CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
+SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
+DROP TABLE t1;
+
+CREATE TABLE t1(a DATETIME NOT NULL, b VARCHAR(20) NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
+SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01' AND a=b AND b='2001-01-01 00:00:00';
+
+SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01';
+EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='2001-01-01 00:00:00' AND a=b AND b='2001-01-01';
+DROP TABLE t1;
+
+CREATE TABLE t1(a DATETIME NOT NULL, b DATE NOT NULL);
+INSERT INTO t1 VALUES('2001-01-01', '2001-01-01');
+SELECT x.a, y.a, z.a FROM t1 x
+ JOIN t1 y ON x.a=y.a
+ JOIN t1 z ON y.a=z.a
+ WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00';
+EXPLAIN EXTENDED SELECT x.a, y.a, z.a FROM t1 x
+ JOIN t1 y ON x.a=y.a
+ JOIN t1 z ON y.a=z.a
+ WHERE x.a='2001-01-01' AND z.a='2001-01-01 00:00:00';
+DROP TABLE t1;
+
+
--echo End of 5.0 tests
#
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 5415d6f4f8a..26be4018de8 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -5292,7 +5292,8 @@ Item *Item_bool_rowready_func2::negated_item()
}
Item_equal::Item_equal(Item_field *f1, Item_field *f2)
- : Item_bool_func(), const_item(0), eval_item(0), cond_false(0)
+ : Item_bool_func(), const_item(0), eval_item(0), cond_false(0),
+ compare_as_dates(FALSE)
{
const_item_cache= 0;
fields.push_back(f1);
@@ -5305,6 +5306,7 @@ Item_equal::Item_equal(Item *c, Item_field *f)
const_item_cache= 0;
fields.push_back(f);
const_item= c;
+ compare_as_dates= f->is_datetime();
}
@@ -5319,9 +5321,45 @@ Item_equal::Item_equal(Item_equal *item_equal)
fields.push_back(item);
}
const_item= item_equal->const_item;
+ compare_as_dates= item_equal->compare_as_dates;
cond_false= item_equal->cond_false;
}
+
+void Item_equal::compare_const(Item *c)
+{
+ if (compare_as_dates)
+ {
+ cmp.set_datetime_cmp_func(this, &c, &const_item);
+ cond_false= cmp.compare();
+ }
+ else
+ {
+ Item_func_eq *func= new Item_func_eq(c, const_item);
+ func->set_cmp_func();
+ func->quick_fix_field();
+ cond_false= !func->val_int();
+ }
+ if (cond_false)
+ const_item_cache= 1;
+}
+
+
+void Item_equal::add(Item *c, Item_field *f)
+{
+ if (cond_false)
+ return;
+ if (!const_item)
+ {
+ DBUG_ASSERT(f);
+ const_item= c;
+ compare_as_dates= f->is_datetime();
+ return;
+ }
+ compare_const(c);
+}
+
+
void Item_equal::add(Item *c)
{
if (cond_false)
@@ -5331,11 +5369,7 @@ void Item_equal::add(Item *c)
const_item= c;
return;
}
- Item_func_eq *func= new Item_func_eq(c, const_item);
- func->set_cmp_func();
- func->quick_fix_field();
- if ((cond_false= !func->val_int()))
- const_item_cache= 1;
+ compare_const(c);
}
void Item_equal::add(Item_field *f)
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 52af6a31c0c..e2c070e2bdb 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1583,7 +1583,9 @@ class Item_equal: public Item_bool_func
List<Item_field> fields; /* list of equal field items */
Item *const_item; /* optional constant item equal to fields items */
cmp_item *eval_item;
+ Arg_comparator cmp;
bool cond_false;
+ bool compare_as_dates;
public:
inline Item_equal()
: Item_bool_func(), const_item(0), eval_item(0), cond_false(0)
@@ -1592,6 +1594,8 @@ public:
Item_equal(Item *c, Item_field *f);
Item_equal(Item_equal *item_equal);
inline Item* get_const() { return const_item; }
+ void compare_const(Item *c);
+ void add(Item *c, Item_field *f);
void add(Item *c);
void add(Item_field *f);
uint members();
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index b2a4958020a..8a5a8176561 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -7527,7 +7527,7 @@ static bool check_simple_equality(Item *left_item, Item *right_item,
already contains a constant and its value is not equal to
the value of const_item.
*/
- item_equal->add(const_item);
+ item_equal->add(const_item, field_item);
}
else
{
View Lorry Controller Status