[MDEV-21436] Character comparison incorect Created: 2020-01-07  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Character Sets
Affects Version/s: 5.5, 10.1, 10.4.11, 10.2, 10.3, 10.4
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Eimantas Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: upstream
Environment:

debian buster + mariadb-10.4.11-linux-systemd-x86_64

Issue Links:
Relates
relates to MDEV-21533 'å' equals '[' in the latin1_swedish_... Confirmed

 Description   

Standard symbols comparison is not working as expected if using latin7 connection.

't' is not LIKE 'T' in latin7,
but is correct in latin1 or utf8.

Expected same comparison in all charsets, but it is different.

SQL:

set names latin1;
 
select 'T' = 't', 't' = 'T', 't' LIKE 'T', upper('t'), LOWER('T'), UPPER('t') = 'T';
 
+-----------+-----------+--------------+------------+------------+------------------+
 
| 'T' = 't' | 't' = 'T' | 't' LIKE 'T' | upper('t') | LOWER('T') | UPPER('t') = 'T' |
 
+-----------+-----------+--------------+------------+------------+------------------+
 
|         1 |         1 |            1 | T          | t          |                1 |
 
+-----------+-----------+--------------+------------+------------+------------------+
 
 
 
set names latin7;
 
select 'T' = 't', 't' = 'T', 't' LIKE 'T', upper('t'), LOWER('T'), UPPER('t') = 'T';
 
+-----------+-----------+--------------+------------+------------+------------------+
 
| 'T' = 't' | 't' = 'T' | 't' LIKE 'T' | upper('t') | LOWER('T') | UPPER('t') = 'T' |
 
+-----------+-----------+--------------+------------+------------+------------------+
 
|         0 |         0 |            0 | T          | t          |                1 |
 
+-----------+-----------+--------------+------------+------------+------------------+
 
 
 
set names utf8;
 
select 'T' = 't', 't' = 'T', 't' LIKE 'T', upper('t'), LOWER('T'), UPPER('t') = 'T';
 
+-----------+-----------+--------------+------------+------------+------------------+
 
| 'T' = 't' | 't' = 'T' | 't' LIKE 'T' | upper('t') | LOWER('T') | UPPER('t') = 'T' |
 
+-----------+-----------+--------------+------------+------------+------------------+
 
|         1 |         1 |            1 | T          | t          |                1 |
 
+-----------+-----------+--------------+------------+------------+------------------+

MySQL same type of bug
https://bugs.mysql.com/bug.php?id=86622

 Comments   
Comment by Alexander Barkov [ 2020-06-23 ]

The same problem is repeatable with:

  • Ø ø
  • Ŗ ŗ

CREATE OR REPLACE TABLE t1 (a VARCHAR(32) CHARACTER SET latin7);
 
INSERT INTO t1 VALUES ('T'),('t');
 
INSERT INTO t1 VALUES (0xA8),(0xB8);
 
INSERT INTO t1 VALUES (0xAA),(0xBA);
 
SELECT t1.a,t2.a, t1.a=t2.a FROM t1, t1 t2 ORDER BY t1.a,t1.a;


+------+------+-----------+
 
| a    | a    | t1.a=t2.a |
 
+------+------+-----------+
 
| Ø    | Ø    |         1 |
 
| Ø    | ŗ    |         0 |
 
| Ø    | t    |         0 |
 
| Ø    | Ŗ    |         0 |
 
| Ø    | T    |         0 |
 
| Ø    | ø    |         0 |
 
| ø    | T    |         0 |
 
| ø    | ø    |         1 |
 
| ø    | Ø    |         0 |
 
| ø    | ŗ    |         0 |
 
| ø    | t    |         0 |
 
| ø    | Ŗ    |         0 |
 
| Ŗ    | Ŗ    |         1 |
 
| Ŗ    | T    |         0 |
 
| Ŗ    | ø    |         0 |
 
| Ŗ    | Ø    |         0 |
 
| Ŗ    | ŗ    |         0 |
 
| Ŗ    | t    |         0 |
 
| ŗ    | ŗ    |         1 |
 
| ŗ    | t    |         0 |
 
| ŗ    | Ŗ    |         0 |
 
| ŗ    | T    |         0 |
 
| ŗ    | ø    |         0 |
 
| ŗ    | Ø    |         0 |
 
| t    | t    |         1 |
 
| t    | Ŗ    |         0 |
 
| t    | T    |         0 |
 
| t    | ø    |         0 |
 
| t    | Ø    |         0 |
 
| t    | ŗ    |         0 |
 
| T    | T    |         1 |
 
| T    | ø    |         0 |
 
| T    | Ø    |         0 |
 
| T    | ŗ    |         0 |
 
| T    | t    |         0 |
 
| T    | Ŗ    |         0 |
 
+------+------+-----------+

Generated at Thu Feb 08 09:07:07 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.