[MDEV-32244] Wrong bit encoding using COALESCE - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.6.15, 11.2.1, 10.4(EOL), 10.5, 10.6, 10.9(EOL), 10.10(EOL), 10.11
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
Component/s: Data types
Labels:
None

Description

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

MDEV-20212 Wrong stored procedure output BIT result format when using binary protocol

Stalled

Activity

Ascending order - Click to sort in descending order

Diego Dupin created issue - 2023-09-25 13:54

Diego Dupin made changes - 2023-09-25 13:55

Field	Original Value	New Value
Description	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 : {code:sql} 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; {code} Result will be : {code:java} 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) {code}	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 : {code:sql} 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; {code} Result will be : {code:java} 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) {code} expected result would have been 0x01 for bit_b and bit_c. Data type for bit_c is expected to be of BIT type

Diego Dupin made changes - 2023-09-25 13:56

Link

This issue relates to MDEV-20212 [ MDEV-20212 ]

Diego Dupin made changes - 2023-09-25 13:58

Description

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 :

{code:sql}
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;
{code}

Result will be :

{code:java}
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)
{code}

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

report initially [here |https://github.com/mariadb-corporation/mariadb-connector-nodejs/issues/251]

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 :

{code:sql}
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;
{code}

Result will be :

{code:java}
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)
{code}

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

Diego Dupin made changes - 2023-09-25 13:59

Description

Reported initially on [Node.js connector |https://github.com/mariadb-corporation/mariadb-connector-nodejs/issues/251]

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 :

{code:sql}
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;
{code}

Result will be :

{code:java}
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)
{code}

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

Alice Sherepa made changes - 2023-09-26 16:20

Link

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

Alice Sherepa made changes - 2023-09-26 16:25

Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.9 [ 26905 ]
Affects Version/s		10.10 [ 27530 ]
Affects Version/s		10.11 [ 27614 ]

Alice Sherepa made changes - 2023-09-26 16:26

Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.9 [ 26905 ]
Fix Version/s		10.10 [ 27530 ]
Fix Version/s		10.11 [ 27614 ]

Alice Sherepa made changes - 2023-09-26 16:26

Assignee

Alexander Barkov [ bar ]

Alice Sherepa made changes - 2023-09-26 16:26

Status

Open [ 1 ]

Confirmed [ 10101 ]

Alexander Barkov made changes - 2023-10-17 08:48

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2023-10-17 10:41

Component/s		Data types [ 13906 ]
Fix Version/s		10.4.32 [ 29300 ]
Fix Version/s		10.5.23 [ 29012 ]
Fix Version/s		10.6.16 [ 29014 ]
Fix Version/s		10.10.7 [ 29018 ]
Fix Version/s		10.11.6 [ 29020 ]
Fix Version/s		11.0.4 [ 29021 ]
Fix Version/s		11.1.3 [ 29023 ]
Fix Version/s		11.2.2 [ 29035 ]
Fix Version/s		11.3.1 [ 29416 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.9 [ 26905 ]
Fix Version/s	10.10 [ 27530 ]
Fix Version/s	10.11 [ 27614 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

People

Assignee:: Alexander Barkov

Reporter:: Diego Dupin

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2023-09-25 13:54

Updated:: 2023-10-17 10:41

Resolved:: 2023-10-17 10:41

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