bug: logic bug, when executing select * from t where 123 !=(not(not 123));

[davidshiz]

Have you read the Contributing Guidelines on issues?

• [X] I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• [X] I confirm there is no existing issue for this

Describe the problem

Both mysql 5.7 innodb and tianmu have this problem

mysql> create table t (id int);
Query OK, 0 rows affected (0.03 sec)

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

mysql> select * from t where 123 !=(not(not 123));
Empty set (0.00 sec)

Expected behavior

mysql 8.0

mysql> select * from t where 123 !=(not(not 123));
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

How To Reproduce

create table t (id int);
insert into t values(1);
select * from t where 123 !=(not(not 123));

Environment

root@ub01:~# /stonedb57/install/bin/mysqld --version
/stonedb57/install/bin/mysqld  Ver 5.7.36-StoneDB for Linux on x86_64 (build-)
build information as follow:
        Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
        Branch name: stonedb-5.7-dev
        Last commit ID: 863cebc58
        Last commit time: Date:   Wed Dec 7 14:16:34 2022 +0800
        Build time: Date: Mon Dec 19 14:29:02 CST 2022
root@ub01:~# cat /etc/issue
Ubuntu 20.04.5 LTS \n \l

Are you interested in submitting a PR to solve the problem?

• [ ] Yes, I will!

[RingsC]

@davidshiz , the result of (not (not 123)) is calced as following: not 123 = 0; not 0 = 1, and therefore, the sql will be select * from t where 123 !=1 then in 8.0 gets the result. But, in mysql 5.7 the optimization maybe optimize the double not to do nothing. Therefore, the statement will be select * from where 123 != 123 ,and gets the result you mentioned.

[RingsC]

@davidshiz you can trace the optimization opers to verify this logical optimization.

[davidshiz]

@davidshiz you can trace the optimization opers to verify this logical optimization.

ok，thanks

[adofsauron]

ACK

[adofsauron]

The result is also empty on official mysql7

mysql> select * from t where 123 !=(not(not 123));
| >find_command [mysql.cc:1117]
| >add_line [mysql.cc:1177]
| | >find_command [mysql.cc:1117]
| | >mysql_real_query [client.c:2520]
| | | >mysql_send_query [client.c:2495]
| | | >cli_advanced_command [client.c:654]
| | | | >net_clear [net.c:197]
| | | | | >vio_blocking [viosocket.c:126]
| | | | | >vio_read_buff [viosocket.c:67]
| | | | | | >vio_read [viosocket.c:36]
| | | | | >vio_blocking [viosocket.c:126]
| | | | >net_write_command [net.c:309]
| | | | >net_flush [net.c:220]
| | | | | >vio_is_blocking [viosocket.c:187]
| | | | | >net_real_write [net.c:431]
| | | | | | >vio_write [viosocket.c:105]
| | >cli_read_query_result [client.c:2420]
| | | >vio_is_blocking [viosocket.c:187]
| | | >vio_read_buff [viosocket.c:67]
| | | | >vio_read [viosocket.c:36]
| | | >vio_read_buff [viosocket.c:67]
| | | >free_old_query [client.c:713]
| | | | >init_alloc_root [my_alloc.c:51]
| | | >cli_read_rows [client.c:1274]
| | | | >vio_is_blocking [viosocket.c:187]
| | | | >vio_read_buff [viosocket.c:67]
| | | | >vio_read_buff [viosocket.c:67]
| | | | >init_alloc_root [my_alloc.c:51]
| | | | >vio_is_blocking [viosocket.c:187]
| | | | >vio_read_buff [viosocket.c:67]
| | | | >vio_read_buff [viosocket.c:67]
| | | >unpack_fields [client.c:1164]
| | >my_realloc [my_realloc.c:30]
| | >my_realloc [my_realloc.c:30]
| | >my_realloc [my_realloc.c:30]
| | >my_realloc [my_realloc.c:30]
| | >my_realloc [my_realloc.c:30]
| | >mysql_store_result [client.c:2538]
| | | >cli_read_rows [client.c:1274]
| | | | >vio_is_blocking [viosocket.c:187]
| | | | >vio_read_buff [viosocket.c:67]
| | | | >vio_read_buff [viosocket.c:67]
| | | | >init_alloc_root [my_alloc.c:51]
Empty set (0.00 sec)

[wisehead]

mysql 5.7 has this bug, too. change the priority to low.