Details
Critical 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
Attachments
Issue Links
- relates to
-
MDEV-8867 Wrong field type or metadata for COALESCE(bit_column, 1)
-
- Closed
-
-
-
- Stalled
-