Bug #71050 json_extract returning same column twice if key names are not fully distinct.

• Submitted: 2 Dec 2013 14:42
• Modified: 11 Dec 2013 22:13
• Reporter: Michael England
• Status: Closed
• Category: MySQL Server: JSON User-defined function ( UDF )
• Severity: S3 (Non-critical)
• Version: 0.2.0
• OS: Linux
• CPU Architecture: Any
• Tags: json_extract

[2 Dec 2013 14:42] Michael England

Description:
An issue has been found using the json_extract() function. If there are multiple JSON keys, for example ‘ts’ to indicate timestamp and ‘t’ indicating a total and a select statement is run on them, the ‘ts’ values are returned twice. Therefore, distinct key names must been given to avoid this issue e.g. ‘ts’ and ‘total’ in this example.

How to repeat:
mysql> select json_extract(jdoc,"ts") as ts, json_extract(jdoc,"t") as t from bug;
+-------------------------------+-------------------------------+
| ts                            | t                             |
+-------------------------------+-------------------------------+
| 2013/11/12-09:15:37.488 +0000 | 2013/11/12-09:15:37.488 +0000 |
| 2013/11/12-06:04:27.281 +0000 | 2013/11/12-06:04:27.281 +0000 |
+-------------------------------+-------------------------------+

The ts column contains timestamps, the t column contains totals.

[2 Dec 2013 16:41] MySQL Verification Team

Hello Michael,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

[2 Dec 2013 16:47] MySQL Verification Team

How to repeat:

//Download udf's from http://labs.mysql.com/, category: MySQL JSON UDFs, and install as per README

use test;
drop table if exists jtest;
CREATE TABLE `jtest` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `jdoc` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

insert into jtest values(1,"{\"ts\": \"2013/11/12-10:15:37.488 +0000\", \"t\": \"10\"}");
insert into jtest values(2,"{\"ts\": \"2013/11/12-10:15:37.488 +0000\", \"t\": \"12\"}");
insert into jtest values(3,"{\"ts\": \"2013/11/12-10:15:37.488 +0000\", \"t\": \"11\"}");

SELECT json_extract(jdoc, "ts") ts, json_extract(jdoc, "t") t FROM test.jtest LIMIT 10;

// 5.6.14

mysql> use test;
Database changed
mysql> drop table if exists jtest;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `jtest` (
    ->   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    ->   `jdoc` blob,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> insert into jtest values(1,"{\"ts\": \"2013/11/12-10:15:37.488 +0000\", \"t\": \"10\"}");
Query OK, 1 row affected (0.00 sec)

mysql> insert into jtest values(2,"{\"ts\": \"2013/11/12-10:15:37.488 +0000\", \"t\": \"12\"}");
Query OK, 1 row affected (0.00 sec)

mysql> insert into jtest values(3,"{\"ts\": \"2013/11/12-10:15:37.488 +0000\", \"t\": \"11\"}");
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> 
mysql> SELECT json_extract(jdoc, "ts") ts, json_extract(jdoc, "t") t FROM test.jtest LIMIT 10;
+-------------------------------+-------------------------------+
| ts                            | t                             |
+-------------------------------+-------------------------------+
| 2013/11/12-10:15:37.488 +0000 | 2013/11/12-10:15:37.488 +0000 |
| 2013/11/12-10:15:37.488 +0000 | 2013/11/12-10:15:37.488 +0000 |
| 2013/11/12-10:15:37.488 +0000 | 2013/11/12-10:15:37.488 +0000 |
+-------------------------------+-------------------------------+
3 rows in set (0.00 sec)

[11 Dec 2013 22:13] Sveta Smirnova

Posted by developer:
 
Fixed in versions 0.2.2 and 0.3.0

Bug fixed for JSON_EXTRACT, JSON_CONTAINS_KEY, JSON_APPEND, JSON_REMOVE, JSON_REPLACE, JSON_SET