[MDEV-8257] Erroneous "Impossible where" when mixing decimal comparison and LIKE Created: 2015-06-01  Updated: 2015-06-09  Resolved: 2015-06-02

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.0, 10.1
Fix Version/s: 10.1.5

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None


 Description   

This script:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a DECIMAL(8,2));
 
INSERT INTO t1 VALUES (10);
 
SELECT * FROM t1 WHERE a=10.0;
 
SELECT * FROM t1 WHERE a LIKE 10.00;

returns one row in both SELECT queries.

Now if I join the two conditions into a single query:

SELECT * FROM t1 WHERE a=10.0 AND a LIKE 10.00;

it returns empty set.

EXPLAIN tells that where condition is Impossible:

MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=10.0 AND a LIKE 10.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 |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+

The above is wrong.
It seems that LIKE erroneously sets cmp_context for the arguments to DECIMAL_RESULT.
It should set cmp_context either to STRING_RESULT or keep IMPOSSIBLE_RESULT.
Generated at Thu Feb 08 07:25:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.