Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.2.14
-
None
-
Ubuntu Server 16.04 LTS
Description
In MariaDB 10.2.14 the following SQLs do not work as intended, specifically BETWEEN seems to be broken. In 10.2.13 everything works as expected.
-- Not work
|
SELECT
|
*
|
FROM (SELECT date('2018-01-01') AS DAT |
UNION ALL |
SELECT date('2018-01-01') AS DAT) AS t |
WHERE t.DAT BETWEEN date ('2017-01-01') AND date ('2019-01-01'); |
|
|
-- not work
|
SELECT
|
*
|
FROM (SELECT date('2018-01-01') AS d) AS t |
WHERE t.d BETWEEN date ('2017-01-01') AND date ('2019-01-01'); |
-- work
|
SELECT 'aaaa' AS d FROM dual |
WHERE date('2018-01-01') BETWEEN date ('2017-01-01') AND date ('2019-01-01'); |
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
Activity
An extremely serious bug for us.
We expect you to fix it quickly!
Thanks!
A smaller test script:
SELECT * FROM (SELECT date('2018-01-01') AS d) AS t WHERE t.d BETWEEN date ('2017-01-01') AND date ('2019-01-01'); |
It returns one row in 10.1, and empty set in 10.2.
This patch fixes the problem:
diff --git a/sql/item.cc b/sql/item.cc |
index 007b4f4..5fc68e4 100644 |
--- a/sql/item.cc
|
+++ b/sql/item.cc
|
@@ -9862,7 +9862,7 @@ Item *Item_cache_temporal::convert_to_basic_const_item(THD *thd)
|
{
|
Item *new_item;
|
DBUG_ASSERT(value_cached || example != 0);
|
- if (null_value) |
+ if (!has_value()) |
new_item= (Item*) new (thd->mem_root) Item_null(thd);
|
else |
{
|
However:
- I'm not sure if it's correct (for example, perhaps the value should be cached right at the cache creation time, instead of the convert_to_basic_const() time)
- I guess it should be fixed the same way for all Item_cache_xxx::convert_to_basic_const_item(), but I could not make up covering scripts for Item_cache_int, Item_cache_real, Item_cache_decimal, Item_cache_str.
Reassigning to Igor who added Item_cache_temporal::convert_to_basic_const_item().
after be3651b7005ed550be5a2a4b8bb2c600f9870cfa commit (10.2):
explain extended SELECT *FROM (SELECT date('2018-01-01') AS DATUNION ALLSELECT date('2018-01-01') AS DAT) AS tWHERE t.DAT BETWEEN date ('2017-01-01') AND date ('2019-01-01');id select_type table type possible_keys key key_len ref rows filtered Extra1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE3 UNION NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHEREWarnings:Note 1003 select `t`.`DAT` AS `DAT` from (select cast('2018-01-01' as date) AS `DAT` union all select cast('2018-01-01' as date) AS `DAT`) `t` where `t`.`DAT` between <cache>(cast('2017-01-01' as date)) and <cache>(cast('2019-01-01' as date))explain extended SELECT *FROM (SELECT date('2018-01-01') AS d) AS tWHERE t.d BETWEEN date ('2017-01-01') AND date ('2019-01-01');id select_type table type possible_keys key key_len ref rows filtered Extra1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHEREWarnings:Note 1003 select NULL AS `d` from (select cast('2018-01-01' as date) AS `d`) `t` where 0before:
explain extended SELECT *FROM (SELECT date('2018-01-01') AS DATUNION ALLSELECT date('2018-01-01') AS DAT) AS tWHERE t.DAT BETWEEN date ('2017-01-01') AND date ('2019-01-01');id select_type table type possible_keys key key_len ref rows filtered Extra1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used3 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables usedWarnings:Note 1003 select `t`.`DAT` AS `DAT` from (select cast('2018-01-01' as date) AS `DAT` union all select cast('2018-01-01' as date) AS `DAT`) `t` where `t`.`DAT` between <cache>(cast('2017-01-01' as date)) and <cache>(cast('2019-01-01' as date))explain extended SELECT *FROM (SELECT date('2018-01-01') AS d) AS tWHERE t.d BETWEEN date ('2017-01-01') AND date ('2019-01-01');id select_type table type possible_keys key key_len ref rows filtered Extra1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.002 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables usedWarnings:Note 1003 select '2018-01-01' AS `d` from dual where 1