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

[Jiangrb]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

explain 
select a from (
	select JSON_OBJECT('number', `temp1`.`number`, 'name', `temp1`.`name`)  'a' from  
	(
		select 1  as number, 'name-1' as name  union 
		(select 2, 'name-2' ) union 
		(select 3, 'name-3' ) union 		
		(select 4, 'name-4' ) union 
		(select 5, 'name-5' ) union 
		(select 6, 'name-2' ) 
	) temp1 
) temp  
where  a ->> '$.number' = 1

执行计划可以执行，但是执行会报错。Invalid JSON text: The document root must not be followed by other values

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

执行结果是：

{"name": "name-1", "number": 1}

3. What did you see instead (Required)

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

4. What is your TiDB version? (Required)

5.7.25-TiDB-v6.2.0

[Jiangrb]

执行计划中，比较的表达式：

eq(cast(json_unquote(cast(json_extract(json_object("number", cast(Column#13, json BINARY), "name", cast(cast(Column#14, json BINARY), json BINARY)), "$.number"), var_string(16777216))), double BINARY), 1)

可能是因为:
"name", cast(cast(Column#14, json BINARY), json BINARY) 中有两次转换json的操作

[YangKeao]

执行计划中，比较的表达式：

eq(cast(json_unquote(cast(json_extract(json_object("number", cast(Column#13, json BINARY), "name", cast(cast(Column#14, json BINARY), json BINARY)), "$.number"), var_string(16777216))), double BINARY), 1)

可能是因为: "name", cast(cast(Column#14, json BINARY), json BINARY) 中有两次转换json的操作

Though casting from json to json multiple times seems really wired (and I really expect planner to avoid this), it's not the direct cause of this problem: as casting from json to json will always success.

The problem is that the ParseToJSONFlag flag is added somewhere, so it uses ParseBinaryJSONFromString, but not CreateBinaryJSON (which can accept a unquoted string) 🤔, and throw this error.

[Jiangrb]

@YangKeao I've verified what you just said before, and it's true, casting from json to json will always success.

From this simple example, I just want to filter the data I want from the spliced json object. Once I put the field of json object in the where condition, it will go wrong. As you can see from the execution plan, it decode the value of each string property in the json object to json.

The execution of this SQL in mysql will not report an error, but the execution in tidb will.

I have tried to disable the push of certain expressions, such as the json_object () function, cast (), etc., but to no avail.

I hope tidb team can consider solving it .

[YangKeao]

I have tried to disable the push of certain expressions, such as the json_object () function, cast (), etc., but to no avail.

I hope tidb team can consider solving it .

Sure. It's a bug of TiDB. The former comments are just some intermedia result while I was finding the cause. I'm trying to locate the problem and fix it 🍻 .