[MDEV-18125] JSON_VALUE broken in functions Created: 2019-01-03  Updated: 2019-04-29  Resolved: 2019-04-29

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.2.21
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Toshko Andreev Assignee: Alexander Barkov
Resolution: Duplicate Votes: 0
Labels: None
Environment:

Ubuntu 16.04 x64

Issue Links:
Duplicate
duplicates MDEV-11829 Please add support for datetime with ... Open

 Description   

We have found a serious problem with JSON_VALUE which apparently was partially fixed in 10.2.16. Specifically we're extracting a Date from JSON and if that extraction is in a function it does not work.

ERROR: Truncated incorrect datetime value: '2018-07-26T00:00:00+03:00'

If the extraction is done from the query (ie no function that does it) then it works fine.

Please see the following example:

SET NAMES 'utf8';
 
 
 
USE import;
 
 
 
DELIMITER $$
 
 
 
CREATE DEFINER = 'jsupport'@'10.8.0.%'
 
FUNCTION J_SON ()
 
RETURNS date
 
BEGIN
 
 
 
  RETURN (SELECT
 
      date (JSON_VALUE('{"b":"2018-07-26T00:00:00+03:00"}', '$.b')));
 
 
 
END
 
$$
 
 
 
DELIMITER ;


-- WORKING -> SELECT date (JSON_VALUE('{"b":"2018-07-26T00:00:00+03:00"}', '$.b'));
 
-- BROKEN    -> SELECT J_SON();



 Comments   
Comment by Alice Sherepa [ 2019-01-03 ]

There is a warning after truncation anyway, but inside the function server returns error. 

MariaDB [test]> CREATE or replace FUNCTION f1 () RETURNS date RETURN (select date ("2018-07-26aaa"));
 
Query OK, 0 rows affected (0.006 sec)
 
 
 
MariaDB [test]> show create function f1;
 
+----------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
 
| Function | sql_mode                                                                                  | Create Function                                                                                       | character_set_client | collation_connection | Database Collation |
 
+----------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
 
| f1       | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS date
 
RETURN (select date ("2018-07-26aaa")) | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
 
+----------+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [test]> SELECT f1();
 
ERROR 1292 (22007): Truncated incorrect date value: '2018-07-26aaa'
 
Error (Code 1292): Truncated incorrect date value: '2018-07-26aaa'
 
Note (Code 4094): At line 2 in test.f1
 
 
 
MariaDB [test]> SELECT date ("2018-07-26aaa"); 
+------------------------+
 
| date ("2018-07-26aaa") |
 
+------------------------+
 
| 2018-07-26             |
 
+------------------------+
 
1 row in set, 1 warning (0.000 sec)
 
Warning (Code 1292): Truncated incorrect date value: '2018-07-26aaa'
 
 
 
MariaDB [test]> show variables like 'sql_mode';
 
+---------------+-------------------------------------------------------------------------------------------+
 
| Variable_name | Value                                                                                     |
 
+---------------+-------------------------------------------------------------------------------------------+
 
| sql_mode      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
 
+---------------+-------------------------------------------------------------------------------------------+
 
1 row in set (0.001 sec)


MariaDB [test]> set sql_mode='';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> CREATE or replace FUNCTION f1 () RETURNS date RETURN (select date ("2018-07-26aaa"));
 
Query OK, 0 rows affected (0.006 sec)
 
 
 
MariaDB [test]>  SELECT f1();
 
+------------+
 
| f1()       |
 
+------------+
 
| 2018-07-26 |
 
+------------+
 
1 row in set, 1 warning (0.001 sec)
 
 
 
Warning (Code 1292): Truncated incorrect date value: '2018-07-26aaa'

Comment by Alexander Barkov [ 2019-04-29 ]

MariaDB does not support yet timestamp format with time zone. This will be done in MDEV-11829.

So the warning happens because of the '+03:00' part in the timestamp string. If you remove the '+03:00' part it works without warnings or errors:

SET NAMES 'utf8';
 
SET sql_mode=DEFAULT;
 
DELIMITER $$
 
CREATE OR REPLACE FUNCTION f1 () RETURNS date
 
BEGIN
 
  RETURN (SELECT
 
      date (JSON_VALUE('{"b":"2018-07-26T00:00:00"}', '$.b')));
 
END
 
$$
 
DELIMITER ;
 
SELECT date(JSON_VALUE('{"b":"2018-07-26T00:00:00"}', '$.b')) AS c1, f1();


+------------+------------+
 
| c1         | f1()       |
 
+------------+------------+
 
| 2018-07-26 | 2018-07-26 |
 
+------------+------------+

Possible workarounds:

Use empty sql_mode at the function creation time

This will suppress converting warnings to errors inside the stored function:

SET NAMES 'utf8';
 
SET sql_mode='';
 
DELIMITER $$
 
CREATE OR REPLACE FUNCTION f1 () RETURNS date
 
BEGIN
 
  RETURN (SELECT
 
      date (JSON_VALUE('{"b":"2018-07-26T00:00:00+03:00"}', '$.b')));
 
END
 
$$
 
DELIMITER ;


SET sql_mode=DEFAULT; -- Execution time sql_mode is not important
 
SELECT date(JSON_VALUE('{"b":"2018-07-26T00:00:00+03:00"}', '$.b')) AS c1, f1();


+------------+------------+
 
| c1         | f1()       |
 
+------------+------------+
 
| 2018-07-26 | 2018-07-26 |
 
+------------+------------+
 
1 row in set, 2 warnings (0.001 sec)

Remove the '+03:00' part before passing the value to date()

The warning will disappear.

If can be LEFT(datestr, 19) in simple cases, or REGEXP_SUBSTR(datexpr,'...') in more complex cases.

SET NAMES 'utf8';
 
SET sql_mode=DEFAULT;
 
DELIMITER $$
 
CREATE OR REPLACE FUNCTION f1 () RETURNS date
 
BEGIN
 
  RETURN (SELECT
 
      date (LEFT(JSON_VALUE('{"b":"2018-07-26T00:00:00+03:00"}', '$.b'),19)));
 
END
 
$$
 
DELIMITER ;


SELECT date(LEFT(JSON_VALUE('{"b":"2018-07-26T00:00:00+03:00"}', '$.b'),19)) AS c1, f1();


+------------+------------+
 
| c1         | f1()       |
 
+------------+------------+
 
| 2018-07-26 | 2018-07-26 |
 
+------------+------------+
 
1 row in set (0.001 sec)

Generated at Thu Feb 08 08:41:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.