Invalidate column id 17 for table with unique key tidb_shard

[JaySon-Huang]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

https://docs.pingcap.com/tidb/dev/tidb-functions#tidb_shard

CREATE TABLE `lineorder` (
  `LO_ORDERKEY` int(11) NOT NULL,
  `LO_LINENUMBER` int(11) NOT NULL,
  `LO_CUSTKEY` int(11) DEFAULT NULL,
  `LO_PARTKEY` int(11) DEFAULT NULL,
  `LO_SUPPKEY` int(11) DEFAULT NULL,
  `LO_ORDERDATE` int(11) DEFAULT NULL,
  `LO_ORDPRIORITY` char(15) DEFAULT NULL,
  `LO_SHIPPRIORITY` char(1) DEFAULT NULL,
  `LO_QUANTITY` int(11) DEFAULT NULL,
  `LO_EXTENDEDPRICE` decimal(10,0) DEFAULT NULL,
  `LO_DISCOUNT` int(11) DEFAULT NULL,
  `LO_REVENUE` decimal(10,0) DEFAULT NULL,
  `LO_SUPPLYCOST` decimal(10,0) DEFAULT NULL,
  `LO_TAX` int(11) DEFAULT NULL,
  `LO_COMMITDATE` int(11) DEFAULT NULL,
  `LO_SHIPMODE` char(10) DEFAULT NULL,
  KEY `idx_lo_custkey` (`LO_CUSTKEY`),
  UNIQUE KEY `UK_ORDR_LINE` ((tidb_shard(`LO_ORDERKEY`)),`LO_ORDERKEY`,`LO_LINENUMBER`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T! SHARD_ROW_ID_BITS=6 */;

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

-- success with correct result
hat> SELECT MAX(LO_ORDERKEY) FROM HAT.LINEORDER;

3. What did you see instead (Required)

-- failed to run
hat> SELECT MAX(LO_ORDERKEY) FROM HAT.LINEORDER;
(1105, u'DB::String TiDB::TableInfo::getColumnName(const DB::ColumnID) const: Invalidate column id 17 for table lineorder')
hat> explain SELECT MAX(LO_ORDERKEY) FROM HAT.LINEORDER;
+--------------------------+--------------+-------------------+-----------------+---------------------------------------------------+
| id                       | estRows      | task              | access object   | operator info                                     |
+--------------------------+--------------+-------------------+-----------------+---------------------------------------------------+
| StreamAgg_9              | 1.00         | root              |                 | funcs:max(hat.lineorder.lo_orderkey)->Column#19   |
| └─TopN_10                | 1.00         | root              |                 | hat.lineorder.lo_orderkey:desc, offset:0, count:1 |
|   └─TableReader_19       | 1.00         | root              |                 | data:TopN_18                                      |
|     └─TopN_18            | 1.00         | batchCop[tiflash] |                 | hat.lineorder.lo_orderkey:desc, offset:0, count:1 |
|       └─TableFullScan_16 | 626767254.00 | batchCop[tiflash] | table:lineorder | keep order:false                                  |
+--------------------------+--------------+-------------------+-----------------+---------------------------------------------------+

[2022/07/31 16:47:48.508 +08:00] [INFO] [DAGStorageInterpreter.cpp:917] ["DAGStorageInterpreter:Table 10367 schema OK, no syncing required. Schema version [storage, global, query]: [18602, 27195, 27195]"] [thread_id=7970]
[2022/07/31 16:47:48.509 +08:00] [ERROR] [DAGDriver.cpp:197] ["DAGDriver:DB Exception: DB::String TiDB::TableInfo::getColumnName(const DB::ColumnID) const: Invalidate column id 17 for table lineorder

       0x1d272d3    StackTrace::StackTrace() [tiflash+30569171]
                    dbms/src/Common/StackTrace.cpp:23
       0x1d248d6    DB::Exception::Exception(std::__1::basic_string<char, std::__1::char_traits<char>, std::__1::allocator<char> > const&, int) [tiflash+30558422]
                    dbms/src/Common/Exception.h:41
       0x79bf911    TiDB::TableInfo::getColumnName(long) const [tiflash+127662353]
                    dbms/src/Storages/Transaction/TiDB.cpp:1032
       0x7b5a130    DB::DAGStorageInterpreter::getColumnsForTableScan(long) [tiflash+129343792]
                    dbms/src/Flash/Coprocessor/DAGStorageInterpreter.cpp:972
       0x7b546ee    DB::DAGStorageInterpreter::prepare() [tiflash+129320686]
                    dbms/src/Flash/Coprocessor/DAGStorageInterpreter.cpp:342
       0x7b543a2    DB::DAGStorageInterpreter::execute(DB::DAGPipeline&) [tiflash+129319842]
                    dbms/src/Flash/Coprocessor/DAGStorageInterpreter.cpp:243
       0x7b166fb    DB::DAGQueryBlockInterpreter::handleTableScan(DB::TiDBTableScan const&, DB::DAGPipeline&) [tiflash+129066747]
                    dbms/src/Flash/Coprocessor/DAGQueryBlockInterpreter.cpp:182
       0x7b1e322    DB::DAGQueryBlockInterpreter::executeImpl(DB::DAGPipeline&) [tiflash+129098530]
                    dbms/src/Flash/Coprocessor/DAGQueryBlockInterpreter.cpp:796
       0x7b20a10    DB::DAGQueryBlockInterpreter::execute() [tiflash+129108496]
                    dbms/src/Flash/Coprocessor/DAGQueryBlockInterpreter.cpp:944
       0x7b1330d    DB::InterpreterDAG::executeQueryBlock(DB::DAGQueryBlock&) [tiflash+129053453]
                    dbms/src/Flash/Coprocessor/InterpreterDAG.cpp:73
       0x7b1348e    DB::InterpreterDAG::execute() [tiflash+129053838]
                    dbms/src/Flash/Coprocessor/InterpreterDAG.cpp:82
       0x75e94ad    DB::(anonymous namespace)::executeQueryImpl(DB::IQuerySource&, DB::Context&, bool, DB::QueryProcessingStage::Enum) [tiflash+123638957]
                    dbms/src/Interpreters/executeQuery.cpp:230
       0x75eaac3    DB::executeQuery(DB::DAGQuerySource&, DB::Context&, bool, DB::QueryProcessingStage::Enum) [tiflash+123644611]
                    dbms/src/Interpreters/executeQuery.cpp:428
       0x7af700d    DB::DAGDriver<true>::execute() [tiflash+128937997]
                    dbms/src/Flash/Coprocessor/DAGDriver.cpp:94
       0x7ad6522    DB::BatchCoprocessorHandler::execute() [tiflash+128804130]
                    dbms/src/Flash/BatchCoprocessorHandler.cpp:78
       0x7ad2d3f    std::__1::__function::__func<DB::FlashService::BatchCoprocessor(grpc_impl::ServerContext*, coprocessor::BatchRequest const*, grpc_impl::ServerWriter<coprocessor::BatchResponse>*)::$_23, std::__1::allocator<DB::FlashService::BatchCoprocessor(grpc_impl::ServerContext*, coprocessor::BatchRequest const*, grpc_impl::ServerWriter<coprocessor::BatchResponse>*)::$_23>, grpc::Status ()>::operator()() [tiflash+128789823]
                    /usr/local/bin/../include/c++/v1/__functional/function.h:345
       0x7ad5968    std::__1::__packaged_task_func<std::__1::function<grpc::Status ()>, std::__1::allocator<std::__1::function<grpc::Status ()> >, grpc::Status ()>::operator()() [tiflash+128801128]
                    /usr/local/bin/../include/c++/v1/future:1687
       0x7ad5a65    std::__1::packaged_task<grpc::Status ()>::operator()() [tiflash+128801381]
                    /usr/local/bin/../include/c++/v1/future:1960
       0x7c75e59    ThreadPool::worker() [tiflash+130506329]
                    libs/libcommon/src/ThreadPool.cpp:129
       0x7c76183    void* std::__1::__thread_proxy<std::__1::tuple<std::__1::unique_ptr<std::__1::__thread_struct, std::__1::default_delete<std::__1::__thread_struct> >, ThreadPool::ThreadPool(unsigned long, std::__1::function<void ()>)::$_1> >(void*) [tiflash+130507139]
                    /usr/local/bin/../include/c++/v1/thread:291
  0x7fbac73bcea5    start_thread [libpthread.so.0+32421]
  0x7fbac6ecf9fd    __clone [libc.so.6+1042941]"] [thread_id=7970]

lineorder.json.zip
The schema of table lineorder

  {
   "id": 17,
   "name": {
    "O": "_V$_UK_ORDR_LINE_0",
    "L": "_v$_uk_ordr_line_0"
   },
   "offset": 16,
   "origin_default": null,
   "origin_default_bit": null,
   "default": null,
   "default_bit": null,
   "default_is_expr": false,
   "generated_expr_string": "tidb_shard(`LO_ORDERKEY`)",
   "generated_stored": false,
   "dependences": {
    "lo_orderkey": {}
   },

4. What is your TiFlash version? (Required)

v6.1.0

[JaySon-Huang]

If we continue to run some queries on TiFlash, it ends up with lots of coprocessor request and OOT

-- example of the query plan that raise error
hat> explain SELECT C_NATION, S_NATION, D_YEAR, SUM(LO_REVENUE) AS REVENUE   FROM HAT.CUSTOMER, HAT.LINEORDER, HAT.SUPPLIER, HAT.DATE   WHERE LO_CUSTKEY = C_CUSTKEY   AND LO_SUPPKEY = S_SUPPKEY   AND LO_ORDERDATE = D_DATEKEY   AND C_REGION = 'ASIA'
                            AND S_REGION = 'ASIA'   AND D_YEAR >= 1992 AND D_YEAR <= 1997   GROUP BY C_NATION, S_NATION, D_YEAR   ORDER BY D_YEAR ASC, REVENUE DESC;
+---------------------------------------+--------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                    | estRows      | task         | access object   | operator info                                                                                                                                                                                                                                                                                        |
+---------------------------------------+--------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_18                         | 6.00         | root         |                 | hat.customer.c_nation, hat.supplier.s_nation, hat.date.d_year, Column#53                                                                                                                                                                                                                             |
| └─Sort_19                             | 6.00         | root         |                 | hat.date.d_year, Column#53:desc                                                                                                                                                                                                                                                                      |
|   └─HashAgg_23                        | 6.00         | root         |                 | group by:hat.customer.c_nation, hat.date.d_year, hat.supplier.s_nation, funcs:sum(hat.lineorder.lo_revenue)->Column#53, funcs:firstrow(hat.customer.c_nation)->hat.customer.c_nation, funcs:firstrow(hat.supplier.s_nation)->hat.supplier.s_nation, funcs:firstrow(hat.date.d_year)->hat.date.d_year |
|     └─Projection_27                   | 22060362.34  | root         |                 | hat.customer.c_nation, hat.lineorder.lo_revenue, hat.supplier.s_nation, hat.date.d_year                                                                                                                                                                                                              |
|       └─HashJoin_40                   | 22060362.34  | root         |                 | inner join, equal:[eq(hat.lineorder.lo_suppkey, hat.supplier.s_suppkey)]                                                                                                                                                                                                                             |
|         ├─TableReader_129(Build)      | 41854.12     | root         |                 | data:Selection_128                                                                                                                                                                                                                                                                                   |
|         │ └─Selection_128             | 41854.12     | cop[tiflash] |                 | eq(hat.supplier.s_region, "ASIA")                                                                                                                                                                                                                                                                    |
|         │   └─TableFullScan_127       | 200000.00    | cop[tiflash] | table:supplier  | keep order:false                                                                                                                                                                                                                                                                                     |
|         └─HashJoin_62(Probe)          | 116924525.84 | root         |                 | inner join, equal:[eq(hat.lineorder.lo_custkey, hat.customer.c_custkey)]                                                                                                                                                                                                                             |
|           ├─TableReader_123(Build)    | 601853.37    | root         |                 | data:Selection_122                                                                                                                                                                                                                                                                                   |
|           │ └─Selection_122           | 601853.37    | cop[tiflash] |                 | eq(hat.customer.c_region, "ASIA")                                                                                                                                                                                                                                                                    |
|           │   └─TableFullScan_121     | 3000000.00   | cop[tiflash] | table:customer  | keep order:false                                                                                                                                                                                                                                                                                     |
|           └─HashJoin_98(Probe)        | 657285679.69 | root         |                 | inner join, equal:[eq(hat.date.d_datekey, hat.lineorder.lo_orderdate)]                                                                                                                                                                                                                               |
|             ├─TableReader_117(Build)  | 2192.00      | root         |                 | data:Selection_116                                                                                                                                                                                                                                                                                   |
|             │ └─Selection_116         | 2192.00      | cop[tiflash] |                 | ge(hat.date.d_year, 1992), le(hat.date.d_year, 1997)                                                                                                                                                                                                                                                 |
|             │   └─TableFullScan_115   | 2557.00      | cop[tiflash] | table:date      | keep order:false                                                                                                                                                                                                                                                                                     |
|             └─Projection_106(Probe)   | 675793339.00 | root         |                 | hat.lineorder.lo_custkey, hat.lineorder.lo_suppkey, hat.lineorder.lo_orderdate, hat.lineorder.lo_revenue, tidb_shard(hat.lineorder.lo_orderkey)                                                                                                                                                      |
|               └─TableReader_105       | 675793339.00 | root         |                 | data:Selection_104                                                                                                                                                                                                                                                                                   |
|                 └─Selection_104       | 675793339.00 | cop[tiflash] |                 | not(isnull(hat.lineorder.lo_custkey)), not(isnull(hat.lineorder.lo_orderdate)), not(isnull(hat.lineorder.lo_suppkey))                                                                                                                                                                                |
|                   └─TableFullScan_103 | 675793339.00 | cop[tiflash] | table:lineorder | keep order:false                                                                                                                                                                                                                                                                                     |
+---------------------------------------+--------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[windtalker]

The bug need to be triggered if the unique index is created after the table is created:

mysql> create table test(id int, value int);
Query OK, 0 rows affected (0.07 sec)

mysql> alter table test set tiflash replica 1;
Query OK, 0 rows affected (0.08 sec)

mysql> create unique index uk on test((tidb_shard(id)), id);
Query OK, 0 rows affected (2.77 sec)

mysql> explain select max(value) from test;
+----------------------------------+----------+-------------------+---------------+-------------------------------------------+
| id                               | estRows  | task              | access object | operator info                             |
+----------------------------------+----------+-------------------+---------------+-------------------------------------------+
| StreamAgg_10                     | 1.00     | root              |               | funcs:max(test.test.value)->Column#5      |
| └─TopN_11                        | 1.00     | root              |               | test.test.value:desc, offset:0, count:1   |
|   └─Projection_22                | 10000.00 | root              |               | test.test.value, tidb_shard(test.test.id) |
|     └─TableReader_21             | 1.00     | root              |               | data:TopN_20                              |
|       └─TopN_20                  | 1.00     | batchCop[tiflash] |               | test.test.value:desc, offset:0, count:1   |
|         └─Selection_19           | 9990.00  | batchCop[tiflash] |               | not(isnull(test.test.value))              |
|           └─TableFullScan_18     | 10000.00 | batchCop[tiflash] | table:test    | keep order:false, stats:pseudo            |
+----------------------------------+----------+-------------------+---------------+-------------------------------------------+
7 rows in set (0.00 sec)

mysql>  select max(value) from test;
ERROR 1105 (HY000): DB::String TiDB::TableInfo::getColumnName(const DB::ColumnID) const: Invalidate column id 3 for table test

If the unique index is created in the create table statement, TiFlash will have this virtual column, and the above sql will be executed without error in TiFlash, however, the value in the virtual column are all nulls, which I think is not right, so instead of throw error explicity, the sql may return wrong error in this case.