static pruning mode gets wrong results

[jackysp]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t100` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`col1` int(10) NOT NULL DEFAULT '0' COMMENT 'test',
`money` bigint(20) NOT NULL COMMENT 'test',
`logtime` datetime NOT NULL COMMENT '记录时间',
PRIMARY KEY (`ID`,`logtime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 COMMENT='test'
PARTITION BY RANGE COLUMNS(logtime) (
PARTITION `p20220608` VALUES LESS THAN ("20220609"),
PARTITION `p20220609` VALUES LESS THAN ("20220610"),
PARTITION `p20220610` VALUES LESS THAN ("20220611"),
PARTITION `p20220611` VALUES LESS THAN ("20220612"),
PARTITION `p20220612` VALUES LESS THAN ("20220613"),
PARTITION `p20220613` VALUES LESS THAN ("20220614"),
PARTITION `p20220614` VALUES LESS THAN ("20220615"),
PARTITION `p20220615` VALUES LESS THAN ("20220616"),
PARTITION `p20220616` VALUES LESS THAN ("20220617"),
PARTITION `p20220617` VALUES LESS THAN ("20220618"),
PARTITION `p20220618` VALUES LESS THAN ("20220619"),
PARTITION `p20220619` VALUES LESS THAN ("20220620"),
PARTITION `p20220620` VALUES LESS THAN ("20220621"),
PARTITION `p20220621` VALUES LESS THAN ("20220622"),
PARTITION `p20220622` VALUES LESS THAN ("20220623"),
PARTITION `p20220623` VALUES LESS THAN ("20220624"),
PARTITION `p20220624` VALUES LESS THAN ("20220625")
 );

insert into t100(col1,money,logtime) values (100,10,'2022-06-09 00:00:00');

insert into t100(col1,money,logtime) values (100,10,'2022-06-10 00:00:00');

SELECT col1,sum(money) FROM t100 WHERE logtime>='2022-06-09 00:00:00' AND col1=100 ;

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

mysql> SELECT col1,sum(money) FROM t100 WHERE logtime>='2022-06-09 00:00:00' AND col1=100 ;
+------+------------+
| col1 | sum(money) |
+------+------------+
|  100 |         20 |
+------+------------+

3. What did you see instead (Required)

mysql> SELECT col1,sum(money) FROM t100 WHERE logtime>='2022-06-09 00:00:00' AND col1=100 ;
+------+------------+
| col1 | sum(money) |
+------+------------+
| NULL |         20 |
+------+------------+

Sometimes will get this result. Try more times.

4. What is your TiDB version? (Required)

5d4745e

[jackysp]

It will also happen in v4.0. If using the following SQL, bypass the wrong only_full_group_by check.

mysql> SELECT any_value(col1),sum(money) FROM t100 WHERE logtime>='2022-06-09 00:00:00' AND col1=100 ;
+-----------------+------------+
| any_value(col1) | sum(money) |
+-----------------+------------+
|            NULL |         20 |
+-----------------+------------+
1 row in set (0.01 sec)

But if turn the pruning mode to 'dynamic', the bug disappears. I think the refactor of the partition pruning mode fixed this bug.

PTAL @mjonss @qw4990

[mjonss]

Another workaround seems to be adding group by 1 to the query.

[zimulala]

@mjonss
Do you have time to fix this problem?

[mjonss]

@zimulala I will start and see if I can solve it or needs help from the optimizer team.

[XuHuaiyu]

@mjonss This is a blocking issue for v5.3.2, we'd better merge the PR for branch release-5.3 ASAP (before Jun. 20th).

[qw4990]

@zimulala I will start and see if I can solve it or needs help from the optimizer team.

I guess the cause is that some partitioning StreamAggs return empty or null, and the upper HashAgg cannot handle these results correctly.

[tiancaiamao]

@qw4990 You're right.
The tikv StreamAgg is not returning the correct result.
I print out the result of UnionAll.Next() to see what's returned:

mysql> SELECT * FROM t100 WHERE logtime>='2022-06-09 00:00:00' AND col1=100 ;
+----+------+-------+---------------------+
| ID | col1 | money | logtime             |
+----+------+-------+---------------------+
|  1 |  100 |    10 | 2022-06-09 00:00:00 |
|  3 |  100 |    10 | 2022-06-10 00:00:00 |
+----+------+-------+---------------------+
2 rows in set (0.00 sec)

UnionExec.Next() ...
1, 100, 10, 2022-06-09 00:00:00
UnionExec.Next() ...
3, 100, 10, 2022-06-10 00:00:00

mysql> SELECT col1,sum(money) FROM t100 WHERE logtime>='2022-06-09 00:00:00' AND col1=100 ;
+------+------------+
| col1 | sum(money) |
+------+------------+
| NULL |         20 |
+------+------------+
1 row in set (0.01 sec)

UnionExec.Next() ...
NULL, NULL
UnionExec.Next() ...
10, 100
UnionExec.Next() ...
10, 100
UnionExec.Next() ...
NULL, NULL
UnionExec.Next() ...
NULL, NULL
UnionExec.Next() ...
NULL, NULL
UnionExec.Next() ...
NULL, NULL
UnionExec.Next() ...
NULL, NULL
UnionExec.Next() ...
NULL, NULL
UnionExec.Next() ...
NULL, NULL
UnionExec.Next() ...
NULL, NULL
UnionExec.Next() ...
NULL, NULL
UnionExec.Next() ...
NULL, NULL
UnionExec.Next() ...
NULL, NULL
UnionExec.Next() ...
NULL, NULL
UnionExec.Next() ...
NULL, NULL

[tiancaiamao]

The bug is caused by here:

tidb/executor/aggregate.go

Lines 1368 to 1379 in 2de01b4

	if e.childResult.NumRows() == 0 {
	if !e.isChildReturnEmpty {
	err = e.appendResult2Chunk(chk)
	} else if e.defaultVal != nil {
	chk.Append(e.defaultVal, 0, 1)
	}
	e.executed = true
	return err
	}
	// Reach here, "e.childrenResults[0].NumRows() > 0" is guaranteed.
	e.isChildReturnEmpty = false
	e.inputRow = e.inputIter.Begin()

The table reader return empty chunk, however, the stream agg add a default value (null) on line 1372

[tiancaiamao]

This issue is similiar to #30923
For query select avg(a) from t, when t is an empty table, it returns NULL instead of empty.
We have an internal process for adding the default NULL chunk data.

We should distinguish partial agg and final agg, and add NULL only for the final agg.