[MDEV-27484] Inconsistent result with implicit cast upon comparison strings and numbers Created: 2022-01-12  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.9, 10.10, 10.11, 11.0, 11.1
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Trivial
Reporter: Elena Stepanova Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: upstream


 Description    

CREATE TABLE t (f char(8));
 
INSERT INTO t VALUES (''),('');
 
SELECT * FROM t WHERE f BETWEEN 'a' AND 3;
 
SELECT * FROM t WHERE f >= 'a';
 
 
 
# Cleanup
 
DROP TABLE t;

BETWEEN query returns a result set (with warnings), while less restrictive >= returns an empty result set.

SELECT * FROM t WHERE f BETWEEN 'a' AND 3;
 
f
 
 
 
 
 
Warnings:
 
Warning	1292	Truncated incorrect DOUBLE value: '        '
 
Warning	1292	Truncated incorrect DOUBLE value: 'a'
 
Warning	1292	Truncated incorrect DOUBLE value: '        '
 
Warning	1292	Truncated incorrect DOUBLE value: 'a'
 
SELECT * FROM t WHERE f >= 'a';
 
f
 
DROP TABLE t;

It may be not a bug at all, if 'a' is first cast to a number, and then both numbers are cast back to strings; but this is certainly weird behavior at the end, that a more restrictive condition (BETWEEN) returns more results than a less restrictive one (>=).
Generated at Thu Feb 08 09:53:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.