diff options
| author | Alexander Barkov <bar@mysql.com> | 2010-11-19 20:15:47 +0300 |
|---|---|---|
| committer | Alexander Barkov <bar@mysql.com> | 2010-11-19 20:15:47 +0300 |
| commit | ba68b26ae93855a595efc8d80ccbb9b96bbdeb53 (patch) | |
| tree | 3cc1e07c8e586dcdda173c67dc0ad20d9ea83d69 /mysql-test/include | |
| parent | 76ce2feb5fb5a280049c49becad3806cd58db5c3 (diff) | |
| download | mariadb-git-ba68b26ae93855a595efc8d80ccbb9b96bbdeb53.tar.gz | |
Bug#58190 BETWEEN no longer uses indexes for date or datetime fields
Regression introduced by WL#2649.
Problem: queries with date/datetime columns did not use indexes:
set names non_latin1_charset;
select * from date_index_test
where date_column between '2010-09-01' and '2010-10-01';
before WL#2649 indexes worked fine because charset of
date/datetime
columns was BINARY which always won.
Fix: testing that collation of the operation matches collation
of the field is only needed in case of "real" string data types.
For DATE, DATETIME it's not needed.
@ mysql-test/include/ctype_numconv.inc
@ mysql-test/r/ctype_binary.result
@ mysql-test/r/ctype_cp1251.result
@ mysql-test/r/ctype_latin1.result
@ mysql-test/r/ctype_ucs.result
@ mysql-test/r/ctype_utf8.result
Adding tests
@ sql/field.h
Adding new method Field_str::match_collation_to_optimize_range()
for use in opt_range.cc to distinguish between
"real string" types like CHAR, VARCHAR, TEXT
(Field_string, Field_varstring, Field_blob)
and "almost string" types DATE, TIME, DATETIME
(Field_newdate, Field_datetime, Field_time, Field_timestamp)
@ sql/opt_range.cc
Using new method instead of checking result_type() against STRING result.
Note:
Another part of this problem (which is not regression)
is submitted separately (see bug##58329).
Diffstat (limited to 'mysql-test/include')
| -rw-r--r-- | mysql-test/include/ctype_numconv.inc | 15 |
1 files changed, 15 insertions, 0 deletions
diff --git a/mysql-test/include/ctype_numconv.inc b/mysql-test/include/ctype_numconv.inc index c4a39879947..83d69cfa40d 100644 --- a/mysql-test/include/ctype_numconv.inc +++ b/mysql-test/include/ctype_numconv.inc @@ -1723,6 +1723,21 @@ DROP TABLE t1; --echo # +--echo # Bug#58190 BETWEEN no longer uses indexes for date or datetime fields +--echo # +SELECT @@collation_connection; +CREATE TABLE t1 ( + id INT(11) DEFAULT NULL, + date_column DATE DEFAULT NULL, + KEY(date_column)); +INSERT INTO t1 VALUES (1,'2010-09-01'),(2,'2010-10-01'); +EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01'; +ALTER TABLE t1 MODIFY date_column DATETIME DEFAULT NULL; +EXPLAIN SELECT * FROM t1 WHERE date_column BETWEEN '2010-09-01' AND '2010-10-01'; +DROP TABLE t1; + + +--echo # --echo # Bug#52159 returning time type from function and empty left join causes debug assertion --echo # CREATE FUNCTION f1() RETURNS TIME RETURN 1; |