From ce38949ba23ab311f274aa4196be09d18d30e5a6 Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Sat, 11 Mar 2017 15:36:50 -0800 Subject: [PATCH] Improve expression evaluation test coverage. Upcoming patches are revamping expression evaluation significantly. It therefore seems prudent to try to ensure that the coverage of the existing evaluation code is high. This commit adds coverage for the cases that can reasonably be tested. There's still a bunch of unreachable error messages and such, but otherwise this achieves nearly full regression test coverage (with the exception of the unused GetAttributeByNum/GetAttributeByName). Author: Andres Freund Discussion: https://postgr.es/m/20170310194021.ek4bs4bl2khxkmll@alap3.anarazel.de --- src/backend/executor/execQual.c | 1 + src/test/regress/expected/arrays.out | 81 +++++++++++++++++++++++ src/test/regress/expected/boolean.out | 24 +++++++ src/test/regress/expected/case.out | 8 +++ src/test/regress/expected/expressions.out | 77 +++++++++++++++++++++ src/test/regress/expected/inherit.out | 6 ++ src/test/regress/expected/privileges.out | 37 +++++++++++ src/test/regress/expected/rowtypes.out | 40 +++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/serial_schedule | 1 + src/test/regress/sql/arrays.sql | 33 +++++++++ src/test/regress/sql/boolean.sql | 20 ++++++ src/test/regress/sql/case.sql | 5 ++ src/test/regress/sql/expressions.sql | 36 ++++++++++ src/test/regress/sql/inherit.sql | 1 + src/test/regress/sql/privileges.sql | 12 ++++ src/test/regress/sql/rowtypes.sql | 23 +++++++ 17 files changed, 406 insertions(+), 1 deletion(-) create mode 100644 src/test/regress/expected/expressions.out create mode 100644 src/test/regress/sql/expressions.sql diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQual.c index 4566219ca8..4ff0188bcc 100644 --- a/src/backend/executor/execQual.c +++ b/src/backend/executor/execQual.c @@ -392,6 +392,7 @@ ExecEvalArrayRef(ArrayRefExprState *astate, } else { + /* this is currently unreachable */ econtext->caseValue_datum = array_get_slice(array_source, i, upper.indx, lower.indx, diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 8c5050577b..c730563f03 100644 --- a/src/test/regress/expected/arrays.out +++ b/src/test/regress/expected/arrays.out @@ -187,6 +187,44 @@ select ('[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[])[1:2][2]; {{5,6},{8,9}} (1 row) +-- +-- check subscription corner cases +-- +-- More subscripts than MAXDIMS(6) +SELECT ('{}'::int[])[1][2][3][4][5][6][7]; +ERROR: number of array dimensions (7) exceeds the maximum allowed (6) +-- NULL index yields NULL when selecting +SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][NULL][1]; + int4 +------ + +(1 row) + +SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][NULL:1][1]; + int4 +------ + +(1 row) + +SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][1:NULL][1]; + int4 +------ + +(1 row) + +-- NULL index in assignment is an error +UPDATE arrtest + SET c[NULL] = '{"can''t assign"}' + WHERE array_dims(c) is not null; +ERROR: array subscript in assignment must not be null +UPDATE arrtest + SET c[NULL:1] = '{"can''t assign"}' + WHERE array_dims(c) is not null; +ERROR: array subscript in assignment must not be null +UPDATE arrtest + SET c[1:NULL] = '{"can''t assign"}' + WHERE array_dims(c) is not null; +ERROR: array subscript in assignment must not be null -- test slices with empty lower and/or upper index CREATE TEMP TABLE arrtest_s ( a int2[], @@ -263,6 +301,36 @@ SELECT f1[:1] FROM POINT_TBL; ERROR: slices of fixed-length arrays not implemented SELECT f1[:] FROM POINT_TBL; ERROR: slices of fixed-length arrays not implemented +-- subscript assignments to fixed-width result in NULL if previous value is NULL +UPDATE point_tbl SET f1[0] = 10 WHERE f1 IS NULL RETURNING *; + f1 +---- + +(1 row) + +INSERT INTO point_tbl(f1[0]) VALUES(0) RETURNING *; + f1 +---- + +(1 row) + +-- NULL assignments get ignored +UPDATE point_tbl SET f1[0] = NULL WHERE f1::text = '(10,10)'::point::text RETURNING *; + f1 +--------- + (10,10) +(1 row) + +-- but non-NULL subscript assignments work +UPDATE point_tbl SET f1[0] = -10, f1[1] = -10 WHERE f1::text = '(10,10)'::point::text RETURNING *; + f1 +----------- + (-10,-10) +(1 row) + +-- but not to expand the range +UPDATE point_tbl SET f1[3] = 10 WHERE f1::text = '(-10,-10)'::point::text RETURNING *; +ERROR: array subscript out of range -- -- test array extension -- @@ -1099,6 +1167,12 @@ SELECT CAST(ARRAY[[[[[['a','bb','ccc']]]]]] as text[]) as "{{{{{{a,bb,ccc}}}}}}" {{{{{{a,bb,ccc}}}}}} (1 row) +SELECT NULL::text[]::int[] AS "NULL"; + NULL +------ + +(1 row) + -- scalar op any/all (array) select 33 = any ('{1,2,3}'); ?column? @@ -1214,6 +1288,13 @@ select 33 = all ('{33,null,33}'); (1 row) +-- nulls later in the bitmap +SELECT -1 != ALL(ARRAY(SELECT NULLIF(g.i, 900) FROM generate_series(1,1000) g(i))); + ?column? +---------- + +(1 row) + -- test indexes on arrays create temp table arr_tbl (f1 int[] unique); insert into arr_tbl values ('{1,2,3}'); diff --git a/src/test/regress/expected/boolean.out b/src/test/regress/expected/boolean.out index 463278dca1..a6e6000c66 100644 --- a/src/test/regress/expected/boolean.out +++ b/src/test/regress/expected/boolean.out @@ -442,6 +442,29 @@ SELECT '' AS "Not True", f1 | f (4 rows) +-- +-- Tests for BooleanTest +-- +CREATE TABLE BOOLTBL3 (d text, b bool, o int); +INSERT INTO BOOLTBL3 (d, b, o) VALUES ('true', true, 1); +INSERT INTO BOOLTBL3 (d, b, o) VALUES ('false', false, 2); +INSERT INTO BOOLTBL3 (d, b, o) VALUES ('null', null, 3); +SELECT + d, + b IS TRUE AS istrue, + b IS NOT TRUE AS isnottrue, + b IS FALSE AS isfalse, + b IS NOT FALSE AS isnotfalse, + b IS UNKNOWN AS isunknown, + b IS NOT UNKNOWN AS isnotunknown +FROM booltbl3 ORDER BY o; + d | istrue | isnottrue | isfalse | isnotfalse | isunknown | isnotunknown +-------+--------+-----------+---------+------------+-----------+-------------- + true | t | f | f | t | f | t + false | f | t | t | f | f | t + null | f | t | f | t | t | f +(3 rows) + -- -- Clean up -- Many tables are retained by the regression test, but these do not seem @@ -450,3 +473,4 @@ SELECT '' AS "Not True", f1 -- DROP TABLE BOOLTBL1; DROP TABLE BOOLTBL2; +DROP TABLE BOOLTBL3; diff --git a/src/test/regress/expected/case.out b/src/test/regress/expected/case.out index 09d5516fb5..4cc4851475 100644 --- a/src/test/regress/expected/case.out +++ b/src/test/regress/expected/case.out @@ -72,6 +72,14 @@ SELECT '6' AS "One", 6 | 6 (1 row) +SELECT '7' AS "None", + CASE WHEN random() < 0 THEN 1 + END AS "NULL on no matches"; + None | NULL on no matches +------+-------------------- + 7 | +(1 row) + -- Constant-expression folding shouldn't evaluate unreachable subexpressions SELECT CASE WHEN 1=0 THEN 1/0 WHEN 1=1 THEN 1 ELSE 2/0 END; case diff --git a/src/test/regress/expected/expressions.out b/src/test/regress/expected/expressions.out new file mode 100644 index 0000000000..719455b0eb --- /dev/null +++ b/src/test/regress/expected/expressions.out @@ -0,0 +1,77 @@ +-- +-- expression evaluated tests that don't fit into a more specific file +-- +-- +-- Tests for SQLVAlueFunction +-- +-- current_date (always matches because of transactional behaviour) +SELECT date(now())::text = current_date::text; + ?column? +---------- + t +(1 row) + +-- current_time / localtime +SELECT now()::timetz::text = current_time::text; + ?column? +---------- + t +(1 row) + +SELECT now()::time::text = localtime::text; + ?column? +---------- + t +(1 row) + +-- current_timestamp / localtimestamp (always matches because of transactional behaviour) +SELECT current_timestamp = NOW(); + ?column? +---------- + t +(1 row) + +-- precision +SELECT length(current_timestamp::text) >= length(current_timestamp(0)::text); + ?column? +---------- + t +(1 row) + +-- localtimestamp +SELECT now()::timestamp::text = localtimestamp::text; + ?column? +---------- + t +(1 row) + +-- current_role/user/user is tested in rolnames.sql +-- current database / catalog +SELECT current_catalog = current_database(); + ?column? +---------- + t +(1 row) + +-- current_schema +SELECT current_schema; + current_schema +---------------- + public +(1 row) + +SET search_path = 'notme'; +SELECT current_schema; + current_schema +---------------- + +(1 row) + +SET search_path = 'pg_catalog'; +SELECT current_schema; + current_schema +---------------- + pg_catalog +(1 row) + +RESET search_path; diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index 795d9f575c..6494b205c4 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -710,6 +710,12 @@ select derived::base from derived; (0) (1 row) +select NULL::derived::base; + base +------ + +(1 row) + drop table derived; drop table base; create table p1(ff1 int); diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 8ac46ecef2..720675032a 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -586,6 +586,43 @@ ERROR: must be owner of function testfunc1 DROP FUNCTION testfunc1(int); -- ok -- restore to sanity GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC; +-- verify privilege checks on coercions +BEGIN; +SELECT NULL::int4[]::int8[]; + int8 +------ + +(1 row) + +SELECT '{1}'::int4[]::int8[]; + int8 +------ + {1} +(1 row) + +REVOKE ALL ON FUNCTION int8(integer) FROM PUBLIC; +SELECT NULL::int4[]::int8[]; + int8 +------ + +(1 row) + +SELECT '{1}'::int4[]::int8[]; --superuser, suceed + int8 +------ + {1} +(1 row) + +SET SESSION AUTHORIZATION regress_user4; +SELECT NULL::int4[]::int8[]; --other user, no elements to convert + int8 +------ + +(1 row) + +SELECT '{1}'::int4[]::int8[]; --other user, fail +ERROR: permission denied for function int8 +ROLLBACK; -- privileges on types -- switch to superuser \c - diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out index 25b08281c8..4acbc9aac8 100644 --- a/src/test/regress/expected/rowtypes.out +++ b/src/test/regress/expected/rowtypes.out @@ -711,3 +711,43 @@ select r, r is null as isnull, r is not null as isnotnull from r; (,) | t | f (6 rows) +-- +-- Tests for component access / FieldSelect +-- +CREATE TABLE compositetable(a text, b text) WITH OIDS; +INSERT INTO compositetable(a, b) VALUES('fa', 'fb'); +-- composite type columns can't directly be accessed (error) +SELECT d.a FROM (SELECT compositetable AS d FROM compositetable) s; +ERROR: missing FROM-clause entry for table "d" +LINE 1: SELECT d.a FROM (SELECT compositetable AS d FROM compositeta... + ^ +-- but can be accessed with proper parens +SELECT (d).a, (d).b FROM (SELECT compositetable AS d FROM compositetable) s; + a | b +----+---- + fa | fb +(1 row) + +-- oids can't be accessed in composite types (error) +SELECT (d).oid FROM (SELECT compositetable AS d FROM compositetable) s; +ERROR: column "oid" not found in data type compositetable +LINE 1: SELECT (d).oid FROM (SELECT compositetable AS d FROM composi... + ^ +-- accessing non-existing column in NULL datum errors out +SELECT (NULL::compositetable).nonexistant; +ERROR: column "nonexistant" not found in data type compositetable +LINE 1: SELECT (NULL::compositetable).nonexistant; + ^ +-- existing column in a NULL composite yield NULL +SELECT (NULL::compositetable).a; + a +--- + +(1 row) + +-- oids can't be accessed in composite types (error) +SELECT (NULL::compositetable).oid; +ERROR: column "oid" not found in data type compositetable +LINE 1: SELECT (NULL::compositetable).oid; + ^ +DROP TABLE compositetable; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 13bf49445b..9f38349e90 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -30,7 +30,7 @@ test: point lseg line box path polygon circle date time timetz timestamp timesta # geometry depends on point, lseg, box, path, polygon and circle # horology depends on interval, timetz, timestamp, timestamptz, reltime and abstime # ---------- -test: geometry horology regex oidjoins type_sanity opr_sanity +test: geometry horology regex oidjoins type_sanity opr_sanity expressions # ---------- # These four each depend on the previous one diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 645ab9b2dc..2987b24ebb 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -49,6 +49,7 @@ test: regex test: oidjoins test: type_sanity test: opr_sanity +test: expressions test: insert test: insert_conflict test: create_function_1 diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql index 9de7207563..25dd4e2c6d 100644 --- a/src/test/regress/sql/arrays.sql +++ b/src/test/regress/sql/arrays.sql @@ -103,6 +103,26 @@ select ('{{1,2,3},{4,5,6},{7,8,9}}'::int[])[1:2][2]; select '[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[]; select ('[0:2][0:2]={{1,2,3},{4,5,6},{7,8,9}}'::int[])[1:2][2]; +-- +-- check subscription corner cases +-- +-- More subscripts than MAXDIMS(6) +SELECT ('{}'::int[])[1][2][3][4][5][6][7]; +-- NULL index yields NULL when selecting +SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][NULL][1]; +SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][NULL:1][1]; +SELECT ('{{{1},{2},{3}},{{4},{5},{6}}}'::int[])[1][1:NULL][1]; +-- NULL index in assignment is an error +UPDATE arrtest + SET c[NULL] = '{"can''t assign"}' + WHERE array_dims(c) is not null; +UPDATE arrtest + SET c[NULL:1] = '{"can''t assign"}' + WHERE array_dims(c) is not null; +UPDATE arrtest + SET c[1:NULL] = '{"can''t assign"}' + WHERE array_dims(c) is not null; + -- test slices with empty lower and/or upper index CREATE TEMP TABLE arrtest_s ( a int2[], @@ -134,6 +154,16 @@ SELECT f1[0:] FROM POINT_TBL; SELECT f1[:1] FROM POINT_TBL; SELECT f1[:] FROM POINT_TBL; +-- subscript assignments to fixed-width result in NULL if previous value is NULL +UPDATE point_tbl SET f1[0] = 10 WHERE f1 IS NULL RETURNING *; +INSERT INTO point_tbl(f1[0]) VALUES(0) RETURNING *; +-- NULL assignments get ignored +UPDATE point_tbl SET f1[0] = NULL WHERE f1::text = '(10,10)'::point::text RETURNING *; +-- but non-NULL subscript assignments work +UPDATE point_tbl SET f1[0] = -10, f1[1] = -10 WHERE f1::text = '(10,10)'::point::text RETURNING *; +-- but not to expand the range +UPDATE point_tbl SET f1[3] = 10 WHERE f1::text = '(-10,-10)'::point::text RETURNING *; + -- -- test array extension -- @@ -316,6 +346,7 @@ SELECT ARRAY[1,2,3]::text[]::int[]::float8[] is of (float8[]) as "TRUE"; SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] AS "{{a,bc},{def,hijk}}"; SELECT ARRAY[['a','bc'],['def','hijk']]::text[]::varchar[] is of (varchar[]) as "TRUE"; SELECT CAST(ARRAY[[[[[['a','bb','ccc']]]]]] as text[]) as "{{{{{{a,bb,ccc}}}}}}"; +SELECT NULL::text[]::int[] AS "NULL"; -- scalar op any/all (array) select 33 = any ('{1,2,3}'); @@ -341,6 +372,8 @@ select 33 = all (null::int[]); select null::int = all ('{1,2,3}'); select 33 = all ('{1,null,3}'); select 33 = all ('{33,null,33}'); +-- nulls later in the bitmap +SELECT -1 != ALL(ARRAY(SELECT NULLIF(g.i, 900) FROM generate_series(1,1000) g(i))); -- test indexes on arrays create temp table arr_tbl (f1 int[] unique); diff --git a/src/test/regress/sql/boolean.sql b/src/test/regress/sql/boolean.sql index 5c46fb92fc..cbf335467b 100644 --- a/src/test/regress/sql/boolean.sql +++ b/src/test/regress/sql/boolean.sql @@ -201,6 +201,24 @@ SELECT '' AS "Not True", f1 FROM BOOLTBL2 WHERE f1 IS NOT TRUE; +-- +-- Tests for BooleanTest +-- +CREATE TABLE BOOLTBL3 (d text, b bool, o int); +INSERT INTO BOOLTBL3 (d, b, o) VALUES ('true', true, 1); +INSERT INTO BOOLTBL3 (d, b, o) VALUES ('false', false, 2); +INSERT INTO BOOLTBL3 (d, b, o) VALUES ('null', null, 3); + +SELECT + d, + b IS TRUE AS istrue, + b IS NOT TRUE AS isnottrue, + b IS FALSE AS isfalse, + b IS NOT FALSE AS isnotfalse, + b IS UNKNOWN AS isunknown, + b IS NOT UNKNOWN AS isnotunknown +FROM booltbl3 ORDER BY o; + -- -- Clean up -- Many tables are retained by the regression test, but these do not seem @@ -211,3 +229,5 @@ SELECT '' AS "Not True", f1 DROP TABLE BOOLTBL1; DROP TABLE BOOLTBL2; + +DROP TABLE BOOLTBL3; diff --git a/src/test/regress/sql/case.sql b/src/test/regress/sql/case.sql index a7ae7b4a9e..59268f8cdf 100644 --- a/src/test/regress/sql/case.sql +++ b/src/test/regress/sql/case.sql @@ -58,6 +58,11 @@ SELECT '6' AS "One", ELSE 7 END AS "Two WHEN with default"; + +SELECT '7' AS "None", + CASE WHEN random() < 0 THEN 1 + END AS "NULL on no matches"; + -- Constant-expression folding shouldn't evaluate unreachable subexpressions SELECT CASE WHEN 1=0 THEN 1/0 WHEN 1=1 THEN 1 ELSE 2/0 END; SELECT CASE 1 WHEN 0 THEN 1/0 WHEN 1 THEN 1 ELSE 2/0 END; diff --git a/src/test/regress/sql/expressions.sql b/src/test/regress/sql/expressions.sql new file mode 100644 index 0000000000..3427fdfdd7 --- /dev/null +++ b/src/test/regress/sql/expressions.sql @@ -0,0 +1,36 @@ +-- +-- expression evaluated tests that don't fit into a more specific file +-- + +-- +-- Tests for SQLVAlueFunction +-- + + +-- current_date (always matches because of transactional behaviour) +SELECT date(now())::text = current_date::text; + + +-- current_time / localtime +SELECT now()::timetz::text = current_time::text; +SELECT now()::time::text = localtime::text; + +-- current_timestamp / localtimestamp (always matches because of transactional behaviour) +SELECT current_timestamp = NOW(); +-- precision +SELECT length(current_timestamp::text) >= length(current_timestamp(0)::text); +-- localtimestamp +SELECT now()::timestamp::text = localtimestamp::text; + +-- current_role/user/user is tested in rolnames.sql + +-- current database / catalog +SELECT current_catalog = current_database(); + +-- current_schema +SELECT current_schema; +SET search_path = 'notme'; +SELECT current_schema; +SET search_path = 'pg_catalog'; +SELECT current_schema; +RESET search_path; diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index 836ec22c20..e3e9e34895 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -194,6 +194,7 @@ create table base (i integer); create table derived () inherits (base); insert into derived (i) values (0); select derived::base from derived; +select NULL::derived::base; drop table derived; drop table base; diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index 3d74abf043..e3275febea 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -398,6 +398,18 @@ DROP FUNCTION testfunc1(int); -- ok -- restore to sanity GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC; +-- verify privilege checks on coercions +BEGIN; +SELECT NULL::int4[]::int8[]; +SELECT '{1}'::int4[]::int8[]; +REVOKE ALL ON FUNCTION int8(integer) FROM PUBLIC; +SELECT NULL::int4[]::int8[]; +SELECT '{1}'::int4[]::int8[]; --superuser, suceed +SET SESSION AUTHORIZATION regress_user4; +SELECT NULL::int4[]::int8[]; --other user, no elements to convert +SELECT '{1}'::int4[]::int8[]; --other user, fail +ROLLBACK; + -- privileges on types -- switch to superuser diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowtypes.sql index a62dee2ef8..0d9c62b486 100644 --- a/src/test/regress/sql/rowtypes.sql +++ b/src/test/regress/sql/rowtypes.sql @@ -310,3 +310,26 @@ with r(a,b) as (values (1,row(1,2)), (1,row(null,null)), (1,null), (null,row(1,2)), (null,row(null,null)), (null,null) ) select r, r is null as isnull, r is not null as isnotnull from r; + + +-- +-- Tests for component access / FieldSelect +-- +CREATE TABLE compositetable(a text, b text) WITH OIDS; +INSERT INTO compositetable(a, b) VALUES('fa', 'fb'); + +-- composite type columns can't directly be accessed (error) +SELECT d.a FROM (SELECT compositetable AS d FROM compositetable) s; +-- but can be accessed with proper parens +SELECT (d).a, (d).b FROM (SELECT compositetable AS d FROM compositetable) s; +-- oids can't be accessed in composite types (error) +SELECT (d).oid FROM (SELECT compositetable AS d FROM compositetable) s; + +-- accessing non-existing column in NULL datum errors out +SELECT (NULL::compositetable).nonexistant; +-- existing column in a NULL composite yield NULL +SELECT (NULL::compositetable).a; +-- oids can't be accessed in composite types (error) +SELECT (NULL::compositetable).oid; + +DROP TABLE compositetable; -- 2.40.0