| Bug #116163 | COALESCE(BIT), IF(BIT) return a wrong result | ||
|---|---|---|---|
| Submitted: | 19 Sep 2024 14:08 | Modified: | 24 Sep 2024 12:36 |
| Reporter: | Guilhem Bichot | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 9.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[19 Sep 2024 14:31]
MySQL Verification Team
Hello Guilhem, Thank you for the report and test case. regards, Umesh
[19 Sep 2024 14:50]
Guilhem Bichot
IF() is also affected, if using a BIT(32) column (observed no problem with BIT(8)). Example: CREATE TABLE t1 (c0 bit(32)); insert into t1 values(0x00AABBCC); select hex(c0), c0, coalesce(c0), coalesce(c0,c0), ifnull(c0,c0), if(1=1,c0,c0) from t1; +---------+------------+----------------------------+----------------------------------+------------------------------+------------------------------+ | hex(c0) | c0 | coalesce(c0) | coalesce(c0,c0) | ifnull(c0,c0) | if(1=1,c0,c0) | +---------+------------+----------------------------+----------------------------------+------------------------------+------------------------------+ | AABBCC | 0x00AABBCC | 0x3131313839313936 | 0x3131313839313936 | 0x3131313839313936 | 0x00C2AAC2BBC38C | +---------+------------+----------------------------+----------------------------------+------------------------------+------------------------------+ The last column, IF(), should return the value of c0 but it does not (and it's differently from wrong from the wrong value of COALESCE).
[24 Sep 2024 12:36]
Guilhem Bichot
GROUP_CONCAT, with the same table as above: +select group_concat(c0),group_concat(c0 order by 1+1) from t0; +group_concat(c0) group_concat(c0 order by 1+1) +A,B 66,65 (this is from a mtr test) We see that the addition of ORDER BY makes GROUP_CONCAT use numeric values (66,65) ; without it, it uses characters having these ASCII codes. FWIW it happens identically with ORDER BY BINARY ''. I doubt this behaviour is intentional.
Description: tested with github's commit 596f0d238489a9cf9f43ce1ff905984f58d227b6 (HEAD -> trunk, tag: mysql-cluster-9.0.1, tag: mysql-9.0.1, origin/trunk) Author: Bjorn Munch <bjorn.munch@oracle.com> Date: Fri Jul 12 21:03:23 2024 +0200 Update License Book I do: CREATE TABLE t0 (c0 bit(8)); INSERT INTO t0 (c0) VALUES (65), (66); The I select COALESCE(c0,c0). This is expected to return something of the common type of c0 and c0, which should be BIT. But it does not (and so does its cousin IFNULL): mysql> select hex(c0), c0, coalesce(c0), coalesce(c0,c0), ifnull(c0,c0) from t0; +---------+------------+----------------------------+----------------------------------+------------------------------+ | hex(c0) | c0 | coalesce(c0) | coalesce(c0,c0) | ifnull(c0,c0) | +---------+------------+----------------------------+----------------------------------+------------------------------+ | 41 | 0x41 | 0x3635 | 0x3635 | 0x3635 | | 42 | 0x42 | 0x3636 | 0x3636 | 0x3636 | +---------+------------+----------------------------+----------------------------------+------------------------------+ 0x41 vs 0x3635, alas. This is observed in the 'mysql' CLI (also happens when I put this in a mtr test). The same happens with COALESCE(c0) (a single c0 argument). For what it's worth: 0x3635 is two hex codes, which in ASCII correspond to characters '6' and '5', and 65 is the decimal value of the ASCII code of 'A' (41 hex = 65 decimal). How to repeat: CREATE TABLE t0 (c0 bit(8)); INSERT INTO t0 (c0) VALUES (65), (66); select hex(c0), c0, coalesce(c0,c0), ifnull(c0,c0) from t0;