[MDEV-30661] UPPER() returns an empty string for U+0251 in uca1400 collations for utf8 - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.10(EOL), 10.11
Fix Version/s: 10.11.3, 11.0.2, 10.10.4
Component/s: Character Sets
Labels:
None

Description

The problem described in ~~MDEV-30556~~ is repeatable in 10.10 with uca1400 collations:

CREATE OR REPLACE TABLE bad_case_folding

  code INT NOT NULL,

  c VARCHAR(32) CHARACTER SET utf8mb4 COLLATE uca1400_ai_ci NOT NULL

);

DELIMITER $$

FOR code IN 0..0x10FFFF

DO

  BEGIN

    DECLARE str TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT CHAR(code USING utf32);

    IF LENGTH(LOWER(str))=0 OR LENGTH(UPPER(str))=0 THEN

      INSERT INTO bad_case_folding VALUES (code, str);

    END IF;

  END;

END FOR;

$$

DELIMITER ;

SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM bad_case_folding;

+-----------+---------------+---------------+----+

| HEX(code) | HEX(LOWER(c)) | HEX(UPPER(c)) | c  |

+-----------+---------------+---------------+----+

| 23A       |               | C8BA          | Ⱥ  |

| 23E       |               | C8BE          | Ⱦ  |

| 23F       | C8BF          |               | ȿ  |

| 240       | C980          |               | ɀ  |

| 250       | C990          |               | ɐ  |

| 251       | C991          |               | ɑ  |

| 252       | C992          |               | ɒ  |

| 26B       | C9AB          |               | ɫ  |

| 271       | C9B1          |               | ɱ  |

| 27D       | C9BD          |               | ɽ  |

+-----------+---------------+---------------+----+

Or a faster test version:

CREATE OR REPLACE TABLE bad_case_folding

  code INT NOT NULL,

  c VARCHAR(32) CHARACTER SET utf8mb4 COLLATE uca1400_ai_ci NOT NULL DEFAULT ''

);

INSERT INTO bad_case_folding (code) VALUES (0x23A),(0x23E),(0x23F),(0x240),(0x250),(0x251),(0x252),(0x26B),(0x271),(0x27D);

UPDATE bad_case_folding SET c=CHAR(code USING utf32);

SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM bad_case_folding ORDER BY code;

+-----------+---------------+---------------+----+

| HEX(code) | HEX(LOWER(c)) | HEX(UPPER(c)) | c  |

+-----------+---------------+---------------+----+

| 23A       |               | C8BA          | Ⱥ  |

| 23E       |               | C8BE          | Ⱦ  |

| 23F       | C8BF          |               | ȿ  |

| 240       | C980          |               | ɀ  |

| 250       | C990          |               | ɐ  |

| 251       | C991          |               | ɑ  |

| 252       | C992          |               | ɒ  |

| 26B       | C9AB          |               | ɫ  |

| 271       | C9B1          |               | ɱ  |

| 27D       | C9BD          |               | ɽ  |

+-----------+---------------+---------------+----+

Attachments

Issue Links

blocks

MDEV-19123 Change default charset from latin1 to utf8mb4

Closed

MDEV-25829 Change default Unicode collation to uca1400_ai_ci

Closed

MDEV-27490 Allow full utf8mb4 for identifiers

Stalled

causes

MCOL-5437 columnstore fails to compile to due old cs->casedn_multiply use

Closed

relates to

MDEV-30577 Case folding for uca1400 collations is not up to date

Closed

MDEV-30556 UPPER() returns an empty string for U+0251 in Unicode-5.2.0+ collations for utf8

Closed

(1 relates to)

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2023-02-16 07:33

Alexander Barkov made changes - 2023-02-16 07:33

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-30556~~ [ ~~MDEV-30556~~ ]

Alexander Barkov made changes - 2023-02-16 07:34

Description

The problem described in ~~MDEV-30556~~ is repeatable in 10.10 with uca1400 collations:

{code:sql}
CREATE OR REPLACE TABLE bad_case_folding
(
  code INT NOT NULL,
  c VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL
);
DELIMITER $$

FOR code IN 0..0x10FFFF
DO
  BEGIN
    DECLARE str TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT CHAR(code USING utf32);
    IF LENGTH(LOWER(str))=0 OR LENGTH(UPPER(str))=0 THEN
      INSERT INTO bad_case_folding VALUES (code, str);
    END IF;
  END;
END FOR;
$$
DELIMITER ;

SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM bad_case_folding;
{code}
{noformat}
+-----------+---------------+---------------+----+
| HEX(code) | HEX(LOWER(c)) | HEX(UPPER(c)) | c |
+-----------+---------------+---------------+----+
| 23A | | C8BA | Ⱥ |
| 23E | | C8BE | Ⱦ |
| 23F | C8BF | | ȿ |
| 240 | C980 | | ɀ |
| 250 | C990 | | ɐ |
| 251 | C991 | | ɑ |
| 252 | C992 | | ɒ |
| 26B | C9AB | | ɫ |
| 271 | C9B1 | | ɱ |
| 27D | C9BD | | ɽ |
+-----------+---------------+---------------+----+
{noformat}

Or a faster test version:

{code:sql}
CREATE OR REPLACE TABLE bad_case_folding
(
  code INT NOT NULL,
  c VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT ''
);
INSERT INTO bad_case_folding (code) VALUES (0x23A),(0x23E),(0x23F),(0x240),(0x250),(0x251),(0x252),(0x26B),(0x271),(0x27D);
UPDATE bad_case_folding SET c=CHAR(code USING utf32);
SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM bad_case_folding ORDER BY code;
{code}
{noformat}
+-----------+---------------+---------------+----+
| HEX(code) | HEX(LOWER(c)) | HEX(UPPER(c)) | c |
+-----------+---------------+---------------+----+
| 23A | | C8BA | Ⱥ |
| 23E | | C8BE | Ⱦ |
| 23F | C8BF | | ȿ |
| 240 | C980 | | ɀ |
| 250 | C990 | | ɐ |
| 251 | C991 | | ɑ |
| 252 | C992 | | ɒ |
| 26B | C9AB | | ɫ |
| 271 | C9B1 | | ɱ |
| 27D | C9BD | | ɽ |
+-----------+---------------+---------------+----+
{noformat}

The problem described in ~~MDEV-30556~~ is repeatable in 10.10 with uca1400 collations:

{code:sql}
CREATE OR REPLACE TABLE bad_case_folding
(
  code INT NOT NULL,
  c VARCHAR(32) CHARACTER SET utf8mb4 COLLATE uca1400_ai_ci NOT NULL
);
DELIMITER $$

FOR code IN 0..0x10FFFF
DO
  BEGIN
    DECLARE str TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci DEFAULT CHAR(code USING utf32);
    IF LENGTH(LOWER(str))=0 OR LENGTH(UPPER(str))=0 THEN
      INSERT INTO bad_case_folding VALUES (code, str);
    END IF;
  END;
END FOR;
$$
DELIMITER ;

SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM bad_case_folding;
{code}
{noformat}
+-----------+---------------+---------------+----+
| HEX(code) | HEX(LOWER(c)) | HEX(UPPER(c)) | c |
+-----------+---------------+---------------+----+
| 23A | | C8BA | Ⱥ |
| 23E | | C8BE | Ⱦ |
| 23F | C8BF | | ȿ |
| 240 | C980 | | ɀ |
| 250 | C990 | | ɐ |
| 251 | C991 | | ɑ |
| 252 | C992 | | ɒ |
| 26B | C9AB | | ɫ |
| 271 | C9B1 | | ɱ |
| 27D | C9BD | | ɽ |
+-----------+---------------+---------------+----+
{noformat}

Or a faster test version:

{code:sql}
CREATE OR REPLACE TABLE bad_case_folding
(
  code INT NOT NULL,
  c VARCHAR(32) CHARACTER SET utf8mb4 COLLATE uca1400_ai_ci NOT NULL DEFAULT ''
);
INSERT INTO bad_case_folding (code) VALUES (0x23A),(0x23E),(0x23F),(0x240),(0x250),(0x251),(0x252),(0x26B),(0x271),(0x27D);
UPDATE bad_case_folding SET c=CHAR(code USING utf32);
SELECT HEX(code), HEX(LOWER(c)), HEX(UPPER(c)), c FROM bad_case_folding ORDER BY code;
{code}
{noformat}
+-----------+---------------+---------------+----+
| HEX(code) | HEX(LOWER(c)) | HEX(UPPER(c)) | c |
+-----------+---------------+---------------+----+
| 23A | | C8BA | Ⱥ |
| 23E | | C8BE | Ⱦ |
| 23F | C8BF | | ȿ |
| 240 | C980 | | ɀ |
| 250 | C990 | | ɐ |
| 251 | C991 | | ɑ |
| 252 | C992 | | ɒ |
| 26B | C9AB | | ɫ |
| 271 | C9B1 | | ɱ |
| 27D | C9BD | | ɽ |
+-----------+---------------+---------------+----+
{noformat}

Alexander Barkov made changes - 2023-02-16 07:36

Link

This issue blocks MDEV-27490 [ MDEV-27490 ]

Alexander Barkov made changes - 2023-02-16 07:36

Link

This issue blocks ~~MDEV-19123~~ [ ~~MDEV-19123~~ ]

Alexander Barkov made changes - 2023-02-16 07:36

Link

This issue blocks ~~MDEV-25829~~ [ ~~MDEV-25829~~ ]

Alexander Barkov made changes - 2023-02-16 13:45

Link

This issue relates to ~~MDEV-30577~~ [ ~~MDEV-30577~~ ]

Alexander Barkov made changes - 2023-02-17 14:45

Fix Version/s		10.10.4 [ 28522 ]
Fix Version/s		10.11.3 [ 28524 ]
Fix Version/s	10.10 [ 27530 ]
Fix Version/s	10.11 [ 27614 ]

Alexander Barkov made changes - 2023-02-17 14:45

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2023-02-17 14:45

Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Daniel Black made changes - 2023-02-23 03:48

Link

This issue causes ~~MCOL-5437~~ [ ~~MCOL-5437~~ ]

Alexander Barkov made changes - 2023-02-24 14:44

Fix Version/s

11.0.2 [ 28706 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2023-02-16 07:33

Updated:: 2023-05-08 18:57

Resolved:: 2023-02-17 14:45

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration