Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL)
-
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.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
This script: {code} 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; {code} returns one row in both SELECT queries. Now if I join the two conditions into a single query: {code} SELECT * FROM t1 WHERE a=10.0 AND a LIKE 10.00; {code} it returns empty set. EXPLAIN tells that where condition is Impossible: {noformat} 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 | +------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+ {noformat} |
This script: {code} 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; {code} returns one row in both SELECT queries. Now if I join the two conditions into a single query: {code} SELECT * FROM t1 WHERE a=10.0 AND a LIKE 10.00; {code} it returns empty set. EXPLAIN tells that where condition is Impossible: {noformat} 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 | +------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+ {noformat} 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. |
| Component/s | Optimizer [ 10200 ] | |
| Fix Version/s | 10.1.5 [ 18813 ] | |
| Fix Version/s | 10.1 [ 16100 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 69813 ] | MariaDB v4 [ 149232 ] |