Wrong query result when session variable is used as group by item

[guo-shaoge]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t1;
create table t1(c1 int);
insert into t1 values(1), (2), (3), (4), (5), (6);
select floor(dt.rn/2) rownum, count(c1) from (select @rownum := @rownum + 1 rn, c1 from (select @rownum := -1) drn, t1) dt group by floor(dt.rn/2) order by rownum;

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

mysql> select floor(dt.rn/2) rownum, count(c1) from (select @rownum := @rownum + 1 rn, c1 from (select @rownum := -1) drn, t1) dt group by floor(dt.rn/2) order by rownum;
+--------+-----------+
| rownum | count(c1) |
+--------+-----------+
|      0 |         2 |
|      1 |         2 |
|      2 |         2 |
+--------+-----------+
3 rows in set (0.00 sec)

3. What did you see instead (Required)

mysql> select floor(dt.rn/2) rownum, count(c1) from (select @rownum := @rownum + 1 rn, c1 from (select @rownum := -1) drn, t1) dt group by floor(dt.rn/2) order by rownum;
+--------+-----------+
| rownum | count(c1) |
+--------+-----------+
|      0 |        15 |
+--------+-----------+

4. What is your TiDB version? (Required)

master/5.1.0

[guo-shaoge]

In v4.x, plan is ok.

mysql> explain select floor(dt.rn/2) rownum, count(c1) from (select @rownum := @rownum + 1 rn, c1 from (select @rownum := -1) drn, t1) dt group by floor(dt.rn/2) order by rownum;
+---------------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------+
| id                                    | estRows | task      | access object | operator info                                                                             |
+---------------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------+
| Sort_10                               | 1.00    | root      |               | Column#6:asc                                                                              |
| └─Projection_12                       | 1.00    | root      |               | floor(div(cast(Column#4, decimal(20,0) BINARY), 2))->Column#6, Column#5                   |
|   └─HashAgg_13                        | 1.00    | root      |               | group by:Column#13, funcs:count(Column#11)->Column#5, funcs:firstrow(Column#12)->Column#4 |
|     └─Projection_21                   | 6.00    | root      |               | test.t1.c1, Column#4, floor(div(cast(Column#4, decimal(20,0) BINARY), 2))->Column#13      |
|       └─Projection_14                 | 6.00    | root      |               | setvar(rownum, plus(getvar(rownum), 1))->Column#4, test.t1.c1                             |
|         └─HashJoin_16                 | 6.00    | root      |               | CARTESIAN inner join                                                                      |
|           ├─Projection_17(Build)      | 1.00    | root      |               | setvar(rownum, -1)->Column#1                                                              |
|           │ └─TableDual_18            | 1.00    | root      |               | rows:1                                                                                    |
|           └─TableReader_20(Probe)     | 6.00    | root      |               | data:TableFullScan_19                                                                     |
|             └─TableFullScan_19        | 6.00    | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                            |
+---------------------------------------+---------+-----------+---------------+-------------------------------------------------------------------------------------------+
10 rows in set (0.01 sec)

In master, groupby item of HashAgg_13 is wrong. Guess we pruned that column unexpectedly. Check here (make group by item be constant one) and here (replace column in groupby item to setvar expr).

mysql> explain select floor(dt.rn/2) rownum, count(c1) from (select @rownum := @rownum + 1 rn, c1 from (select @rownum := -1) drn, t1) dt group by floor(dt.rn/2) order by rownum;
+-------------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------+
| id                                  | estRows  | task      | access object | operator info                                                                     |
+-------------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------+
| Sort_10                             | 1.00     | root      |               | Column#6                                                                          |
| └─Projection_12                     | 1.00     | root      |               | floor(div(cast(Column#4, decimal(20,0) BINARY), 2))->Column#6, Column#5           |
|   └─HashAgg_13                      | 1.00     | root      |               | group by:1, funcs:count(Column#11)->Column#5, funcs:firstrow(Column#12)->Column#4 |
|     └─Projection_20                 | 10000.00 | root      |               | test.t1.c1, setvar(rownum, plus(getvar(rownum), 1))->Column#12                    |
|       └─HashJoin_15                 | 10000.00 | root      |               | CARTESIAN inner join                                                              |
|         ├─Projection_16(Build)      | 1.00     | root      |               | setvar(rownum, -1)->Column#1                                                      |
|         │ └─TableDual_17            | 1.00     | root      |               | rows:1                                                                            |
|         └─TableReader_19(Probe)     | 10000.00 | root      |               | data:TableFullScan_18                                                             |
|           └─TableFullScan_18        | 10000.00 | cop[tikv] | table:t1      | keep order:false, stats:pseudo                                                    |
+-------------------------------------+----------+-----------+---------------+-----------------------------------------------------------------------------------+

[yudongusa]

@guo-shaoge @time-and-fate can we verify and close this issue since #27093 was already fixed and closed?

[time-and-fate]

@guo-shaoge @time-and-fate can we verify and close this issue since #27093 was already fixed and closed?

I'll close it after #27370 merged. I closed #27093 because it's duplicated with this one.

[tiancaiamao]

4.0.16 reject this one in triage