[MDEV-7149] Constant propagation erroneously applied for LIKE - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL)
Fix Version/s: 10.0.16
Component/s: Optimizer
Labels:
None

Description

SET NAMES utf8 COLLATE utf8_german2_ci;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci);

INSERT INTO t1 VALUES ('ae'),('ä');

SELECT * FROM t1 WHERE a='ae';

SELECT * FROM t1 WHERE a LIKE 'ä';

returns these results:

+------+

| a    |

+------+

| ae   |

| ä    |

+------+

2 rows in set (0.01 sec)

+------+

| a    |

+------+

| ä    |

+------+

1 row in set (0.00 sec)

This is correct. Equality works taking into account contractions and expansions and returns both rows, while LIKE is performed one-character-to-one-character, so only one record matches.

Now if I join both conditions with AND in a single WHERE, I expect one row with 'ä' to be returned, as it matches both conditions:

SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';

But in fact I get an empty set.

EXPLAIN EXTENDED returns "Impossible where", which is not correct:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';

+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+

| 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 |

+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+

After tracing the code, it seems that constant propagation was erroneously applied.

Attachments

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2014-11-20 12:19

Alexander Barkov made changes - 2014-11-20 12:20

Field	Original Value	New Value
Description	{code:sql} SET NAMES utf8 COLLATE utf8_german2_ci; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci); INSERT INTO t1 VALUES ('ae'),('ä'); SELECT * FROM t1 WHERE a='ae'; SELECT * FROM t1 WHERE a LIKE 'ä'; {code} returns these results: {noformat} +------+ \| a \| +------+ \| ae \| \| ä \| +------+ 2 rows in set (0.01 sec) +------+ \| a \| +------+ \| ä \| +------+ 1 row in set (0.00 sec) {noformat} This is correct. Equality works taking into account contractions and expansions, while LIKE is performed one-character-to-one-character. Now if I join both conditions with AND in a single WHERE, I expect one row with 'ä' to be returned: {code:sql} SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä'; {code} But in fact I get empty set. EXPLAIN EXTENDED returns "Impossible where", which is not correct: {noformat} +------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+ \| 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}	{code:sql} SET NAMES utf8 COLLATE utf8_german2_ci; DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci); INSERT INTO t1 VALUES ('ae'),('ä'); SELECT * FROM t1 WHERE a='ae'; SELECT * FROM t1 WHERE a LIKE 'ä'; {code} returns these results: {noformat} +------+ \| a \| +------+ \| ae \| \| ä \| +------+ 2 rows in set (0.01 sec) +------+ \| a \| +------+ \| ä \| +------+ 1 row in set (0.00 sec) {noformat} This is correct. Equality works taking into account contractions and expansions and returns both rows, while LIKE is performed one-character-to-one-character, so only one record matches. Now if I join both conditions with AND in a single WHERE, I expect one row with 'ä' to be returned: {code:sql} SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä'; {code} But in fact I get empty set. EXPLAIN EXTENDED returns "Impossible where", which is not correct: {noformat} +------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+ \| 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}

Alexander Barkov made changes - 2014-11-20 12:20

Description

{code:sql}
SET NAMES utf8 COLLATE utf8_german2_ci;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci);
INSERT INTO t1 VALUES ('ae'),('ä');
SELECT * FROM t1 WHERE a='ae';
SELECT * FROM t1 WHERE a LIKE 'ä';
{code}
returns these results:
{noformat}
+------+
| a |
+------+
| ae |
| ä |
+------+
2 rows in set (0.01 sec)

+------+
| a |
+------+
| ä |
+------+
1 row in set (0.00 sec)
{noformat}
This is correct. Equality works taking into account contractions and expansions and returns both rows, while LIKE is performed one-character-to-one-character, so only one record matches.

Now if I join both conditions with AND in a single WHERE, I expect one row with 'ä' to be returned:
{code:sql}
SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';
{code}
But in fact I get empty set.

EXPLAIN EXTENDED returns "Impossible where", which is not correct:
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| 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}

{code:sql}
SET NAMES utf8 COLLATE utf8_german2_ci;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci);
INSERT INTO t1 VALUES ('ae'),('ä');
SELECT * FROM t1 WHERE a='ae';
SELECT * FROM t1 WHERE a LIKE 'ä';
{code}
returns these results:
{noformat}
+------+
| a |
+------+
| ae |
| ä |
+------+
2 rows in set (0.01 sec)

+------+
| a |
+------+
| ä |
+------+
1 row in set (0.00 sec)
{noformat}
This is correct. Equality works taking into account contractions and expansions and returns both rows, while LIKE is performed one-character-to-one-character, so only one record matches.

Now if I join both conditions with AND in a single WHERE, I expect one row with 'ä' to be returned, as it matches both conditions:
{code:sql}
SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';
{code}
But in fact I get empty set.

EXPLAIN EXTENDED returns "Impossible where", which is not correct:
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| 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}

Alexander Barkov made changes - 2014-11-20 12:20

Description

{code:sql}
SET NAMES utf8 COLLATE utf8_german2_ci;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci);
INSERT INTO t1 VALUES ('ae'),('ä');
SELECT * FROM t1 WHERE a='ae';
SELECT * FROM t1 WHERE a LIKE 'ä';
{code}
returns these results:
{noformat}
+------+
| a |
+------+
| ae |
| ä |
+------+
2 rows in set (0.01 sec)

+------+
| a |
+------+
| ä |
+------+
1 row in set (0.00 sec)
{noformat}
This is correct. Equality works taking into account contractions and expansions and returns both rows, while LIKE is performed one-character-to-one-character, so only one record matches.

Now if I join both conditions with AND in a single WHERE, I expect one row with 'ä' to be returned, as it matches both conditions:
{code:sql}
SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';
{code}
But in fact I get empty set.

EXPLAIN EXTENDED returns "Impossible where", which is not correct:
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| 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}

{code:sql}
SET NAMES utf8 COLLATE utf8_german2_ci;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci);
INSERT INTO t1 VALUES ('ae'),('ä');
SELECT * FROM t1 WHERE a='ae';
SELECT * FROM t1 WHERE a LIKE 'ä';
{code}
returns these results:
{noformat}
+------+
| a |
+------+
| ae |
| ä |
+------+
2 rows in set (0.01 sec)

+------+
| a |
+------+
| ä |
+------+
1 row in set (0.00 sec)
{noformat}
This is correct. Equality works taking into account contractions and expansions and returns both rows, while LIKE is performed one-character-to-one-character, so only one record matches.

Now if I join both conditions with AND in a single WHERE, I expect one row with 'ä' to be returned, as it matches both conditions:
{code:sql}
SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';
{code}
But in fact I get an empty set.

EXPLAIN EXTENDED returns "Impossible where", which is not correct:
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| 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}

Alexander Barkov made changes - 2014-11-20 12:21

Description

{code:sql}
SET NAMES utf8 COLLATE utf8_german2_ci;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci);
INSERT INTO t1 VALUES ('ae'),('ä');
SELECT * FROM t1 WHERE a='ae';
SELECT * FROM t1 WHERE a LIKE 'ä';
{code}
returns these results:
{noformat}
+------+
| a |
+------+
| ae |
| ä |
+------+
2 rows in set (0.01 sec)

+------+
| a |
+------+
| ä |
+------+
1 row in set (0.00 sec)
{noformat}
This is correct. Equality works taking into account contractions and expansions and returns both rows, while LIKE is performed one-character-to-one-character, so only one record matches.

Now if I join both conditions with AND in a single WHERE, I expect one row with 'ä' to be returned, as it matches both conditions:
{code:sql}
SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';
{code}
But in fact I get an empty set.

EXPLAIN EXTENDED returns "Impossible where", which is not correct:
{noformat}
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| 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}

{code:sql}
SET NAMES utf8 COLLATE utf8_german2_ci;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci);
INSERT INTO t1 VALUES ('ae'),('ä');
SELECT * FROM t1 WHERE a='ae';
SELECT * FROM t1 WHERE a LIKE 'ä';
{code}
returns these results:
{noformat}
+------+
| a |
+------+
| ae |
| ä |
+------+
2 rows in set (0.01 sec)

+------+
| a |
+------+
| ä |
+------+
1 row in set (0.00 sec)
{noformat}
This is correct. Equality works taking into account contractions and expansions and returns both rows, while LIKE is performed one-character-to-one-character, so only one record matches.

Now if I join both conditions with AND in a single WHERE, I expect one row with 'ä' to be returned, as it matches both conditions:
{code:sql}
SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';
{code}
But in fact I get an empty set.

EXPLAIN EXTENDED returns "Impossible where", which is not correct:
{noformat}
mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| 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}

Alexander Barkov made changes - 2014-11-20 12:22

Description

{code:sql}
SET NAMES utf8 COLLATE utf8_german2_ci;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci);
INSERT INTO t1 VALUES ('ae'),('ä');
SELECT * FROM t1 WHERE a='ae';
SELECT * FROM t1 WHERE a LIKE 'ä';
{code}
returns these results:
{noformat}
+------+
| a |
+------+
| ae |
| ä |
+------+
2 rows in set (0.01 sec)

+------+
| a |
+------+
| ä |
+------+
1 row in set (0.00 sec)
{noformat}
This is correct. Equality works taking into account contractions and expansions and returns both rows, while LIKE is performed one-character-to-one-character, so only one record matches.

Now if I join both conditions with AND in a single WHERE, I expect one row with 'ä' to be returned, as it matches both conditions:
{code:sql}
SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';
{code}
But in fact I get an empty set.

EXPLAIN EXTENDED returns "Impossible where", which is not correct:
{noformat}
mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| 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}

{code:sql}
SET NAMES utf8 COLLATE utf8_german2_ci;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_german2_ci);
INSERT INTO t1 VALUES ('ae'),('ä');
SELECT * FROM t1 WHERE a='ae';
SELECT * FROM t1 WHERE a LIKE 'ä';
{code}
returns these results:
{noformat}
+------+
| a |
+------+
| ae |
| ä |
+------+
2 rows in set (0.01 sec)

+------+
| a |
+------+
| ä |
+------+
1 row in set (0.00 sec)
{noformat}
This is correct. Equality works taking into account contractions and expansions and returns both rows, while LIKE is performed one-character-to-one-character, so only one record matches.

Now if I join both conditions with AND in a single WHERE, I expect one row with 'ä' to be returned, as it matches both conditions:
{code:sql}
SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';
{code}
But in fact I get an empty set.

EXPLAIN EXTENDED returns "Impossible where", which is not correct:
{noformat}
mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='ae' AND a LIKE 'ä';
+------+-------------+-------+------+---------------+------+---------+------+------+----------+------------------+
| 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}

After tracing the code, it seems that constant propagation was erroneously applied.

Alexander Barkov made changes - 2014-11-28 16:16

Component/s		Optimizer [ 10200 ]
Fix Version/s		10.0.16 [ 17900 ]
Fix Version/s	10.0 [ 16000 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:50

Workflow

MariaDB v2 [ 58633 ]

MariaDB v3 [ 61804 ]

Sergei Golubchik made changes - 2021-12-06 21:40

Workflow

MariaDB v3 [ 61804 ]

MariaDB v4 [ 148497 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2014-11-20 12:19

Updated:: 2014-11-28 16:16

Resolved:: 2014-11-28 16:16

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration