Wrong value when query a partition table with `LIMIT`

[bb7133]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

Scripts:

drop table if exists test_partition;

CREATE TABLE `test_partition` (
  `a` varchar(100) NOT NULL,
  `b` date NOT NULL,
  `c` varchar(100) NOT NULL,
  `d` datetime DEFAULT NULL,
  `e` datetime DEFAULT NULL,
  `f` bigint(20) DEFAULT NULL,
  `g` bigint(20) DEFAULT NULL,
  `h` bigint(20) DEFAULT NULL,
  `i` bigint(20) DEFAULT NULL,
  `j` bigint(20) DEFAULT NULL,
  `k` bigint(20) DEFAULT NULL,
  `l` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`a`,`b`,`c`) /*T![clustered_index] NONCLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE ( TO_DAYS(`b`) ) (
  PARTITION `pmin` VALUES LESS THAN (737821),
  PARTITION `p20200601` VALUES LESS THAN (738289)
);

INSERT INTO test_partition (a, b, c, d, e, f, g, h, i, j, k, l) VALUES('aaa', '2021-05-05', '428ff6a1-bb37-42ac-9883-33d7a29961e6', '2021-05-06 08:13:38', '2021-05-06 13:28:08', 0, 8, 3, 0, 9, 1, 0);

select c,j,l
from test_partition 
where `c`='428ff6a1-bb37-42ac-9883-33d7a29961e6' and `a`='aaa';

select c,j,l
from test_partition 
where `c`='428ff6a1-bb37-42ac-9883-33d7a29961e6' and `a`='aaa' limit 0, 200;

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

tidb> select c,j,l from test_partition  where `c`='428ff6a1-bb37-42ac-9883-33d7a29961e6' and `a`='aaa';
+--------------------------------------+------+------+
| c                                    | j    | l    |
+--------------------------------------+------+------+
| 428ff6a1-bb37-42ac-9883-33d7a29961e6 |    9 |    0 |
+--------------------------------------+------+------+
1 row in set (0.00 sec)

tidb> select c,j,l from test_partition  where `c`='428ff6a1-bb37-42ac-9883-33d7a29961e6' and `a`='aaa' limit 0, 200;
+--------------------------------------+------+------+
| c                                    | j    | l    |
+--------------------------------------+------+------+
| 428ff6a1-bb37-42ac-9883-33d7a29961e6 |    9 |    0 |
+--------------------------------------+------+------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

tidb> select c,j,l from test_partition  where `c`='428ff6a1-bb37-42ac-9883-33d7a29961e6' and `a`='aaa';
+--------------------------------------+------+------+
| c                                    | j    | l    |
+--------------------------------------+------+------+
| 428ff6a1-bb37-42ac-9883-33d7a29961e6 |    9 |    0 |
+--------------------------------------+------+------+
1 row in set (0.00 sec)

tidb> select c,j,l from test_partition  where `c`='428ff6a1-bb37-42ac-9883-33d7a29961e6' and `a`='aaa' limit 0, 200;
+-----+---------------------+------+
| c   | j                   | l    |
+-----+---------------------+------+
| aaa | 3558184994028925492 |    9 |
+-----+---------------------+------+
1 row in set (0.00 sec)

4. What is your TiDB version? (Required)

TiDB v5.0.1

Other versions

It is not reproduced in v4.0, but in current master(aecff1c), an error is reported:

tidb> select c,j,l from test_partition  where `c`='428ff6a1-bb37-42ac-9883-33d7a29961e6' and `a`='aaa' limit 0, 200;
ERROR 1105 (HY000): Internal error: UnionExec chunk column count mismatch, req: 3, result: 4

So I'll assume it is not a known bug.

Update: when the table is specified as `clustered, the error doesn't reproduce.

[bb7133]

PTAL @mjonss

[zhangjinpeng87]

@bb7133 Is it easy to fix?

[bb7133]

Looks that 1 projection is missing:

v5.0:

tidb> select c,j,l
    -> from test_partition
    -> where `c`='428ff6a1-bb37-42ac-9883-33d7a29961e6' and `a`='aaa' limit 0, 200;
+-----+---------------------+------+ 
| c   | j                   | l    |
+-----+---------------------+------+
| aaa | 3558184994028925492 |    9 |
+-----+---------------------+------+
1 row in set (0.00 sec)

tidb>
tidb> explain select c,j,l from test_partition  where `c`='428ff6a1-bb37-42ac-9883-33d7a29961e6' and `a`='aaa' limit 0, 200;
+--------------------------------------+---------+-----------+-------------------------------------------------------------------+-------------------------------------------------------------------+ 
| id                                   | estRows | task      | access object                                                     | operator info                                                     |
+--------------------------------------+---------+-----------+-------------------------------------------------------------------+-------------------------------------------------------------------+
| Limit_18                             | 0.02    | root      |                                                                   | offset:0, count:200                                               |
| └─PartitionUnion_19                  | 0.02    | root      |                                                                   |                                                                   |
|   ├─IndexLookUp_31                   | 0.01    | root      |                                                                   | limit embedded(offset:0, count:200)                               |
|   │ ├─Limit_30(Build)                | 0.01    | cop[tikv] |                                                                   | offset:0, count:200                                               |
|   │ │ └─Selection_29                 | 0.01    | cop[tikv] |                                                                   | eq(test.test_partition.c, "428ff6a1-bb37-42ac-9883-33d7a29961e6") |
|   │ │   └─IndexRangeScan_27          | 10.00   | cop[tikv] | table:test_partition, partition:pmin, index:PRIMARY(a, b, c)      | range:["aaa","aaa"], keep order:false, stats:pseudo               |
|   │ └─TableRowIDScan_28(Probe)       | 0.01    | cop[tikv] | table:test_partition, partition:pmin                              | keep order:false, stats:pseudo                                    |
|   └─IndexLookUp_50                   | 0.01    | root      |                                                                   | limit embedded(offset:0, count:200)                               | 
|     ├─Limit_49(Build)                | 0.01    | cop[tikv] |                                                                   | offset:0, count:200                                               |
|     │ └─Selection_48                 | 0.01    | cop[tikv] |                                                                   | eq(test.test_partition.c, "428ff6a1-bb37-42ac-9883-33d7a29961e6") |
|     │   └─IndexRangeScan_46          | 10.00   | cop[tikv] | table:test_partition, partition:p20200601, index:PRIMARY(a, b, c) | range:["aaa","aaa"], keep order:false, stats:pseudo               |
|     └─TableRowIDScan_47(Probe)       | 0.01    | cop[tikv] | table:test_partition, partition:p20200601                         | keep order:false, stats:pseudo                                    |
+--------------------------------------+---------+-----------+-------------------------------------------------------------------+-------------------------------------------------------------------+
12 rows in set (0.00 sec)

v4.0:

tidb> select c,j,l 
    -> from test_partition
    -> where `c`='428ff6a1-bb37-42ac-9883-33d7a29961e6' and `a`='aaa' limit 0, 200;
+--------------------------------------+------+------+ 
| c                                    | j    | l    |
+--------------------------------------+------+------+
| 428ff6a1-bb37-42ac-9883-33d7a29961e6 |    9 |    0 |
+--------------------------------------+------+------+
1 row in set (0.01 sec)

tidb> explain select c,j,l from test_partition  where `c`='428ff6a1-bb37-42ac-9883-33d7a29961e6' and `a`='aaa' limit 0, 200;
+----------------------------------------+---------+-----------+-------------------------------------------------------------------+---------------------------------------------------------------------+ 
| id                                     | estRows | task      | access object                                                     | operator info                                                       |
+----------------------------------------+---------+-----------+-------------------------------------------------------------------+---------------------------------------------------------------------+
| Projection_14                          | 0.02    | root      |                                                                   | test.test_partition.c, test.test_partition.j, test.test_partition.l |
| └─Limit_17                             | 0.02    | root      |                                                                   | offset:0, count:200                                                 |
|   └─PartitionUnion_18                  | 0.02    | root      |                                                                   |                                                                     |
|     ├─IndexLookUp_30                   | 0.01    | root      |                                                                   | limit embedded(offset:0, count:200)                                 |
|     │ ├─Limit_29(Build)                | 0.01    | cop[tikv] |                                                                   | offset:0, count:200                                                 |
|     │ │ └─Selection_28                 | 0.01    | cop[tikv] |                                                                   | eq(test.test_partition.c, "428ff6a1-bb37-42ac-9883-33d7a29961e6")   |
|     │ │   └─IndexRangeScan_26          | 10.00   | cop[tikv] | table:test_partition, partition:pmin, index:PRIMARY(a, b, c)      | range:["aaa","aaa"], keep order:false, stats:pseudo                 |
|     │ └─TableRowIDScan_27(Probe)       | 0.01    | cop[tikv] | table:test_partition, partition:pmin                              | keep order:false, stats:pseudo                                      |
|     └─IndexLookUp_49                   | 0.01    | root      |                                                                   | limit embedded(offset:0, count:200)                                 |
|       ├─Limit_48(Build)                | 0.01    | cop[tikv] |                                                                   | offset:0, count:200                                                 | 
|       │ └─Selection_47                 | 0.01    | cop[tikv] |                                                                   | eq(test.test_partition.c, "428ff6a1-bb37-42ac-9883-33d7a29961e6")   |
|       │   └─IndexRangeScan_45          | 10.00   | cop[tikv] | table:test_partition, partition:p20200601, index:PRIMARY(a, b, c) | range:["aaa","aaa"], keep order:false, stats:pseudo                 |
|       └─TableRowIDScan_46(Probe)       | 0.01    | cop[tikv] | table:test_partition, partition:p20200601                         | keep order:false, stats:pseudo                                      |
+----------------------------------------+---------+-----------+-------------------------------------------------------------------+---------------------------------------------------------------------+
13 rows in set (0.01 sec)

[bb7133]

@bb7133 Is it easy to fix?

@zhangjinpeng1987 , @mjonss is working on it, not so difficult.

[mjonss]

I can reproduce the ERROR 1105 (HY000): Internal error: UnionExec chunk column count mismatch, req: 3, result: 4 error. The plan was changed in #20288 and I have not yet managed to understand the cause, some pointers would be appreciated.
Also is the issue that the projection is missing when introducing the Limit in pull/20288? Or that the Limit is not doing the projection/column filtering internally?
(I have some issues reproducing this on my Ubuntu Linux 20.10, but can reproduce it on my macbook air M1, but then I cannot build the pull/20288 commit :( )

[qw4990]

After going through this issue and some related code, I found it's a little complex...
How about disabling inline-projection on Limit for partition tables as a quick fix of this issue? @mjonss @bb7133

[mjonss]

After going through this issue and some related code, I found it's a little complex...
How about disabling inline-projection on Limit for partition tables as a quick fix of this issue? @mjonss @bb7133

@qw4990 I'm OK with reverting #20288 as a quick fix. Or do you mean something else by 'disabling inline-projection on Limit for partition tables'? I.e. should I add a condition to not do it for partitioned tables only? I would guess that it would be possible to create a test case using UNION instead of partitioning and hit this issue as well.

[qw4990]

After going through this issue and some related code, I found it's a little complex...
How about disabling inline-projection on Limit for partition tables as a quick fix of this issue? @mjonss @bb7133

@qw4990 I'm OK with reverting #20288 as a quick fix. Or do you mean something else by 'disabling inline-projection on Limit for partition tables'? I.e. should I add a condition to not do it for partitioned tables only? I would guess that it would be possible to create a test case using UNION instead of partitioning and hit this issue as well.

OK, got it. Then it seems disabling it is not a good idea, but reverting #20288 seems also complex since there are quite a few changes after it. It's a bit late for me now, I'll go on to investigate it more detailedly tomorrow.

[qw4990]

It seems a complex issue about the optimizer. :(
As shown above, I printed the number and names of returned columns of each node in the plan in some places of the optimizer.

After column_pruning (which is one step of logical optimization) the top Projection is inlined into the 2 Limit since their children DataSource returns 4 columns but themselves return 3, so some work about projection must be done by them.
For now, this plan is correct, Union needs 3 columns, and its 2 children Limit return 3 columns.

Buf after physical optimization, one of these 2 (Limit + DataSource) structures is converted to IndexLookUp and it returns 4 columns instead of 3. I guess this is where the problem happens, and the root cause is that inline-projection is not supported by the rule which converts (Limit + DataSource) to IndexLookUp.

This issue is more related to the optimizer instead of runtime or partition tables, so how about letting me go on to investigate it and you can have more time to solve other partition table issues. @mjonss