Unknown error when WHERE clause contains both "NOT" and "OR" operators

[anitajjx]

Can be reproduced ?

Yes

Steps:

CREATE TABLE t1 (spID int,userID int,score smallint);
INSERT INTO t1 VALUES (1,1,1);
INSERT INTO t1 VALUES (2,2,2);
INSERT INTO t1 VALUES (2,1,4);
INSERT INTO t1 VALUES (3,3,3);
INSERT INTO t1 VALUES (1,1,5);
INSERT INTO t1 VALUES (4,6,10);
INSERT INTO t1 VALUES (5,11,99);

SELECT * FROM t1 where t1.userID<6 OR NOT t1.userID;
SELECT * FROM t1 where NOT t1.userID OR t1.userID<6;
SELECT * FROM t1 where NOT t1.userID || t1.userID<6;

Expected behavior:

mysql> SELECT * FROM t1 where NOT t1.userID || t1.userID<6;
+------+--------+-------+
| spID | userID | score |
+------+--------+-------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 2 | 1 | 4 |
| 3 | 3 | 3 |
| 1 | 1 | 5 |
+------+--------+-------+
5 rows in set, 1 warning (0.00 sec)

Actual behavior:

mysql> SELECT * FROM t1 where t1.userID<6 OR NOT t1.userID;
ERROR 1105 (HY000): unknown error:[42000][42000][42000]or not yet implemented for SEL, INT
mysql> SELECT * FROM t1 where NOT t1.userID OR t1.userID<6;
ERROR 1105 (HY000): unknown error:[42000][42000][42000]or not yet implemented for INT, SEL
mysql> SELECT * FROM t1 where NOT t1.userID || t1.userID<6;
ERROR 1105 (HY000): unknown error:[42000][42000][42000]or not yet implemented for INT, SEL

Environment:

• Version or commit-id (e.g. v0.1.0 or 8b23a93):commit 3aebaf5
• Hardware parameters:
• OS type:
• Others:

Configuration file:

Additional context:

• Error message from client:
• Server log:
• Other information:

[nnsgmsone]

This question is the same as issue782. The direct not attribute is not supported at this time

[anitajjx]

But there is no error when querying with NOT and AND operators together, please see below:

mysql> SELECT * FROM t1 where NOT t1.userID AND t1.userID<6;
+------+--------+-------+
| spID | userID | score |
+------+--------+-------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 2 | 1 | 4 |
| 3 | 3 | 3 |
| 1 | 1 | 5 |
+------+--------+-------+
5 rows in set (0.01 sec)

[nnsgmsone]

There is no handling of queries that directly treat columns as filter conditions, so the behavior may be odd.

[m-schen]

support.

[m-schen]

fixed. but there is a bug #1428 that.
you can verify this issue with where NOT userID || userID<6; instead of where NOT t1.userID || t1.userID<6;

[lignay]

Fixed on dac6e12