feature: An error occurred when a subquery includes both GROUP BY and DISTINCT

[haitaoguan]

Have you read the Contributing Guidelines on issues?

• I have read the Contributing Guidelines on issues.

Please confirm if bug report does NOT exists already ?

• I confirm there is no existing issue for this

Describe the problem

CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(10) NOT NULL,
  `last_name` varchar(10) NOT NULL,
  `sex` varchar(5) NOT NULL,
  `score` int(11) NOT NULL,
  `copy_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=TIANMU DEFAULT CHARSET=utf8mb4

mysql> SELECT bb.first_name, bb.last_name
    ->   FROM (SELECT count(distinct a.first_name) AS first_name,
    ->                SUBSTR(a.last_name, 1, 10) AS last_name
    ->           FROM t_test a, t_test b
    ->          WHERE a.id = b.id
    ->          GROUP BY SUBSTR(a.last_name, 1, 10)) bb;
ERROR 6 (HY000): The query includes syntax that is not supported by the storage engine. Either restructure the query with supported syntax, or enable the MySQL core::Query Path in config file to execute the query with reduced performance.

mysql> SELECT bb.first_name, bb.last_name
    ->   FROM (SELECT count(a.first_name) AS first_name,
    ->                SUBSTR(a.last_name, 1, 10) AS last_name
    ->           FROM t_test a, t_test b
    ->          WHERE a.id = b.id
    ->          GROUP BY SUBSTR(a.last_name, 1, 10)) bb;
Empty set (0.01 sec)

mysql> SELECT bb.first_name, bb.last_name
    ->   FROM (SELECT count(distinct a.first_name) AS first_name,
    ->                SUBSTR(a.last_name, 1, 10) AS last_name
    ->           FROM t_test a, t_test b
    ->          WHERE a.id = b.id) bb;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
|          0 | NULL      |
+------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT bb.first_name, bb.last_name
    ->   FROM (SELECT count(a.first_name) AS first_name,
    ->                SUBSTR(a.last_name, 1, 10) AS last_name
    ->           FROM t_test a, t_test b
    ->          WHERE a.id = b.id) bb;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
|          0 | NULL      |
+------------+-----------+
1 row in set (0.00 sec)

mysql> SELECT count(distinct a.first_name) AS first_name,
    ->                SUBSTR(a.last_name, 1, 10) AS last_name
    ->           FROM t_test a, t_test b
    ->          WHERE a.id = b.id
    ->          GROUP BY SUBSTR(a.last_name, 1, 10)
    -> ;
Empty set (0.00 sec)

Expected behavior

No response

How To Reproduce

No response

Environment

build information as follow:
Repository address: https://github.com/stoneatom/stonedb.git:stonedb-5.7-dev
Branch name: stonedb-5.7-dev
Last commit ID: 29e3f7c
Last commit time: Date: Thu Jan 12 17:58:24 2023 +0800
Build time: Date: Mon Jan 16 08:21:32 CST 2023

Are you interested in submitting a PR to solve the problem?

• Yes, I will!