Tidb: The sql running result is error!

[yowner]

Please answer these questions before submitting your issue. Thanks!

1.What did you do?
create table stake(userId int,day int);
insert into stake VALUES(1,20180602);
insert into stake VALUES(1,20180602);
insert into stake VALUES(2,20180602);

insert into stake VALUES(1,20180603);
insert into stake VALUES(3,20180603);
insert into stake VALUES(3,20180603);

select
t.day,
(select count(distinct userId) from stake where day=t.day) stakes,
(select count(distinct userId) from stake where day=t.day
and userId in(select userId from stake where day=DATE_FORMAT(ADDDATE(str_to_date(t.day,'%Y%m%d'),INTERVAL +1 day),'%Y%m%d'))) stakes2
from(
select
distinct day
from stake
) t;

1. What did you expect to see?

day | stakes | stakes2
-- | -- | --
20180602 | 2 | 1
20180603 | 2 | 0

1. What did you see instead?

day | stakes | stakes2
-- | -- | --
20180602 | 2 | 2
20180603 | 2 | 0

1. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?
   Release Version: v2.0.5
   Git Commit Hash: 43ab80012ae6a154e221e124a2ad3a396f0de822
   Git Branch: release-2.0
   UTC Build Time: 2018-07-06 08:52:37
   GoVersion: go version go1.10.2 linux/amd64
   TiKV Min Version: 2.0.0-rc.4.1

[coocood]

@yowner
Thank you for your report, we'll investigate this issue.

[coocood]

@yowner
I tried this in MySQL, the result is

+----------+--------+---------+
| day      | stakes | stakes2 |
+----------+--------+---------+
| 20180602 |      2 |       1 |
| 20180603 |      2 |       0 |
+----------+--------+---------+

[yowner]

yes, but running in TiDB is:
+----------+--------+---------+
| day | stakes | stakes2 |
+----------+--------+---------+
| 20180602 | 2 | 2 |
| 20180603 | 2 | 0 |
+----------+--------+---------+

[coocood]

@yowner I edited the issue to swap the expected and actual.

[coocood]

@yowner
Please try the latest master, it's fixed in master.

[yowner]

It's slowly! I run it consume 290 seconds in TiDB when the table have 2345809 rows. But mysql only 3 seconds.

create INDEX idx_stake_day_userId on stake(day,userId);
stake.zip

[winoros]

@yowner Thanks for your reporting.
There're some optimization we can do to accelerate this sql.
It may take some time to do this.
Sorry for your inconvenience.
We need:

• push the add_date function down to tikv side. or change the DataSource structure in TiDB.
• add a variable to control the decorrelate optimization.

For your side. You'd better change the day column from int type to date type.

[yowner]

@winoros The day column is int type that save less disk space. Int type is 4 Bytes, but date is 8 Bytes.

[nullnotnil]

I am going to close this issue as stale. Please feel free to re-open if you have additional questions. Thanks!