Tiflash Fail to Execute Query

[Dylan0222]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS person;
CREATE TABLE person (
    id INT NOT NULL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address_info JSON,
    age DOUBLE GENERATED ALWAYS AS ((id + 100) /2) VIRTUAL
);

ALTER TABLE person SET TIFLASH REPLICA 1;
INSERT INTO person (id, name, address_info) 
VALUES 
    (1, 'Alice', '{"street": "123 Elm St", "city": "Wonderland", "zipcode": "12345"}'),
    (2, 'Bob', '{"street": "456 Oak St", "city": "Nowhere", "zipcode": "67890"}'),
    (3, 'Charlie', '{"street": "789 Pine St", "city": "Somewhere", "zipcode": "11223"}');

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

I find that the returned result is inconsistent across two queries. Moreover, Tiflash cannot even execute this query.

3. What did you see instead (Required)

mysql> SELECT /*+ READ_FROM_STORAGE(tikv[person]) */ age FROM person limit 1;
+------+
| age  |
+------+
| 50.5 |
+------+
1 row in set (0.04 sec)

mysql> SELECT /*+ READ_FROM_STORAGE(tiflash[person]) */ age FROM person limit 1;
ERROR 1815 (HY000): Internal : Can't find a proper physical plan for this query

4. What is your TiDB version? (Required)

TiDB v8.4.0

[Dylan0222]

/label affects-8.4
/label affects-8.5

[Dylan0222]

But this query can work well @jebter:
mysql> SELECT /*+ READ_FROM_STORAGE(tikv[person]) */ age FROM person order by age;
+------+
| age |
+------+
| 50.5 |
| 51 |
| 51.5 |
+------+
3 rows in set (0.04 sec)

mysql> SELECT /*+ READ_FROM_STORAGE(tiflash[person]) */ age FROM person order by age;
+------+
| age |
+------+
| 50.5 |
| 51 |
| 51.5 |
+------+
3 rows in set (0.04 sec)

[Dylan0222]

@yibin87 Could you please help check this issue as well? It seems strange that TiFlash cannot execute it. Thank you very much.

[winoros]

mysql> set @@tidb_enforce_mpp=on;
Query OK, 0 rows affected (0.00 sec)

mysql> select age FROM person limit 1;
+------+
| age  |
+------+
| 50.5 |
+------+
1 row in set (0.00 sec)

mysql> explain select age FROM person limit 1;
+------------------------------+---------+-----------+---------------+--------------------------------+
| id                           | estRows | task      | access object | operator info                  |
+------------------------------+---------+-----------+---------------+--------------------------------+
| Limit_7                      | 1.00    | root      |               | offset:0, count:1              |
| └─Projection_14              | 1.00    | root      |               | test.person.age                |
|   └─TableReader_13           | 1.00    | root      |               | data:Limit_12                  |
|     └─Limit_12               | 1.00    | cop[tikv] |               | offset:0, count:1              |
|       └─TableFullScan_10     | 1.00    | cop[tikv] | table:person  | keep order:false, stats:pseudo |
+------------------------------+---------+-----------+---------------+--------------------------------+
5 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                  |
+---------+------+----------------------------------------------------------------------------------------------------------+
| Warning | 1105 | MPP mode may be blocked because column `test.person.age` is a virtual column which is not supported now. |
+---------+------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MPP mode may be blocked because column test.person.age is a virtual column which is not supported now.

@Dylan0222

[yibin87]

SELECT /*+ READ_FROM_STORAGE(tiflash[person]) */ age FROM person; works properly, it seems caused by the "limit" operator, we may have some inappropriate limitations here.