Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4.5, 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
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.
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