tidb sql count bug

[jiucaiProductions]

-- user default test db
use test;

select @@Version;
-- 5.7.10-TiDB-v2.1.0-5-g4dad722

CREATE TABLE t1 (
name varchar(100) DEFAULT NULL
);

insert into t1 (name) values ('test');

-- return 0
SELECT COUNT(1)
FROM (
SELECT COUNT(1)
FROM (
SELECT *
FROM t1
WHERE name = 'test') t)
t2;

insert into t1 (name) values ('test2');

-- return 1
SELECT COUNT(1)
FROM (
SELECT COUNT(1)
FROM (
SELECT *
FROM t1
WHERE name = 'test') t)
t2;

truncate table t1;

-- repeat above, the seconds count sql return 0;

[eurekaka]

@jiucaiProductions thanks for the report, we will fix this.

I reproduced this issue on latest master branch. Here is the simplified reproduction step:

mysql> CREATE TABLE t1 (name varchar(100) DEFAULT NULL);

mysql> insert into t1 (name) values ('test');
Query OK, 1 row affected (0.00 sec)

mysql> explain analyze SELECT COUNT(1) FROM ( SELECT COUNT(1) FROM ( SELECT * FROM t1 WHERE name = 'test') t) t2;
+----------------------------+----------+------+-------------------------------------------------------------+----------------------------------+
| id                         | count    | task | operator info                                               | execution info                   |
+----------------------------+----------+------+-------------------------------------------------------------+----------------------------------+
| StreamAgg_13               | 1.00     | root | funcs:count(1)                                              | time:242.867µs, loops:2, rows:1  |
| └─StreamAgg_28             | 1.00     | root |                                                             | time:239.484µs, loops:1, rows:0  |
|   └─TableReader_29         | 1.00     | root | data:StreamAgg_17                                           | time:236.995µs, loops:1, rows:0  |
|     └─StreamAgg_17         | 1.00     | cop  |                                                             |                                  |
|       └─Selection_27       | 10.00    | cop  | eq(test.t1.name, "test")                                    |                                  |
|         └─TableScan_26     | 10000.00 | cop  | table:t1, range:[-inf,+inf], keep order:false, stats:pseudo | time:0s, loops:0, rows:1         |
+----------------------------+----------+------+-------------------------------------------------------------+----------------------------------+
6 rows in set (0.00 sec)

mysql> explain analyze SELECT COUNT(1) FROM ( SELECT COUNT(1) FROM ( SELECT * FROM t1 WHERE name = 'test') t) t2;
+----------------------------+----------+------+-------------------------------------------------------------+----------------------------------+
| id                         | count    | task | operator info                                               | execution info                   |
+----------------------------+----------+------+-------------------------------------------------------------+----------------------------------+
| StreamAgg_13               | 1.00     | root | funcs:count(1)                                              | time:262.616µs, loops:2, rows:1  |
| └─StreamAgg_28             | 1.00     | root |                                                             | time:258.004µs, loops:1, rows:0  |
|   └─TableReader_29         | 1.00     | root | data:StreamAgg_17                                           | time:255.293µs, loops:1, rows:0  |
|     └─StreamAgg_17         | 1.00     | cop  |                                                             |                                  |
|       └─Selection_27       | 10.00    | cop  | eq(test.t1.name, "test")                                    |                                  |
|         └─TableScan_26     | 10000.00 | cop  | table:t1, range:[-inf,+inf], keep order:false, stats:pseudo | time:0s, loops:0, rows:1         |
+----------------------------+----------+------+-------------------------------------------------------------+----------------------------------+
6 rows in set (0.00 sec)

mysql> explain analyze SELECT COUNT(1) FROM ( SELECT COUNT(1) FROM ( SELECT * FROM t1 WHERE name = 'test') t) t2;
+--------------------------+-------+------+-------------------------------------------------------------+----------------------------------+
| id                       | count | task | operator info                                               | execution info                   |
+--------------------------+-------+------+-------------------------------------------------------------+----------------------------------+
| StreamAgg_13             | 1.00  | root | funcs:count(1)                                              | time:267.2µs, loops:2, rows:1    |
| └─StreamAgg_18           | 1.00  | root |                                                             | time:258.924µs, loops:2, rows:1  |
|   └─TableReader_25       | 0.00  | root | data:Selection_24                                           | time:252.562µs, loops:2, rows:1  |
|     └─Selection_24       | 0.00  | cop  | eq(test.t1.name, "test")                                    |                                  |
|       └─TableScan_23     | 1.00  | cop  | table:t1, range:[-inf,+inf], keep order:false, stats:pseudo | time:0s, loops:0, rows:1         |
+--------------------------+-------+------+-------------------------------------------------------------+----------------------------------+
5 rows in set (0.00 sec)

After auto analyze has been executed in background, plan generated would change to not push aggregation down to storage layer. Looks like the problem is caused by TableReader_29 and StreamAgg_17. Note that I am using mockTikv here.

[winoros]

We've located the root cause of this problem. It will be fixed soon