适用范围
openGauss 5.0.0 and later
问题概述
exists condition copy到gsql 回显异常
1. gsql执行回显异常
drop table matched;
drop table t_ext_buy;
CREATE TABLE matched (
code character varying(12) NOT NULL,
source_id numeric NOT NULL,
source_account numeric NOT NULL,
program_id character varying(12) NOT NULL,
transaction_date timestamp without time zone NOT NULL,
transaction_numeric numeric NOT NULL,
value1 numeric NOT NULL,
function1 numeric NOT NULL,
indicator_1 character varying(1),
indicator_2 character varying(1),
comments_1 character varying(52),
comments_2 character varying(52)
)
WITH (orientation=row, compression=no);
ALTER TABLE matched ADD CONSTRAINT matched_pk PRIMARY KEY (code, source_id, source_account, program_id, transaction_date, transaction_numeric, value1, function1);
CREATE TABLE t_ext_buy (
code character varying(12),
source_id numeric,
source_account numeric,
program_id character varying(12),
transaction_date timestamp without time zone,
transaction_numeric numeric,
value1 numeric,
function1 numeric,
indicator character varying(1),
comments character varying(50)
)
WITH (orientation=row, compression=no);
[omm@og1 ~]$ gsql -d postgres -r -U team1 -W Root_1234
gsql ((openGauss 5.0.1 build 4b650ff4) compiled at 2023-08-25 18:03:57 commit 0 last mr debug)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=> select b.code,
openGauss-> b.source_id,
openGauss-> b.source_account,
openGauss-> b.program_id,
openGauss-> b.transaction_date,
openGauss-> b.transaction_numeric,
openGauss-> b.value1,
openGauss-> b.function1,
openGauss-> b.indicator,
openGauss-> b.comments
openGauss-> from t_ext_buy b
openGauss-> where exists (select 1 from matched m
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> where m.code
openGauss(> where m.code = b.code
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> and m.source_id = b.source_id
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> and m.source_account = b.source_account
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> and m.program_id = b.program_id
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> and m.transaction_date = b.transaction_date
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> and m.transaction_numeric = b.transaction_numeric
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> and m.value1
openGauss(> and m.value1 = b.value1
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> and m.function1 = b.function1);
code | source_id | source_account | program_id | transaction_date | transaction_numeric | value1 | function1 | indicator | comments
------+-----------+----------------+------------+------------------+---------------------+--------+-----------+-----------+----------
(0 rows)
openGauss=> q
[omm@og1 ~]$
2. gsql执行sql文件回显正常
[omm@og1 ~]$ cat /home/omm/query2.sql
select b.code,
b.source_id,
b.source_account,
b.program_id,
b.transaction_date,
b.transaction_numeric,
b.value1,
b.function1,
b.indicator,
b.comments
from t_ext_buy b
where exists (select 1 from matched m
where m.code = b.code
and m.source_id = b.source_id
and m.source_account = b.source_account
and m.program_id = b.program_id
and m.transaction_date = b.transaction_date
and m.transaction_numeric = b.transaction_numeric
and m.value1 = b.value1
and m.function1 = b.function1);
[omm@og1 ~]$
[omm@og1 ~]$ gsql -d postgres -r -U team1 -W Root_1234 -f /home/omm/query2.sql
code | source_id | source_account | program_id | transaction_date | transaction_numeric | value1 | function1 | indicator | comments
------+-----------+----------------+------------+------------------+---------------------+--------+-----------+-----------+----------
(0 rows)
total time: 3 ms
[omm@og1 ~]$
3. pg15 测试结果
[postgres@pg15 ~]$ psql
psql (15.0)
Type "help" for help.
postgres=#
postgres=# CREATE TABLE matched (
postgres(# code character varying(12) NOT NULL,
postgres(# source_id numeric NOT NULL,
postgres(# source_account numeric NOT NULL,
postgres(# program_id character varying(12) NOT NULL,
postgres(# transaction_date timestamp without time zone NOT NULL,
postgres(# transaction_numeric numeric NOT NULL,
postgres(# value1 numeric NOT NULL,
postgres(# function1 numeric NOT NULL,
postgres(# indicator_1 character varying(1),
postgres(# indicator_2 character varying(1),
postgres(# comments_1 character varying(52),
postgres(# comments_2 character varying(52)
postgres(# );
ALTER TABLE matched ADD CONSTRAINT matched_pk PRIMARY KEY (code, source_id, source_account, program_id, transaction_date, transaction_numeric, value1, function1);
CREATE TABLE t_ext_buy (
code character varying(12),
source_id numeric,
source_account numeric,
program_id character varying(12),
transaction_date timestamp without time zone,
transaction_numeric numeric,
value1 numeric,
function1 numeric,
indicator character varying(1),
comments character varying(50)
);
select b.code,
b.source_id,
b.source_account,
b.program_id,
b.transaction_date,
b.transaction_numeric,
b.value1,
b.function1,
b.indicator,
b.comments
from t_ext_buy b
where exists (select 1 from matched m
where m.code = b.code
and m.source_id = b.source_id
and m.source_account = b.source_account
and m.program_id = b.program_id
and m.transaction_date = b.transaction_date
and m.transaction_numeric = b.transaction_numeric
and m.value1 = b.value1
and m.function1 = b.function1);CREATE TABLE
postgres=# ALTER TABLE matched ADD CONSTRAINT matched_pk PRIMARY KEY (code, source_id, source_account, program_id, transaction_date, transaction_numeric, value1, function1);
ALTER TABLE
postgres=#
postgres=# CREATE TABLE t_ext_buy (
postgres(# code character varying(12),
postgres(# source_id numeric,
postgres(# source_account numeric,
postgres(# program_id character varying(12),
postgres(# transaction_date timestamp without time zone,
postgres(# transaction_numeric numeric,
postgres(# value1 numeric,
postgres(# function1 numeric,
postgres(# indicator character varying(1),
postgres(# comments character varying(50)
postgres(# );
CREATE TABLE
postgres=#
postgres=# select b.code,
postgres-# b.source_id,
postgres-# b.source_account,
postgres-# b.program_id,
postgres-# b.transaction_date,
postgres-# b.transaction_numeric,
postgres-# b.value1,
postgres-# b.function1,
postgres-# b.indicator,
postgres-# b.comments
postgres-# from t_ext_buy b
postgres-# where exists (select 1 from matched m
postgres(#
postgres(#
postgres(#
postgres(# where m.code
postgres(# where m.code = b.code
postgres(#
postgres(# and m.source_id = b.source_id
postgres(#
postgres(# and m.source_account = b.source_account
postgres(#
postgres(# and m.program_id = b.program_id
postgres(#
postgres(# and m.transaction_date = b.transaction_date
postgres(#
postgres(# and m.transaction_numeric = b.transaction_numeric
postgres(#
postgres(# and m.value1
postgres(# and m.value1 = b.value1
postgres(#
postgres(# and m.function1 = b.function1);
code | source_id | source_account | program_id | transaction_date | transaction_numeric | value1 | function1 | indicator | comments
------+-----------+----------------+------------+------------------+---------------------+--------+-----------+-----------+----------
(0 rows)
postgres=#
问题原因
readline后的补全能力的问题。测试一下不在-r是否与pg表现一致
解决方案
不在-r时，显示结果正常
[omm@og1 team1]$ gsql -d postgres -U team1 -W Root_1234
gsql ((openGauss 5.0.1 build 4b650ff4) compiled at 2023-08-25 18:03:57 commit 0 last mr debug)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=> select b.code,
b.source_id,
b.source_account,
b.program_id,
b.transaction_date,
b.transaction_numeric,
openGauss-> openGauss-> openGauss-> openGauss-> openGauss-> openGauss-> b.value1,
b.function1,
openGauss-> openGauss-> b.indicator,
b.comments
openGauss-> openGauss-> from t_ext_buy b
openGauss-> where exists (select 1 from matched m
where m.code = b.code
openGauss(> openGauss(> and m.source_id = b.source_id
and m.source_account = b.source_account
openGauss(> openGauss(> and m.program_id = b.program_id
openGauss(> and m.transaction_date = b.transaction_date
openGauss(> and m.transaction_numeric = b.transaction_numeric
and m.value1 = b.value1
openGauss(> openGauss(> and m.function1 = b.function1);
^CCancel request sent
ERROR: canceling statement due to user request
openGauss=> q
[omm@og1 team1]$ gsql -d postgres -r -U team1 -W Root_1234
gsql ((openGauss 5.0.1 build 4b650ff4) compiled at 2023-08-25 18:03:57 commit 0 last mr debug)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
openGauss=> select b.code,
b.source_id,
b.source_account,
b.program_id,
b.transaction_date,
b.transaction_numeric,
openGauss-> b.source_id,
openGauss-> b.source_account,
openGauss-> b.program_id,
openGauss-> b.transaction_date,
openGauss-> b.transaction_numeric,
openGauss-> b.value1,
openGauss-> b.function1,
openGauss-> b.indicator,
openGauss-> b.comments
openGauss-> from t_ext_buy b
openGauss-> where exists (select 1 from matched m
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> where m.code
openGauss(> where m.code = b.code
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> and m.source_id = b.source_id
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> and m.source_account = b.source_account
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> and m.program_id = b.program_id
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> and m.transaction_date = b.transaction_date
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> and m.transaction_numeric = b.transaction_numeric
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> and m.value1
openGauss(> and m.value1 = b.value1
openGauss(>
ABORT BEGIN CLUSTER CREATE DELETE FROM END GRANT LOCK REASSIGN RESET SECURITY LABEL START UPDATE
ALTER CALL COMMENT CURSOR DISCARD EXECUTE INSERT MOVE REFRESH REVOKE SELECT TABLE VACUUM
ANALYSE CHECKPOINT COMMIT DEALLOCATE DO EXPLAIN LISTEN NOTIFY REINDEX ROLLBACK SET TRUNCATE VALUES
ANALYZE CLOSE COPY DECLARE DROP FETCH LOAD PREPARE RELEASE SAVEPOINT SHOW UNLISTEN WITH
openGauss(> and m.function1 = b.function1);
^CCancel request sent
ERROR: canceling statement due to user request
openGauss=> q
[omm@og1 team1]$
回归通过
参考文档
https://gitee.com/opengauss/openGauss-server/issues/I7XS9S
