From ed275aea4270adf9c3ebeeb65a9a9af7b157e3ca Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Tue, 25 Jun 2002 17:27:20 +0000 Subject: The attached patch fixes some spelling mistakes, makes the comments on one of the optimizer functions a lot more clear, adds a summary of the recent KSQO discussion to the comments in the code, adds regression tests for the bug with sequence state Tom fixed recently and another reg. test, and removes some PostQuel legacy stuff: ExecAppend -> ExecInsert, ExecRetrieve -> ExecSelect, etc. This was changed because the elog() messages from this routine are user-visible, so we should be using the SQL terms. Neil Conway --- src/test/regress/expected/alter_table.out | 24 ++++++++--------- src/test/regress/expected/create_misc.out | 12 ++++++++- src/test/regress/expected/domain.out | 8 +++--- src/test/regress/expected/insert.out | 2 +- src/test/regress/expected/select_having.out | 9 +++++++ src/test/regress/output/constraints.source | 42 ++++++++++++++--------------- src/test/regress/sql/create_misc.sql | 7 +++++ src/test/regress/sql/select_having.sql | 4 +++ 8 files changed, 69 insertions(+), 39 deletions(-) (limited to 'src/test') diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 31278f89f06..f2e31919cc5 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -411,7 +411,7 @@ create table atacc1 ( test int ); alter table atacc1 add constraint atacc_test1 check (test>3); -- should fail insert into atacc1 (test) values (2); -ERROR: ExecAppend: rejected due to CHECK constraint atacc_test1 +ERROR: ExecInsert: rejected due to CHECK constraint atacc_test1 -- should succeed insert into atacc1 (test) values (4); drop table atacc1; @@ -436,7 +436,7 @@ create table atacc1 ( test int, test2 int, test3 int); alter table atacc1 add constraint atacc_test1 check (test+test23), test2 int); alter table atacc1 add check (test2>test); -- should fail for $2 insert into atacc1 (test2, test) values (3, 4); -ERROR: ExecAppend: rejected due to CHECK constraint $2 +ERROR: ExecInsert: rejected due to CHECK constraint $2 drop table atacc1; -- inheritance related tests create table atacc1 (test int); @@ -454,11 +454,11 @@ create table atacc3 (test3 int) inherits (atacc1, atacc2); alter table atacc2 add constraint foo check (test2>0); -- fail and then succeed on atacc2 insert into atacc2 (test2) values (-3); -ERROR: ExecAppend: rejected due to CHECK constraint foo +ERROR: ExecInsert: rejected due to CHECK constraint foo insert into atacc2 (test2) values (3); -- fail and then succeed on atacc3 insert into atacc3 (test2) values (-3); -ERROR: ExecAppend: rejected due to CHECK constraint foo +ERROR: ExecInsert: rejected due to CHECK constraint foo insert into atacc3 (test2) values (3); drop table atacc3; drop table atacc2; @@ -470,7 +470,7 @@ create table atacc3 (test3 int) inherits (atacc1, atacc2); alter table only atacc2 add constraint foo check (test2>0); -- fail and then succeed on atacc2 insert into atacc2 (test2) values (-3); -ERROR: ExecAppend: rejected due to CHECK constraint foo +ERROR: ExecInsert: rejected due to CHECK constraint foo insert into atacc2 (test2) values (3); -- both succeed on atacc3 insert into atacc3 (test2) values (-3); @@ -608,7 +608,7 @@ insert into atacc1 (test2, test) values (3, 3); insert into atacc1 (test2, test) values (2, 3); ERROR: Cannot insert a duplicate key into unique index atacc1_pkey insert into atacc1 (test2, test) values (1, NULL); -ERROR: ExecAppend: Fail to add null value in not null attribute test +ERROR: ExecInsert: Fail to add null value in not null attribute test drop table atacc1; -- alter table / alter column [set/drop] not null tests -- try altering system catalogs, should fail @@ -658,9 +658,9 @@ create table parent (a int); create table child (b varchar(255)) inherits (parent); alter table parent alter a set not null; insert into parent values (NULL); -ERROR: ExecAppend: Fail to add null value in not null attribute a +ERROR: ExecInsert: Fail to add null value in not null attribute a insert into child (a, b) values (NULL, 'foo'); -ERROR: ExecAppend: Fail to add null value in not null attribute a +ERROR: ExecInsert: Fail to add null value in not null attribute a alter table parent alter a drop not null; insert into parent values (NULL); insert into child (a, b) values (NULL, 'foo'); @@ -671,14 +671,14 @@ ERROR: ALTER TABLE: Attribute "a" contains NULL values delete from parent; alter table only parent alter a set not null; insert into parent values (NULL); -ERROR: ExecAppend: Fail to add null value in not null attribute a +ERROR: ExecInsert: Fail to add null value in not null attribute a alter table child alter a set not null; insert into child (a, b) values (NULL, 'foo'); -ERROR: ExecAppend: Fail to add null value in not null attribute a +ERROR: ExecInsert: Fail to add null value in not null attribute a delete from child; alter table child alter a set not null; insert into child (a, b) values (NULL, 'foo'); -ERROR: ExecAppend: Fail to add null value in not null attribute a +ERROR: ExecInsert: Fail to add null value in not null attribute a drop table child; drop table parent; -- test setting and removing default values diff --git a/src/test/regress/expected/create_misc.out b/src/test/regress/expected/create_misc.out index 1842314ce9a..9e1faa0a37f 100644 --- a/src/test/regress/expected/create_misc.out +++ b/src/test/regress/expected/create_misc.out @@ -142,7 +142,7 @@ INSERT INTO serialTest VALUES ('foo'); INSERT INTO serialTest VALUES ('bar'); INSERT INTO serialTest VALUES ('force', 100); INSERT INTO serialTest VALUES ('wrong', NULL); -ERROR: ExecAppend: Fail to add null value in not null attribute f2 +ERROR: ExecInsert: Fail to add null value in not null attribute f2 SELECT * FROM serialTest; f1 | f2 -------+----- @@ -151,3 +151,13 @@ SELECT * FROM serialTest; force | 100 (3 rows) +CREATE SEQUENCE sequence_test; +BEGIN; +SELECT nextval('sequence_test'); + nextval +--------- + 1 +(1 row) + +DROP SEQUENCE sequence_test; +END; diff --git a/src/test/regress/expected/domain.out b/src/test/regress/expected/domain.out index 7127215869a..c5615b57572 100644 --- a/src/test/regress/expected/domain.out +++ b/src/test/regress/expected/domain.out @@ -88,14 +88,14 @@ create table nulltest , col4 dnull ); INSERT INTO nulltest DEFAULT VALUES; -ERROR: ExecAppend: Fail to add null value in not null attribute col1 +ERROR: ExecInsert: Fail to add null value in not null attribute col1 INSERT INTO nulltest values ('a', 'b', 'c', 'd'); -- Good INSERT INTO nulltest values (NULL, 'b', 'c', 'd'); -ERROR: ExecAppend: Fail to add null value in not null attribute col1 +ERROR: ExecInsert: Fail to add null value in not null attribute col1 INSERT INTO nulltest values ('a', NULL, 'c', 'd'); -ERROR: ExecAppend: Fail to add null value in not null attribute col2 +ERROR: ExecInsert: Fail to add null value in not null attribute col2 INSERT INTO nulltest values ('a', 'b', NULL, 'd'); -ERROR: ExecAppend: Fail to add null value in not null attribute col3 +ERROR: ExecInsert: Fail to add null value in not null attribute col3 INSERT INTO nulltest values ('a', 'b', 'c', NULL); -- Good select * from nulltest; col1 | col2 | col3 | col4 diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out index e4ecf934171..1a5225b0a36 100644 --- a/src/test/regress/expected/insert.out +++ b/src/test/regress/expected/insert.out @@ -3,7 +3,7 @@ -- create table inserttest (col1 int4, col2 int4 NOT NULL, col3 text default 'testing'); insert into inserttest (col1, col2, col3) values (DEFAULT, DEFAULT, DEFAULT); -ERROR: ExecAppend: Fail to add null value in not null attribute col2 +ERROR: ExecInsert: Fail to add null value in not null attribute col2 insert into inserttest (col2, col3) values (3, DEFAULT); insert into inserttest (col1, col2, col3) values (DEFAULT, 5, DEFAULT); insert into inserttest values (DEFAULT, 5, 'test'); diff --git a/src/test/regress/expected/select_having.out b/src/test/regress/expected/select_having.out index 3f069996fc9..29321e44146 100644 --- a/src/test/regress/expected/select_having.out +++ b/src/test/regress/expected/select_having.out @@ -21,6 +21,15 @@ SELECT b, c FROM test_having 3 | bbbb (2 rows) +-- HAVING is equivalent to WHERE in this case +SELECT b, c FROM test_having + GROUP BY b, c HAVING b = 3; + b | c +---+---------- + 3 | BBBB + 3 | bbbb +(2 rows) + SELECT lower(c), count(c) FROM test_having GROUP BY lower(c) HAVING count(*) > 2 OR min(a) = max(a); lower | count diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source index 8986118fd48..3dafd9a8771 100644 --- a/src/test/regress/output/constraints.source +++ b/src/test/regress/output/constraints.source @@ -62,12 +62,12 @@ CREATE TABLE CHECK_TBL (x int, INSERT INTO CHECK_TBL VALUES (5); INSERT INTO CHECK_TBL VALUES (4); INSERT INTO CHECK_TBL VALUES (3); -ERROR: ExecAppend: rejected due to CHECK constraint check_con +ERROR: ExecInsert: rejected due to CHECK constraint check_con INSERT INTO CHECK_TBL VALUES (2); -ERROR: ExecAppend: rejected due to CHECK constraint check_con +ERROR: ExecInsert: rejected due to CHECK constraint check_con INSERT INTO CHECK_TBL VALUES (6); INSERT INTO CHECK_TBL VALUES (1); -ERROR: ExecAppend: rejected due to CHECK constraint check_con +ERROR: ExecInsert: rejected due to CHECK constraint check_con SELECT '' AS three, * FROM CHECK_TBL; three | x -------+--- @@ -82,13 +82,13 @@ CREATE TABLE CHECK2_TBL (x int, y text, z int, CHECK (x > 3 and y <> 'check failed' and z < 8)); INSERT INTO CHECK2_TBL VALUES (4, 'check ok', -2); INSERT INTO CHECK2_TBL VALUES (1, 'x check failed', -2); -ERROR: ExecAppend: rejected due to CHECK constraint sequence_con +ERROR: ExecInsert: rejected due to CHECK constraint sequence_con INSERT INTO CHECK2_TBL VALUES (5, 'z check failed', 10); -ERROR: ExecAppend: rejected due to CHECK constraint sequence_con +ERROR: ExecInsert: rejected due to CHECK constraint sequence_con INSERT INTO CHECK2_TBL VALUES (0, 'check failed', -2); -ERROR: ExecAppend: rejected due to CHECK constraint sequence_con +ERROR: ExecInsert: rejected due to CHECK constraint sequence_con INSERT INTO CHECK2_TBL VALUES (6, 'check failed', 11); -ERROR: ExecAppend: rejected due to CHECK constraint sequence_con +ERROR: ExecInsert: rejected due to CHECK constraint sequence_con INSERT INTO CHECK2_TBL VALUES (7, 'check ok', 7); SELECT '' AS two, * from CHECK2_TBL; two | x | y | z @@ -107,7 +107,7 @@ CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'), CONSTRAINT INSERT_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8), CHECK (x + z = 0)); INSERT INTO INSERT_TBL(x,z) VALUES (2, -2); -ERROR: ExecAppend: rejected due to CHECK constraint insert_con +ERROR: ExecInsert: rejected due to CHECK constraint insert_con SELECT '' AS zero, * FROM INSERT_TBL; zero | x | y | z ------+---+---+--- @@ -120,13 +120,13 @@ SELECT 'one' AS one, nextval('insert_seq'); (1 row) INSERT INTO INSERT_TBL(y) VALUES ('Y'); -ERROR: ExecAppend: rejected due to CHECK constraint insert_con +ERROR: ExecInsert: rejected due to CHECK constraint insert_con INSERT INTO INSERT_TBL(y) VALUES ('Y'); INSERT INTO INSERT_TBL(x,z) VALUES (1, -2); -ERROR: ExecAppend: rejected due to CHECK constraint $2 +ERROR: ExecInsert: rejected due to CHECK constraint $2 INSERT INTO INSERT_TBL(z,x) VALUES (-7, 7); INSERT INTO INSERT_TBL VALUES (5, 'check failed', -5); -ERROR: ExecAppend: rejected due to CHECK constraint insert_con +ERROR: ExecInsert: rejected due to CHECK constraint insert_con INSERT INTO INSERT_TBL VALUES (7, '!check failed', -7); INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-'); SELECT '' AS four, * FROM INSERT_TBL; @@ -139,9 +139,9 @@ SELECT '' AS four, * FROM INSERT_TBL; (4 rows) INSERT INTO INSERT_TBL(y,z) VALUES ('check failed', 4); -ERROR: ExecAppend: rejected due to CHECK constraint $2 +ERROR: ExecInsert: rejected due to CHECK constraint $2 INSERT INTO INSERT_TBL(x,y) VALUES (5, 'check failed'); -ERROR: ExecAppend: rejected due to CHECK constraint insert_con +ERROR: ExecInsert: rejected due to CHECK constraint insert_con INSERT INTO INSERT_TBL(x,y) VALUES (5, '!check failed'); INSERT INTO INSERT_TBL(y) VALUES ('-!NULL-'); SELECT '' AS six, * FROM INSERT_TBL; @@ -162,7 +162,7 @@ SELECT 'seven' AS one, nextval('insert_seq'); (1 row) INSERT INTO INSERT_TBL(y) VALUES ('Y'); -ERROR: ExecAppend: rejected due to CHECK constraint insert_con +ERROR: ExecInsert: rejected due to CHECK constraint insert_con SELECT 'eight' AS one, currval('insert_seq'); one | currval -------+--------- @@ -193,11 +193,11 @@ CREATE TABLE INSERT_CHILD (cx INT default 42, INHERITS (INSERT_TBL); INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,11); INSERT INTO INSERT_CHILD(x,z,cy) VALUES (7,-7,6); -ERROR: ExecAppend: rejected due to CHECK constraint insert_child_cy +ERROR: ExecInsert: rejected due to CHECK constraint insert_child_cy INSERT INTO INSERT_CHILD(x,z,cy) VALUES (6,-7,7); -ERROR: ExecAppend: rejected due to CHECK constraint $1 +ERROR: ExecInsert: rejected due to CHECK constraint $1 INSERT INTO INSERT_CHILD(x,y,z,cy) VALUES (6,'check failed',-6,7); -ERROR: ExecAppend: rejected due to CHECK constraint insert_con +ERROR: ExecInsert: rejected due to CHECK constraint insert_con SELECT * FROM INSERT_CHILD; x | y | z | cx | cy ---+--------+----+----+---- @@ -227,7 +227,7 @@ SELECT '' AS three, * FROM INSERT_TBL; INSERT INTO INSERT_TBL SELECT * FROM tmp WHERE yd = 'try again'; INSERT INTO INSERT_TBL(y,z) SELECT yd, -7 FROM tmp WHERE yd = 'try again'; INSERT INTO INSERT_TBL(y,z) SELECT yd, -8 FROM tmp WHERE yd = 'try again'; -ERROR: ExecAppend: rejected due to CHECK constraint insert_con +ERROR: ExecInsert: rejected due to CHECK constraint insert_con SELECT '' AS four, * FROM INSERT_TBL; four | x | y | z ------+---+---------------+---- @@ -246,7 +246,7 @@ UPDATE INSERT_TBL SET x = NULL WHERE x = 5; UPDATE INSERT_TBL SET x = 6 WHERE x = 6; UPDATE INSERT_TBL SET x = -z, z = -x; UPDATE INSERT_TBL SET x = z, z = x; -ERROR: ExecReplace: rejected due to CHECK constraint insert_con +ERROR: ExecUpdate: rejected due to CHECK constraint insert_con SELECT * FROM INSERT_TBL; x | y | z ---+---------------+---- @@ -293,7 +293,7 @@ ERROR: Cannot insert a duplicate key into unique index primary_tbl_pkey INSERT INTO PRIMARY_TBL VALUES (4, 'three'); INSERT INTO PRIMARY_TBL VALUES (5, 'one'); INSERT INTO PRIMARY_TBL (t) VALUES ('six'); -ERROR: ExecAppend: Fail to add null value in not null attribute i +ERROR: ExecInsert: Fail to add null value in not null attribute i SELECT '' AS four, * FROM PRIMARY_TBL; four | i | t ------+---+------- @@ -313,7 +313,7 @@ INSERT INTO PRIMARY_TBL VALUES (1, 'three'); INSERT INTO PRIMARY_TBL VALUES (4, 'three'); INSERT INTO PRIMARY_TBL VALUES (5, 'one'); INSERT INTO PRIMARY_TBL (t) VALUES ('six'); -ERROR: ExecAppend: Fail to add null value in not null attribute i +ERROR: ExecInsert: Fail to add null value in not null attribute i SELECT '' AS three, * FROM PRIMARY_TBL; three | i | t -------+---+------- diff --git a/src/test/regress/sql/create_misc.sql b/src/test/regress/sql/create_misc.sql index 078450a7544..2277d5c8b2b 100644 --- a/src/test/regress/sql/create_misc.sql +++ b/src/test/regress/sql/create_misc.sql @@ -217,3 +217,10 @@ INSERT INTO serialTest VALUES ('force', 100); INSERT INTO serialTest VALUES ('wrong', NULL); SELECT * FROM serialTest; + +CREATE SEQUENCE sequence_test; + +BEGIN; +SELECT nextval('sequence_test'); +DROP SEQUENCE sequence_test; +END; diff --git a/src/test/regress/sql/select_having.sql b/src/test/regress/sql/select_having.sql index 44b0329ee5f..28b22d9859f 100644 --- a/src/test/regress/sql/select_having.sql +++ b/src/test/regress/sql/select_having.sql @@ -18,6 +18,10 @@ INSERT INTO test_having VALUES (9, 4, 'CCCC', 'j'); SELECT b, c FROM test_having GROUP BY b, c HAVING count(*) = 1; +-- HAVING is equivalent to WHERE in this case +SELECT b, c FROM test_having + GROUP BY b, c HAVING b = 3; + SELECT lower(c), count(c) FROM test_having GROUP BY lower(c) HAVING count(*) > 2 OR min(a) = max(a); -- cgit v1.2.3