Registering pre-existing tables
onek
int4_tbl
INT4_TBL
tenk1
text_tbl
road
TEXT_TBL
int8_tbl
--
-- SUBSELECT
--
SELECT 1 AS one WHERE 1 IN (SELECT 1);
SELECT 1 AS zero WHERE 1 NOT IN (SELECT 1);
SELECT 1 AS zero WHERE 1 IN (SELECT 2);
-- Check grammar's handling of extra parens in assorted contexts
SELECT * FROM (SELECT 1 AS x) ss;
SELECT * FROM ((SELECT 1 AS x)) ss;
(SELECT 2) UNION SELECT 2;
((SELECT 2)) UNION SELECT 2;
SELECT ((SELECT 2) UNION SELECT 2);
SELECT (((SELECT 2)) UNION SELECT 2);
SELECT (SELECT ARRAY[1,2,3])[1];
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 360
SELECT (SELECT ARRAY[1,2,3])[1];
^
SELECT ((SELECT ARRAY[1,2,3]))[2];
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 360
SELECT ((SELECT ARRAY[1,2,3]))[2];
^
SELECT (((SELECT ARRAY[1,2,3])))[3];
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: alternative is not implemented yet : 360
SELECT (((SELECT ARRAY[1,2,3])))[3];
^
-- Set up some simple test tables
CREATE TABLE SUBSELECT_TBL (
f1 integer,
f2 integer,
f3 float
);
INSERT INTO SUBSELECT_TBL VALUES (1, 2, 3);
INSERT INTO SUBSELECT_TBL VALUES (2, 3, 4);
INSERT INTO SUBSELECT_TBL VALUES (3, 4, 5);
INSERT INTO SUBSELECT_TBL VALUES (1, 1, 1);
INSERT INTO SUBSELECT_TBL VALUES (2, 2, 2);
INSERT INTO SUBSELECT_TBL VALUES (3, 3, 3);
INSERT INTO SUBSELECT_TBL VALUES (6, 7, 8);
INSERT INTO SUBSELECT_TBL VALUES (8, 9, NULL);
SELECT * FROM SUBSELECT_TBL;
-- Uncorrelated subselects
SELECT f1 AS "Constant Select" FROM SUBSELECT_TBL
WHERE f1 IN (SELECT 1);
SELECT f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL);
SELECT f1 AS "Uncorrelated Field" FROM SUBSELECT_TBL
WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE
f2 IN (SELECT f1 FROM SUBSELECT_TBL));
SELECT f1, f2
FROM SUBSELECT_TBL
WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
WHERE f3 IS NOT NULL);
-stdin-:: Error: Parse Sql
-stdin-::3:18: Error: alternative is not implemented yet : 138
WHERE (f1, f2) NOT IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
^
-- Correlated subselects
SELECT f1 AS "Correlated Field", f2 AS "Second Field"
FROM SUBSELECT_TBL upper
WHERE f1 IN (SELECT f2 FROM SUBSELECT_TBL WHERE f1 = upper.f1);
SELECT f1 AS "Correlated Field", f3 AS "Second Field"
FROM SUBSELECT_TBL upper
WHERE f1 IN
(SELECT f2 FROM SUBSELECT_TBL WHERE CAST(upper.f2 AS float) = f3);
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: AssumeColumnOrder, At function: PgReplaceUnknown, At function: OrderedMap, At function: OrderedFilter, At function: OrderedMap, At function: EquiJoin
SELECT f1 AS "Correlated Field", f3 AS "Second Field"
^
-stdin-::3:12: Error: At function: Aggregate
WHERE f1 IN
^
-stdin-:/lib/yql/aggregate.yql:644:18: Error: At function: AggregationTraits
^
-stdin-:/lib/yql/aggregate.yql:678:111: Error: At function: SafeCast
^
-stdin-::3:12: Error: At function: FromPg, At function: PgResolvedOp
WHERE f1 IN
^
-stdin-::3:9: Error: At function: Member
WHERE f1 IN
^
-stdin-::3:9: Error: Member not found: _yql_join_sublink_0__alias_upper.f1. Did you mean _yql_join_sublink_0__alias_upper.f2?
WHERE f1 IN
^
SELECT f1 AS "Correlated Field", f3 AS "Second Field"
FROM SUBSELECT_TBL upper
WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL
WHERE f2 = CAST(f3 AS integer));
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: AssumeColumnOrder, At function: PgReplaceUnknown, At function: OrderedMap, At function: OrderedFilter, At function: OrderedMap, At function: EquiJoin
SELECT f1 AS "Correlated Field", f3 AS "Second Field"
^
-stdin-::3:12: Error: At function: Aggregate
WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL
^
-stdin-:/lib/yql/aggregate.yql:644:18: Error: At function: AggregationTraits
^
-stdin-:/lib/yql/aggregate.yql:678:111: Error: At function: SafeCast
^
-stdin-::3:12: Error: At function: FromPg, At function: PgResolvedOp
WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL
^
-stdin-::3:9: Error: At function: Member
WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL
^
-stdin-::3:9: Error: Member not found: _yql_join_sublink_0__alias_upper.f3. Did you mean _yql_join_sublink_0__alias_upper.f1?
WHERE f3 IN (SELECT upper.f1 + f2 FROM SUBSELECT_TBL
^
SELECT f1 AS "Correlated Field"
FROM SUBSELECT_TBL
WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
WHERE f3 IS NOT NULL);
-stdin-:: Error: Parse Sql
-stdin-::3:18: Error: alternative is not implemented yet : 138
WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
^
--
-- Use some existing tables in the regression test
--
SELECT ss.f1 AS "Correlated Field", ss.f3 AS "Second Field"
FROM SUBSELECT_TBL ss
WHERE f1 NOT IN (SELECT f1+1 FROM INT4_TBL
WHERE f1 != ss.f1 AND f1 < 2147483647);
select q1, float8(count(*)) / (select count(*) from int8_tbl)
from int8_tbl group by q1 order by q1;
-stdin-:: Fatal: Optimization
-stdin-::1:1: Fatal: ydb/library/yql/core/common_opt/yql_co_pgselect.cpp:3637 ExpandPgSelectImpl(): requirement order failed
select q1, float8(count(*)) / (select count(*) from int8_tbl)
^
-stdin-:: Fatal: ydb/library/yql/core/common_opt/yql_co_pgselect.cpp:727 RewriteSubLinksPartial(): requirement status.Level != IGraphTransformer::TStatus::Error failed
-- Unspecified-type literals in output columns should resolve as text
SELECT *, pg_typeof(f1) FROM
(SELECT 'foo' AS f1 FROM generate_series(1,3)) ss ORDER BY 1;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem
-- Unspecified-type literals in output columns should resolve as text
^
-stdin-::2:21: Error: Star is incompatible to column reference
SELECT *, pg_typeof(f1) FROM
^
-- ... unless there's context to suggest differently
explain (verbose, costs off) select '42' union all select '43';
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- ... unless there's context to suggest differently
^
explain (verbose, costs off) select '42' union all select 43;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (verbose, costs off) select '42' union all select 43;
^
-- check materialization of an initplan reference (bug #14524)
explain (verbose, costs off)
select 1 = all (select (select 1));
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- check materialization of an initplan reference (bug #14524)
^
select 1 = all (select (select 1));
--
-- Check EXISTS simplification with LIMIT
--
explain (costs off)
select * from int4_tbl o where exists
(select 1 from int4_tbl i where i.f1=o.f1 limit null);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
--
^
explain (costs off)
select * from int4_tbl o where not exists
(select 1 from int4_tbl i where i.f1=o.f1 limit 1);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
explain (costs off)
select * from int4_tbl o where exists
(select 1 from int4_tbl i where i.f1=o.f1 limit 0);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
--
-- Test cases to catch unpleasant interactions between IN-join processing
-- and subquery pullup.
--
select count(*) from
(select 1 from tenk1 a
where unique1 IN (select hundred from tenk1 b)) ss;
select count(distinct ss.ten) from
(select ten from tenk1 a
where unique1 IN (select hundred from tenk1 b)) ss;
select count(*) from
(select 1 from tenk1 a
where unique1 IN (select distinct hundred from tenk1 b)) ss;
select count(distinct ss.ten) from
(select ten from tenk1 a
where unique1 IN (select distinct hundred from tenk1 b)) ss;
--
-- Test cases to check for overenthusiastic optimization of
-- "IN (SELECT DISTINCT ...)" and related cases. Per example from
-- Luca Pireddu and Michael Fuhr.
--
CREATE TEMP TABLE foo (id integer);
CREATE TEMP TABLE bar (id1 integer, id2 integer);
INSERT INTO foo VALUES (1);
INSERT INTO bar VALUES (1, 1);
INSERT INTO bar VALUES (2, 2);
INSERT INTO bar VALUES (3, 1);
-- These cases require an extra level of distinct-ing above subquery s
SELECT * FROM foo WHERE id IN
(SELECT id2 FROM (SELECT DISTINCT id1, id2 FROM bar) AS s);
SELECT * FROM foo WHERE id IN
(SELECT id2 FROM (SELECT id1,id2 FROM bar GROUP BY id1,id2) AS s);
SELECT * FROM foo WHERE id IN
(SELECT id2 FROM (SELECT id1, id2 FROM bar UNION
SELECT id1, id2 FROM bar) AS s);
-- These cases do not
SELECT * FROM foo WHERE id IN
(SELECT id2 FROM (SELECT DISTINCT ON (id2) id1, id2 FROM bar) AS s);
SELECT * FROM foo WHERE id IN
(SELECT id2 FROM (SELECT id2 FROM bar GROUP BY id2) AS s);
SELECT * FROM foo WHERE id IN
(SELECT id2 FROM (SELECT id2 FROM bar UNION
SELECT id2 FROM bar) AS s);
--
-- Test case to catch problems with multiply nested sub-SELECTs not getting
-- recalculated properly. Per bug report from Didier Moens.
--
CREATE TABLE orderstest (
approver_ref integer,
po_ref integer,
ordercanceled boolean
);
INSERT INTO orderstest VALUES (1, 1, false);
INSERT INTO orderstest VALUES (66, 5, false);
INSERT INTO orderstest VALUES (66, 6, false);
INSERT INTO orderstest VALUES (66, 7, false);
INSERT INTO orderstest VALUES (66, 1, true);
INSERT INTO orderstest VALUES (66, 8, false);
INSERT INTO orderstest VALUES (66, 1, false);
INSERT INTO orderstest VALUES (77, 1, false);
INSERT INTO orderstest VALUES (1, 1, false);
INSERT INTO orderstest VALUES (66, 1, false);
INSERT INTO orderstest VALUES (1, 1, false);
CREATE VIEW orders_view AS
SELECT *,
(SELECT CASE
WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved'
END) AS "Approved",
(SELECT CASE
WHEN ord.ordercanceled
THEN 'Canceled'
ELSE
(SELECT CASE
WHEN ord.po_ref=1
THEN
(SELECT CASE
WHEN ord.approver_ref=1
THEN '---'
ELSE 'Approved'
END)
ELSE 'PO'
END)
END) AS "Status",
(CASE
WHEN ord.ordercanceled
THEN 'Canceled'
ELSE
(CASE
WHEN ord.po_ref=1
THEN
(CASE
WHEN ord.approver_ref=1
THEN '---'
ELSE 'Approved'
END)
ELSE 'PO'
END)
END) AS "Status_OK"
FROM orderstest ord;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: expected at least one target column
CREATE VIEW orders_view AS
^
SELECT * FROM orders_view;
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.orders_view
DROP TABLE orderstest cascade;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: CASCADE is not implemented
DROP TABLE orderstest cascade;
^
--
-- Test cases to catch situations where rule rewriter fails to propagate
-- hasSubLinks flag correctly. Per example from Kyle Bateman.
--
create temp table parts (
partnum text,
cost float8
);
create temp table shipped (
ttype char(2),
ordnum int4,
partnum text,
value float8
);
create temp view shipped_view as
select * from shipped where ttype = 'wt';
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: expected at least one target column
create temp view shipped_view as
^
create rule shipped_view_insert as on insert to shipped_view do instead
insert into shipped values('wt', new.ordnum, new.partnum, new.value);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 265
create rule shipped_view_insert as on insert to shipped_view do instead
^
insert into parts (partnum, cost) values (1, 1234.56);
insert into shipped_view (ordnum, partnum, value)
values (0, 1, (select cost from parts where partnum = '1'));
-stdin-:: Error: Parse Sql
-stdin-::2:19: Error: SubLinks are not allowed in: VALUES
values (0, 1, (select cost from parts where partnum = '1'));
^
select * from shipped_view;
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.shipped_view
create rule shipped_view_update as on update to shipped_view do instead
update shipped set partnum = new.partnum, value = new.value
where ttype = new.ttype and ordnum = new.ordnum;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 265
create rule shipped_view_update as on update to shipped_view do instead
^
update shipped_view set value = 11
from int4_tbl a join int4_tbl b
on (a.f1 = (select f1 from int4_tbl c where c.f1=b.f1))
where ordnum = a.f1;
-stdin-:: Error: Parse Sql
-stdin-::3:18: Error: SubLinks are not allowed in: JOIN ON
on (a.f1 = (select f1 from int4_tbl c where c.f1=b.f1))
^
select * from shipped_view;
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.shipped_view
select f1, ss1 as relabel from
(select *, (select sum(f1) from int4_tbl b where f1 >= a.f1) as ss1
from int4_tbl a) ss;
--
-- Test cases involving PARAM_EXEC parameters and min/max index optimizations.
-- Per bug report from David Sanchez i Gregori.
--
select * from (
select max(unique1) from tenk1 as a
where exists (select 1 from tenk1 as b where b.thousand = a.unique2)
) ss;
select * from (
select min(unique1) from tenk1 as a
where not exists (select 1 from tenk1 as b where b.unique2 = 10000)
) ss;
--
-- Test that an IN implemented using a UniquePath does unique-ification
-- with the right semantics, as per bug #4113. (Unfortunately we have
-- no simple way to ensure that this test case actually chooses that type
-- of plan, but it does in releases 7.4-8.3. Note that an ordering difference
-- here might mean that some other plan type is being used, rendering the test
-- pointless.)
--
create temp table numeric_table (num_col numeric);
insert into numeric_table values (1), (1.000000000000000000001), (2), (3);
create temp table float_table (float_col float8);
insert into float_table values (1), (2), (3);
select * from float_table
where float_col in (select num_col from numeric_table);
select * from numeric_table
where num_col in (select float_col from float_table);
--
-- Test case for bug #4290: bogus calculation of subplan param sets
--
create temp table ta (id int primary key, val int);
insert into ta values(1,1);
insert into ta values(2,2);
create temp table tb (id int primary key, aval int);
insert into tb values(1,1);
insert into tb values(2,1);
insert into tb values(3,2);
insert into tb values(4,2);
create temp table tc (id int primary key, aid int);
insert into tc values(1,1);
insert into tc values(2,2);
select
( select min(tb.id) from tb
where tb.aval = (select ta.val from ta where ta.id = tc.aid) ) as min_tb_id
from tc;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem
select
^
-stdin-::2:3: Error: At function: PgSubLink, At function: PgSelect
( select min(tb.id) from tb
^
-stdin-:: Error: At function: PgSetItem
-stdin-::2:3: Error: At function: PgWhere
( select min(tb.id) from tb
^
-stdin-::3:19: Error: At function: PgOp
where tb.aval = (select ta.val from ta where ta.id = tc.aid) ) as min_tb_id
^
-stdin-::3:21: Error: At function: PgSubLink, At function: PgSelect, At function: PgSetItem
where tb.aval = (select ta.val from ta where ta.id = tc.aid) ) as min_tb_id
^
-stdin-::3:58: Error: No such column: aid
where tb.aval = (select ta.val from ta where ta.id = tc.aid) ) as min_tb_id
^
--
-- Test case for 8.3 "failed to locate grouping columns" bug
--
create temp table t1 (f1 numeric(14,0), f2 varchar(30));
select * from
(select distinct f1, f2, (select f2 from t1 x where x.f1 = up.f1) as fs
from t1 up) ss
group by f1,f2,fs;
--
-- Test case for bug #5514 (mishandling of whole-row Vars in subselects)
--
create temp table table_a(id integer);
insert into table_a values (42);
create temp view view_a as select * from table_a;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: expected at least one target column
create temp view view_a as select * from table_a;
^
select view_a from view_a;
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.view_a
select (select view_a) from view_a;
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.view_a
select (select (select view_a)) from view_a;
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.view_a
select (select (a.*)::text) from view_a a;
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.view_a
--
-- Check that whole-row Vars reading the result of a subselect don't include
-- any junk columns therein
--
select q from (select max(f1) from int4_tbl group by f1 order by f1) q;
-stdin-:: Fatal: Execution
-stdin-::1:1: Fatal: Execution of node: Pull
--
^
-stdin-::1:1: Fatal: ERROR: input of anonymous composite types is not implemented
--
^
with q as (select max(f1) from int4_tbl group by f1 order by f1)
select q from q;
-stdin-:: Fatal: Execution
-stdin-::1:1: Fatal: Execution of node: Pull
with q as (select max(f1) from int4_tbl group by f1 order by f1)
^
-stdin-::1:1: Fatal: ERROR: input of anonymous composite types is not implemented
with q as (select max(f1) from int4_tbl group by f1 order by f1)
^
--
-- Test case for sublinks pulled up into joinaliasvars lists in an
-- inherited update/delete query
--
begin; -- this shouldn't delete anything, but be safe
delete from road
where exists (
select 1
from
int4_tbl cross join
( select f1, array(select q1 from int8_tbl) as arr
from text_tbl ) ss
where road.name = ss.f1 );
-stdin-:: Fatal: Pre type annotation
-stdin-:: Fatal: tools/enum_parser/enum_serialization_runtime/enum_runtime.cpp:70: Key 'pg_delete' not found in enum NYql::EYtSettingType. Valid options are: 'initial', 'infer_scheme', 'force_infer_schema', 'do_not_fail_on_invalid_schema', 'direct_read', 'view', 'mode', 'scheme', 'weak_concat', 'anonymous', 'with_qb', 'inline', 'sample', 'joinLabel', 'ignore_non_existing', 'warn_non_existing', 'xlock', 'unordered', 'nonUnique', 'userschema', 'usercolumns', 'statcolumns', 'syscolumns', 'ignoretypev3', 'memUsage', 'itemsCount', 'rowFactor', 'ordered', 'keyFilter', 'keyFilter2', 'take', 'skip', 'limit', 'sortLimitBy', 'sortBy', 'reduceBy', 'reduceFilterBy', 'forceTransform', 'weakFields', 'sharded', 'combineChunks', 'jobCount', 'joinReduce', 'firstAsPrimary', 'flow', 'keepSorted', 'keySwitch', 'uniqueBy', 'opHash', 'mapOutputType', 'reduceInputType', 'noDq', 'split', 'compression_codec', 'erasure_codec', 'expiration', 'replication_factor', 'user_attrs', 'media', 'primary_medium', 'keep_meta', 'monotonic_keys', 'mutationid'.
rollback;
--
-- Test case for sublinks pushed down into subselects via join alias expansion
--
select
(select sq1) as qq1
from
(select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy
from int8_tbl) sq0
join
int4_tbl i4 on dummy = i4.f1;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: OrderedFlatMap, At function: EquiJoin, At function: OrderedFlatMap, At function: EquiJoin, At function: OrderedFlatMap, At function: EquiJoin
--
^
-stdin-::7:11: Error: At function: Aggregate, At function: OrderedMap, At function: EquiJoin
(select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy
^
-stdin-::7:11: Error: Cannot compare key columns (a._alias_int4_tbl.f1 has type: pgint4, b._alias_int8_tbl.q2 has type: pgint8)
(select exists(select 1 from int4_tbl where f1 = q2) as sq1, 42 as dummy
^
--
-- Test case for subselect within UPDATE of INSERT...ON CONFLICT DO UPDATE
--
create temp table upsert(key int4 primary key, val text);
insert into upsert values(1, 'val') on conflict (key) do update set val = 'not seen';
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: InsertStmt: not supported onConflictClause
insert into upsert values(1, 'val') on conflict (key) do update set val = 'not seen';
^
insert into upsert values(1, 'val') on conflict (key) do update set val = 'seen with subselect ' || (select f1 from int4_tbl where f1 != 0 limit 1)::text;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: InsertStmt: not supported onConflictClause
insert into upsert values(1, 'val') on conflict (key) do update set val = 'seen with subselect ' || (select f1 from int4_tbl where f1 != 0 limit 1)::text;
^
select * from upsert;
with aa as (select 'int4_tbl' u from int4_tbl limit 1)
insert into upsert values (1, 'x'), (999, 'y')
on conflict (key) do update set val = (select u from aa)
returning *;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: InsertStmt: not supported onConflictClause
with aa as (select 'int4_tbl' u from int4_tbl limit 1)
^
--
-- Test case for cross-type partial matching in hashed subplan (bug #7597)
--
create temp table outer_7597 (f1 int4, f2 int4);
insert into outer_7597 values (0, 0);
insert into outer_7597 values (1, 0);
insert into outer_7597 values (0, null);
insert into outer_7597 values (1, null);
create temp table inner_7597(c1 int8, c2 int8);
insert into inner_7597 values(0, null);
select * from outer_7597 where (f1, f2) not in (select * from inner_7597);
-stdin-:: Error: Parse Sql
-stdin-::1:41: Error: alternative is not implemented yet : 138
select * from outer_7597 where (f1, f2) not in (select * from inner_7597);
^
--
-- Similar test case using text that verifies that collation
-- information is passed through by execTuplesEqual() in nodeSubplan.c
-- (otherwise it would error in texteq())
--
create temp table outer_text (f1 text, f2 text);
insert into outer_text values ('a', 'a');
insert into outer_text values ('b', 'a');
insert into outer_text values ('a', null);
insert into outer_text values ('b', null);
create temp table inner_text (c1 text, c2 text);
insert into inner_text values ('a', null);
insert into inner_text values ('123', '456');
select * from outer_text where (f1, f2) not in (select * from inner_text);
-stdin-:: Error: Parse Sql
-stdin-::1:41: Error: alternative is not implemented yet : 138
select * from outer_text where (f1, f2) not in (select * from inner_text);
^
--
-- Another test case for cross-type hashed subplans: comparison of
-- inner-side values must be done with appropriate operator
--
explain (verbose, costs off)
select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
--
^
select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: AssumeColumnOrder, At function: PgReplaceUnknown, At function: OrderedMap, At function: AsStruct
select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
^
-stdin-::1:20: Error: At function: ToPg, At function: SqlIn
select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
^
-stdin-::1:20: Error: Can't lookup pgtext in collection of pgname: types pgtext and pgname are not comparable
select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
^
--
-- Test that we don't try to hash nested records (bug #17363)
-- (Hashing could be supported, but for now we don't)
--
explain (verbose, costs off)
select row(row(row(1))) = any (select row(row(1)));
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
--
^
select row(row(row(1))) = any (select row(row(1)));
-stdin-:: Error: Parse Sql
-stdin-::1:25: Error: alternative is not implemented yet : 138
select row(row(row(1))) = any (select row(row(1)));
^
--
-- Test case for premature memory release during hashing of subplan output
--
select '1'::text in (select '1'::name union all select '1'::name);
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: AssumeColumnOrder, At function: PgReplaceUnknown, At function: OrderedMap, At function: AsStruct
--
^
-stdin-::4:18: Error: At function: ToPg, At function: SqlIn
select '1'::text in (select '1'::name union all select '1'::name);
^
-stdin-::4:18: Error: Can't lookup pgtext in collection of pgname: types pgtext and pgname are not comparable
select '1'::text in (select '1'::name union all select '1'::name);
^
--
-- Test that we don't try to use a hashed subplan if the simplified
-- testexpr isn't of the right shape
--
-- this fails by default, of course
select * from int8_tbl where q1 in (select c1 from inner_text);
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect
--
^
-stdin-:: Error: At function: PgSetItem
-stdin-::1:1: Error: At function: PgWhere
--
^
-stdin-::6:33: Error: At function: PgSubLink, At function: PgOp
select * from int8_tbl where q1 in (select c1 from inner_text);
^
-stdin-::6:33: Error: Unable to find an overload for operator = with given argument type(s): (int8,text)
select * from int8_tbl where q1 in (select c1 from inner_text);
^
begin;
-- make an operator to allow it to succeed
create function bogus_int8_text_eq(int8, text) returns boolean
language sql as 'select $1::text = $2';
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 261
-- make an operator to allow it to succeed
^
create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 255
create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text);
^
explain (costs off)
select * from int8_tbl where q1 in (select c1 from inner_text);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
select * from int8_tbl where q1 in (select c1 from inner_text);
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect
select * from int8_tbl where q1 in (select c1 from inner_text);
^
-stdin-:: Error: At function: PgSetItem
-stdin-::1:1: Error: At function: PgWhere
select * from int8_tbl where q1 in (select c1 from inner_text);
^
-stdin-::1:33: Error: At function: PgSubLink, At function: PgOp
select * from int8_tbl where q1 in (select c1 from inner_text);
^
-stdin-::1:33: Error: Unable to find an overload for operator = with given argument type(s): (int8,text)
select * from int8_tbl where q1 in (select c1 from inner_text);
^
-- inlining of this function results in unusual number of hash clauses,
-- which we can still cope with
create or replace function bogus_int8_text_eq(int8, text) returns boolean
language sql as 'select $1::text = $2 and $1::text = $2';
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 261
-- inlining of this function results in unusual number of hash clauses,
^
explain (costs off)
select * from int8_tbl where q1 in (select c1 from inner_text);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
select * from int8_tbl where q1 in (select c1 from inner_text);
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect
select * from int8_tbl where q1 in (select c1 from inner_text);
^
-stdin-:: Error: At function: PgSetItem
-stdin-::1:1: Error: At function: PgWhere
select * from int8_tbl where q1 in (select c1 from inner_text);
^
-stdin-::1:33: Error: At function: PgSubLink, At function: PgOp
select * from int8_tbl where q1 in (select c1 from inner_text);
^
-stdin-::1:33: Error: Unable to find an overload for operator = with given argument type(s): (int8,text)
select * from int8_tbl where q1 in (select c1 from inner_text);
^
-- inlining of this function causes LHS and RHS to be switched,
-- which we can't cope with, so hashing should be abandoned
create or replace function bogus_int8_text_eq(int8, text) returns boolean
language sql as 'select $2 = $1::text';
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 261
-- inlining of this function causes LHS and RHS to be switched,
^
explain (costs off)
select * from int8_tbl where q1 in (select c1 from inner_text);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
select * from int8_tbl where q1 in (select c1 from inner_text);
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect
select * from int8_tbl where q1 in (select c1 from inner_text);
^
-stdin-:: Error: At function: PgSetItem
-stdin-::1:1: Error: At function: PgWhere
select * from int8_tbl where q1 in (select c1 from inner_text);
^
-stdin-::1:33: Error: At function: PgSubLink, At function: PgOp
select * from int8_tbl where q1 in (select c1 from inner_text);
^
-stdin-::1:33: Error: Unable to find an overload for operator = with given argument type(s): (int8,text)
select * from int8_tbl where q1 in (select c1 from inner_text);
^
rollback; -- to get rid of the bogus operator
--
-- Test resolution of hashed vs non-hashed implementation of EXISTS subplan
--
explain (costs off)
select count(*) from tenk1 t
where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
--
^
select count(*) from tenk1 t
where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0);
explain (costs off)
select count(*) from tenk1 t
where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
and thousand = 1;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
select count(*) from tenk1 t
where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
and thousand = 1;
-- It's possible for the same EXISTS to get resolved both ways
create temp table exists_tbl (c1 int, c2 int, c3 int) partition by list (c1);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: PARTITION BY clause not supported
-- It's possible for the same EXISTS to get resolved both ways
^
create temp table exists_tbl_null partition of exists_tbl for values in (null);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: table inheritance not supported
create temp table exists_tbl_null partition of exists_tbl for values in (null);
^
create temp table exists_tbl_def partition of exists_tbl default;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: table inheritance not supported
create temp table exists_tbl_def partition of exists_tbl default;
^
insert into exists_tbl select x, x/2, x+1 from generate_series(0,10) x;
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.exists_tbl
analyze exists_tbl;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 275
analyze exists_tbl;
^
explain (costs off)
select * from exists_tbl t1
where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (costs off)
^
select * from exists_tbl t1
where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0);
-stdin-:: Fatal: Table metadata loading
-stdin-:: Fatal: ydb/library/yql/providers/yt/gateway/file/yql_yt_file_services.cpp:44: Table not found: plato.exists_tbl
--
-- Test case for planner bug with nested EXISTS handling
--
select a.thousand from tenk1 a, tenk1 b
where a.thousand = b.thousand
and exists ( select 1 from tenk1 c where b.hundred = c.hundred
and not exists ( select 1 from tenk1 d
where a.thousand = d.thousand ) );
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect
--
^
-stdin-:: Error: At function: PgSetItem
-stdin-::1:1: Error: At function: PgWhere
--
^
-stdin-::6:3: Error: At function: PgAnd
and exists ( select 1 from tenk1 c where b.hundred = c.hundred
^
-stdin-::6:7: Error: At function: PgSubLink, At function: PgSelect
and exists ( select 1 from tenk1 c where b.hundred = c.hundred
^
-stdin-:: Error: At function: PgSetItem
-stdin-::6:7: Error: At function: PgWhere
and exists ( select 1 from tenk1 c where b.hundred = c.hundred
^
-stdin-::7:20: Error: At function: PgAnd
and not exists ( select 1 from tenk1 d
^
-stdin-::7:24: Error: At function: PgNot
and not exists ( select 1 from tenk1 d
^
-stdin-::7:28: Error: At function: PgSubLink, At function: PgSelect, At function: PgSetItem
and not exists ( select 1 from tenk1 d
^
-stdin-::8:43: Error: No such column: thousand
where a.thousand = d.thousand ) );
^
--
-- Check that nested sub-selects are not pulled up if they contain volatiles
--
explain (verbose, costs off)
select x, x from
(select (select now()) as x from (values(1),(2)) v(y)) ss;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
--
^
explain (verbose, costs off)
select x, x from
(select (select random()) as x from (values(1),(2)) v(y)) ss;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (verbose, costs off)
^
explain (verbose, costs off)
select x, x from
(select (select now() where y=y) as x from (values(1),(2)) v(y)) ss;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (verbose, costs off)
^
explain (verbose, costs off)
select x, x from
(select (select random() where y=y) as x from (values(1),(2)) v(y)) ss;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (verbose, costs off)
^
--
-- Test rescan of a hashed subplan (the use of random() is to prevent the
-- sub-select from being pulled up, which would result in not hashing)
--
explain (verbose, costs off)
select sum(ss.tst::int) from
onek o cross join lateral (
select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst,
random() as r
from onek i where i.unique1 = o.unique1 ) ss
where o.ten = 0;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
--
^
select sum(ss.tst::int) from
onek o cross join lateral (
select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst,
random() as r
from onek i where i.unique1 = o.unique1 ) ss
where o.ten = 0;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RangeSubselect: unsupported lateral
select sum(ss.tst::int) from
^
--
-- Test rescan of a SetOp node
--
explain (costs off)
select count(*) from
onek o cross join lateral (
select * from onek i1 where i1.unique1 = o.unique1
except
select * from onek i2 where i2.unique1 = o.unique2
) ss
where o.ten = 1;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
--
^
select count(*) from
onek o cross join lateral (
select * from onek i1 where i1.unique1 = o.unique1
except
select * from onek i2 where i2.unique1 = o.unique2
) ss
where o.ten = 1;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RangeSubselect: unsupported lateral
select count(*) from
^
--
-- Test rescan of a RecursiveUnion node
--
explain (costs off)
select sum(o.four), sum(ss.a) from
onek o cross join lateral (
with recursive x(a) as
(select o.four as a
union
select a + 1 from x
where a < 10)
select * from x
) ss
where o.ten = 1;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
--
^
select sum(o.four), sum(ss.a) from
onek o cross join lateral (
with recursive x(a) as
(select o.four as a
union
select a + 1 from x
where a < 10)
select * from x
) ss
where o.ten = 1;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RangeSubselect: unsupported lateral
select sum(o.four), sum(ss.a) from
^
--
-- Check we don't misoptimize a NOT IN where the subquery returns no rows.
--
create temp table notinouter (a int);
create temp table notininner (b int not null);
insert into notinouter values (null), (1);
select * from notinouter where a not in (select b from notininner);
--
-- Check we behave sanely in corner case of empty SELECT list (bug #8648)
--
create temp table nocolumns();
select exists(select * from nocolumns);
--
-- Check behavior with a SubPlan in VALUES (bug #14924)
--
select val.x
from generate_series(1,10) as s(i),
lateral (
values ((select s.i + 1)), (s.i + 101)
) as val(x)
where s.i < 10 and (select val.x) < 110;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RangeSubselect: unsupported lateral
--
^
-- another variant of that (bug #16213)
explain (verbose, costs off)
select * from
(values
(3 not in (select * from (values (1), (2)) ss1)),
(false)
) ss;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
-- another variant of that (bug #16213)
^
select * from
(values
(3 not in (select * from (values (1), (2)) ss1)),
(false)
) ss;
-stdin-:: Error: Parse Sql
-stdin-::3:6: Error: SubLinks are not allowed in: VALUES
(3 not in (select * from (values (1), (2)) ss1)),
^
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect
select * from
^
-stdin-::1:1: Error: Recursive query does not have the form non-recursive-term UNION [ALL] recursive-term
select * from
^
--
-- Check sane behavior with nested IN SubLinks
--
explain (verbose, costs off)
select * from int4_tbl where
(case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
(select ten from tenk1 b);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
--
^
select * from int4_tbl where
(case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
(select ten from tenk1 b);
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect
select * from int4_tbl where
^
-stdin-:: Error: At function: PgSetItem
-stdin-::1:1: Error: At function: PgWhere
select * from int4_tbl where
^
-stdin-::2:73: Error: At function: PgSubLink, At function: PgOp
(case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
^
-stdin-::2:4: Error: At function: If, At function: Coalesce, At function: FromPg
(case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
^
-stdin-::2:17: Error: Expected computable data, but got: Unit
(case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
^
-stdin-::2:17: Error: Computable required. Atom, key, world, datasink, datasource, type, lambda are not computable
(case when f1 in (select unique1 from tenk1 a) then f1 else null end) in
^
--
-- Check for incorrect optimization when IN subquery contains a SRF
--
explain (verbose, costs off)
select * from int4_tbl o where (f1, f1) in
(select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
--
^
select * from int4_tbl o where (f1, f1) in
(select f1, generate_series(1,50) / 10 g from int4_tbl i group by f1);
-stdin-:: Error: Parse Sql
-stdin-::1:41: Error: alternative is not implemented yet : 138
select * from int4_tbl o where (f1, f1) in
^
--
-- check for over-optimization of whole-row Var referencing an Append plan
--
select (select q from
(select 1,2,3 where f1 > 0
union all
select 4,5,6.0 where f1 <= 0
) q )
from int4_tbl;
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect, At function: PgSetItem, At function: PgResultItem
--
^
-stdin-::4:8: Error: At function: PgSubLink, At function: PgSelect, At function: PgSetItem, At function: PgSelect
select (select q from
^
-stdin-::4:8: Error: At function: PgSetItem
select (select q from
^
-stdin-::5:30: Error: Column reference can't be used without FROM
(select 1,2,3 where f1 > 0
^
-stdin-::4:8: Error: At function: PgSetItem
select (select q from
^
-stdin-::7:32: Error: Column reference can't be used without FROM
select 4,5,6.0 where f1 <= 0
^
--
-- Check for sane handling of a lateral reference in a subquery's quals
-- (most of the complication here is to prevent the test case from being
-- flattened too much)
--
explain (verbose, costs off)
select * from
int4_tbl i4,
lateral (
select i4.f1 > 1 as b, 1 as id
from (select random() order by 1) as t1
union all
select true as b, 2 as id
) as t2
where b and f1 >= 0;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
--
^
select * from
int4_tbl i4,
lateral (
select i4.f1 > 1 as b, 1 as id
from (select random() order by 1) as t1
union all
select true as b, 2 as id
) as t2
where b and f1 >= 0;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RangeSubselect: unsupported lateral
select * from
^
--
-- Check that volatile quals aren't pushed down past a DISTINCT:
-- nextval() should not be called more than the nominal number of times
--
create temp sequence ts1;
-stdin-:: Error: Pre type annotation
-stdin-::1:1: Error: Unexpected tag: pgObject
--
^
select * from
(select distinct ten from tenk1) ss
where ten < 10 + nextval('ts1')
order by 1;
-stdin-:: Fatal: Execution
-stdin-::1:1: Fatal: Execution of node: YtMapReduce!
select * from
^
-stdin-::1:1: Fatal: ERROR: relation "ts1" does not exist
select * from
^
select nextval('ts1');
-stdin-:: Fatal: Execution
-stdin-::1:1: Fatal: Execution of node: Result
select nextval('ts1');
^
-stdin-::1:1: Fatal: ERROR: relation "ts1" does not exist
select nextval('ts1');
^
--
-- Check that volatile quals aren't pushed down past a set-returning function;
-- while a nonvolatile qual can be, if it doesn't reference the SRF.
--
create function tattle(x int, y int) returns bool
volatile language plpgsql as $$
begin
raise notice 'x = %, y = %', x, y;
return x > y;
end$$;
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 261
--
^
explain (verbose, costs off)
select * from
(select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss
where tattle(x, 8);
-stdin-:: Error: Parse Sql
-stdin-::1:1: Error: RawStmt: alternative is not implemented yet : 276
explain (verbose, costs off)
^
select * from
(select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss
where tattle(x, 8);
-stdin-:: Error: Parse Sql
-stdin-::2:19: Error: Generator functions are not allowed in: SELECT
(select 9 as x, unnest(array[1,2,3,11,12,13]) as u) ss
^
-stdin-:: Error: Type annotation
-stdin-::1:1: Error: At function: RemovePrefixMembers, At function: PgSelect
select * from
^
-stdin-::1:1: Error: Recursive query does not have the form non-recursive-term UNION [ALL] recursive-term
select * from
^