inconsistent behavior of Found_Rows and sql_calc_found_rows

[XuHuaiyu]

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

CREATE TABLE t1 (a int not null, b char (10) not null);
insert into t1 values(1,'a'),(2,'b'),(3,'c'),(3,'c');
CREATE TABLE t2 (a int not null, b char (10) not null);
insert into t2 values (3,'c'),(4,'d'),(5,'f'),(6,'e');

(select sql_calc_found_rows  a,b from t1 limit 2)  union all (select a,b from t2 order by a) limit 2;
select found_rows();

2. What did you expect to see?

mysql> (select sql_calc_found_rows  a,b from t1 limit 2)  union all (select a,b from t2 order by a) limit 2;
+---+---+
| a | b |
+---+---+
| 1 | a |
| 2 | b |
+---+---+
2 rows in set (0.00 sec)

mysql> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            6 |
+--------------+
1 row in set (0.01 sec)

3. What did you see instead?

tidb> (select sql_calc_found_rows  a,b from t1 limit 2)  union all (select a,b from
t2 order by a) limit 2;
+------+------+
| a    | b    |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.00 sec)

tidb> select found_rows();
+--------------+
| found_rows() |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

tidb> select tidb_version();
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                     |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v2.1.0-rc.3-158-g1082d5f1a
Git Commit Hash: 1082d5f1ac90c7d73b34061ccef7c5cae1b69b6c
Git Branch: master
UTC Build Time: 2018-11-08 07:01:33
GoVersion: go version go1.11 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) 

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @SunRunAway

[XuHuaiyu]

And this one, Mysql returns error, TiDB doesn't:

select * from (SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION SELECT * FROM t2 order by 1 LIMIT 2) t order by 1;
select found_rows();

[XuHuaiyu]

And this one, MySQL returns error, TiDB doesn't:

(SELECT * FROM t1) UNION all (SELECT SQL_CALC_FOUND_ROWS * FROM t2) LIMIT 1;

[XuHuaiyu]

And this one:

CREATE TABLE t1 (a INT) ENGINE=MEMORY;
CREATE TABLE t2 (a INT) ENGINE=MEMORY;
INSERT INTO t2 VALUES (1);
SELECT SQL_CALC_FOUND_ROWS * FROM t1 UNION ALL SELECT * FROM t2;
SELECT FOUND_ROWS();

[XuHuaiyu]

@morgo Can we create a new issue for enable SQL_CALC_FOUND_ROWS/LOCK IN SHARE MODE by default, and close this bug issue?

[morgo]

I have created #21786 to replace this issue. I am going to close this now.

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Recategorized as a feature request