[MDEV-11338] bit(n) data type gets confused when used with IF(), IFNULL()... Created: 2016-11-23  Updated: 2022-11-29

Status: Stalled
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.1.19, 10.2.9
Fix Version/s: 10.1, 10.3

Type: Bug Priority: Major
Reporter: Dean Trower Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: bit, ifnull, upstream
Environment:

Tested using Linux & phpMyAdmin / mysqli connector


 Description   

Columns of 

bit(n)

type should be treated consistently as numeric data.

However, VERY strange results can be obtained if you use this datatype in an IFNULL(a,b) function or similar. Consider this:

CREATE TABLE t (a bit(5));
 
INSERT INTO t VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(19),(20),(21),(29),(30),(31);
 
 
 
SELECT a, IFNULL(a,a), IFNULL(a,a)+0, IFNULL(a+0,a+0), IFNULL(a+0,a), IFNULL(a,a+0) FROM t;
 
 
 
DROP TABLE t;

The SQL below returns a 6-column result set. All 6 columns SHOULD contain the same NUMERIC data. However, column 2 returns a string of bits (1's and 0's) corresponding to the ASCII encoding of the result interpreted as a string! That is, if the correct result is 25, it actually returns either 11001000110101 (in phpMyAdmin) or 12853 (the decimal equivalent of that binary number, when using mysqli from PHP directly))!

Note that:

ASCII('2') = 50 = 110010 binary,
ASCII('5') = 53 = 00110101 binary

and 12853 = 50*256+53

...which is where those values come from.

Needless to say, this is completely fubar!

Also, using mysqli to retrieve the data yeilds a "Malformed packet" error after the first row containing a double-digit number. Also completely fubar!

If you're writing SQL, a simple workaround is just to add a "+0" or a CAST to coerce the result into numeric form.

However, if you've (for example) just converted a bool column to a bit(1) and expect things to work correctly as before, this bug may bite you!

MySQL (tested v5.6 and v5.7) suffers a very similar (though possibly not quite identical) problem; see MySQL bug #83923.

 Comments   
Comment by Elena Stepanova [ 2016-11-27 ]

Thanks for the report.

Comment by Dean Trower [ 2017-10-14 ]

Still happening in v10.2.9.

Comment by Dean Trower [ 2019-07-10 ]

Still happening in v10.3.16

Comment by Alice Sherepa [ 2022-11-29 ]

currently on 10.3 4e9206736c403206915c0 -10.10 the result is:

MariaDB [test]> SELECT a, IFNULL(a,a), IFNULL(a,a)+0, IFNULL(a+0,a+0), IFNULL(a+0,a), IFNULL(a,a+0) FROM t;
 
+------+-------------+---------------+-----------------+---------------+---------------+
 
| a    | IFNULL(a,a) | IFNULL(a,a)+0 | IFNULL(a+0,a+0) | IFNULL(a+0,a) | IFNULL(a,a+0) |
 
+------+-------------+---------------+-----------------+---------------+---------------+
 
|      | 0           |             0 |               0 |             0 |             0 |
 
|     | 1           |             1 |               1 |             1 |             1 |
 
|     | 2           |             2 |               2 |             2 |             2 |
 
|     | 3           |             3 |               3 |             3 |             3 |
 
|     | 4           |             4 |               4 |             4 |             4 |
 
|     | 5           |             5 |               5 |             5 |             5 |
 
|     | 6           |             6 |               6 |             6 |             6 |
 
|     | 7           |             7 |               7 |             7 |             7 |
 
|    | 8           |             8 |               8 |             8 |             8 |
 
| 	    | 9           |             9 |               9 |             9 |             9 |
 
| 
    | 10          |            10 |              10 |            10 |            10 |
 
| 
      | 11          |            11 |              11 |            11 |            11 |
 
| 
      | 12          |            12 |              12 |            12 |            12 |
 
|     | 19          |            19 |              19 |            19 |            19 |
 
|     | 20          |            20 |              20 |            20 |            20 |
 
|     | 21          |            21 |              21 |            21 |            21 |
 
|     | 29          |            29 |              29 |            29 |            29 |
 
|     | 30          |            30 |              30 |            30 |            30 |
 
|     | 31          |            31 |              31 |            31 |            31 |
 
+------+-------------+---------------+-----------------+---------------+---------------+
 
19 rows in set (0,000 sec)

Comment by Dean Trower [ 2022-11-29 ]

Alice, I'm now using v 10.3.37, and I'm still getting decimal numbers in the IFNULL(a,a) column that correspond to (ASCII encoding of result as a string) => (concatenated bits of that encoding) => (interpreted as decimal), so e.g. "12848" instead of "20".

I get this both using the mysqli connector in PHP, and in cPanel's phpMyAdmin web interface.

I also tested it and verified the same problem on version: 10.9.2-MariaDB-1:10.9.2+maria~deb11
using the website dbfiddle.uk, you can see the results yourself here: https://dbfiddle.uk/euDoHncZ

The discrepancy seems to be because you used the command-line client to output the results directly, rather than a connector.
I don't know if that makes it at least partly a connector bug, rather than a MariaDB core bug, but the type information is being misinterpreted for the IFNULL(a,a) column, leading to scrambling of the (otherwise correct) output data when it's passed through the connector.

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