[MDEV-35391] Inconsistent Comparison Behavior with COMPRESS Function - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Not a Bug
Affects Version/s: 11.4.0
Fix Version/s: N/A
Component/s: Character Sets
Labels:
None

Description

A discrepancy occurs when comparing the output of the COMPRESS function in MariaDB. In the original table t0, querying with COMPRESS(c0) > c1 returns a result as expected. However, after storing COMPRESS(c0) in a derived table t1, applying the equivalent condition on t1 yields no results, even though the data and conditions are identical.

Reproduce Steps:

-- Step 1: Create table t0 and insert data

CREATE TABLE t0 (c0 DATETIME, c1 VARCHAR(16), c2 VARCHAR(9));

INSERT INTO t0 (c0, c1, c2) VALUES ('2003-01-18 19:04:14', 'o4oFjkHVT8LcDfnY', 'djXhVwU7m');

-- Step 2: Query in the original table

MariaDB [test]> SELECT c2 FROM t0 WHERE ((COMPRESS(c0)) > c1);

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

| c2        |

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

| djXhVwU7m |

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

1 row in set, 1 warning (0.012 sec)

-- Step 3: Create derived table t1 with COMPRESS result

CREATE TABLE t1 AS (SELECT (COMPRESS(c0)) AS c0, c1 AS c1, c2 AS c2 FROM t0);

-- Step 4: Apply equivalent comparison in derived table t1

MariaDB [test]> SELECT c2 FROM t1 WHERE ((c0) > c1);

0 rows in set (0.007 sec)

Expected Result:
Both queries should return consistent results.

Attachments

Activity

Ascending order - Click to sort in descending order

Daniel Black added a comment - 2024-11-12 05:35 - edited

SHOW WARNINGS (from first expression)

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

| Level   | Code | Message                                                                                         |

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

| Warning | 1300 | Invalid utf8mb4 character string: '\x9C3200\xD650\xD45\xB4P0\xB4\xB420\xB124\x01\x00$G\x03\xB1' |

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

MariaDB [test]> SELECT COERCIBILITY(COMPRESS(c0)) as hex from t0;

+-----+

| hex |

+-----+

|   6 |

+-----+

MariaDB [test]> SELECT COERCIBILITY(CAST(COMPRESS(c0) as BINARY)) as hex from t0;

+-----+

| hex |

+-----+

|   2 |

+-----+

Based on https://mariadb.com/kb/en/coercibility/ rules the type 6 is converted up to 2 (coercibility of c1) and fails because the compressed binary isn't a valid UTF8MB4 so becomes null.

With an explicit cast, coercibility is 2 so I assume these get a binary comparison:

MariaDB [test]> SELECT c2 FROM t0 WHERE (CAST(COMPRESS(c0) AS BINARY) > c1);

Field   1:  `c2`

Org_field:  `c2`

Catalog:    `def`

Database:   `test`

Table:      `t0`

Org_table:  `t0`

Type:       VAR_STRING

Collation:  utf8mb4_unicode_ci (224)

Length:     36

Max_length: 0

Decimals:   0

Flags:

0 rows in set (0.001 sec)

A 6 coercibility seems to match other functions like concat:

 SELECT COERCIBILITY(CONCAT(c0)) as hex from t0;

| hex |

+-----+

|   6 |

+-----+

For the varchar column:

SELECT COERCIBILITY(CONCAT(c1)) as hex from t0;

+-----+

| hex |

+-----+

|   2 |

+-----+

So I don't think its a bug, just the way coecibility works according to documented implementations.

Though if COMPRESS(c0) is NULL, why did the expression become true?

Daniel Black added a comment - 2024-11-12 05:35 - edited SHOW WARNINGS ( from first expression) + ---------+------+-------------------------------------------------------------------------------------------------+ | Level | Code | Message | + ---------+------+-------------------------------------------------------------------------------------------------+ | Warning | 1300 | Invalid utf8mb4 character string: '\x9C3200\xD650\xD45\xB4P0\xB4\xB420\xB124\x01\x00$G\x03\xB1' | + ---------+------+-------------------------------------------------------------------------------------------------+ MariaDB [test]> SELECT COERCIBILITY(COMPRESS(c0)) as hex from t0; + -----+ | hex | + -----+ | 6 | + -----+ MariaDB [test]> SELECT COERCIBILITY( CAST (COMPRESS(c0) as BINARY )) as hex from t0; + -----+ | hex | + -----+ | 2 | + -----+ Based on https://mariadb.com/kb/en/coercibility/ rules the type 6 is converted up to 2 (coercibility of c1) and fails because the compressed binary isn't a valid UTF8MB4 so becomes null. With an explicit cast, coercibility is 2 so I assume these get a binary comparison: MariaDB [test]> SELECT c2 FROM t0 WHERE (CAST(COMPRESS(c0) AS BINARY) > c1); Field 1: `c2` Org_field: `c2` Catalog: `def` Database: `test` Table: `t0` Org_table: `t0` Type: VAR_STRING Collation: utf8mb4_unicode_ci (224) Length: 36 Max_length: 0 Decimals: 0 Flags: 0 rows in set (0.001 sec) A 6 coercibility seems to match other functions like concat: SELECT COERCIBILITY(CONCAT(c0)) as hex from t0; | hex | + -----+ | 6 | + -----+ For the varchar column : SELECT COERCIBILITY(CONCAT(c1)) as hex from t0; + -----+ | hex | + -----+ | 2 | + -----+ So I don't think its a bug, just the way coecibility works according to documented implementations. Though if COMPRESS(c0) is NULL , why did the expression become true?

Alexander Barkov added a comment - 2024-11-12 06:15

This is not a bug.

In the original query:

SELECT c2 FROM t0 WHERE ((COMPRESS(c0)) > c1);

you compare a function to a column. Column has stronger collation that a function. So comparison is done according to the column collation.

In the query with t1:

SELECT c2 FROM t1 WHERE ((c0) > c1);

you compare two columns. They have equal collation strength. But as c0 is VARBINARY, it wins. So binary comparison is done.

To get equal results from the two queries, I suggest to use VARBINARY(16) instead of VARCHAR(16) for the column c1.

Alexander Barkov added a comment - 2024-11-12 06:15 This is not a bug. In the original query: SELECT c2 FROM t0 WHERE ((COMPRESS(c0)) > c1); you compare a function to a column. Column has stronger collation that a function. So comparison is done according to the column collation. In the query with t1: SELECT c2 FROM t1 WHERE ((c0) > c1); you compare two columns. They have equal collation strength. But as c0 is VARBINARY, it wins. So binary comparison is done. To get equal results from the two queries, I suggest to use VARBINARY(16) instead of VARCHAR(16) for the column c1.

chaos added a comment - 2024-11-12 08:22

Thank you for your kind explanation. I have a question: without changing the type of c1, is there any other way to apply constraints so that the results of these two SELECT statements are equal?

chaos added a comment - 2024-11-12 08:22 Thank you for your kind explanation. I have a question: without changing the type of c1, is there any other way to apply constraints so that the results of these two SELECT statements are equal?

Sergei Golubchik added a comment - 2024-11-12 09:01

You can use the COLLATE clause or CAST

Sergei Golubchik added a comment - 2024-11-12 09:01 You can use the COLLATE clause or CAST

MariaDB Server

Inconsistent Comparison Behavior with COMPRESS Function

Details

Description

Attachments

Activity

People

Dates

Git Integration