sum() over()开窗函数执行报错

🪐 TiDB 技术问题 性能调优
1

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：

【概述】 场景 + 问题概述

 使用sum() over()开窗函数报错，而在mysql 8.0下可以正确执行

【背景】 做过哪些操作

【现象】 业务和数据库现象

【问题】 当前遇到的问题，参考 AskTUG 的 Troubleshooting 读性能慢-慢语句

【统计信息是否最新】

    【执行计划内容】

    【 SQL 文本、schema 以及 数据分布】

    简化后的sql样本

     create table cux_sum_test(amt bigint,name varchar(500));

    insert into cux_sum_test values(1,'a');
    insert into cux_sum_test values(3,'b');
    insert into cux_sum_test values(4,'c');

select
  t2.name
 ,sum(t2.sum_amt) over() sum_amt2
,sum_amt

from
(
select
t1.name
, sum(t1.amt) sum_amt
from
cux_sum_test t1
group by
t1.name) t2

【业务影响】

目前有相当多类似的sql写法，如果不能正常支持需要大量改造

【TiDB 版本】
v5.0.0
集群版本 v1.4.4

【附件】 相关日志及监控（https://metricstool.pingcap.com/)

  • TiUP Cluster Display 信息
  • TiUP CLuster Edit config 信息
  • TiDB-Overview Grafana监控
  • TiDB Grafana 监控
  • TiKV Grafana 监控
  • PD Grafana 监控
  • 对应模块日志（包含问题前后 1 小时日志）
2 个赞
2

测试了一下 v5.0.2 可以正确执行，方便升级到 v5.0.2 吗？

image
image1035×530 13 KB

1 个赞
3

image
目前版本已经升级到5.0.2了，
查询仍然报错SQL 错误 [1105] [HY000]: runtime error: index out of range [0] with length 0
是不是我们有什么设置出了问题

4

在最外层添加 group by t2.name,sum_amt 试试

1 个赞
5

还是不行，你们那边没法复现这个问题吗:joy:

6

  1. 查下您的 sql_mode 看下

    image
    image1300×159 4.77 KB

  2. tiup cluster edit-config xxx 反馈下您的配置

  3. 我这边确实没有复现。
    MySQL [test]> select
    → t2.name
    → ,sum(t2.sum_amt) over() sum_amt2
    → ,sum_amt
    → from
    → (
    → select
    → t1.name
    → , sum(t1.amt) sum_amt
    → from
    → cux_sum_test t1
    → group by
    → t1.name) t2;
    ±-----±---------±--------+
    | name | sum_amt2 | sum_amt |
    ±-----±---------±--------+
    | a | 8 | 1 |
    | c | 8 | 4 |
    | b | 8 | 3 |
    ±-----±---------±--------+
    3 rows in set (0.00 sec)

7

STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

config 配置：

global:
user: tidb
ssh_port: 22
ssh_type: builtin
deploy_dir: /tidb-deploy
data_dir: /tidb-data
os: linux
arch: amd64
monitored:
node_exporter_port: 9100
blackbox_exporter_port: 9115
deploy_dir: /tidb-deploy/monitor-9100
data_dir: /tidb-data/monitor-9100
log_dir: /tidb-deploy/monitor-9100/log
server_configs:
tidb:
log.slow-threshold: 300
performance.max-txn-ttl: 3600000
performance.txn-total-size-limit: 5368709120
tikv:
readpool.coprocessor.use-unified-pool: true
readpool.storage.use-unified-pool: false
pd:
replication.enable-placement-rules: true
replication.location-labels:
- host
tiflash:
logger.level: info
tiflash-learner: {}
pump: {}
drainer: {}
cdc: {}
tidb_servers:

  • host: 192.168.18.93
    ssh_port: 22
    port: 4000
    status_port: 10080
    deploy_dir: /tidb-deploy/tidb-4000
    log_dir: /tidb-deploy/tidb-4000/log
    arch: amd64
    os: linux
    tikv_servers:
  • host: 192.168.18.93
    ssh_port: 22
    port: 20160
    status_port: 20180
    deploy_dir: /tidb-deploy/tikv-20160
    data_dir: /tidb-data/tikv-20160
    log_dir: /tidb-deploy/tikv-20160/log
    config:
    server.labels:
    host: logic-host-1
    arch: amd64
    os: linux
  • host: 192.168.18.94
8

新建session 设置 only_full_group_by 试试。

9

您好，尝试过了，还是不行

10

我使用dberver7.2.1连接发现会报错，换成navicat15.0.25执行sql没问题，会是驱动导致的吗

12

dbeaver确实能复现

image
image937×631 24.4 KB

13

您好，现在代码里跑也有同样的问题
java.sql.SQLException: Parameter index out of range (1 > number of parameters, which is 0).
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.23.jar:8.0.23]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.23.jar:8.0.23]

14

解决方案
1、将name放到最后
2、添加group by

原因未找到，explain analyze的结果基本一致。@yilong

image

2 个赞
15

好的 谢谢:blush:

16

hi 这个调整字段顺序的原则是什么呢? 或者是参考什么规范来调整呢

17

我只是在转换写法的过程中，不小心发现调整顺序能够解决。具体还是需要官方检查实际原因。

1 个赞
18

您好，刚才测试原始sql，通过jdbc跑和通过Navicat跑，发现在dashboard中有些差异

SELECT
r.app,
r.begin_status_base,
r.end_status_base,
r.is_default,
round (
(
CASE
WHEN sum (r.count) = ? THEN ?
ELSE sum (r.count) / sum (sum (r.count)) over (
PARTITION by r.app,
r.matrix_name,
r.begin_status_base,
r.pd_set
)
END
),
?
) avg_rate,
r.matrix_name,
pd_set
FROM
(
SELECT
sr.*
FROM
hrrf_transmatrix_sr sr,
hrrf_transmatrix_def def
WHERE
sr.matrix_name = def.matrix_name
AND def.app = ?
AND sr.app = ?
AND def.cal_method = ?
) r
GROUP BY
r.app,
r.matrix_name,
r.begin_status_base,
r.end_status_base,
r.is_default,
r.pd_set
LIMIT
…

这是通过jdbc跑的sql信息，与navicat跑的相比，多了下面的’ limit … ’
不知道对你们排查问题有没有帮助

19

是这里加的limit 吧

image
image681×562 26.5 KB

20

image
image610×783 16.6 KB

好像问题就是因为这个limit导致的，去掉可以正常查询到结果

21

改成0试试，一般0是不限制

1 个赞