Incorrect result for NOT BETWEEN with mixed DECIMAL (PK) and BIGINT types

Incorrect result for NOT BETWEEN with mixed DECIMAL (PK) and BIGINT types, showing optimizer inconsistency

1. Create a table with a DECIMAL primary key and a BIGINT column
2. Insert two rows, one with a normal range and one with an inverted range
3. Execute a NOT BETWEEN query using these two columns as bounds.Returns 1 row instead of 2
4. Execute a logically equivalent query. This query correctly evaluates the condition for both rows and returns the expected count.

   CREATE TABLE t17 (c1 DECIMAL, c4 BIGINT, PRIMARY KEY (c1));

   INSERT INTO t17 (c1,c4) VALUES (12,85);

   INSERT INTO t17 (c1,c4) VALUES (-87,-300);

   select * from t17;

   SELECT c1 FROM t17 WHERE (((-1) NOT BETWEEN (c1) AND (c4)));-- return 12

   SELECT SUM(count) FROM (SELECT ((-1) NOT BETWEEN (c1) AND (c4)) IS TRUE AS count FROM t17) AS ta_norec;--return 2

MariaDB [test]> CREATE TABLE t17 (c1 DECIMAL, c4 BIGINT, PRIMARY KEY (c1));

Query OK, 0 rows affected (0.015 sec)

MariaDB [test]> INSERT  INTO t17 (c1,c4) VALUES (12,85);

Query OK, 1 row affected (0.006 sec)

MariaDB [test]> INSERT  INTO t17 (c1,c4) VALUES (-87,-300);

Query OK, 1 row affected (0.006 sec)

MariaDB [test]> select * from t17;

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

| c1  | c4   |

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

| -87 | -300 |

|  12 |   85 |

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

2 rows in set (0.001 sec)

MariaDB [test]> SELECT c1 FROM t17 WHERE (-1 NOT BETWEEN c1 AND c4);

+----+

| c1 |

+----+

| 12 |

+----+

1 row in set (0.001 sec)

MariaDB [test]> SELECT SUM(count) FROM (

    ->     SELECT (-1 NOT BETWEEN c1 AND c4) IS TRUE AS count FROM t17

    -> ) AS ta_norec;

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

| SUM(count) |

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

|          2 |

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

1 row in set (0.002 sec)

MariaDB [test]>