[MDEV-8698] Wrong result for SELECT..WHERE a BETWEEN 'a' AND 'c' COLLATE latin1_bin; Created: 2015-08-29  Updated: 2015-09-11  Resolved: 2015-08-29

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

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

Issue Links:
Blocks
blocks MDEV-8728 Fix a number of problems in equal fie... Closed
Relates
relates to MDEV-8680 Wrong result for SELECT..WHERE a IN (... Closed

 Description   

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).
Generated at Thu Feb 08 07:29:06 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.