[MDEV-8698] Wrong result for SELECT..WHERE a BETWEEN 'a' AND 'c' COLLATE latin1_bin; - Jira

XML

Word

Printable

This script:

SET NAMES latin1;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a VARCHAR(10));

INSERT INTO t1 VALUES ('a'),('A'),('b'),('B'),('c'),('C');

SELECT * FROM t1 WHERE a BETWEEN 'a' AND 'c' COLLATE latin1_bin;

returns 3 rows:

+------+

| a    |

+------+

| a    |

| b    |

| c    |

+------+

So far so good.
If I make the condition stricter:

SELECT * FROM t1 WHERE a BETWEEN 'a' AND 'c' COLLATE latin1_bin AND a='a';

it returns 2 rows:

+------+

| a    |

+------+

| a    |

| A    |

+------+

The above result is incorrect. It should return only one row, with 'a' (lower case).

blocks

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

relates to

MDEV-8680 Wrong result for SELECT..WHERE a IN ('a' COLLATE latin1_bin,'b') AND a='a'