MySQL Bugs: #92976: JSON_TABLE return "Unknown database ''" from a function

Bug #92976	JSON_TABLE return "Unknown database ''" from a function
Submitted:	27 Oct 2018 7:37	Modified:	8 May 2019 14:52
Reporter:	William Chiquito	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S2 (Serious)
Version:	8.0.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	functions, json_table

Description:
JSON_TABLE returns the error "Unknown database ''" when it's executed from a function (possibly the error is also generated from a trigger or an event).

How to repeat:
DROP FUNCTION IF EXISTS `FN_COUNT_ROWS`;

DELIMITER //

CREATE FUNCTION `FN_COUNT_ROWS`(`X` JSON)
RETURNS INT DETERMINISTIC
BEGIN
  RETURN (
    SELECT COUNT(*)
    FROM JSON_TABLE(
      `X`,
      '$[*]' COLUMNS(
        `I` INT PATH '$'
      )
    ) `der`
  );
END//

DELIMITER ;

SELECT `FN_COUNT_ROWS`('[1, 2, 3]') `CNT`;
Unknown database ''

See dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6f5cdc2ea8f94d410090d0cf006a9a11

Hello  William,

Thank you for the report and test case.

regards,
Umesh

Can confirm this.

I built a stored function and inside it added this

declare period_jobs JSON;
declare periodQTY INT;

# Actually period_jobs is built in a select, but this is for testing-
set period_jobs = '[{"START":"2018-11-09 12:00:00", "FINISH":"2018-11-10 12:00:00", "QTY":10},{"START":"2018-11-09 12:00:00", "FINISH":"2018-11-12 12:00:00", "QTY":3},{"START":"2018-11-10 12:00:00", "FINISH":"2018-11-11 12:00:00", "QTY":3}]';

select coalesce(sum(`QTY`),0) into periodQTY FROM JSON_TABLE(period_jobs, "$[*]" COLUMNS(`QTY` INTEGER UNSIGNED PATH "$.QTY")) AS tt;

Forgot to mention, tried this on 8.0.11 and 8.0.13

Bug still on v8.0.15

Bug still on v8.0.16

Documented fix as follows in the MySQL 8.0.17 changelog:

    JSON_TABLE() returned the error -Unknown database ''- when
    executed from a stored function.

    The root cause of this issue was that, when merging tables from
    a select that used JSON_TABLE(), MySQL checked only for derived
    tables. This caused the result table returned by JSON_TABLE() to
    be noted as a regular table, so that when attempting to execute
    the query, the server failed to open it. Now MySQL checks
    whether the table to be added is not an internal table, that is,
    not a derived table, a JSON_TABLE() result table, or a reference
    to a recursive common table expression.

Closed.

When will v8.0.17 launch?