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

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.

Hello Michael,

Thank you for the bug report.
Verified as described.

Thanks,
Umesh

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)

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