summaryrefslogtreecommitdiff
path: root/mysql-test/t/range.test
diff options
context:
space:
mode:
authorMartin Hansson <martin.hansson@sun.com>2009-11-02 13:24:07 +0100
committerMartin Hansson <martin.hansson@sun.com>2009-11-02 13:24:07 +0100
commit740b7c4e36aca09b43461543c318f48c379343cc (patch)
treec3d0d1053c98a11a0ee39be7f8a6159d69706175 /mysql-test/t/range.test
parent1955c866243a2f125cbd8cdae271ee8d1a0fbeb6 (diff)
downloadmariadb-git-740b7c4e36aca09b43461543c318f48c379343cc.tar.gz
Bug#47925: regression of range optimizer and date comparison in 5.1.39!
When a query was using a DATE or DATETIME value formatted using any other separator characters beside hyphen '-', a query with a greater-or-equal '>=' condition matching only the greatest value in an indexed column, the result was empty if index range scan was employed. The range optimizer got a new feature between 5.1.38 and 5.1.39 that changes a greater-or-equal condition to a greater-than if the value matching that in the query was not present in the table. But the value comparison function compared the dates as strings instead of dates. The bug was fixed by splitting the function get_date_from_str in two: One part that parses and does error checking. This function is now visible outside the module. The old get_date_from_str now calls the new function. mysql-test/r/range.result: Bug#47925: Test result mysql-test/t/range.test: Bug#47925: Test case sql/item.cc: Bug#47925: Fix + some edit on the comments sql/item.h: Bug#47925: Changed function signature sql/item_cmpfunc.cc: Bug#47925: Split function in two sql/item_cmpfunc.h: Bug#47925: Declaration of new function sql/opt_range.cc: Bug#47925: Added THD to function call sql/time.cc: Bug#47925: Added microsecond comparison
Diffstat (limited to 'mysql-test/t/range.test')
-rw-r--r--mysql-test/t/range.test79
1 files changed, 79 insertions, 0 deletions
diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test
index f0fa99f3d95..3a845471cd0 100644
--- a/mysql-test/t/range.test
+++ b/mysql-test/t/range.test
@@ -1181,4 +1181,83 @@ INSERT INTO t1 VALUES (1), (NULL);
SELECT * FROM t1 WHERE a <> NULL and (a <> NULL or a <= NULL);
DROP TABLE t1;
+--echo #
+--echo # Bug#47925: regression of range optimizer and date comparison in 5.1.39!
+--echo #
+CREATE TABLE t1 ( a DATE, KEY ( a ) );
+CREATE TABLE t2 ( a DATETIME, KEY ( a ) );
+
+--echo # Make optimizer choose range scan
+INSERT INTO t1 VALUES ('2009-09-22'), ('2009-09-22'), ('2009-09-22');
+INSERT INTO t1 VALUES ('2009-09-23'), ('2009-09-23'), ('2009-09-23');
+
+INSERT INTO t2 VALUES ('2009-09-22 12:00:00'), ('2009-09-22 12:00:00'),
+ ('2009-09-22 12:00:00');
+INSERT INTO t2 VALUES ('2009-09-23 12:00:00'), ('2009-09-23 12:00:00'),
+ ('2009-09-23 12:00:00');
+
+--echo # DATE vs DATE
+--replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X
+EXPLAIN
+SELECT * FROM t1 WHERE a >= '2009/09/23';
+SELECT * FROM t1 WHERE a >= '2009/09/23';
+SELECT * FROM t1 WHERE a >= '20090923';
+SELECT * FROM t1 WHERE a >= 20090923;
+SELECT * FROM t1 WHERE a >= '2009-9-23';
+SELECT * FROM t1 WHERE a >= '2009.09.23';
+SELECT * FROM t1 WHERE a >= '2009:09:23';
+
+--echo # DATE vs DATETIME
+--replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X
+EXPLAIN
+SELECT * FROM t2 WHERE a >= '2009/09/23';
+SELECT * FROM t2 WHERE a >= '2009/09/23';
+SELECT * FROM t2 WHERE a >= '2009/09/23';
+SELECT * FROM t2 WHERE a >= '20090923';
+SELECT * FROM t2 WHERE a >= 20090923;
+SELECT * FROM t2 WHERE a >= '2009-9-23';
+SELECT * FROM t2 WHERE a >= '2009.09.23';
+SELECT * FROM t2 WHERE a >= '2009:09:23';
+
+--echo # DATETIME vs DATETIME
+--replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X
+EXPLAIN
+SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00';
+SELECT * FROM t2 WHERE a >= '2009/09/23 12:00:00';
+SELECT * FROM t2 WHERE a >= '20090923120000';
+SELECT * FROM t2 WHERE a >= 20090923120000;
+SELECT * FROM t2 WHERE a >= '2009-9-23 12:00:00';
+SELECT * FROM t2 WHERE a >= '2009.09.23 12:00:00';
+SELECT * FROM t2 WHERE a >= '2009:09:23 12:00:00';
+
+--echo # DATETIME vs DATE
+--replace_column 1 X 2 X 3 X 7 X 8 X 9 X 10 X
+EXPLAIN
+SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00';
+SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00';
+SELECT * FROM t1 WHERE a >= '2009/09/23 00:00:00';
+SELECT * FROM t1 WHERE a >= '20090923000000';
+SELECT * FROM t1 WHERE a >= 20090923000000;
+SELECT * FROM t1 WHERE a >= '2009-9-23 00:00:00';
+SELECT * FROM t1 WHERE a >= '2009.09.23 00:00:00';
+SELECT * FROM t1 WHERE a >= '2009:09:23 00:00:00';
+
+--echo # Test of the new get_date_from_str implementation
+--echo # Behavior differs slightly between the trunk and mysql-pe.
+--echo # The former may give errors for the truncated values, while the latter
+--echo # gives warnings. The purpose of this test is not to interfere, and only
+--echo # preserve existing behavior.
+SELECT str_to_date('2007-10-00', '%Y-%m-%d') >= '' AND
+ str_to_date('2007-10-00', '%Y-%m-%d') <= '2007/10/20';
+
+SELECT str_to_date('2007-20-00', '%Y-%m-%d') >= '2007/10/20' AND
+ str_to_date('2007-20-00', '%Y-%m-%d') <= '';
+
+SELECT str_to_date('2007-10-00', '%Y-%m-%d') BETWEEN '' AND '2007/10/20';
+SELECT str_to_date('2007-20-00', '%Y-%m-%d') BETWEEN '2007/10/20' AND '';
+
+SELECT str_to_date('', '%Y-%m-%d');
+
+DROP TABLE t1, t2;
+
--echo End of 5.1 tests