| Bug #98943 | Procedure returns incorrect result on second invocation | ||
|---|---|---|---|
| Submitted: | 13 Mar 2020 12:28 | Modified: | 16 Mar 2020 13:03 |
| Reporter: | Damjan Vujnovic | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Stored Routines | Severity: | S2 (Serious) |
| Version: | 8.0.19 | OS: | Linux |
| Assigned to: | CPU Architecture: | x86 | |
[13 Mar 2020 12:51]
MySQL Verification Team
Thank you for the bug report. Which is the correct result you expect?
[13 Mar 2020 12:52]
Damjan Vujnovic
This is what should happen (and happens first time): mysql> call check_game(); +------+ | true | +------+ | 1 | +------+ 1 row in set (0.00 sec) This is what happens instead: mysql> call check_game(); Empty set (0.00 sec)
[13 Mar 2020 13:16]
MySQL Verification Team
Here the result on Windows:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> USE test
Database changed
mysql> create table game(
-> game_id varchar(255) primary key,
-> document json
-> );
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> insert into game values (
-> 'test',
-> '{
'> "prize": [{"up": 19, "down": 20}, {"up": 1, "down": 20}],
'> "total": 10
'> }'
-> );
Query OK, 1 row affected (0.01 sec)
mysql> drop procedure if exists check_game;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> create procedure check_game()
-> select true
-> from
-> game,
-> json_table(
-> document,
-> '$.prize[*]' columns(
-> up bigint path '$.up',
-> down bigint path '$.down'
-> )
-> ) p
-> where game_id = 'test'
-> and floor(json_extract(document, '$.total') * up / down) <> (json_extract(document, '$.total') * up / down)
-> limit 1;
Query OK, 0 rows affected (0.01 sec)
mysql> call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
[13 Mar 2020 13:21]
MySQL Verification Team
Not repeatable on Linux too:
mysql 8.0 > create procedure check_game()
-> select true
-> from
-> game,
-> json_table(
-> document,
-> '$.prize[*]' columns(
-> up bigint path '$.up',
-> down bigint path '$.down'
-> )
-> ) p
-> where game_id = 'test'
-> and floor(json_extract(document, '$.total') * up / down) <> (json_extract(document, '$.total') * up / down)
-> limit 1;
Query OK, 0 rows affected (0,30 sec)
mysql 8.0 > call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0,01 sec)
Query OK, 0 rows affected (0,01 sec)
mysql 8.0 > call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)
mysql 8.0 > call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)
mysql 8.0 > call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)
mysql 8.0 > call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)
mysql 8.0 > call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)
mysql 8.0 > call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)
mysql 8.0 > call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)
mysql 8.0 > SHOW VARIABLES LIKE "%VERSION%";
+--------------------------+----------------------------------------+
| Variable_name | Value |
+--------------------------+----------------------------------------+
| immediate_server_version | 999999 |
| innodb_version | 8.0.20 |
| original_server_version | 999999 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version | 8.0.20 |
| version_comment | Source distribution BUILT: 2020-FEB-10 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
| version_compile_zlib | 1.2.11 |
+--------------------------+----------------------------------------+
11 rows in set (0,04 sec)
mysql 8.0 >
[13 Mar 2020 13:34]
Damjan Vujnovic
Interesting :) thank you. I’ve noticed you’re using dev (8.0.20) build on Linux, how about GA (8.0.19)? Thanks again for your help, damjan
[13 Mar 2020 13:53]
MySQL Verification Team
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE test;
Query OK, 1 row affected (0,15 sec)
mysql> USE test;
Database changed
mysql> create table game(
-> game_id varchar(255) primary key,
-> document json
-> );
Query OK, 0 rows affected (0,73 sec)
mysql> insert into game values (
-> 'test',
-> '{
'> "prize": [{"up": 19, "down": 20}, {"up": 1, "down": 20}],
'> "total": 10
'> }'
-> );
Query OK, 1 row affected (0,12 sec)
mysql> drop procedure if exists check_game;
from
game,
json_table(
Query OK, 0 rows affected, 1 warning (0,05 sec)
mysql> create procedure check_game()
-> select true
-> from
-> game,
-> json_table(
-> document,
-> '$.prize[*]' columns(
-> up bigint path '$.up',
-> down bigint path '$.down'
-> )
-> ) p
-> where game_id = 'test'
-> and floor(json_extract(document, '$.total') * up / down) <> (json_extract(document, '$.total') * up / down)
-> limit 1;
Query OK, 0 rows affected (0,16 sec)
mysql> call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)
mysql> call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)
mysql> call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0,01 sec)
Query OK, 0 rows affected (0,01 sec)
mysql> call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)
mysql> call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)
mysql> call check_game();
+------+
| true |
+------+
| 1 |
+------+
1 row in set (0,00 sec)
Query OK, 0 rows affected (0,00 sec)
mysql> SHOW VARIABLES LIKE "%VERSION%";
+--------------------------+-------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------+
| immediate_server_version | 999999 |
| innodb_version | 8.0.19 |
| original_server_version | 999999 |
| protocol_version | 10 |
| slave_type_conversions | |
| tls_version | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| version | 8.0.19 |
| version_comment | MySQL Community Server - GPL |
| version_compile_machine | x86_64 |
| version_compile_os | linux-glibc2.12 |
| version_compile_zlib | 1.2.11 |
+--------------------------+-------------------------------+
11 rows in set (0,00 sec)
mysql>
[13 Mar 2020 14:20]
MySQL Verification Team
Hi Mr. Vujnovic, I was also unable to repeat your problem with 8.0.19: +------+ | true | +------+ | 1 | +------+ 1 row in set (0.00 sec) +------+ | true | +------+ | 1 | +------+ 1 row in set (0.00 sec) +------+ | true | +------+ | 1 | +------+ 1 row in set (0.00 sec)+------+ | true | +------+ | 1 | +------+ 1 row in set (0.00 sec)+------+ | true | +------+ | 1 | +------+ 1 row in set (0.00 sec) +------+ | true | +------+ | 1 | +------+ 1 row in set (0.00 sec) +------+ | true | +------+ | 1 | +------+ 1 row in set (0.00 sec) and so on .....
[13 Mar 2020 14:40]
Damjan Vujnovic
Thank you all for your help. I've just spun a MySQL instance on AWS and tried my little script, and (as I've feared) it does not behave incorrectly :( However, all our workstations and dev/prod instances (mix of macOS and Centos) misbehave. It could be related to the rest of the schema, so I guess my next step is to see if I could produce enough of the schema to trigger it 'from scratch'... Which might be a problem for us (openly publishing DB schema). Is there a way for us to send it through some other channel? Many thanks again for your help and wish you a great weekend, damjan
[13 Mar 2020 14:43]
MySQL Verification Team
Damjan, You are truly welcome !!!
[13 Mar 2020 17:22]
Damjan Vujnovic
Apologies - I just went again over everything and it turns out that on the environments and workstationss with this issue the MySQL version was 8.0.17. I then tried running the above script on clean 8.0.17 installations and the problem does indeed occur. I then upgraded to 8.0.18 and the issue persisted. After the upgrade to 8.0.19 the problem went away... I then tried checking the 8.0.19 changelog and couldn't see anything related, so I just hope it wasn't fixed "by accident"... Were you aware of this issue prior to 8.0.19? Many thanks again and wish you all a great weekend, damjan
[13 Mar 2020 17:27]
Damjan Vujnovic
Actually, I've found it: JSON: If the first argument to JSON_TABLE() was const during the execution of a stored procedure, but not during preparation, it was not re-evaluated when a statement was subsequently executed again, causing an empty result to be returned each time following the first execution of the procedure. (Bug #97097, Bug #30382156) Feel free to close this one.
[16 Mar 2020 13:03]
MySQL Verification Team
Thank you, Mr. Vujnovic.
[14 Jan 10:21]
DK Singh
Hi @Damjan Vujnovic While I'm using 8.0.32 and same issue I'm facing. I'm not using any Json_Table or Temporary Table and all. When I execute the query outside the stored proc it gives me correct result every time but from stored proc gives me correct result only first time
[14 Jan 11:42]
MySQL Verification Team
Thank you Mr. Singh.
Description: The check_game procedure (see below) returns correct result on first invocation only. Every successive invocation (from the same DB connection) returns incorrect result. If the procedure is invoked from a newly created connection it would again return the correct result first time (and keep returning an incorrect one). How to repeat: drop table if exists game; create table game( game_id varchar(255) primary key, document json ); insert into game values ( 'test', '{ "prize": [{"up": 19, "down": 20}, {"up": 1, "down": 20}], "total": 10 }' ); drop procedure if exists check_game; create procedure check_game() select true from game, json_table( document, '$.prize[*]' columns( up bigint path '$.up', down bigint path '$.down' ) ) p where game_id = 'test' and floor(json_extract(document, '$.total') * up / down) <> (json_extract(document, '$.total') * up / down) limit 1; call check_game(); call check_game();