Invalid JSON text: The document root must not be followed by other values

[yongman]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE DATABASE IF NOT EXISTS repro_tcid12965;
USE repro_tcid12965;
CREATE TABLE event_log (
 id BIGINT PRIMARY KEY AUTO_INCREMENT,
 event_name VARCHAR(64),
 device_sn VARCHAR(64),
 event_attributes LONGTEXT
);
ALTER TABLE event_log SET TIFLASH REPLICA 1;

SELECT TABLE_NAME, AVAILABLE, PROGRESS
FROM information_schema.tiflash_replica
WHERE TABLE_SCHEMA = 'repro_tcid12965' AND TABLE_NAME = 'event_log';

// Insert invalid json rows
insert into event_log values
 -> ('banner_bottomsheet_view', 'SN-BAD',"{""product_identifier"":""PLLB001"",""during_ms"":387749,""displays"":[{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":1,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":2,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":3,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":4,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":5,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":6,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":7,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":8,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_TEN_MINUTES_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":9,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_TEN_MINUTES_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":10,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":11,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":12,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":13,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":14,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":15,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":16,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":17,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":18,""banner_key"":""HUMAN_DETECTION_ANTI_PINCH_EVENT""},{""banner_title"":""Device paused, camera detected a person approaching."",""display_order"":19,"

JSON_VALID can filter out the invalid json rows.

MySQL root@localhost:test> SELECT /*+ READ_FROM_STORAGE(TIKV[event_log]) */     DISTINCT event_attributes->>'$.banner_key' AS
                       ->   abnormal_event_name FROM event_log WHERE event_name IN ('banner_bottomsheet_view','notification_b
                       ->  anner_tap')   AND device_sn IS NOT NULL   AND event_name = 'banner_bottomsheet_view' AND JSON_VALI
                       ->  D(event_attributes);
+---------------------+
| abnormal_event_name |
+---------------------+
| <null>              |
+---------------------+

1 row in set
Time: 0.022s
MySQL root@localhost:test> SELECT /*+ READ_FROM_STORAGE(TIFLASH[event_log]) */     DISTINCT event_attributes->>'$.banner_key'
                       ->   AS abnormal_event_name FROM event_log WHERE event_name IN ('banner_bottomsheet_view','notificatio
                       ->  n_banner_tap')   AND device_sn IS NOT NULL   AND event_name = 'banner_bottomsheet_view' AND JSON_V
                       ->  ALID(event_attributes);
+---------------------+
| abnormal_event_name |
+---------------------+
| <null>              |
+---------------------+

1 row in set
Time: 0.147s

But if there are json decode in where clause the JSON_VALID can not take effects and result to exception error.

MySQL root@localhost:test> SELECT /*+ READ_FROM_STORAGE(TIKV[event_log]) */     DISTINCT event_attributes->>'$.banner_key' AS
                       ->   abnormal_event_name FROM event_log WHERE event_name IN ('banner_bottomsheet_view','notification_b
                       ->  anner_tap')   AND device_sn IS NOT NULL   AND event_name = 'banner_bottomsheet_view' AND JSON_VALI
                       ->  D(event_attributes) AND event_attributes->>'$.banner_key'IS NOT NULL;
+---------------------+
| abnormal_event_name |
+---------------------+
+---------------------+

0 rows in set
Time: 0.061s
MySQL root@localhost:test> SELECT /*+ READ_FROM_STORAGE(TIFLASH[event_log]) */     DISTINCT event_attributes->>'$.banner_key'
                       ->   AS abnormal_event_name FROM event_log WHERE event_name IN ('banner_bottomsheet_view','notificatio
                       ->  n_banner_tap')   AND device_sn IS NOT NULL   AND event_name = 'banner_bottomsheet_view' AND JSON_V
                       ->  ALID(event_attributes) AND event_attributes->>'$.banner_key'IS NOT NULL;
(1105, 'other error for mpp stream: Code: 0, e.displayText() = DB::Exception: Receiver state: ERROR, error message: Code: 0, e.displayText() = DB::Exception: Invalid JSON text: The document root must not be followed by other values, details: TAPE_ERROR: The JSON document has an improper structure: missing or superfluous commas, braces, missing keys, etc.  This is a fatal and unrecoverable error., e.what() = DB::Exception,, e.what() = DB::Exception,')

2. What did you expect to see? (Required)

Return values same as TiKV.

3. What did you see instead (Required)

Returned error.

4. What is your TiFlash version? (Required)

master branch.