| Bug #118020 | A critical logic bug and performance bug | ||
|---|---|---|---|
| Submitted: | 20 Apr 23:23 | Modified: | 19 May 14:51 |
| Reporter: | jinhui lai | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S1 (Critical) |
| Version: | 9.2.0, 9.3.0 | OS: | Any (22.04) |
| Assigned to: | CPU Architecture: | Any | |
[20 Apr 23:23]
jinhui lai
[23 Apr 13:47]
MySQL Verification Team
Hello jinhui lai, Thank you for the report and feedback. I tried to replicate the performance drop you saw in your tests, but I don't see it using the report's steps. Is there anything I'm missing here? I'll shortly share the steps attempted at my end. Also, I see you are using a Docker container. Any chance you can share details using a bare metal instance? Thank you so much. Sincerely, Umesh
[23 Apr 13:47]
MySQL Verification Team
9.3.0, 9.2.0 test results
Attachment: 118020.results (application/octet-stream, text), 4.41 KiB.
[23 Apr 22:11]
jinhui lai
This time, i execute the query in a bare metal instance.As follow: -- env: ubuntu 22.04, AMD EPYC 7773X 64-Core Processor git clone https://github.com/mysql/mysql-server.git cd mysql-server git check mysql-9.2.0 sudo apt-get -y install apt-transport-https curl software-properties-common gnutls-dev libssl-dev ncurses-dev pkg-config bison cmake .. && make -j200 && cmake --install . --prefix /path/to/mysql_install /path/to/mysql_install/bin/mysqld --initialize-insecure --basedir=/path/to/mysql_install --datadir=/path/to/data /path/to/mysql_install/bin/mysqld --datadir=/path/to/data -P 3306 --skip-grant-tables --user=root /path/to/mysql_install/bin/mysql -uroot mysql> use information_schema; mysql> SELECT * FROM TABLES CROSS JOIN VIEWS ON TRUE EXCEPT SELECT * FROM TABLES CROSS JOIN VIEWS ON NOT FALSE; -- 7 hour passed, i have got any result, but this sql still is executing. Have you tried it in docker? Can it reproduced in docker container? In our opinion, this query should not be actually executed and should return an empty set directly. Thank you so much for your thorough and insightful analysis. Your dedication and hard work are truly appreciated. I’m very grateful!
[24 Apr 6:18]
MySQL Verification Team
Thank you for the details. Let me retry and get back to you. regards, Umesh
[11 May 12:04]
jinhui lai
There is the information of mysql:9.2.0 image
docker inspect mysql:9.2.0
[
{
"Id": "sha256:5568fddd4f66008ebae555bfee3e999a8f8e6516fbe974e0af11013e83e241fd",
"RepoTags": [
"mysql:9.2.0"
],
"RepoDigests": [
"mysql@sha256:146682692a3aa409eae7b7dc6a30f637c6cb49b6ca901c2cd160becc81127d3b"
],
"Parent": "",
"Comment": "buildkit.dockerfile.v0",
"Created": "2025-01-22T17:15:22Z",
"DockerVersion": "",
"Author": "",
"Config": {
"Hostname": "",
"Domainname": "",
"User": "",
"AttachStdin": false,
"AttachStdout": false,
"AttachStderr": false,
"ExposedPorts": {
"3306/tcp": {},
"33060/tcp": {}
},
"Tty": false,
"OpenStdin": false,
"StdinOnce": false,
"Env": [
"PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin",
"GOSU_VERSION=1.17",
"MYSQL_MAJOR=innovation",
"MYSQL_VERSION=9.2.0-1.el9",
"MYSQL_SHELL_VERSION=9.2.0-1.el9"
],
"Cmd": [
"mysqld"
],
"Image": "",
"Volumes": {
"/var/lib/mysql": {}
},
"WorkingDir": "/",
"Entrypoint": [
"docker-entrypoint.sh"
],
"OnBuild": null,
"Labels": null
},
"Architecture": "amd64",
"Os": "linux",
"Size": 796976318,
"GraphDriver": {
"Data": {
"LowerDir": "/home/docker/overlay2/42c3ed0315e54bd5a76267d25eba039b858404f6bbd04bef8b5739c5fcd78ac6/diff:/home/docker/overlay2/d84e7089bb70ac136dc2e2d9a68ea75100571d49be246b1f6b595e141f535dbe/diff:/home/docker/overlay2/f849d02a40e130bfc48f77c09b59e09613769bdc49a42d3a2bf1c4cb7fd91c91/diff:/home/docker/overlay2/535f24da9f5439b18a693e2d9120e7fbb7525e7c42749a455b70e850ec8a2f56/diff:/home/docker/overlay2/1fe3219d8459bbdf2104ba34611948f8210451d465e5950bcdedec55659ba096/diff:/home/docker/overlay2/eac0abcc2ccb1f4b7aa792814200f6f7e51643a0f132aa547f4563d727186064/diff:/home/docker/overlay2/fa8b5321787264221d4ea205cf721cae42516e0e09a7510ec4e02637fed26817/diff:/home/docker/overlay2/1af961cf07a1516986c11617538b8e8c4afe7a441cca0130244283669290cfb8/diff:/home/docker/overlay2/bd3896bc4646e1037084d3ef119f7fa94d6bc008f93bf5f415928d667fce2d59/diff",
"MergedDir": "/home/docker/overlay2/dce8d0c3c8796b628a3477aceb4a868d443f1b7b88d1ab94aa2441abb7ba6514/merged",
"UpperDir": "/home/docker/overlay2/dce8d0c3c8796b628a3477aceb4a868d443f1b7b88d1ab94aa2441abb7ba6514/diff",
"WorkDir": "/home/docker/overlay2/dce8d0c3c8796b628a3477aceb4a868d443f1b7b88d1ab94aa2441abb7ba6514/work"
},
"Name": "overlay2"
},
"RootFS": {
"Type": "layers",
"Layers": [
"sha256:d7b2257a2277cf62be373f5e32a77274c67cdcdb474af84f2f01b95d8f6ebd0d",
"sha256:9986054dfacc4debd8fc996e555c5e2180d49fc21ebfd005c0b1e78136ce1104",
"sha256:8c039733b996247dfe9588782ce1385a887e4995037a6707abba9fed1b7a67ba",
"sha256:433b6ea8deb0f770e884e6fbb5fe88e18c0a5e6625906c6c5dd05cc753b0c60f",
"sha256:5494a032973c2c9f03c2d978222d83555d6e86b6d1602f2f69335202d1c6125f",
"sha256:bfba4799a5e3c7bee4d194639adf9f4393c9b02fe45289ddd5d499ddeb9bd277",
"sha256:501c1708175892aebd454706f7e09ff3bbea4c899e26af302ccd87eba0580f1d",
"sha256:21cd7e76ec3c2ddf6445fd06ef7d21d46e9ff4e4c04e732b5e1aef54d3d969e8",
"sha256:cfb04b43e3382dfe2082e7f8d800454ded906bc71a284363a7a820583bac93ed",
"sha256:bccedbf428d268739e947541440ec43f4ce59524d4ae1034634256ae5fb4e7a1"
]
},
"Metadata": {
"LastTagTime": "0001-01-01T00:00:00Z"
}
}
]
[12 May 6:06]
jinhui lai
Hi, MySQL developers.Thanks for your time on this case! Maybe my case is not good and caused you difficulties, I am sorry for that. Therefore, it is necessary for me to provide you with more information to help you locate the bug.
I have also reported the same bug case in the MariaDB community, which has been confirmed by MariaDB developers. The reason why this pattern query "q EXCEPT q" returns a non-empty result may be that information_schema is a virtual table and the content in it will be updated. But this is not what I want to emphasize. My core point is that q EXCEPT q will always return an empty set because it should not be actually executed. Below I will give you a more convincing case.
I pulled the latest mysql docker image：
docker inspect mysql:latest
docker inspect --format='{{.RepoDigests}}' mysql:latest
[mysql@sha256:2247f6d47a59e5fa30a27ddc2e183a3e6b05bc045e3d12f8d429532647f61358]
docker run -it --name mysql-9.3.0 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=1213 mysql:latest
docker exec -it mysql-9.3.0 mysql -uroot -p 1213
This is a better case;
mysql> use mysql;
mysql> SELECT * FROM time_zone_transition CROSS JOIN help_topic EXCEPT SELECT * FROM time_zone_transition CROSS JOIN help_topic;
-- more than 18 hours passed, this query is still executing.
MySQL point is: q EXCEPT q should always return an empty set and consume minimal execution time. It should not be executed, but the empty set is returned quickly and directly. However, MySQL execute it. We think this is a performance bug and should be optimized.
[12 May 6:32]
MySQL Verification Team
Hello jinhui lai, Thank you for the report and feedback. I tried to reproduce on multiple instances with identical environments as you observed but still no luck. I'll try to reproduce on docker and get back to you. regards, Umesh
[12 May 6:32]
MySQL Verification Team
Test results
Attachment: 118020_Ubuntu.txt (text/plain), 48.90 KiB.
[12 May 7:33]
jinhui lai
Tanks you for your reply! According your attachment, I think you have reproduced this bug. As present in your attachment: mysql> SELECT * FROM TABLES CROSS JOIN VIEWS ON TRUE EXCEPT SELECT * FROM TABLES CROSS JOIN VIEWS ON NOT FALSE; ... 14 rows in set (10.45 sec) Because this query will always be empty logically, but it returns 14 rows of values. I think it is because information_schema is a virtual table and the values in it will be updated. This is a better case; mysql> use mysql; mysql> SELECT * FROM time_zone_transition CROSS JOIN help_topic EXCEPT SELECT * FROM time_zone_transition CROSS JOIN help_topic; -- more than 18 hours passed, this query is still executing. MySQL point is: q EXCEPT q should always return an empty set and consume minimal execution time. It should not be executed, but the empty set is returned quickly and directly. However, MySQL execute it. We think this is a performance bug and should be optimized.
[19 May 14:51]
MySQL Verification Team
Hello jinhui lai, Thank you for the report and feedback. Verifying for now but I could not see the magnitude of slowness you had observed in your tests (sorry couldn't check on docker) in as much as 15+ attempts at my end. Development team may have follow up questions/request for details and if needed will request you to share. Thank you once again. regards, Umesh
