[#MDEV-30595] The incorrect where condition result of the bit shift operation is performed on a negative double number column.

mysql> DROP TABLE IF EXISTS t0;

Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `t0` (

    ->   `c0` double DEFAULT NULL

    -> );

LECT c0 FROM t0 WHERE ((-1782140092 << 0)) NOT IN (c0,0);Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t0 VALUES (-1782140092);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT c0, (-1782140092 << 0) NOT IN (c0),(-1782140092 << 0) NOT IN (c0,0) FROM t0;

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

| c0          | (-1782140092 << 0) NOT IN (c0) | (-1782140092 << 0) NOT IN (c0,0) |

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

| -1782140092 |                              1 |                                1 |

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

1 row in set (0.00 sec)

mysql> SELECT c0 FROM t0 WHERE ((-1782140092 << 0)) NOT IN (c0);

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

| c0          |

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

| -1782140092 |

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

1 row in set (0.00 sec)

The expected results

mysql> SELECT c0 FROM t0 WHERE ((-1782140092 << 0)) NOT IN (c0,0);

Empty set (0.00 sec)

--source include/have_innodb.inc

CREATE TABLE `t0` ( `c0` double DEFAULT NULL )engine=innodb;

SELECT c0 FROM t0 WHERE ((-1782140092 << 0)) NOT IN (c0,0);

INSERT INTO t0 VALUES (-1782140092);

SELECT c0, (-1782140092 << 0) NOT IN (c0),(-1782140092 << 0) NOT IN (c0,0) FROM t0;

SELECT c0 FROM t0 WHERE ((-1782140092 << 0)) NOT IN (c0,0);

MariaDB [test]> SELECT c0 FROM t0 WHERE ((-1782140092 << 0)) NOT IN (c0,0);

Empty set (0.000 sec)

MariaDB [test]> explain extended SELECT c0 FROM t0 WHERE ((-1782140092 << 0)) NOT IN (c0,0);

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

| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

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

|    1 | SIMPLE      | t0    | ALL  | NULL          | NULL | NULL    | NULL | 1    |   100.00 | Using where |

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

1 row in set, 1 warning (0.000 sec)

Note (Code 1003): select `test`.`t0`.`c0` AS `c0` from `test`.`t0` where <cache>(-1782140092 << 0) not in (`test`.`t0`.`c0`,0)

MariaDB [test]> alter table t0 engine=aria;

Query OK, 1 row affected (0.080 sec)

Records: 1  Duplicates: 0  Warnings: 0

MariaDB [test]> SELECT c0 FROM t0 WHERE ((-1782140092 << 0)) NOT IN (c0,0);

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

| c0          |

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

| -1782140092 |

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

1 row in set (0.001 sec)

MariaDB [test]> explain extended SELECT c0 FROM t0 WHERE ((-1782140092 << 0)) NOT IN (c0,0);

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

| id   | select_type | table | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |

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

|    1 | SIMPLE      | t0    | system | NULL          | NULL | NULL    | NULL | 1    |   100.00 |       |

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

1 row in set, 1 warning (0.000 sec)

Note (Code 1003): select -1782140092 AS `c0` from dual where 1

[MDEV-30595] The incorrect where condition result of the bit shift operation is performed on a negative double number column. Created: 2023-02-07 Updated: 2023-11-28
Status:	Confirmed
Project:	MariaDB Server
Component/s:	Storage Engine - InnoDB
Affects Version/s:	10.5.16, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s:	10.4, 10.5, 10.6