聚合查询在常量 HAVING 条件下结果不一致

【标题描述】:
【测试类型：SQL功能/存储功能/接口功能/工具功能/性能/并发/压力长稳/故障注入/安全/资料/编码规范】【测试版本：x.x.x】 问题描述

【操作系统和硬件信息】(查询命令: cat /etc/system-release, uname -a):

【测试环境】(单机/1主x备x级联备):opengauss/opengauss:7.0.0-RC2.B015-openEuler22.03

【被测功能】:

【测试类型】:

【数据库版本】(查询命令: gaussdb -V):

【预置条件】:

【操作步骤】(请填写详细的操作步骤):
DROP DATABASE IF EXISTS database16;
CREATE DATABASE database16  dbcompatibility 'PG' ;
CREATE UNLOGGED TABLE t0(c0 boolean  PRIMARY KEY UNIQUE, CHECK(t0.c0)) WITH (fillfactor=14, autovacuum_enabled=1, autovacuum_vacuum_scale_factor=0.1, autovacuum_freeze_max_age=966522616, autovacuum_vacuum_threshold=216204360, autovacuum_freeze_min_age=75292265);
CREATE TABLE IF NOT EXISTS t1(c0 TEXT  PRIMARY KEY NULL, c1 money , c2 REAL  DEFAULT (length((((B'110011001100100110100010100001')&(B'1110011000101001011011110010100')))::VARCHAR(754))));
CREATE TABLE IF NOT EXISTS t2(LIKE t1);
CREATE TABLE IF NOT EXISTS t3(LIKE t2);
CREATE UNLOGGED TABLE t4(LIKE t2);
ALTER TABLE  t2 ALTER c2  SET DATA TYPE TEXT;
ANALYZE;
DELETE FROM t3 WHERE ((t3.c1)IS NOT DISTINCT FROM(t3.c1));
UPDATE t4 SET c0='', c2=DEFAULT WHERE ((t4.c1)<=((0.9660137)::MONEY));
COMMENT ON COLUMN t3.c1 IS '61188686';
INSERT INTO t4(c0) VALUES((((('' COLLATE "en_CA.utf8") COLLATE "en_GB.utf8") COLLATE "zh_SG.utf8") COLLATE "en_SG.utf8"));
UPDATE t2 SET c0=DEFAULT WHERE (((-4.04246396E8)::VARCHAR(504))LIKE(t2.c2));
INSERT INTO t2(c2, c0) VALUES('1445798830', ''), ('?97UA', '-1002768976'), ((((((('7g??/D�ku*')||(NULL)))||(CAST(8.1497267E8 AS MONEY))) COLLATE "en_SC.utf8"))::VARCHAR, 'dN');
INSERT INTO t1(c1, c0, c2) VALUES((0.71965736)::MONEY, ((lower(('>T?rS' COLLATE "en_ZM.utf8")))||(inet_client_port())), 0.3864697246411277);
INSERT INTO t4(c0, c1) VALUES('', CAST(0.23080726 AS MONEY)), ('\r)', (0.19789)::MONEY), (((((lower('(154407983,1838706703]'::int4range))::VARCHAR(169) COLLATE "en_AG.utf8"))||(CAST(0.24533069 AS MONEY))), (0.6096909)::MONEY);
INSERT INTO t2(c2, c1, c0) VALUES('''', (1.02190445E9)::MONEY, '');
DROP INDEX IF EXISTS i0 RESTRICT;
ALTER TABLE  t4 FORCE ROW LEVEL SECURITY;
SET gin_fuzzy_search_limit=DEFAULT;
DROP INDEX IF EXISTS i0;
UPDATE t1 SET c1=CAST(0.745251 AS MONEY) WHERE ((NOT ((- (NULL))::BOOLEAN))OR(t1.c0!~t1.c0));
SET CONSTRAINTS ALL DEFERRED;
DROP INDEX IF EXISTS i2, i0 CASCADE;
CREATE UNIQUE INDEX i0 ON t4(c2 DESC NULLS LAST);
INSERT INTO t3(c0, c2, c1) VALUES('gPp8?#��', 0.35611507, CAST(0.67273813 AS MONEY)), ((((CAST((('[-1003033922,-252079077]'::int4range)-('[1117850561,1201634052)'::int4range)) AS VARCHAR) COLLATE "en_SC.utf8"))||(0.7098657952027526)), 0.5927187280104332, CAST(0.44576284 AS MONEY)), (((CAST(0.5185723 AS MONEY))::VARCHAR COLLATE "en_ZW.utf8"), 0.24375021263751495, CAST(0.17254746 AS MONEY));
UPDATE t0 SET c0=FALSE WHERE t0.c0;
INSERT INTO t0(c0) VALUES(((CAST(0.7819588853662728 AS INT))!=(- (((strpos('<FqMeh', 'Gu'))*(masklen('189.157.103.7')))))));
SET SESSION geqo_generations=DEFAULT;
ALTER TABLE  t4 SET WITHOUT CLUSTER, ALTER c1  TYPE DECIMAL, CLUSTER ON i0, ALTER c0 SET NOT NULL, DISABLE ROW LEVEL SECURITY;
INSERT INTO t4(c0, c2) VALUES('s', 1.024136059E9);
SET seq_page_cost=DEFAULT;
INSERT INTO t4(c2, c1, c0) VALUES(0.44584176403748055, 0.9440953627418128, ''), (0.09406641164814289, 0.45772138942464113, (lower(('' COLLATE "zh_SG.utf8")) COLLATE "en_SG.utf8")), (-1.468176583E9, 0.0036014494143322695, 'dF|Lr');
COMMIT;

-- Query: "SELECT SUM(t3.c2) FROM t3, t1, t2* LEFT OUTER JOIN t0 ON t0.c0 CROSS JOIN t4* GROUP BY t1.c1"; It misses: "[38.162685]"\r\n-- Query: "SELECT ALL SUM(t3.c2) FROM t3, t1, t2* LEFT OUTER JOIN t0 ON t0.c0 CROSS JOIN t4 GROUP BY t1.c1 HAVING (CAST(0.8563384 AS MONEY)) IS NOT NULL;SELECT SUM(t3.c2) FROM t3, t1, t2 LEFT OUTER JOIN t0 ON t0.c0 CROSS JOIN t4* GROUP BY t1.c1 HAVING NOT (((0.8563384)::MONEY) IS NOT NULL);SELECT ALL SUM(t3.c2) FROM t3, t1, t2 LEFT OUTER JOIN t0 ON t0.c0 CROSS JOIN t4 GROUP BY t1.c1 HAVING ((CAST(0.8563384 AS MONEY)) IS NOT NULL) IS NULL"; It misses: "[38.162704]";
SELECT SUM(t3.c2) FROM t3, t1, t2* LEFT OUTER JOIN t0 ON t0.c0 CROSS JOIN t4* GROUP BY t1.c1;
SELECT ALL SUM(t3.c2) FROM t3, t1, t2* LEFT OUTER JOIN t0 ON t0.c0 CROSS JOIN t4 GROUP BY t1.c1 HAVING (CAST(0.8563384 AS MONEY)) IS NOT NULL UNION ALL SELECT SUM(t3.c2) FROM t3, t1, t2 LEFT OUTER JOIN t0 ON t0.c0 CROSS JOIN t4* GROUP BY t1.c1 HAVING NOT (((0.8563384)::MONEY) IS NOT NULL)UNION ALL SELECT ALL SUM(t3.c2) FROM t3, t1, t2 LEFT OUTER JOIN t0 ON t0.c0 CROSS JOIN t4 GROUP BY t1.c1 HAVING ((CAST(0.8563384 AS MONEY)) IS NOT NULL) IS NULL;

【预期输出】: 两查询结果一致

【实际输出】: 查询1--38.162685
             查询2--38.162704

【原因分析】:
1. 这个问题的根因
2. 问题推断过程
3. 还有哪些原因可能造成类似现象
4. 该问题是否有临时规避措施
5. 问题解决方案
6. 预计修复问题时间

【日志信息】(请附上日志文件、截图、coredump信息):

【测试代码】:

openGauss/openGauss-server

内容风险标识

评论 (0)

openGauss/openGauss-server .gitee-modal { width: 500px !important; }

内容风险标识