Wrong value when query a partition table with `LIMIT` #24636

bb7133 · 2021-05-13T12:24:00Z

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.

The text was updated successfully, but these errors were encountered:

bb7133 · 2021-05-13T12:42:29Z

PTAL @mjonss

zhangjinpeng87 · 2021-05-14T09:35:06Z

@bb7133 Is it easy to fix?

bb7133 · 2021-05-14T09:35:26Z

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 · 2021-05-14T09:36:01Z

@bb7133 Is it easy to fix?

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

mjonss · 2021-05-17T09:18:13Z

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 · 2021-05-17T11:53:00Z

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 · 2021-05-17T12:00:47Z

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 · 2021-05-17T12:53:47Z

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 · 2021-05-18T08:22:01Z

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

qw4990 · 2021-05-26T06:51:01Z

I'm stuck in ONCALL this week and I'll go on to finish this next week.

qw4990 · 2021-06-02T08:49:21Z

A minimal reproducible case:

drop table if exists test_partition;

CREATE TABLE test_partition (
  a int,
  b date,
  c int,
  PRIMARY KEY (`a`,`b`)
)
PARTITION BY RANGE ( TO_DAYS(`b`) ) (
  PARTITION `p0` VALUES LESS THAN (737821),
  PARTITION `p1` VALUES LESS THAN (738289)
);

INSERT INTO test_partition (a, b, c) VALUES(0, '2021-05-05', 0);

select c from test_partition where a=0 limit 1;

ti-srebot · 2021-06-04T05:54:31Z

Please edit this comment or add a new comment to complete the following information

Not a bug

Remove the 'type/bug' label
Add notes to indicate why it is not a bug

Duplicate bug

Add the 'type/duplicate' label
Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions

ref #24636, close #35952, close #43528

…43883) ref #24636, close #35952, close #43528

…43884) ref #24636, close #35952, close #43528

bb7133 added the type/bug The issue is confirmed as a bug. label May 13, 2021

bb7133 assigned mjonss May 13, 2021

aytrack added severity/critical sig/execution SIG execution labels May 14, 2021

ichn-hu mentioned this issue May 14, 2021

Welcome to contribute #20804

Closed

XuHuaiyu added sig/planner SIG: Planner and removed sig/execution SIG execution labels May 24, 2021

qw4990 self-assigned this Jun 1, 2021

qw4990 mentioned this issue Jun 2, 2021

planner: fix a panic caused by sinking a Limit with inlined Proj into IndexLookUp when accessing a partition table #25063

Merged

ti-chi-bot closed this as completed in #25063 Jun 4, 2021

ti-srebot added the need-more-info label Jun 4, 2021

ti-srebot mentioned this issue Jun 4, 2021

planner: fix a panic caused by sinking a Limit with inlined Proj into IndexLookUp when accessing a partition table (#25063) #25139

Merged

ti-srebot mentioned this issue Jun 18, 2021

planner: fix a panic caused by sinking a Limit with inlined Proj into IndexLookUp when accessing a partition table (#25063) #25541

Closed

winoros mentioned this issue Apr 4, 2023

planner: refine the codes of embeded limit of double read #42818

Merged

12 tasks

ti-chi-bot bot pushed a commit that referenced this issue May 16, 2023

planner: refine the codes of embeded limit of double read (#42818)

4422222

ref #24636, close #35952, close #43528

This was referenced May 16, 2023

planner: refine the codes of embeded limit of double read (#42818) #43883

Merged

planner: refine the codes of embeded limit of double read (#42818) #43884

Merged

ti-chi-bot bot pushed a commit that referenced this issue May 17, 2023

planner: refine the codes of embeded limit of double read (#42818) (#…

e20734d

…43883) ref #24636, close #35952, close #43528

ti-chi-bot bot pushed a commit that referenced this issue May 17, 2023

planner: refine the codes of embeded limit of double read (#42818) (#…

dd79f8e

…43884) ref #24636, close #35952, close #43528

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Wrong value when query a partition table with `LIMIT` #24636

Wrong value when query a partition table with `LIMIT` #24636

bb7133 commented May 13, 2021 •

edited

Loading

bb7133 commented May 13, 2021

zhangjinpeng87 commented May 14, 2021

bb7133 commented May 14, 2021

bb7133 commented May 14, 2021

mjonss commented May 17, 2021

qw4990 commented May 17, 2021

mjonss commented May 17, 2021

qw4990 commented May 17, 2021 •

edited

Loading

qw4990 commented May 18, 2021 •

edited

Loading

qw4990 commented May 26, 2021

qw4990 commented Jun 2, 2021

ti-srebot commented Jun 4, 2021

Wrong value when query a partition table with LIMIT #24636

Wrong value when query a partition table with LIMIT #24636

Comments

bb7133 commented May 13, 2021 • edited Loading

Bug Report

1. Minimal reproduce step (Required)

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

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

Other versions

bb7133 commented May 13, 2021

zhangjinpeng87 commented May 14, 2021

bb7133 commented May 14, 2021

bb7133 commented May 14, 2021

mjonss commented May 17, 2021

qw4990 commented May 17, 2021

mjonss commented May 17, 2021

qw4990 commented May 17, 2021 • edited Loading

qw4990 commented May 18, 2021 • edited Loading

qw4990 commented May 26, 2021

qw4990 commented Jun 2, 2021

ti-srebot commented Jun 4, 2021

Please edit this comment or add a new comment to complete the following information

Not a bug

Duplicate bug

Bug

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions

Wrong value when query a partition table with `LIMIT` #24636

Wrong value when query a partition table with `LIMIT` #24636

bb7133 commented May 13, 2021 •

edited

Loading

qw4990 commented May 17, 2021 •

edited

Loading

qw4990 commented May 18, 2021 •

edited

Loading