[MDEV-32244] Wrong bit encoding using COALESCE Created: 2023-09-25  Updated: 2023-10-17  Resolved: 2023-10-17

Status: Closed
Project: MariaDB Server
Component/s: Data types
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 10.6.15, 11.2.1
Fix Version/s: 10.4.32, 10.5.23, 10.6.16, 10.10.7, 10.11.6, 11.0.4, 11.1.3, 11.2.2, 11.3.1

Type: Bug Priority: Critical
Reporter: Diego Dupin Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-8867 Wrong field type or metadata for COAL... Closed
relates to MDEV-20212 Wrong stored procedure output BIT res... Stalled

 Description   

Reported initially on Node.js connector

When using COALESCE on BIT fields, results type and value are wrong.

  • Using COALESCE on 2 bit field will be of type BIT, but value will be string encoded, so value 0x01 will be returned as 0x31 (=ascii '1')
  • Using COALESCE on 1 bit field and null will be of type NEWDECIMAL, with text value as well

Here is how to reproduced : 

CREATE TABLE coalesce_test ( bit_a BIT);
 
INSERT INTO coalesce_test (bit_a) VALUES (1);
 
SELECT bit_a, COALESCE(bit_a, bit_a) AS bit_b, COALESCE(bit_a, null) AS bit_c FROM coalesce_test;

Result will be : 

MariaDB [testn]> SELECT bit_a, COALESCE(bit_a, bit_a) AS bit_b, COALESCE(bit_a, null) AS bit_c FROM coalesce_test;
 
Field   1:  `bit_a`
 
Org_field:  `bit_a`
 
Catalog:    `def`
 
Database:   `testn`
 
Table:      `coalesce_test`
 
Org_table:  `coalesce_test`
 
Type:       BIT
 
Collation:  binary (63)
 
Length:     1
 
Max_length: 1
 
Decimals:   0
 
Flags:      UNSIGNED
 
 
 
Field   2:  `bit_b`
 
Org_field:  ``
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       BIT
 
Collation:  binary (63)
 
Length:     1
 
Max_length: 1
 
Decimals:   0
 
Flags:      UNSIGNED BINARY
 
 
 
Field   3:  `bit_c`
 
Org_field:  ``
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       NEWDECIMAL
 
Collation:  binary (63)
 
Length:     2
 
Max_length: 1
 
Decimals:   0
 
Flags:      BINARY NUM
 
 
 
 
 
+-------+-------+-------+
 
| bit_a | bit_b | bit_c |
 
+-------+-------+-------+
 
|      | 1     |     1 |
 
+-------+-------+-------+
 
1 row in set (0.000 sec)

expected result would have been 0x01 for bit_b and bit_c. Data type for bit_c is expected to be of BIT type

 Comments   
Comment by Alexander Barkov [ 2023-10-17 ]

The same problem is repeatable with the YEAR data type:

CREATE OR REPLACE TABLE t1 (c1 YEAR);
 
INSERT INTO t1 (c1) VALUES (2001);
 
CREATE OR REPLACE TABLE t2 AS SELECT c1, COALESCE(c1, c1) AS c2, COALESCE(c1, null) AS c3 FROM t1;
 
SHOW CREATE TABLE t2;


+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Table | Create Table                                                                                                                                                                    |
 
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| t2    | CREATE TABLE `t2` (
 
  `c1` year(4) DEFAULT NULL,
 
  `c2` year(4) DEFAULT NULL,
 
  `c3` decimal(4,0) DEFAULT NULL
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
 
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Generated at Thu Feb 08 10:29:54 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.