JSON filtering error after horizontal connection #25813
Assignees
Labels
affects-5.0
This bug affects 5.0.x versions.
affects-5.1
This bug affects 5.1.x versions.
affects-5.2
This bug affects 5.2.x versions.
affects-5.3
This bug affects 5.3.x versions.
affects-5.4
This bug affects the 5.4.x(LTS) versions.
affects-6.0
affects-6.1
This bug affects the 6.1.x(LTS) versions.
component/json
severity/major
sig/execution
SIG execution
type/bug
The issue is confirmed as a bug.
Comments
|
@linhantao Thanks for your feedback, can you add some information about the table structure and the steps to reproduce? |
|
@jingshanglu repeat the steps as follows:
2、insert one data:
3、error sql:
4、 success sql:
|
Closed
jebter
added
affects-5.0
|
There is something wrong when parsing binary from json str But for the normal one, |
2 tasks
Reproduceminimal reproduce: create table t(a json);
insert into t values('{"id": "ish"}');
select * from t t1 left join t t2 on t1.a=t2.a where t2.a='ish';
+--------------------------------+---------+-----------+---------------+--------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+---------+-----------+---------------+--------------------------------------------------------------+
| Selection_8 | 0.80 | root | | eq(test.t.a, cast("ish", json BINARY)) |
| └─HashJoin_9 | 1.00 | root | | left outer join, equal:[eq(test.t.a, test.t.a)] |
| ├─TableReader_15(Build) | 0.80 | root | | data:Selection_14 |
| │ └─Selection_14 | 0.80 | cop[tikv] | | not(isnull(cast(test.t.a, var_string(4294967295)))) |
| │ └─TableFullScan_13 | 1.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─TableReader_12(Probe) | 1.00 | root | | data:TableFullScan_11 |
| └─TableFullScan_11 | 1.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+--------------------------------+---------+-----------+---------------+--------------------------------------------------------------+
7 rows in set (0.00 sec)this issue will reproduce when :
Root Causeactually mysql> SELECT CAST('"ish"' AS json);
+-----------------------+
| CAST('"ish"' AS json) |
+-----------------------+
| "ish" |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT CAST('ish' AS json);
ERROR 3140 (22032): Invalid JSON text: The document root must not be followed by other values.phase planBuilder:
func (c *compareFunctionClass) generateCmpSigs(ctx sessionctx.Context, args []Expression, tp types.EvalType) (sig builtinFunc, err error) {
if tp == types.ETJson {
// In compare, if we cast string to JSON, we shouldn't parse it.
for i := range args {
DisableParseJSONFlag4Expr(args[i])
}
}phase logicalOptimize phase executor Fix#35759 will fix this issue. suggest closing the cherry-picks of this pr, because it has a little effect and difficult trigger condition. |
This was referenced Jun 28, 2022
Closed
Closed
Closed
Closed
Closed
Closed
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
TIDB version : V5.1 / V5.0
error message：[Err] 3140 - Invalid JSON text: The document root must not be followed by other values.
When left join is used, JSON value filtering is performed on the right table, and an error occur，
When using a right join, JSON value filtering is performed on the left table and an error occurs.
for example：
When a left join is used, JSON value filtering is performed on the left table without error.Such as:
However, the values in the JSON filter after the concatenation are all numeric, so the query can be executed without letters
for example：
The text was updated successfully, but these errors were encountered: