Error in batchCop Query on Table with Generated Columns

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: batchCop 方式查询包含生成列的表出错

| username: bindsang

[TiDB Usage Environment] Production / Testing
[TiDB Version] 7.1.2
[Reproduction Path]

  1. Create a table with a virtual generated column
  2. Set the tiflash replica count to 1
  3. Insert some data
  4. Set the execution engine. set tidb_isolation_read_engines = ‘tikv,tiflash’;
  5. Execute a select query with sorting, including the previously created generated column in the query result (using * or explicitly specifying the generated column name), with an order by clause and a limit clause, without an offset or with an offset value of 0

[Encountered Problem: Phenomenon and Impact]
After executing according to the above reproduction path, the server returns an error message similar to the following:

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

[Resource Configuration] Enter TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachments: Screenshots/Logs/Monitoring]

Attached are the table creation statement and the executed SQL
– Create table
CREATE TABLE generate_demo (
id BIGINT(20) NOT NULL,
created_at DATETIME NOT NULL,
is_removed TINYINT(1) NOT NULL,
updated_at DATETIME DEFAULT NULL,
__since_at DATETIME GENERATED ALWAYS AS (updated_at) VIRTUAL,
PRIMARY KEY (id)
);
– Set tiflash replica
ALTER TABLE test.generate_demo SET tiflash replica 1;

– Initialize data
INSERT INTO generate_demo
(id, created_at, is_removed, updated_at)
VALUES (1, NOW(), 0, NULL),
(2, NOW(), 1, NOW())
;

– Query

SELECT *
FROM generate_demo
ORDER BY created_at ASC
LIMIT 100;

[Remarks]

In my local development environment, there is also a TiDB 6.1 version, and the same execution method can return results normally.
Additionally, a few phenomena in version 7.1 that can return results normally:

  1. The select statement contains only order by or only limit, either one can return results normally.
  2. Both order by and offset clauses exist simultaneously, and the offset is set to a value greater than 0, which can also return results normally.
  3. Further experiments confirmed that only virtual generated columns have issues, while stored generated columns do not. However, when adding a new column to a table, only virtual generated columns are supported.

Additional information after further verification:

  1. After checking the execution plan, it was found that the batchCop method was actually used when the error occurred, not the mpp method, which was not noticed before. Enabling tidb_enforce_mpp was previously thought to default to using the mpp method, but it turns out that it is not necessarily the case, and the execution plan must be checked to confirm. The output information of explain + the previously erroneous query statement is as follows:

    Executing explain analyze + the previously erroneous query statement reports the same error as directly executing the previously erroneous query statement.
  2. When tidb_isolation_read_engines is set to ‘tiflash’, the execution method changes to the cop method.

    In this mode, the SQL can be executed normally.
| username: redgame | Original post link

Is this the error related to insufficient space or insufficient permissions?

| username: tidb菜鸟一只 | Original post link

Looks like a bug.

| username: bindsang | Original post link

It shouldn’t be.
It’s just a query statement, and even with only one or two pieces of data in the table, it still happens. Both memory and disk are sufficient. Moreover, it can be reproduced in any environment.
I am using the root account, and the permissions are already the highest.

| username: changpeng75 | Original post link

Will there be an error if the only column name after select is updated_at?
Will there be an error if the value of update is not null?

| username: bindsang | Original post link

The situation you mentioned will not occur. Point 5 in the description under “Reproduction Path” already mentions that as long as the result set contains generated column fields, an error will occur; if it does not contain them, no error will occur.

| username: Soysauce520 | Original post link

The virtual column caused TiFlash to not store data correctly. Forcing it to use TiFlash allows you to see it.

| username: bindsang | Original post link

There is no problem querying directly without limit and order by and without adding hints. Adding order by and limit will cause an error. Please refer to the previous reproduction steps and the provided SQL example.

SELECT /*+ read_from_storage(tiflash[generate_demo]) */ *
FROM generate_demo
ORDER BY created_at ASC
LIMIT 100;

| username: changpeng75 | Original post link

It is possible that it is indeed a bug. There is no problem with stored generated columns because the columns are actually generated and the data is stored. However, virtual generated columns only “generate” the column names and data at the time of the query, similar to the relationship between views and materialized views.

| username: changpeng75 | Original post link

If there are no issues with querying on TiKV, it is possible that the parsing generated on TiFlash did not correctly generate the virtual column names.

| username: bindsang | Original post link

There are no issues with queries when only using TiKV.

I feel that the virtual generated column is not actually generated in TiFlash. The query results need to return this column information, but it cannot be found in TiFlash. The virtual generated column field is not supplemented, leading to a missing column when mapping the result set fields.

From the error message, I can roughly guess what’s going on.
The table has 4 regular fields and one generated column field. In TiFlash, there are only table_scan_0~3, just missing table_scan_4.

| username: changpeng75 | Original post link

Moreover, there is no issue in version 6.1, it feels more like a bug.
MySQL does not have this problem, it is possible that TiDB has an issue in the implementation of the MySQL engine.

| username: Soysauce520 | Original post link

Yes, I might not have expressed it clearly. Forcing the use of TiFlash is to verify the situation you mentioned, indicating that there is no problem with the data in TiKV, but there is an issue with the data in TiFlash. It seems that TiFlash has a problem supporting virtual columns. To resolve this, an issue needs to be raised.

| username: bindsang | Original post link

I have already posted it on GitHub, welcome to take a look

| username: 有猫万事足 | Original post link

The description is quite clear, it seems highly likely to be a bug related to generated columns.