IT expression_index with Cached failed

[jingshanglu]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

[2021-08-30T06:45:36.840Z] time="2021-08-30T14:45:36+08:00" level=fatal msg="run test [expression_index] err: sql:SELECT COUNT(*) AS should_be_2 FROM INFORMATION_SCHEMA.COLUMNS\nWHERE TABLE_NAME = \"t1\";: failed to run query \n\"SELECT COUNT(*) AS should_be_2 FROM INFORMATION_SCHEMA.COLUMNS\nWHERE TABLE_NAME = \"t1\";\" \n around line 12, \nwe need(102):\nSELECT COUNT(*) AS should_be_2 FROM INFORMATION_SCHEMA.COLUMNS\nWHERE TABLE_NAME = \"t1\";\nshould_be_2\n2\n\nbut got(102):\nSELECT COUNT(*) AS should_be_2 FROM INFORMATION_SCHEMA.COLUMNS\nWHERE TABLE_NAME = \"t1\";\nshould_be_2\n4\n\n"

[2021-08-30T06:45:36.840Z] + echo 'tidb-server(PID: 472) stopped'

[2021-08-30T06:45:36.840Z] tidb-server(PID: 472) stopped

[2021-08-30T06:45:36.840Z] + kill -9 472

script returned exit code 1

ci: https://ci.pingcap.net/blue/organizations/jenkins/tidb_ghpr_integration_common_test/detail/tidb_ghpr_integration_common_test/6426/pipeline/145
pr: #27199

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

[jingshanglu]

/cc @YuJuncen

[wjhuang2016]

Fixed by https://github.com/pingcap/tidb-test/pull/1271

[zhouqiang-cl]

Fixed by pingcap/tidb-test#1271

Seems No. it still fail

[zhouqiang-cl]

https://ci.pingcap.net/blue/organizations/jenkins/tidb_ghpr_integration_common_test/detail/tidb_ghpr_integration_common_test/6457/pipeline

in integraion test with TIKV it fail

[morgo]

@wjhuang2016 it is a different query plan:

we need(491):
EXPLAIN SELECT * FROM t1 WHERE CAST(f1->>\"$.name\" AS CHAR(30)) COLLATE utf8mb4_bin = \"james\";
id\testRows\ttask\taccess object\toperator info
Projection_4\t1.00\troot\t\texpression_index.t1.f1
└─Selection_7\t3.20\troot\t\teq(cast(json_unquote(cast(json_extract(expression_index.t1.f1, $.name), var_string(16777216))), var_string(30)), \"james\")
  └─TableReader_6\t4.00\troot\t\tdata:TableFullScan_5
    └─TableFullScan_5\t4.00\tcop[tikv]\ttable:t1\tkeep order:false

But got:

EXPLAIN SELECT * FROM t1 WHERE CAST(f1->>\"$.name\" AS CHAR(30)) COLLATE utf8mb4_bin = \"james\";
id\testRows\ttask\taccess object\toperator info
Projection_4\t1.00\troot\t\texpression_index.t1.f1
└─IndexLookUp_10\t1.00\troot\t\t
  ├─IndexRangeScan_8(Build)\t1.00\tcop[tikv]\ttable:t1, index:idx1(cast(json_unquote(json_extract(`f1`, _utf8mb4'$.name')) as char(30)) collate utf8mb4_bin)\trange:[\"james\",\"james\"], keep order:false
  └─TableRowIDScan_9(Probe)\t1.00\tcop[tikv]\ttable:t1\tkeep order:false

[zhouqiang-cl]

fixed by https://github.com/pingcap/tidb-test/pull/1273

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'backport-x.y.z',
and then remove 'needs-more-info' label.

[jingshanglu]

https://ci.pingcap.net/blue/organizations/jenkins/tidb_ghpr_integration_common_test/detail/tidb_ghpr_integration_common_test/6506/pipeline

case expression_index failed, maybe related this one.

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'backport-x.y.z',
and then remove 'needs-more-info' label.