| -- |
| -- 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_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; |
| -- |
| -- Tests for BETWEEN |
| -- |
| -- start_ignore |
| -- GPDB_13_MERGE_FIXME: |
| -- ORCA does support 2 phase aggregate, but not clear why it doesn't generate the plan |
| -- that makes use of 2 phase aggregate. However, the plan is correct. |
| -- NOTE: we should consider remove this optimizer answer file after we fix this issue. |
| -- end_ignore |
| explain (costs off) |
| select count(*) from date_tbl |
| where f1 between '1997-01-01' and '1998-01-01'; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------- |
| Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on date_tbl |
| Filter: ((f1 >= '01-01-1997'::date) AND (f1 <= '01-01-1998'::date)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 rows) |
| |
| select count(*) from date_tbl |
| where f1 between '1997-01-01' and '1998-01-01'; |
| count |
| ------- |
| 3 |
| (1 row) |
| |
| explain (costs off) |
| select count(*) from date_tbl |
| where f1 not between '1997-01-01' and '1998-01-01'; |
| QUERY PLAN |
| -------------------------------------------------------------------------------- |
| Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on date_tbl |
| Filter: ((f1 < '01-01-1997'::date) OR (f1 > '01-01-1998'::date)) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 rows) |
| |
| select count(*) from date_tbl |
| where f1 not between '1997-01-01' and '1998-01-01'; |
| count |
| ------- |
| 13 |
| (1 row) |
| |
| explain (costs off) |
| select count(*) from date_tbl |
| where f1 between symmetric '1997-01-01' and '1998-01-01'; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on date_tbl |
| Filter: (((f1 >= '01-01-1997'::date) AND (f1 <= '01-01-1998'::date)) OR ((f1 >= '01-01-1998'::date) AND (f1 <= '01-01-1997'::date))) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 rows) |
| |
| select count(*) from date_tbl |
| where f1 between symmetric '1997-01-01' and '1998-01-01'; |
| count |
| ------- |
| 3 |
| (1 row) |
| |
| explain (costs off) |
| select count(*) from date_tbl |
| where f1 not between symmetric '1997-01-01' and '1998-01-01'; |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------------------------------------------------- |
| Aggregate |
| -> Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on date_tbl |
| Filter: (((f1 < '01-01-1997'::date) OR (f1 > '01-01-1998'::date)) AND ((f1 < '01-01-1998'::date) OR (f1 > '01-01-1997'::date))) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 rows) |
| |
| select count(*) from date_tbl |
| where f1 not between symmetric '1997-01-01' and '1998-01-01'; |
| count |
| ------- |
| 13 |
| (1 row) |
| |
| -- |
| -- 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 numeric_tbl.f1, |
| numeric_tbl.f1::numeric(16,4) AS f1164, |
| numeric_tbl.f1::numeric AS f1n, |
| numeric_tbl.f2, |
| numeric_tbl.f2::numeric(16,4) AS f2164, |
| numeric_tbl.f2 AS f2n |
| FROM numeric_tbl; |
| |
| explain (verbose, costs off) select * from numeric_view; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| Output: f1, ((f1)::numeric(16,4)), ((f1)::numeric), f2, ((f2)::numeric(16,4)), f2 |
| -> Seq Scan on public.numeric_tbl |
| Output: f1, (f1)::numeric(16,4), f1, f2, (f2)::numeric(16,4), f2 |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (5 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 bpchar_tbl.f1, |
| bpchar_tbl.f1::character(14) AS f114, |
| bpchar_tbl.f1::bpchar AS f1n, |
| bpchar_tbl.f2, |
| bpchar_tbl.f2::character(14) AS f214, |
| bpchar_tbl.f2 AS f2n |
| FROM bpchar_tbl; |
| |
| explain (verbose, costs off) select * from bpchar_view |
| where f1::bpchar = 'foo'; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------ |
| Gather Motion 1:1 (slice1; segments: 1) |
| Output: f1, ((f1)::character(14)), ((f1)::bpchar), f2, ((f2)::character(14)), f2 |
| -> Index Scan using bpchar_tbl_f1_key on public.bpchar_tbl |
| Output: f1, (f1)::character(14), f1, f2, (f2)::character(14), f2 |
| Index Cond: ((bpchar_tbl.f1)::bpchar = 'foo'::bpchar) |
| Optimizer: Pivotal Optimizer (GPORCA) |
| (6 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) |
| |
| 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 operator = ( |
| leftarg = myint, |
| rightarg = myint, |
| commutator = =, |
| negator = <>, |
| procedure = myinteq, |
| 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) |
| |
| -- 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) |
| |
| rollback; |
