[Bug]: Group By 索引号语句结果与MySQL中表现不一致

[WuMenglong]

Is there an existing issue for the same bug?

• I have checked the existing issues.

Branch Name

v1.1.3

Commit ID

14c4496

Other Environment Information

No response

Actual Behavior

问题描述：
使用 Tableau ODBC 方式连接 MatrixOne，在使用 Tableau 分析时，其生成的 SQL 执行出现多处如下报错：

根据提示，调整 sql_mode，关闭 ONLY_FULL_GROUP_BY 模式：

SET global sql_mode='ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,STRICT_TRANS_TAB LES';

关闭 ONLY_FULL_GROUP_BY 后上述报错不再出现，但Group By 1的执行结果集与在 MySQL 下的不一致，且由于是 BI 中自动生成的语句，无法人为手动调整SQL为 GROUP BY YEAR(time_1)：
MySQL：

MatrixOne：

Expected Behavior

No response

Steps to Reproduce

以下是不需要使用BI的复现操作：
-- 创建数据库

CREATE DATABASE stock_data;

-- 创建表
```sql
USE stock_data;
CREATE TABLE stockpriceus2_5min_copy1 (
    id INT AUTO_INCREMENT PRIMARY KEY,
    time_1 DATETIME,
    price DECIMAL(10, 2)
);

-- 插入示例数据

INSERT INTO stockpriceus2_5min_copy1 (time_1, price) VALUES 
('2023-06-25 14:00:00', 150.50),
('2023-06-25 14:05:00', 151.00),
('2022-06-25 14:10:00', 149.75),
('2021-06-25 14:15:00', 148.25),
('2020-06-25 14:20:00', 147.00);

-- 执行SQL

# 与MySQL结果一致的SQL
SELECT YEAR(`time_1`) AS yr_time_1_ok
FROM stockpriceus2_5min_copy1
GROUP BY YEAR(`time_1`);

# 与MySQL不一致SQL
SELECT YEAR(`time_1`) AS yr_time_1_ok
FROM stockpriceus2_5min_copy1
GROUP BY 1;

### Additional information

_No response_

[volgariver6]

@daviszhen please help on it.

[aronchanisme]

1. Business impact:
   Issue from community user, which has an impact on BI tool Tableau connecting to MO. Not a PRD case.

2. Severity
   Downgrade from s-1 to s0 based on business impact

3. Target version
   Probably can be fixed in v1.2.2 based on dev assessment and roadmap of v1.2.1

[aunjgr]

on leave

[aunjgr]

main上已解决。1.2等pr合并

[aunjgr]

PR都合并了。

[aronchanisme]

待验证

[aronchanisme]

待验证

[aronchanisme]

Fixed. @WuMenglong Pls kindly take a look, thx.

Test result

1. 1.2-dev(ebcfce8e5)

github@test0:/data/mo/1.2-dev/matrixone$ mo_ctl connect
2024-07-22 10:44:21.298 UTC+0800    [INFO]    Checking connectivity
2024-07-22 10:44:21.386 UTC+0800    [INFO]    Ok, connecting for user ... 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7004
Server version: 8.0.30-MatrixOne-v85 MatrixOne

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE stock_data;
Query OK, 1 row affected (0.04 sec)

mysql> 
mysql> -- 创建表
mysql> ```sql
    `> USE stock_data;
    `> CREATE TABLE stockpriceus2_5min_copy1 (
    `>     id INT AUTO_INCREMENT PRIMARY KEY,
    `>     time_1 DATETIME,
    `>     price DECIMAL(10, 2)
    `> );
    `> 
    `> ^C
mysql> USE stock_data;
Database changed
mysql> CREATE TABLE stockpriceus2_5min_copy1 (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     time_1 DATETIME,
    ->     price DECIMAL(10, 2)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO stockpriceus2_5min_copy1 (time_1, price) VALUES 
    -> ('2023-06-25 14:00:00', 150.50),
    -> ('2023-06-25 14:05:00', 151.00),
    -> ('2022-06-25 14:10:00', 149.75),
    -> ('2021-06-25 14:15:00', 148.25),
    -> ('2020-06-25 14:20:00', 147.00);
Query OK, 5 rows affected (0.01 sec)

mysql> select * from stockpriceus2_5min_copy1;
+------+---------------------+--------+
| id   | time_1              | price  |
+------+---------------------+--------+
|    1 | 2023-06-25 14:00:00 | 150.50 |
|    2 | 2023-06-25 14:05:00 | 151.00 |
|    3 | 2022-06-25 14:10:00 | 149.75 |
|    4 | 2021-06-25 14:15:00 | 148.25 |
|    5 | 2020-06-25 14:20:00 | 147.00 |
+------+---------------------+--------+
5 rows in set (0.00 sec)

mysql> SELECT YEAR(`time_1`) AS yr_time_1_ok
    -> FROM stockpriceus2_5min_copy1
    -> GROUP BY YEAR(`time_1`);
+--------------+
| yr_time_1_ok |
+--------------+
|         2023 |
|         2022 |
|         2021 |
|         2020 |
+--------------+
4 rows in set (0.00 sec)

mysql> SELECT YEAR(`time_1`) AS yr_time_1_ok
    -> FROM stockpriceus2_5min_copy1
    -> GROUP BY 1;
+--------------+
| yr_time_1_ok |
+--------------+
|         2023 |
|         2022 |
|         2021 |
|         2020 |
+--------------+
4 rows in set (0.00 sec)

mysql> select git_version();
+---------------+
| git_version() |
+---------------+
| ebcfce8e5     |
+---------------+
1 row in set (0.00 sec)

mysql> system mo_ctl get_cid
2024-07-22 10:45:13.079 UTC+0800    [INFO]    Try get mo commit id
commit ebcfce8e541ecd1dfb05411553b5e6b15da09741
Author: LiuBo <[email protected]>
Date:   Fri Jul 19 22:04:24 2024 +0800

    [enhancement] stats: add metrics for stats trigger updating (#17618)
    
    add metrics for stats trigger updating
    
    Approved by: @XuPeng-SH, @zhangxu19830126, @sukki37
2024-07-22 10:45:13.134 UTC+0800    [INFO]    Get commit id succeeded
mysql> system mo_ctl get_branch
2024-07-22 10:45:15.092 UTC+0800    [INFO]    Try get mo branch
2024-07-22 10:45:15.144 UTC+0800    [INFO]    Get branch succeeded, current branch: 1.2-dev
mysql> exit
Bye
2024-07-22 10:45:18.354 UTC+0800    [INFO]    Connect succeeded and finished. Bye

2. main(0cc26c508)

github@test0:/data/mo/1.2-dev/matrixone$ mo_ctl connect
2024-07-22 10:47:04.439 UTC+0800    [INFO]    Checking connectivity
2024-07-22 10:47:04.513 UTC+0800    [INFO]    Ok, connecting for user ... 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2704
Server version: 8.0.30-MatrixOne-v026508 MatrixOne

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select git_version();
+---------------+
| git_version() |
+---------------+
| 0cc26c508     |
+---------------+
1 row in set (0.00 sec)

mysql> system mo_ctl get_cid
2024-07-22 10:47:16.499 UTC+0800    [INFO]    Try get mo commit id
commit 0cc26c5089c918c6feb3a0548f06f19c82e0a766
Author: nitao <[email protected]>
Date:   Mon Jul 22 01:01:55 2024 +0800

    Refactor prepare to reuse Compile part 2 (#17625)
    
    fix a bug that cause tpch hang when refactoring prepare statement
    
    Approved by: @ouyuanning
2024-07-22 10:47:16.550 UTC+0800    [INFO]    Get commit id succeeded
mysql> system mo_ctl get_branch
2024-07-22 10:47:18.418 UTC+0800    [INFO]    Try get mo branch
2024-07-22 10:47:18.474 UTC+0800    [INFO]    Get branch succeeded, current branch: main
mysql> CREATE DATABASE stock_data;
Query OK, 1 row affected (0.05 sec)

mysql> USE stock_data;
Database changed
mysql> CREATE TABLE stockpriceus2_5min_copy1 (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     time_1 DATETIME,
    ->     price DECIMAL(10, 2)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO stockpriceus2_5min_copy1 (time_1, price) VALUES 
    -> ('2023-06-25 14:00:00', 150.50),
    -> ('2023-06-25 14:05:00', 151.00),
    -> ('2022-06-25 14:10:00', 149.75),
    -> ('2021-06-25 14:15:00', 148.25),
    -> ('2020-06-25 14:20:00', 147.00);
Query OK, 5 rows affected (0.02 sec)

mysql> select * from stockpriceus2_5min_copy1;
+------+---------------------+--------+
| id   | time_1              | price  |
+------+---------------------+--------+
|    1 | 2023-06-25 14:00:00 | 150.50 |
|    2 | 2023-06-25 14:05:00 | 151.00 |
|    3 | 2022-06-25 14:10:00 | 149.75 |
|    4 | 2021-06-25 14:15:00 | 148.25 |
|    5 | 2020-06-25 14:20:00 | 147.00 |
+------+---------------------+--------+
5 rows in set (0.00 sec)

mysql> SELECT YEAR(`time_1`) AS yr_time_1_ok
    -> FROM stockpriceus2_5min_copy1
    -> GROUP BY YEAR(`time_1`);
+--------------+
| yr_time_1_ok |
+--------------+
|         2023 |
|         2022 |
|         2021 |
|         2020 |
+--------------+
4 rows in set (0.00 sec)

mysql> SELECT YEAR(`time_1`) AS yr_time_1_ok
    -> FROM stockpriceus2_5min_copy1
    -> GROUP BY 1;
+--------------+
| yr_time_1_ok |
+--------------+
|         2023 |
|         2022 |
|         2021 |
|         2020 |
+--------------+
4 rows in set (0.00 sec)

mysql> exit
Bye
2024-07-22 10:47:48.675 UTC+0800    [INFO]    Connect succeeded and finished. Bye

[heni02]

@Ariznawlll 添加到bvtcase