[MDEV-14934] JSON boolean values are not automatically converted to TINYINT(1) Created: 2018-01-12  Updated: 2018-01-15  Resolved: 2018-01-15

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

Type: Bug Priority: Minor
Reporter: Heinz Wiesinger Assignee: Sergei Golubchik
Resolution: Won't Fix Votes: 0
Labels: None


 Description    

CREATE TABLE IF NOT EXISTS `json_boolean` (
 
    `id` INT(11) UNSIGNED AUTO_INCREMENT,
 
    `json` JSON NOT NULL,
 
    `value` TINYINT(1) AS (JSON_VALUE(json, '$.value')) PERSISTENT,
 
    PRIMARY KEY (`id`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


MariaDB> INSERT INTO json_boolean (`json`) VALUES ('{"value":true}');
 
ERROR 1366 (22007): Incorrect integer value: 'true' for column 'value' at row 1

Since BOOLEAN is an alias for TINYINT(1) I think it would make sense that JSON booleans automatically work as such as well.

 Comments   
Comment by Elena Stepanova [ 2018-01-13 ]

It's not really related to virtual columns. Here is a similar example applicable both to MySQL and MariaDB, it shows the difference.

DROP TABLE IF EXISTS json2;
 
CREATE TABLE json2 (v TINYINT(1));
 
INSERT INTO json2 VALUES (JSON_EXTRACT('{"value":true}', '$.value'));

MariaDB 10.2

 
MariaDB [test]> INSERT INTO json2 VALUES (JSON_EXTRACT('{"value":true}', '$.value'));
 
ERROR 1366 (22007): Incorrect integer value: 'true' for column 'v' at row 1

MySQL 5.7

 
mysql> INSERT INTO json2 VALUES (JSON_EXTRACT('{"value":true}', '$.value'));
 
Query OK, 1 row affected (0.04 sec)

Comment by Sergei Golubchik [ 2018-01-15 ]

We cannot really do it.

The standard syntax (SQL:2016, part 2, 6.27 <JSON value function>) is

<JSON value function> ::=
 
  JSON_VALUE <left paren>
 
      <JSON API common syntax>
 
      [ <JSON returning clause> ]
 
      [ <JSON value empty behavior> ON EMPTY ]
 
      [ <JSON value error behavior> ON ERROR ]
 
  <right paren>
 
 
 
<JSON returning clause> ::=
 
  RETURNING <data type>

And the Syntax Rules say

1) If <JSON returning clause> is not specified, then an implementation-defined character string type is implicit.

MariaDB does not support <JSON returning clause> yet, so JSON_VALUE() always returns a "character string type", as required by the SQL Standard.

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