This issue requires an assignee. Since you haven't specified one, we've assigned TestManager as the default assignee for this issue.
opengauss_botWelcome To openGauss Community
Hey @shpswd1 , thanks for your contribution to the community.
Bot Usage Manual
I'm the Bot here serving you. You can find the instructions on how to interact with me at Here . That means you can comment below every pull request or issue to trigger Bot Commands.
Contact Guide
If you have any questions, please contact the SIG: StorageEngine, AI, CM, CloudNative, SecurityTechnology, SQLEngine ,
and any of the maintainers: @CarrotGo, @chendong76, @chenxiaobin19, @congzhou2603, @dodders, @hwworkholic, @jemappellehc, @muyulinzhong, @quemingjian, @shenzheng4, @shirley_zhengx, @superlchf, @totaj, @wlff234, @wofanzheng, @ywzq1161327784 ,
and any of the committers: @Igali, @bihua111, @cailei19, @h_ray, @levy53071, @libiao2024, @lihaixiao, @mrzack, @wangfeihuo, @wuyuechuan, @xiong_xjun, @zhangfengzhi123, @zhangxubo, @zhangzq131, @zjh_hw .
一个解决方法是 在transformselectstmt 和parseCheckAggregates 时把 order by id 节点产生的target 节点去掉
上面的方案好像没完全解决，主要是看看如何识别 不要走 最大最小优化，select id, max(id1) from k1;只要走optimize_minmax_aggregates 走索引，就不行
自验证OK：
CREATE DATABASE issue8344 DBCOMPATIBILITY 'B';
\c issue8344
CREATE EXTENSION IF NOT EXISTS dolphin;
CREATE TABLE tbkl (
id INTEGER
);
INSERT INTO tbkl VALUES
(1),
(2),
(3);
CREATE INDEX i_id_tbkl ON tbkl(id);
SET enable_seqscan = off;
SET dolphin.sql_mode =
'sql_mode_strict,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length';
EXPLAIN
SELECT count(*)
FROM (
SELECT max(id)
FROM tbkl
ORDER BY id
) AS s;
EXPLAIN VERBOSE
SELECT count(*)
FROM (
SELECT max(id)
FROM tbkl
ORDER BY id
) AS s;
回归版本：7.0.0 B019
回归结论：通过
回归过程：
测试类型
SQL功能
测试版本
6.0.0
问题描述
操作系统和硬件信息
x86服务器
测试环境
企业版主备
被测功能
预置条件
操作步骤
tb=# set enable_seqscan=off;
SET
tb=# \d+ tbkl
WARNING: The info of distribution for the table is not available due to dedicated connection to datanode
CONTEXT: referenced column: distributekey
Table "public.tbkl"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+---------+--------------+-------------
id | integer | | plain | |
Indexes:
"i_id_tbkl" btree (id) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
tb=# set sql_b.sql_mode = 'sql_mode_strict,pipes_as_concat,ansi_quotes,no_zero_date,pad_char_to_full_length';
SET
tb=# explain select count(*) from (select max(id) from tbkl order by id);
QUERY PLAN
Aggregate (cost=0.85..0.86 rows=1 width=8)
-> Sort (cost=0.83..0.84 rows=1 width=0)
Sort Key: public.tbkl.id NULLS FIRST
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.81 rows=1 width=4)
-> Index Only Scan Backward using i_id_tbkl on tbkl (cost=0.00..16273772.30 rows=20008548 width=4)
Index Cond: (id IS NOT NULL)
-> Result (cost=0.00..0.01 rows=1 width=0)
(8 rows)
tb=# explain verbose select count(*) from (select max(id) from tbkl order by id);
QUERY PLAN
Aggregate (cost=0.85..0.86 rows=1 width=8)
Output: count(*)
-> Sort (cost=0.83..0.84 rows=1 width=0)
Output: ($0), public.tbkl.id
Sort Key: public.tbkl.id NULLS FIRST
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.81 rows=1 width=4)
Output: public.tbkl.id
-> Index Only Scan Backward using i_id_tbkl on public.tbkl (cost=0.00..16273772.30 rows=20008548 width=4)
Output: public.tbkl.id
Index Cond: (public.tbkl.id IS NOT NULL)
-> Result (cost=0.00..0.01 rows=1 width=0)
Output: $0, public.tbkl.id
(13 rows)
预期输出
输出结果
实际输出
产生core
日志信息
根本原因是执行计划 result节点 引用了 public.tbkl.id，在子查询的 transformselectstmt节点就存在改target 引用
提单组织
社区用户
测试代码