Wordpress 5.5.1 incompatible query with SQL_CALC_FOUND_ROWS DISTINCT

[donbowman]

Bug Report

In wordpress 5.5.1, this query is emitted:
SELECT SQL_CALC_FOUND_ROWS DISTINCT posts.ID FROM posts WHERE 1=1 AND posts.post_type = 'post' AND ((posts.post_status = 'future')) ORDER BY posts.post_date DESC LIMIT 0, 10;

This causes a syntax error, it works in mysql:

MySQL [www_database]> SELECT SQL_CALC_FOUND_ROWS DISTINCT posts.ID FROM posts  WHERE 1=1  AND posts.post_type = 'post' AND ((posts.post_status = 'future'))  ORDER BY posts.post_date DESC LIMIT 0, 10;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 35 near "DISTINCT posts.ID FROM posts  WHERE 1=1  AND posts.post_type = 'post' AND ((posts.post_status = 'future'))  ORDER BY posts.post_date DESC LIMIT 0, 10" 
MySQL [www_database]> 

I have looked at https://docs.pingcap.com/tidb/v2.1/sql-statement-select/ which says:

SQL_CALC_FOUND_ROWS | To guarantee compatibility with MySQL, TiDB parses this syntax, but will ignore it.
-- | --

but it may not w/ distinct?

1. Minimal reproduce step (Required)

+------------------------+
| Tables_in_www_database |
+------------------------+
| commentmeta            |
| comments               |
| links                  |
| manage_schedule        |
| options                |
| postmeta               |
| posts                  |
| sm_sync                |
| term_relationships     |
| term_taxonomy          |
| termmeta               |
| terms                  |
| usermeta               |
| users                  |
| wpcsplog               |
+------------------------+

CREATE TABLE `posts` (
  `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT 0,
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_title` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_excerpt` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `to_ping` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `pinged` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT 0,
  `guid` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT 0,
  `post_type` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (`ID`),
  KEY `post_name` (`post_name`(191)),
  KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
  KEY `post_parent` (`post_parent`),
  KEY `post_author` (`post_author`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci AUTO_INCREMENT=124960 |

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

I expected 0 rows and no sql errors

3. What did you see instead (Required)

sql syntax error around distinc

4. What is your TiDB version? (Required)

MySQL [www_database]> SHOW VARIABLES LIKE "%version%";
+-------------------------+--------------------------------------------------------------------------+
| Variable_name           | Value                                                                    |
+-------------------------+--------------------------------------------------------------------------+
| innodb_version          | 5.6.25                                                                   |
| protocol_version        | 10                                                                       |
| tidb_row_format_version | 2                                                                        |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2                                                    |
| version                 | 5.7.25-TiDB-v4.0.6                                                       |
| version_comment         | TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible |
| version_compile_machine | x86_64                                                                   |
| version_compile_os      | osx10.8                                                                  |
+-------------------------+--------------------------------------------------------------------------+

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.6
Edition: Community
Git Commit Hash: 51d365fc45fdfc039eb204a96268c5bd1c55075f
Git Branch: heads/refs/tags/v4.0.6
UTC Build Time: 2020-09-15 09:50:30
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Here is a minimal testcase against master:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
 id INT NOT NULL PRIMARY KEY auto_increment
);
SELECT SQL_CALC_FOUND_ROWS DISTINCT id FROM t1 LIMIT 1;

..

mysql> SELECT SQL_CALC_FOUND_ROWS DISTINCT id FROM t1 LIMIT 1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 35 near "DISTINCT id FROM t1 LIMIT 1" 

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-1258-gcc3d9db37-dirty
Edition: Community
Git Commit Hash: cc3d9db37ca45437e2a44c297bfba03c22663f20
Git Branch: master
UTC Build Time: 2020-09-20 08:37:56
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

Note that SQL_CALC_FOUND_ROWS is not supported by TiDB, and since #19506 will produce an error by default. This feature is also deprecated in MySQL, but still works. Here is the output from MySQL 8.0.21:

mysql [localhost:8021] {msandbox} (test) > SELECT SQL_CALC_FOUND_ROWS DISTINCT id FROM t1 LIMIT 1;
Empty set, 1 warning (0.00 sec)

mysql [localhost:8021] {msandbox} (test) > show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                 |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | SQL_CALC_FOUND_ROWS is deprecated and will be removed in a future release. Consider using two separate queries instead. |
+---------+------+-------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Interestingly, TiDB will accept the DISTINCT / SQL_CALC_FOUND_ROWS in the other order:

mysql> SET tidb_enable_noop_functions = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DISTINCT SQL_CALC_FOUND_ROWS id FROM t1 LIMIT 1;
Empty set (0.00 sec)

[morgo]

#18425 is a duplicate of this issue. Here is the added testcase:

drop table if exists t;
create table t (a int);
insert into t values(1);
select STRAIGHT_JOIN SQL_SMALL_RESULT count(*) from t;
select SQL_SMALL_RESULT STRAIGHT_JOIN count(*) from t;

[morgo]

Confirming this is fixed(ish) in master after updated the parser dependency and rebuilding:

mysql> CREATE TABLE t1 (
    ->  id INT NOT NULL PRIMARY KEY auto_increment
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT SQL_CALC_FOUND_ROWS DISTINCT id FROM t1 LIMIT 1;
ERROR 1235 (42000): function SQL_CALC_FOUND_ROWS has only noop implementation in tidb now, use tidb_enable_noop_functions to enable these functions

The error is because TiDB does not actually implement SQL_CALC_FOUND_ROWS (it ignores this keyword, see #8235). If you are okay with that restriction, run SET GLOBAL tidb_enable_noop_functions=1. Wordpress works okay with this mode, except pagination may not appear correctly (since the FOUND_ROWS() function only returns how many rows in the last result, not the total possible rows).

Wordpress has plans to remove this functionality, since SQL_CALC_FOUND_ROWS is deprecated in MySQL as well. See: https://core.trac.wordpress.org/ticket/47280

[wjhuang2016]

Close by pingcap/parser#1122

[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

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions