Can’t find a proper physical plan for this query when using TiFlash with explicit transaction

[Kongdom]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE test_202304141 (id INT NOT NULL PRIMARY KEY);
CREATE TABLE test_202304142 (id INT NOT NULL PRIMARY KEY);

ALTER TABLE test_202304141 SET TIFLASH REPLICA 1;

SELECT * FROM INFORMATION_SCHEMA.tiflash_replica 
WHERE TABLE_NAME = 'test_202304141';

INSERT INTO test_202304141 VALUES (1);

BEGIN; 
INSERT INTO test_202304142 
SELECT /*+ read_from_storage(tiflash[t]) */ * FROM test_202304141 t;      
COMMIT;

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

run correctly

3. What did you see instead (Required)

Internal : Can't find a proper physical plan for this query

4. What is your TiDB version? (Required)

Release Version: v6.5.0
Edition: Community
Git Commit Hash: 706c3fa3c526cdba5b3e9f066b1a568fb96c56e3
Git Branch: heads/refs/tags/v6.5.0
UTC Build Time: 2022-12-27 03:50:44
GoVersion: go1.19.3
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

[jebter]

Please refer to：https://docs.pingcap.com/tidb/stable/optimizer-hints#syntax

[Kongdom]

When just using TiFlash，the statement runs normally。When using TiFlash with explicit transaction，the statement runs with an error。

[JaySon-Huang]

It is a known limitation from https://docs.pingcap.com/tidb/stable/tiflash-results-materialization @joey-yez @zanmato1984 @jebter

If the SQL mode of the current session is strict (which means the sql_mode value contains either STRICT_TRANS_TABLES or STRICT_ALL_TABLES), the SELECT subquery in INSERT INTO SELECT cannot be pushed down to TiFlash.

In v6.5.x, we can make the query run successfully by setting set @@tidb_enable_tiflash_read_for_write_stmt=true; Since v7.1.x, if we remove STRICT_TRANS_TABLES from the default sql_mode, the query can run successfully

-- fail by "Can't find a proper physical plan for this query" under default sql_mode
test> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
test> BEGIN;
   -> INSERT INTO test_202304142
   -> SELECT /*+ read_from_storage(tiflash[t]) */ * FROM test_202304141 t;
   -> COMMIT;
Query OK, 0 rows affected

(1815, "Internal : Can't find a proper physical plan for this query")

-- remove `STRICT_TRANS_TABLES` and the query can success
test> set sql_mode='ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
test> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
test> BEGIN;
   -> INSERT INTO test_202304142
   -> SELECT /*+ read_from_storage(tiflash[t]) */ * FROM test_202304141 t;
   -> COMMIT;
Query OK, 0 rows affected
Time: 0.001s

Query OK, 1 row affected
Time: 0.048s

Query OK, 0 rows affected
Time: 0.002s

[qw4990]

Duplicated with https://github.com/pingcap/tidb/issues/51360

[seiya-annie]

/found customer

[seiya-annie]

/found community