search

matrixorigin / matrixone

Hyperconverged cloud-edge native database
https://docs.matrixorigin.cn/en
Apache License 2.0
1.72k stars 271 forks source link

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

Open WuMenglong opened 1 month ago

WuMenglong commented 1 month ago

Is there an existing issue for the same bug?

Branch Name

v1.1.3

Commit ID

14c4496

Other Environment Information

No response

Actual Behavior

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

根据提示，调整 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： image

MatrixOne： image

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 commented 1 month ago

@daviszhen please help on it.

aronchanisme commented 1 month ago

  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 commented 3 weeks ago

on leave

aunjgr commented 2 weeks ago

main上已解决。1.2等pr合并

aunjgr commented 2 weeks ago

PR都合并了。

aronchanisme commented 1 week ago

待验证

aronchanisme commented 1 week ago

待验证

aronchanisme commented 5 days ago

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 g.user.lb@gmail.com 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`)
```bash
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 <badboynt@126.com>
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