[MDEV-8723] Wrong result for SELECT..WHERE COLLATION(a)='binary' AND a='a' - Jira

XML

Word

Printable

In this script:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARBINARY(10));

INSERT INTO t1 VALUES ('a');

SELECT * FROM t1 WHERE COLLATION(a)='binary';

SELECT * FROM t1 WHERE a='a';

the two SELECT queries return one row.

Now if I join both conditions using AND in the same query:

SELECT * FROM t1 WHERE COLLATION(a)='binary' AND a='a';

It returns empty set.

A similar script:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARBINARY(10));

INSERT INTO t1 VALUES ('a');

SELECT * FROM t1 WHERE CHARSET(a)='binary';

SELECT * FROM t1 WHERE a='a';

SELECT * FROM t1 WHERE CHARSET(a)='binary' AND a='a';

A similar script:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARBINARY(10));

INSERT INTO t1 VALUES ('a');

SELECT * FROM t1 WHERE COERCIBILITY(a)=2;

SELECT * FROM t1 WHERE a='a';

SELECT * FROM t1 WHERE COERCIBILITY(a)=2 AND a='a';

A similar script:

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARBINARY(10));

INSERT INTO t1 VALUES ('a');

SELECT * FROM t1 WHERE WEIGHT_STRING(a)='a';

SELECT * FROM t1 WHERE a='a';

SELECT * FROM t1 WHERE WEIGHT_STRING(a)='a' AND a='a';

blocks

MDEV-8728 Fix a number of problems in equal field and equal expression propagation

relates to

MDEV-8695 Wrong result for SELECT..WHERE varchar_column='a' AND CRC32(varchar_column)=3904355907