Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32244

Wrong bit encoding using COALESCE

Details

    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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8867 Wrong field type or metadata for COALESCE(bit_column, 1)

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20212 Wrong stored procedure output BIT result format when using binary protocol

          • Major - Major loss of function.
          • Stalled

          Activity

            bar Alexander Barkov added a comment -

            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 |
            		 
            +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

            bar Alexander Barkov added a comment - 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 | +-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

            People

              bar Alexander Barkov
              diego dupin Diego Dupin
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.