Bug #92976 JSON_TABLE return "Unknown database ''" from a function
Submitted: 27 Oct 2018 7:37 Modified: 28 Oct 2018 8:06
Reporter: William Chiquito Email Updates:
Status: Verified 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

[27 Oct 2018 7:37] William Chiquito 
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
[28 Oct 2018 8:06] Umesh Shastry 
Hello  William,

Thank you for the report and test case.

regards,
Umesh
[9 Nov 2018 18:20] Justin Levene 
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;
[9 Nov 2018 23:26] Justin Levene 
Forgot to mention, tried this on 8.0.11 and 8.0.13
[9 Apr 15:50] Justin Levene 
Bug still on v8.0.15