[#MDEV-30590] The bitwise calculation with a negative number in the WHERE statement causes the incorrect result.

After IS NULL calculation is performed on columns of the VARCHAR type in the WHERE statement, we perform a bitwise calculation with a negative number. Then, we compare the result with the column of the VARCHAR type. The final query result is incorrect.

mysql> DROP TABLE IF EXISTS t1;

Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `t1` (

    ->   `c0` char(100) NOT NULL

    -> );

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES (''),(1865621236),('qB');

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT c0 FROM t1 WHERE (((c0 IS NULL) | -1519378307) > c0);

Empty set, 2 warnings (0.00 sec)

mysql> SELECT c0,(((c0 IS NULL) | -1519378307) > c0) FROM t1;

+------------+-------------------------------------+

| c0         | (((c0 IS NULL) | -1519378307) > c0) |

+------------+-------------------------------------+

|            |                                   1 |

| 1865621236 |                                   1 |

| qB         |                                   1 |

+------------+-------------------------------------+

3 rows in set, 2 warnings (0.00 sec)

we miss three items (''), (1865621236), ('qB') when performing SELECT c0 FROM t1 WHERE (((c0 IS NULL) | -1519378307) > c0); . The result is incorrect.

[MDEV-30590] The bitwise calculation with a negative number in the WHERE statement causes the incorrect result. Created: 2023-02-07 Updated: 2023-03-19 Resolved: 2023-03-19
Status:	Closed
Project:	MariaDB Server
Component/s:	Data types
Affects Version/s:	10.5.16
Fix Version/s:	N/A

[MDEV-30590] The bitwise calculation with a negative number in the WHERE statement causes the incorrect result. Created: 2023-02-07 Updated: 2023-03-19 Resolved: 2023-03-19