diff options
Diffstat (limited to '')
| -rw-r--r-- | src/test/regress/expected/expressions.out | 423 |
1 files changed, 423 insertions, 0 deletions
diff --git a/src/test/regress/expected/expressions.out b/src/test/regress/expected/expressions.out new file mode 100644 index 0000000..caeeb19 --- /dev/null +++ b/src/test/regress/expected/expressions.out @@ -0,0 +1,423 @@ +-- +-- expression evaluation 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()::timetz(4)::text = current_time(4)::text; + ?column? +---------- + t +(1 row) + +SELECT now()::time::text = localtime::text; + ?column? +---------- + t +(1 row) + +SELECT now()::time(3)::text = localtime(3)::text; + ?column? +---------- + t +(1 row) + +-- current_time[stamp]/ localtime[stamp] (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) + +-- precision overflow +SELECT current_time = current_time(7); +WARNING: TIME(7) WITH TIME ZONE precision reduced to maximum allowed, 6 + ?column? +---------- + t +(1 row) + +SELECT current_timestamp = current_timestamp(7); +WARNING: TIMESTAMP(7) WITH TIME ZONE precision reduced to maximum allowed, 6 + ?column? +---------- + t +(1 row) + +SELECT localtime = localtime(7); +WARNING: TIME(7) precision reduced to maximum allowed, 6 + ?column? +---------- + t +(1 row) + +SELECT localtimestamp = localtimestamp(7); +WARNING: TIMESTAMP(7) precision reduced to maximum allowed, 6 + ?column? +---------- + t +(1 row) + +-- current_role/user/user is tested in rolenames.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; +-- +-- Test parsing of a no-op cast to a type with unspecified typmod +-- +begin; +create table numeric_tbl (f1 numeric(18,3), f2 numeric); +create view numeric_view as + select + f1, f1::numeric(16,4) as f1164, f1::numeric as f1n, + f2, f2::numeric(16,4) as f2164, f2::numeric as f2n + from numeric_tbl; +\d+ numeric_view + View "public.numeric_view" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------------+-----------+----------+---------+---------+------------- + f1 | numeric(18,3) | | | | main | + f1164 | numeric(16,4) | | | | main | + f1n | numeric | | | | main | + f2 | numeric | | | | main | + f2164 | numeric(16,4) | | | | main | + f2n | numeric | | | | main | +View definition: + SELECT f1, + f1::numeric(16,4) AS f1164, + f1::numeric AS f1n, + f2, + f2::numeric(16,4) AS f2164, + f2 AS f2n + FROM numeric_tbl; + +explain (verbose, costs off) select * from numeric_view; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------------- + Seq Scan on public.numeric_tbl + Output: numeric_tbl.f1, (numeric_tbl.f1)::numeric(16,4), (numeric_tbl.f1)::numeric, numeric_tbl.f2, (numeric_tbl.f2)::numeric(16,4), numeric_tbl.f2 +(2 rows) + +-- bpchar, lacking planner support for its length coercion function, +-- could behave differently +create table bpchar_tbl (f1 character(16) unique, f2 bpchar); +create view bpchar_view as + select + f1, f1::character(14) as f114, f1::bpchar as f1n, + f2, f2::character(14) as f214, f2::bpchar as f2n + from bpchar_tbl; +\d+ bpchar_view + View "public.bpchar_view" + Column | Type | Collation | Nullable | Default | Storage | Description +--------+---------------+-----------+----------+---------+----------+------------- + f1 | character(16) | | | | extended | + f114 | character(14) | | | | extended | + f1n | bpchar | | | | extended | + f2 | bpchar | | | | extended | + f214 | character(14) | | | | extended | + f2n | bpchar | | | | extended | +View definition: + SELECT f1, + f1::character(14) AS f114, + f1::bpchar AS f1n, + f2, + f2::character(14) AS f214, + f2 AS f2n + FROM bpchar_tbl; + +explain (verbose, costs off) select * from bpchar_view + where f1::bpchar = 'foo'; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------------------------ + Index Scan using bpchar_tbl_f1_key on public.bpchar_tbl + Output: bpchar_tbl.f1, (bpchar_tbl.f1)::character(14), (bpchar_tbl.f1)::bpchar, bpchar_tbl.f2, (bpchar_tbl.f2)::character(14), bpchar_tbl.f2 + Index Cond: ((bpchar_tbl.f1)::bpchar = 'foo'::bpchar) +(3 rows) + +rollback; +-- +-- Ordinarily, IN/NOT IN can be converted to a ScalarArrayOpExpr +-- with a suitably-chosen array type. +-- +explain (verbose, costs off) +select random() IN (1, 4, 8.0); + QUERY PLAN +------------------------------------------------------------ + Result + Output: (random() = ANY ('{1,4,8}'::double precision[])) +(2 rows) + +explain (verbose, costs off) +select random()::int IN (1, 4, 8.0); + QUERY PLAN +--------------------------------------------------------------------------- + Result + Output: (((random())::integer)::numeric = ANY ('{1,4,8.0}'::numeric[])) +(2 rows) + +-- However, if there's not a common supertype for the IN elements, +-- we should instead try to produce "x = v1 OR x = v2 OR ...". +-- In most cases that'll fail for lack of all the requisite = operators, +-- but it can succeed sometimes. So this should complain about lack of +-- an = operator, not about cast failure. +select '(0,0)'::point in ('(0,0,0,0)'::box, point(0,0)); +ERROR: operator does not exist: point = box +LINE 1: select '(0,0)'::point in ('(0,0,0,0)'::box, point(0,0)); + ^ +HINT: No operator matches the given name and argument types. You might need to add explicit type casts. +-- +-- Tests for ScalarArrayOpExpr with a hashfn +-- +-- create a stable function so that the tests below are not +-- evaluated using the planner's constant folding. +begin; +create function return_int_input(int) returns int as $$ +begin + return $1; +end; +$$ language plpgsql stable; +create function return_text_input(text) returns text as $$ +begin + return $1; +end; +$$ language plpgsql stable; +select return_int_input(1) in (10, 9, 2, 8, 3, 7, 4, 6, 5, 1); + ?column? +---------- + t +(1 row) + +select return_int_input(1) in (10, 9, 2, 8, 3, 7, 4, 6, 5, null); + ?column? +---------- + +(1 row) + +select return_int_input(1) in (null, null, null, null, null, null, null, null, null, null, null); + ?column? +---------- + +(1 row) + +select return_int_input(1) in (10, 9, 2, 8, 3, 7, 4, 6, 5, 1, null); + ?column? +---------- + t +(1 row) + +select return_int_input(null::int) in (10, 9, 2, 8, 3, 7, 4, 6, 5, 1); + ?column? +---------- + +(1 row) + +select return_int_input(null::int) in (10, 9, 2, 8, 3, 7, 4, 6, 5, null); + ?column? +---------- + +(1 row) + +select return_text_input('a') in ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j'); + ?column? +---------- + t +(1 row) + +-- NOT IN +select return_int_input(1) not in (10, 9, 2, 8, 3, 7, 4, 6, 5, 1); + ?column? +---------- + f +(1 row) + +select return_int_input(1) not in (10, 9, 2, 8, 3, 7, 4, 6, 5, 0); + ?column? +---------- + t +(1 row) + +select return_int_input(1) not in (10, 9, 2, 8, 3, 7, 4, 6, 5, 2, null); + ?column? +---------- + +(1 row) + +select return_int_input(1) not in (10, 9, 2, 8, 3, 7, 4, 6, 5, 1, null); + ?column? +---------- + f +(1 row) + +select return_int_input(1) not in (null, null, null, null, null, null, null, null, null, null, null); + ?column? +---------- + +(1 row) + +select return_int_input(null::int) not in (10, 9, 2, 8, 3, 7, 4, 6, 5, 1); + ?column? +---------- + +(1 row) + +select return_int_input(null::int) not in (10, 9, 2, 8, 3, 7, 4, 6, 5, null); + ?column? +---------- + +(1 row) + +select return_text_input('a') not in ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', 'j'); + ?column? +---------- + f +(1 row) + +rollback; +-- Test with non-strict equality function. +-- We need to create our own type for this. +begin; +create type myint; +create function myintin(cstring) returns myint strict immutable language + internal as 'int4in'; +NOTICE: return type myint is only a shell +create function myintout(myint) returns cstring strict immutable language + internal as 'int4out'; +NOTICE: argument type myint is only a shell +create function myinthash(myint) returns integer strict immutable language + internal as 'hashint4'; +NOTICE: argument type myint is only a shell +create type myint (input = myintin, output = myintout, like = int4); +create cast (int4 as myint) without function; +create cast (myint as int4) without function; +create function myinteq(myint, myint) returns bool as $$ +begin + if $1 is null and $2 is null then + return true; + else + return $1::int = $2::int; + end if; +end; +$$ language plpgsql immutable; +create function myintne(myint, myint) returns bool as $$ +begin + return not myinteq($1, $2); +end; +$$ language plpgsql immutable; +create operator = ( + leftarg = myint, + rightarg = myint, + commutator = =, + negator = <>, + procedure = myinteq, + restrict = eqsel, + join = eqjoinsel, + merges +); +create operator <> ( + leftarg = myint, + rightarg = myint, + commutator = <>, + negator = =, + procedure = myintne, + restrict = eqsel, + join = eqjoinsel, + merges +); +create operator class myint_ops +default for type myint using hash as + operator 1 = (myint, myint), + function 1 myinthash(myint); +create table inttest (a myint); +insert into inttest values(1::myint),(null); +-- try an array with enough elements to cause hashing +select * from inttest where a in (1::myint,2::myint,3::myint,4::myint,5::myint,6::myint,7::myint,8::myint,9::myint, null); + a +--- + 1 + +(2 rows) + +select * from inttest where a not in (1::myint,2::myint,3::myint,4::myint,5::myint,6::myint,7::myint,8::myint,9::myint, null); + a +--- +(0 rows) + +select * from inttest where a not in (0::myint,2::myint,3::myint,4::myint,5::myint,6::myint,7::myint,8::myint,9::myint, null); + a +--- +(0 rows) + +-- ensure the result matched with the non-hashed version. We simply remove +-- some array elements so that we don't reach the hashing threshold. +select * from inttest where a in (1::myint,2::myint,3::myint,4::myint,5::myint, null); + a +--- + 1 + +(2 rows) + +select * from inttest where a not in (1::myint,2::myint,3::myint,4::myint,5::myint, null); + a +--- +(0 rows) + +select * from inttest where a not in (0::myint,2::myint,3::myint,4::myint,5::myint, null); + a +--- +(0 rows) + +rollback; |