[Bug]: `SQL syntax error` is fired

[DanielZhangQD]

Is there an existing issue for the same bug?

• [X] I have checked the existing issues.

Branch Name

1.2-dev

Commit ID

v1.2.3-2fed24bdf-2024-09-11

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

CREATE TABLE `agg_billing_records_daily_unit_name` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `account_period` VARCHAR(32) DEFAULT NULL,
  `start_time` DATETIME DEFAULT NULL,
  `end_time` DATETIME DEFAULT NULL,
  `organization_id` VARCHAR(20) DEFAULT NULL,
  `instance_id` VARCHAR(64) DEFAULT NULL,
  `instance_plan_type` VARCHAR(20) DEFAULT NULL,
  `instance_charge_type` TINYINT NOT NULL DEFAULT '0',
  `bill_type` TINYINT DEFAULT NULL,
  `price` DECIMAL(18,8) DEFAULT NULL,
  `unit_name` VARCHAR(32) DEFAULT NULL,
  `unit_info` TEXT DEFAULT NULL,
  `payment` TEXT DEFAULT NULL,
  `units` TEXT DEFAULT NULL,
  `discount` DECIMAL(4,3) NOT NULL DEFAULT '1.000',
  `usage` DECIMAL(28,8) NOT NULL DEFAULT '0.00000000',
  `amount` DECIMAL(28,6) NOT NULL DEFAULT '0.000000',
  `derate_amount` DECIMAL(28,6) NOT NULL DEFAULT '0.000000',
  `round_down_amount` DECIMAL(28,6) NOT NULL DEFAULT '0.000000',
  `payable_amount` DECIMAL(28,2) NOT NULL DEFAULT '0.00',
  `owed_amount` DECIMAL(28,2) NOT NULL DEFAULT '0.00',
  `cash_amount` DECIMAL(28,2) NOT NULL DEFAULT '0.00',
  `coupon_amount` DECIMAL(28,2) NOT NULL DEFAULT '0.00',
  `created_at` DATETIME DEFAULT NULL,
  `updated_at` DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_period_org_ins_unit` (`account_period`,`organization_id`,`instance_id`,`unit_name`),
  KEY `idx_coupon_amount` (`coupon_amount`),
  KEY `idx_time_instance` (`start_time`,`instance_id`),
  KEY `idx_amount` (`amount`),
  KEY `idx_derate_amount` (`derate_amount`),
  KEY `idx_payable_amount` (`payable_amount`),
  KEY `idx_owed_amount` (`owed_amount`),
  KEY `idx_cash_amount` (`cash_amount`)
)

mysql> SELECT sum(amount) as amount, sum(derate_amount) as derate_amount, sum(round_down_amount) as round_down_amount, sum(payable_amount) as payable_amount, sum(owed_amount) as owed_amount, sum(cash_amount) as cash_amount, sum(coupon_amount) as coupon_amount FROM `billing`.`agg_billing_records_daily_unit_name` WHERE organization_id = '98A-79410' AND start_time >= '2024-08-31 16:00:00' AND start_time < '2024-09-30 16:00:00' ORDER BY start_time DESC, instance_id ASC;
ERROR 1149 (HY000): SQL syntax error: column "agg_billing_records_daily_unit_name.start_time" must appear in the GROUP BY clause or be used in an aggregate function

Expected Behavior

SQL succeeds

Steps to Reproduce

See detail in `Actual Behavior`

Additional information

The issue is hit in Cloud dev env and it works with MySQL

[aunjgr]

not working on it today

[aunjgr]

查询本身写得有问题。全局聚合函数，结果只有一行，不应该再接ORDER BY。

麻烦 @DanielZhangQD 再检查一下，查询是不是写错了？

[DanielZhangQD]

查询本身写得有问题。全局聚合函数，结果只有一行，不应该再接ORDER BY。

麻烦 @DanielZhangQD 再检查一下，查询是不是写错了？

@aunjgr This SQL works with MySQL.

[DanielZhangQD]

平台这里优化一下 SQL

[DanielZhangQD]

Fixed by https://github.com/matrixorigin/mocloud-services/pull/1298/files