Bug #97097 Stored Procedure, JSON_TABLE and "const" join type

• Submitted: 3 Oct 2019 13:37
• Modified: 24 Oct 2019 20:16
• Reporter: William Chiquito
• Status: Closed
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 8.0.17
• OS: Any
• CPU Architecture: Any
• Tags: json_table, SP

[3 Oct 2019 13:37] William Chiquito

Description:
Repeatedly invoking a stored procedure that uses a "const" join type doesn't show the expected results after the first invocation. When the condition changes in the WHERE statement (within the stored procedure) of "... `tb_test`.`id` = 1;" to "... `tb_test`.`id`> 0;", everything works as expected, because join type changes from "const" to "range" (use EXPLAIN to verify).

How to repeat:
SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.17    |
+-----------+
1 row in set (0.00 sec)

mysql> DROP PROCEDURE IF EXISTS `sp_test`;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLE IF EXISTS `tb_test`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `tb_test` (
    ->   `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->   `data_json` JSON
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `tb_test`
    ->   (`id`, `data_json`)
    -> VALUES
    ->   (1, '[{"x": 1}]'), (2, '[{"x": 2}]');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> DELIMITER //

mysql> CREATE PROCEDURE `sp_test`()
    -> BEGIN
    ->   SELECT
    ->     `tb_test`.*
    ->   FROM
    ->     `tb_test`,
    ->       JSON_TABLE(
    ->         `data_json`,
    ->         '$[*]' COLUMNS(
    ->           ROWID FOR ORDINALITY,
    ->           `test` INT PATH '$.x' DEFAULT '0' ON ERROR DEFAULT '0' ON EMPTY
    ->         )
    ->       ) `x`
    ->   WHERE
    ->     `tb_test`.`id` = 1;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL `sp_test`;
+----+------------+
| id | data_json  |
+----+------------+
|  1 | [{"x": 1}] |
+----+------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> CALL `sp_test`;
Empty set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

[3 Oct 2019 18:09] MySQL Verification Team

Thank you for the bug report.

[24 Oct 2019 20:16] Jon Stephens

Documented fix as follows in the MySQL 8.0.19 changelog:

    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.

Closed.