Bug #83907 A where condition on a function behaves different than inlining the function

• Submitted: 21 Nov 2016 11:17
• Modified: 23 Nov 2016 0:14
• Reporter: Jesper Kristensen
• Status: Verified
• Category: MySQL Server: User-defined functions ( UDF )
• Severity: S2 (Serious)
• Version: 5.7.16
• OS: Debian (Using official Docker images)
• CPU Architecture: Any

[21 Nov 2016 11:17] Jesper Kristensen

Description:
When a where condition uses a function whose argument is a column from a left joined table then it seems to be doing some optimizations where it overlooks the possibility of that argument being NULL due to the left join.

Inlining the function fixes the problem.

I have tested it on various official Docker images and the problem appears in all of them (5.5.40, 5.5.53, 5.6.17, 5.6.34, 5.7.4 and 5.7.16)

How to repeat:
CREATE DATABASE test;
USE test;
CREATE TABLE t1 (n INTEGER PRIMARY KEY);
INSERT INTO t1 (n) VALUES (0), (1), (2);
CREATE TABLE t2 (n INTEGER PRIMARY KEY, b BOOLEAN);
INSERT INTO t2 (n, b) VALUES (0, FALSE), (1, TRUE);
CREATE FUNCTION neg(b BOOL) RETURNS BOOL DETERMINISTIC NO SQL RETURN IF(b, FALSE, TRUE);
-- Having a constraint on the function gives an incomplete result compared to inlining it
-- Returns 2 rows:
SELECT n, b, f(b) FROM t1 LEFT JOIN t2 USING (n) WHERE IF(b, FALSE, TRUE);
-- Returns 1 row:
SELECT n, b, f(b) FROM t1 LEFT JOIN t2 USING (n) WHERE neg(b);

-- If WHERE is replaced by HAVING then both queries returns 2 rows
SELECT n, b, f(b) FROM t1 LEFT JOIN t2 USING (n) HAVING IF(b, FALSE, TRUE);
SELECT n, b, f(b) FROM t1 LEFT JOIN t2 USING (n) HAVING neg(b);

# I tested it using Docker:
docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=1234 -d mysql:5.7.16
# Wait ~10 seconds
docker run -it --link some-mysql:mysql --rm mysql:5.7.16 sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"'

[22 Nov 2016 23:50] MySQL Verification Team

Please print here the output of your test case; It fails for me:

SELECT n, b, f(b) FROM t1 LEFT JOIN t2 USING (n) WHERE IF(b, FALSE, TRUE);
ERROR 1305 (42000): FUNCTION test.f does not exist.

Thanks in advance.

[23 Nov 2016 0:05] MySQL Verification Team

mysql> select * from t1;
+---+
| n |
+---+
| 0 |
| 1 |
| 2 |
+---+
3 rows in set (0.00 sec)

mysql> select * from t2;
+---+------+
| n | b    |
+---+------+
| 0 |    0 |
| 1 |    1 |
+---+------+
2 rows in set (0.00 sec)

mysql> SELECT n, b, neg(b) FROM t1 LEFT JOIN t2 USING (n) WHERE IF(b, FALSE, TRUE);
+---+------+--------+
| n | b    | neg(b) |
+---+------+--------+
| 0 |    0 |      1 |
| 2 | NULL |      1 |
+---+------+--------+
2 rows in set (0.05 sec)

mysql> SELECT n, b, neg(b) FROM t1 LEFT JOIN t2 USING (n) WHERE neg(b);
+---+------+--------+
| n | b    | neg(b) |
+---+------+--------+
| 0 |    0 |      1 |
+---+------+--------+
1 row in set (0.00 sec)

mysql> SELECT n, b, neg(b) FROM t1 LEFT JOIN t2 USING (n) HAVING IF(b, FALSE, TRUE);
+---+------+--------+
| n | b    | neg(b) |
+---+------+--------+
| 0 |    0 |      1 |
| 2 | NULL |      1 |
+---+------+--------+
2 rows in set (0.00 sec)

mysql> SELECT n, b, neg(b) FROM t1 LEFT JOIN t2 USING (n) HAVING neg(b);
+---+------+--------+
| n | b    | neg(b) |
+---+------+--------+
| 0 |    0 |      1 |
| 2 | NULL |      1 |
+---+------+--------+
2 rows in set (0.00 sec)

[23 Nov 2016 0:14] MySQL Verification Team

Hi,

Thanks for the report, yes select neg(null) behaves differently then select where neg(null) but only in join where that null was "calculated" .. as for e.g. 

mysql> insert into t2 values (2,null);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t2;
+---+------+
| n | b    |
+---+------+
| 0 |    0 |
| 1 |    1 |
| 2 | NULL |
+---+------+
3 rows in set (0.00 sec)

mysql> select b,neg(B) from t2; -- this is ok
+------+--------+
| b    | neg(B) |
+------+--------+
|    0 |      1 |
|    1 |      0 |
| NULL |      1 |
+------+--------+
3 rows in set (0.00 sec)

mysql> select b,neg(b) from t2 where b; -- this is ok
+------+--------+
| b    | neg(b) |
+------+--------+
|    1 |      0 |
+------+--------+
1 row in set (0.00 sec)

mysql> select b,neg(b) from t2 where neg(b); -- this is ok
+------+--------+
| b    | neg(b) |
+------+--------+
|    0 |      1 |
| NULL |      1 |
+------+--------+
2 rows in set (0.00 sec)

so it's only not ok with left join when you are missing the value

mysql> insert into t1 values (3);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT n, b, neg(b) FROM t1 LEFT JOIN t2 USING (n) WHERE neg(b);
+---+------+--------+
| n | b    | neg(b) |
+---+------+--------+
| 0 |    0 |      1 |
| 2 | NULL |      1 |
+---+------+--------+
2 rows in set (0.00 sec)

mysql>

the 2,null,1 row is here (null from t2 not from "missing" value)

I'm verifying this, thanks for the report
Bogdan Kecman