Invalid query result on a cached table

[sayJason]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t1 (c1 int, c2 int, UNIQUE i1 (c1, c2));
INSERT INTO t1 VALUES (7, null),(5,1);
SELECT c1 FROM t1 WHERE ('m' = ALL (SELECT /*+ IGNORE_INDEX(t1, i1) */ c2 FROM t1)) IS NOT UNKNOWN; -- {}
SELECT c1 FROM t1 WHERE ('m' = ALL (SELECT /*+ USE_INDEX(t1, i1) */ c2 FROM t1)) IS NOT UNKNOWN; -- {5,7}

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

The first query should also return {5,7}.

3. What did you see instead (Required)

The first query returns an empty result.

4. What is your TiDB version? (Required)

Release Version: v8.0.0
Edition: Community
Git Commit Hash: 8ba1fa4
Git Branch: HEAD
UTC Build Time: 2024-03-28 14:22:15
GoVersion: go1.21.4
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[winoros]

The input to the StreamAgg is the same

And the plan is also the same

mysql> explain select 'm' = ALL (SELECT /*+ USE_INDEX(t1, i1) */ c2 FROM t1) from t1;
+----------------------------------+---------+-----------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| id                               | estRows | task      | access object              | operator info                                                                                                                                |
+----------------------------------+---------+-----------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_9                     | 2.00    | root      |                            | or(and(and(le(Column#11, 1), eq(0, Column#10)), if(ne(Column#12, 0), <nil>, 1)), or(eq(Column#13, 0), 0))->Column#14                         |
| └─HashJoin_10                    | 2.00    | root      |                            | CARTESIAN inner join                                                                                                                         |
|   ├─StreamAgg_17(Build)          | 1.00    | root      |                            | funcs:firstrow(Column#20)->Column#10, funcs:count(distinct Column#21)->Column#11, funcs:sum(Column#22)->Column#12, funcs:count(1)->Column#13 |
|   │ └─Projection_22              | 2.00    | root      |                            | test.t1.c2->Column#20, test.t1.c2->Column#21, cast(isnull(test.t1.c2), decimal(20,0) BINARY)->Column#22                                      |
|   │   └─IndexReader_21           | 2.00    | root      |                            | index:IndexFullScan_20                                                                                                                       |
|   │     └─IndexFullScan_20       | 2.00    | cop[tikv] | table:t1, index:i1(c1, c2) | keep order:false, stats:pseudo                                                                                                               |
|   └─TableReader_13(Probe)        | 2.00    | root      |                            | data:TableFullScan_12                                                                                                                        |
|     └─TableFullScan_12           | 2.00    | cop[tikv] | table:t1                   | keep order:false, stats:pseudo                                                                                                               |
+----------------------------------+---------+-----------+----------------------------+----------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set, 2 warnings (0.00 sec)

mysql> explain select 'm' = ALL (SELECT /*+ IGNORE_INDEX(t1, i1) */ c2 FROM t1) from t1;
+----------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| id                               | estRows | task      | access object | operator info                                                                                                                                |
+----------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_9                     | 2.00    | root      |               | or(and(and(le(Column#11, 1), eq(0, Column#10)), if(ne(Column#12, 0), <nil>, 1)), or(eq(Column#13, 0), 0))->Column#14                         |
| └─HashJoin_10                    | 2.00    | root      |               | CARTESIAN inner join                                                                                                                         |
|   ├─StreamAgg_17(Build)          | 1.00    | root      |               | funcs:firstrow(Column#18)->Column#10, funcs:count(distinct Column#19)->Column#11, funcs:sum(Column#20)->Column#12, funcs:count(1)->Column#13 |
|   │ └─Projection_22              | 2.00    | root      |               | test.t1.c2->Column#18, test.t1.c2->Column#19, cast(isnull(test.t1.c2), decimal(20,0) BINARY)->Column#20                                      |
|   │   └─TableReader_21           | 2.00    | root      |               | data:TableFullScan_20                                                                                                                        |
|   │     └─TableFullScan_20       | 2.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                                                               |
|   └─TableReader_13(Probe)        | 2.00    | root      |               | data:TableFullScan_12                                                                                                                        |
|     └─TableFullScan_12           | 2.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                                                                               |
+----------------------------------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------+
8 rows in set, 2 warnings (0.00 sec)

[sayJason]

@winoros These two queries use different query plans. When ignoring the index i1, a table full scan is applied, while using the index i1, an index scan is applied.

[yibin87]

From the filter projection's expression, we can see that if first_row(c2) is nil, the filter expression should be nil.
If first_row(c2) is 7, eq(0, first_row(c2)) will be false, and the filter expression will be false.
Thus, it seems a planner issue.

[yibin87]

Similar to this one: #20007,

[yibin87]

/severity major

[yibin87]

/remove severity critical

[yibin87]

/remove-severity critical

[yibin87]

Long existing problem, and not common used cases. Adjust to major severity.

[yibin87]

/sig planner

[yibin87]

/assign @yibin87

[yibin87]

/remove-sig execution

[yibin87]

It's a planner issue, and I'm going to fix it.