[#MDEV-30589] The negative value shift operations within the WHERE statement results in the incorrect query result.

When the integer column in the WHERE statement is compared with the negative integer (after the shift operation, it overflows to the positive maximum value), the processing result of the positive boundary value is incorrect. As a result, the query result is incorrect.

mysql> DROP TABLE IF EXISTS t0;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `t0` (

    ->   `c0` mediumint NOT NULL,

    ->   KEY `c0` (`c0`)

    -> );

Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t0 VALUES(-8388608),(0),(8388607),(8388606);

Query OK, 4 rows affected (0.01 sec)

Records: 4  Duplicates: 0  Warnings: 0

mysql> select c0,(c0 < (-432288127 << 1)) from t0;

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

| c0       | (c0 < (-432288127 << 1)) |

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

| -8388608 |                        1 |

|        0 |                        1 |

|  8388606 |                        1 |

|  8388607 |                        1 |

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

4 rows in set (0.00 sec)

mysql> select c0 from t0 where (c0 < (-432288127 << 1));

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

| c0       |

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

| -8388608 |

|        0 |

|  8388606 |

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

3 rows in set (0.00 sec)

we miss one query result (8388607) when the condition is in the WHERE statement.

[MDEV-30589] The negative value shift operations within the WHERE statement results in the incorrect query result. Created: 2023-02-07 Updated: 2023-11-28
Status:	Confirmed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	10.5.16
Fix Version/s:	10.5, 10.6, 10.11

[MDEV-30589] The negative value shift operations within the WHERE statement results in the incorrect query result. Created: 2023-02-07 Updated: 2023-11-28