[MDEV-19779] Optimizer replacement of expressions leads to 'value is out of range' exception and ignores range restrictions. Created: 2019-06-17  Updated: 2019-09-14

Status: Confirmed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5, 10.0, 10.1, 10.4.5, 10.2, 10.3, 10.4
Fix Version/s: 5.5

Type: Bug Priority: Major
Reporter: - Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: error, upstream
Environment:

MariaDB Docker (Bionic) on Ubuntu Linux 18.04


 Description   

Summary: The following sql query leads to ' BIGINT value is out of range in '"A"."B"."C" * "A"."B"."C" * "A"."B"."C" * "A"."B"."C" * "A"."B"."C" * "A"."B"."C"'', propably due to expression replacement:

CREATE SCHEMA A;
 
CREATE TABLE A.B (C BIGINT);
 
INSERT INTO A.B VALUES (-5500);
 
SELECT 1 AS A FROM (SELECT B.C * B.C AS A FROM A.B WHERE B.C * B.C < 10) A WHERE ( A.A * A.A * A.A * A.A ) = A.A ;

Normally, the condition in the inner SELECT should restrict execution of the outher WHERE clause to 'valid' expressions, but it seems like the expression 'B.C * B.C' from the inner select replaces the outer ones without the check before.

In Postgres, this is handled correctly, no exception occurs (and no result is returned because nothing machtes the query currently), in MariaDB, an exception occurs.

The same exception occurs in MySQL 8.0.16. I'm not sure about other mariadb or mysql versions.

Even though this example is a bit 'constructed', all WHERE conditions which restrict the range of expressions are affected.

 Comments   
Comment by Alice Sherepa [ 2019-06-17 ]

Thanks a lot! Reproduced as described on 5.5-10.4

MariaDB [test]> create table t1 (c bigint);
 
Query OK, 0 rows affected (0.044 sec)
 
 
 
MariaDB [test]> insert into t1 values (-5500);
 
Query OK, 1 row affected (0.012 sec)
 
 
 
MariaDB [test]> select 1 from (select c*c as a from t1 where c*c < 10) dt
 
    -> where (a * a * a * a) = a ;
 
ERROR 1690 (22003): BIGINT value is out of range in '`test`.`t1`.`c` * `test`.`t1`.`c` * `test`.`t1`.`c` * `test`.`t1`.`c` * `test`.`t1`.`c` * `test`.`t1`.`c`'
 
 
 
MariaDB [test]> explain extended 
    -> select 1 from (select c*c as a from t1 where c*c < 10) dt
 
    -> where (a * a * a * a) = a ;
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
 
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1    |   100.00 | Using where |
 
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
 
1 row in set, 1 warning (0.001 sec)
 
 
 
Note (Code 1003): select 1 AS `1` from `test`.`t1` where `test`.`t1`.`c` * `test`.`t1`.`c` * `test`.`t1`.`c` * `test`.`t1`.`c` * `test`.`t1`.`c` * `test`.`t1`.`c` * `test`.`t1`.`c` * `test`.`t1`.`c` = `test`.`t1`.`c` * `test`.`t1`.`c` and `test`.`t1`.`c` * `test`.`t1`.`c` < 10

Generated at Thu Feb 08 08:54:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.