An batchCop query for a table containing generated columns returns an error

[bindsang]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

-- create table
create table generate_demo (
id bigint not null,
created_at datetime not null,
is_removed boolean not null,
updated_at datetime null,
__since_at datetime as (updated_at) virtual, -- generated column
primary key (id)
);
-- set tiflash replica
alter table test.generate_demo set tiflash replica 1;
-- init data
insert into generate_demo (id, created_at, is_removed, updated_at) values (1, now(), 0, null), (2, now(), 1, now())
;

This error occurs only if the __since_at column is set to virtual.
use 'alter table .... add column....' to add generated column is the same with the statement 'create table .....'

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

I expect to see the below query execute successful and return correct results.

set tidb_isolation_read_engines = 'tikv,tiflash';
select * from generate_demo order by created_at asc limit 100;

set tidb_isolation_read_engines = 'tiflash';
explain select * from generate_demo order by created_at asc limit 100;

3. What did you see instead (Required)

I received error like this:

Error Code: 1105
Not found column table_scan_1 in block. There are only columns: table_scan_0, table_scan_2

the execution plan is:

id                          estRows  task               access object        operator info                                       
--------------------------  -------  -----------------  -------------------  ----------------------------------------------------
TopN_7                      2.00     root                                    test.generate_demo.created_at, offset:0, count:100  
└─TableReader_14            2.00     root                                    data:TopN_13                                        
  └─TopN_13                 2.00     batchCop[tiflash]                       test.generate_demo.created_at, offset:0, count:100  
    └─TableFullScan_12      2.00     batchCop[tiflash]  table:generate_demo  keep order:false, stats:pseudo                      

tiflash error log:

DB Exception: Not found column table_scan_4 in block. There are only columns: table_scan_0, table_scan_1, table_scan_2, table_scan_3
       0x1c541ae	DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) [tiflash+29704622]
                	dbms/src/Common/Exception.h:46
       0x76294f9	DB::Block::getByName(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&) [tiflash+123901177]
                	dbms/src/Core/Block.cpp:205
       0x788f11e	DB::ExpressionAction::execute(DB::Block&) const [tiflash+126415134]
                	dbms/src/Interpreters/ExpressionActions.cpp:360
       0x763d4cb	DB::ExpressionBlockInputStream::readImpl() [tiflash+123983051]
                	dbms/src/DataStreams/ExpressionBlockInputStream.cpp:42
       0x7645885	DB::IProfilingBlockInputStream::read(DB::PODArray<unsigned char, 4096ul, Allocator<false>, 15ul, 16ul>*&, bool) [tiflash+124016773]
                	dbms/src/DataStreams/IProfilingBlockInputStream.cpp:75
       0x7645575	DB::IProfilingBlockInputStream::read() [tiflash+124015989]
                	dbms/src/DataStreams/IProfilingBlockInputStream.cpp:43
       0x82c03f1	DB::DataStreamExecutor::execute(DB::ResultHandler&&) [tiflash+137102321]
                	dbms/src/Flash/Executor/DataStreamExecutor.cpp:49
       0x82c6f79	DB::QueryExecutor::execute(std::__1::function<void (DB::Block const&)>) [tiflash+137129849]
                	dbms/src/Flash/Executor/QueryExecutor.cpp:27
       0x8143a13	DB::DAGDriver<true>::execute() [tiflash+135543315]
                	dbms/src/Flash/Coprocessor/DAGDriver.cpp:145
       0x8118cab	DB::BatchCoprocessorHandler::execute() [tiflash+135367851]
                	dbms/src/Flash/BatchCoprocessorHandler.cpp:86
       0x81151b6	std::__1::__function::__func<DB::FlashService::BatchCoprocessor(grpc::ServerContext*, coprocessor::BatchRequest const*, grpc::ServerWriter<coprocessor::BatchResponse>*)::$_9, std::__1::allocator<DB::FlashService::BatchCoprocessor(grpc::ServerContext*, coprocessor::BatchRequest const*, grpc::ServerWriter<coprocessor::BatchResponse>*)::$_9>, grpc::Status ()>::operator()() [tiflash+135352758]
                	/usr/local/bin/../include/c++/v1/__functional/function.h:345
       0x8113648	std::__1::__packaged_task_func<std::__1::function<grpc::Status ()>, std::__1::allocator<std::__1::function<grpc::Status ()> >, grpc::Status ()>::operator()() [tiflash+135345736]
                	/usr/local/bin/../include/c++/v1/future:1687
       0x8113805	std::__1::packaged_task<grpc::Status ()>::operator()() [tiflash+135346181]
                	/usr/local/bin/../include/c++/v1/future:1960
       0x8680754	void* std::__1::__thread_proxy<std::__1::tuple<std::__1::unique_ptr<std::__1::__thread_struct, std::__1::default_delete<std::__1::__thread_struct> >, legacy::ThreadPool::ThreadPool(unsigned long, std::__1::function<void ()>)::$_0> >(void*) [tiflash+141035348]
                	/usr/local/bin/../include/c++/v1/thread:291
  0x7f2cbaf29609	start_thread [libpthread.so.0+34313]
  0x7f2cbad6d353	clone [libc.so.6+1176403]

4. What is your TiFlash version? (Required)

TiDB
Release Version: v7.1.2
Edition:         Community
Git Commit Hash: 1b60452040258606e96b830b040aabf54625a8f3
Git Branch:      heads/refs/tags/v7.1.2
UTC Build Time:  2023-10-17 06:57:45
Enable Features: jemalloc sm4(GmSSL) avx2 avx512 unwind thinlto
Profile:         RELWITHDEBINFO

Raft Proxy
Git Commit Hash:   7aed51bfdf89131d37eabb6b379de4b3c26cc1ac
Git Commit Branch: HEAD
UTC Build Time:    2023-10-17 07:11:55
Rust Version:      rustc 1.67.0-nightly (96ddd32c4 2022-11-14)
Storage Engine:    tiflash
Prometheus Prefix: tiflash_proxy_
Profile:           release
Enable Features:   Unknown (env var does not exist when building)

[SeaRise]

minor case, v7.1.3, both enable planner and disable planner

use test;
drop table if exists t;
create table t (col int null, __col int as (col) virtual);
alter table test.t set tiflash replica 1;
insert into t (col) values (1);
set tidb_isolation_read_engines='tiflash';
select * from t order by col limit 10;

Not found column table_scan_1 in block. There are only columns: table_scan_0
+----------------------------+---------+-------------------+---------------+--------------------------------+
| id                         | estRows | task              | access object | operator info                  |
+----------------------------+---------+-------------------+---------------+--------------------------------+
| TopN_7                     | 1.00    | root              |               | test.t.col, offset:0, count:10 |
| └─TableReader_16           | 1.00    | root              |               | data:TopN_15                   |
|   └─TopN_15                | 1.00    | batchCop[tiflash] |               | test.t.col, offset:0, count:10 |
|     └─TableFullScan_14     | 1.00    | batchCop[tiflash] | table:t       | keep order:false, stats:pseudo |
+----------------------------+---------+-------------------+---------------+--------------------------------+

Also for v7.5.0, master

use test;
drop table if exists t;
create table t (col int null, __col int as (col) virtual);
alter table test.t set tiflash replica 1;
insert into t (col) values (1);
set tidb_isolation_read_engines='tiflash';
set tidb_allow_tiflash_cop=true;
 set tidb_allow_batch_cop=2;
set tidb_allow_mpp=0;
select * from t order by col limit 10;
ERROR 1105 (HY000): Not found column table_scan_1 in block. There are only columns: table_scan_0
+----------------------------+---------+-------------------+---------------+--------------------------------+
| id                         | estRows | task              | access object | operator info                  |
+----------------------------+---------+-------------------+---------------+--------------------------------+
| TopN_7                     | 1.00    | root              |               | test.t.col, offset:0, count:10 |
| └─TableReader_15           | 1.00    | root              |               | data:TopN_14                   |
|   └─TopN_14                | 1.00    | batchCop[tiflash] |               | test.t.col, offset:0, count:10 |
|     └─TableFullScan_13     | 1.00    | batchCop[tiflash] | table:t       | keep order:false, stats:pseudo |
+----------------------------+---------+-------------------+---------------+--------------------------------+

[SeaRise]

It seems to be a problem with topn

But the schema of the plan is correct.

2024-02-27 16:20:07 (UTC+08:00)TiFlash 10.2.12.124:3930[PhysicalPlan.cpp:259] ["build optimized physical plan: 
<Projection, TopN_15> | is_tidb_operator: false, schema: <TopN_15_table_scan_0, Nullable(Int32)>, <TopN_15_table_scan_1, Nullable(Int32)>
 <TopN, TopN_15> | is_tidb_operator: true, schema: <table_scan_0, Nullable(Int32)>, <table_scan_1, Nullable(Int32)>
  <TableScan, TableFullScan_14> | is_tidb_operator: true, schema: <table_scan_0, Nullable(Int32)>, <table_scan_1, Nullable(Int32)>
"] [source=BatchCoprocessorHandler] [thread_id=110]

[guo-shaoge]

I located the error is because

tiflash/dbms/src/DataStreams/MergeSortingBlockInputStream.cpp

Line 91 in 1b3e193

SortHelper::removeConstantsFromBlock(block);

remove the GeneratedColumnHolder column, and tiflash cannot find corresponding column in block.