[MDEV-25377] JSON_TABLE: Wrong value with implicit conversion Created: 2021-04-09  Updated: 2021-04-20  Resolved: 2021-04-18

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

Type: Bug Priority: Blocker
Reporter: Elena Stepanova Assignee: Alexey Botchkov
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-17399 Add support for JSON_TABLE Closed
relates to MDEV-25149 JSON_TABLE: Inconsistency in implicit... Closed

 Description    

select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted tinyint path '$', original text path '$')) as jt;
 
 
 
select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted tinyint path '$', original text path '$')) as jt order by converted;
 
 
 
select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted tinyint path '$', original text path '$')) as jt order by original;

In all queries below, the value 1 is valid for the tinyint column, while the values "foo" and 1000 are not, so they get implicitly converted.

The first query, without ORDER BY, converts both to NULL and returns them in the order of appearance:

bb-10.6-mdev17399-hf a96203921343 with a local patch producing the conversion warnings

 
+-----------+----------+
 
| converted | original |
 
+-----------+----------+
 
|      NULL | foo      |
 
|         1 | 1        |
 
|      NULL | 1000     |
 
+-----------+----------+
 
3 rows in set, 2 warnings (0.001 sec)

However, the same query with ORDER BY (by either column) converts 1000 to NULL, but "foo" to 0: 

MariaDB [test]> select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted tinyint path '$', original text path '$')) as jt order by original;
 
+-----------+----------+
 
| converted | original |
 
+-----------+----------+
 
|         1 | 1        |
 
|      NULL | 1000     |
 
|         0 | foo      |
 
+-----------+----------+
 
3 rows in set (0.001 sec)

Moreover, if the converted values are used in ORDER BY, the final order appears to be incorrect:

MariaDB [test]> select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted tinyint path '$', original text path '$')) as jt order by converted;
 
+-----------+----------+
 
| converted | original |
 
+-----------+----------+
 
|         0 | foo      |
 
|      NULL | 1000     |
 
|         1 | 1        |
 
+-----------+----------+
 
3 rows in set, 2 warnings (0.001 sec)

(In fact, 0 is probably still NULL during ORDER BY, hence the order, but it doesn't look right).

 Comments   
Comment by Sergei Petrunia [ 2021-04-13 ]

Ok the rows are sorted correctly. The reason for bad result with ORDER BY is that the row is not decoded correctly from the rowid.

Idea of a fix:

@@ -555,7 +561,11 @@ int ha_json_table::fill_column_values(uchar * buf, uchar *pos)
 
 
 int ha_json_table::rnd_pos(uchar * buf, uchar *pos)
 
 {
 
-  return fill_column_values(buf, pos) ? HA_ERR_JSON_TABLE : 0;
 
+  auto cf_orig= table->in_use->count_cuted_fields;
 
+  table->in_use->count_cuted_fields= CHECK_FIELD_EXPRESSION;
 
+  auto res= fill_column_values(buf, pos);
 
+  table->in_use->count_cuted_fields= cf_orig;
 
+  return res;
 
 }

Comment by Sergei Petrunia [ 2021-04-18 ]

Fixed by fix for MDEV-25149. Testcase added.

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