Hi, Alena!
On Tue, Apr 1, 2025 at 2:11 AM Alena Rybakina <a.rybak...@postgrespro.ru>
wrote:
> 4.1) explain analyze SELECT ten
>
> FROM onek t WHERE unique1 IN ( VALUES (0), ((2 IN ( SELECT unique2 FROM
> onek c WHERE c.unique2 in ((values(0),(2))))::integer)) );
>
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------
> Seq Scan on onek t (cost=180.11..410.25 rows=2 width=6) (actual
> time=5.014..13.256 rows=3.00 loops=1) Filter: (unique1 = ANY (ARRAY[0,
> ((ANY (2 = (hashed SubPlan 1).col1)))::integer])) Rows Removed by Filter:
> 10005 *Buffers: shared hit=110* SubPlan 1 -> Seq Scan on onek c
> (cost=0.00..180.10 rows=3 width=4) (actual time=0.022..4.951 rows=2.00
> loops=1) Filter: (unique2 = ANY ('{0,2}'::integer[])) Rows Removed by
> Filter: 10006 *Buffers: shared hit=55* Planning: Buffers: shared hit=6
> dirtied=1 Planning Time: 0.502 ms Execution Time: 13.348 ms (13 rows)
>
> The query plan without our patch:
>
> --------------------------------------------------------------------------------------------------------------------------------------------
> Hash Semi Join (cost=0.05..181.42 rows=2 width=6) (actual time=5.072..9.076
> rows=3.00 loops=1) Hash Cond: (t.unique1 = "*VALUES*".column1) *Buffers:
> shared hit=55 read=55* -> Seq Scan on onek t (cost=0.00..155.08
> rows=10008 width=10) (actual time=0.145..1.802 rows=10008.00 loops=1) *
> Buffers: shared hit=52 read=3* -> Hash (cost=0.03..0.03 rows=2 width=4)
> (actual time=4.908..4.912 rows=2.00 loops=1) Buckets: 1024 Batches: 1
> Memory Usage: 9kB *Buffers: shared hit=3 read=52* -> Values Scan on
> "*VALUES*" (cost=0.00..0.03 rows=2 width=4) (actual time=0.003..4.901
> rows=2.00 loops=1) *Buffers: shared hit=3 read=52* SubPlan 1 -> Hash Semi
> Join (cost=0.05..181.42 rows=2 width=4) (actual time=0.036..4.861 rows=2.00
> loops=1) Hash Cond: (c.unique2 = "*VALUES*_1".column1) *Buffers: shared
> hit=3 read=52* -> Seq Scan on onek c (cost=0.00..155.08 rows=10008
> width=4) (actual time=0.009..2.120 rows=10008.00 loops=1) *Buffers:
> shared hit=3 read=52* -> Hash (cost=0.03..0.03 rows=2 width=4) (actual
> time=0.006..0.008 rows=2.00 loops=1) Buckets: 1024 Batches: 1 Memory Usage:
> 9kB -> Values Scan on "*VALUES*_1" (cost=0.00..0.03 rows=2 width=4) (actual
> time=0.001..0.002 rows=2.00 loops=1) Planning: Buffers: shared hit=102
> read=22 Planning Time: 1.853 ms Execution Time: 9.281 ms (23 rows)
>
I think I managed to understand what is going on.
When we run a query with SOAP over a constant array
then convert_saop_to_hashed_saop_walker() provides acceleration with
hashing.
# explain analyze select * from test where val IN (5000, 4000, 9000, 2000,
1000, 140050);
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..21925.00 rows=6 width=4) (actual
time=2.015..223.984 rows=6.00 loops=1)
Filter: (val = ANY ('{5000,4000,9000,2000,1000,140050}'::integer[]))
Rows Removed by Filter: 999994
Buffers: shared hit=2228 read=2197
Planning Time: 0.246 ms
Execution Time: 224.036 ms
(6 rows)
But when there is expression or subselect, then hashing doesn't work and
query becomes slower.
# explain analyze select * from test where val IN (5000, 4000, 9000, 2000,
1000, (select 140050));
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.01..21925.01 rows=6 width=4) (actual
time=0.904..396.495 rows=6.00 loops=1)
Filter: (val = ANY (ARRAY[5000, 4000, 9000, 2000, 1000, (InitPlan
1).col1]))
Rows Removed by Filter: 999994
Buffers: shared hit=2292 read=2133
InitPlan 1
-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002
rows=1.00 loops=1)
Planning Time: 0.160 ms
Execution Time: 396.538 ms
(8 rows)
In contrast, hashing is always available with VALUES.
# explain analyze select * from test where val in (VALUES (5000), (4000),
(9000), (2000), (1000), ((select 140050)));
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Hash Semi Join (cost=0.16..17050.23 rows=6 width=4) (actual
time=1.589..225.061 rows=6.00 loops=1)
Hash Cond: (test.val = "*VALUES*".column1)
Buffers: shared hit=2356 read=2069
InitPlan 1
-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.003..0.003
rows=1.00 loops=1)
-> Seq Scan on test (cost=0.00..14425.00 rows=1000000 width=4) (actual
time=0.460..91.912 rows=1000000.00 loops=1)
Buffers: shared hit=2356 read=2069
-> Hash (cost=0.08..0.08 rows=6 width=4) (actual time=0.049..0.050
rows=6.00 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Values Scan on "*VALUES*" (cost=0.00..0.08 rows=6 width=4)
(actual time=0.009..0.032 rows=6.00 loops=1)
Planning Time: 0.627 ms
Execution Time: 225.155 ms
(12 rows)
I think we should allow our transformation only when the array is constant
(attached patchset). In future we may implement dynamic SAOP hashing, and
then allow our transformation in more cases.
------
Regards,
Alexander Korotkov
Supabase
From 30ae89e0a459b2808b05107f5fbc238704c992f1 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Sat, 29 Mar 2025 09:46:33 +0200
Subject: [PATCH v8 1/2] Extract make_SAOP_expr() function from
match_orclause_to_indexcol()
This commit extracts the code to generate ScalarArrayOpExpr on top of the list
of expressions from match_orclause_to_indexcol() into a separate function
make_SAOP_expr(). This function was extracted to be used in optimization for
conversion of 'x IN (VALUES ...)' to 'x = ANY ...'. make_SAOP_expr() is
placed in clauses.c file as only two additional headers were needed there
compared with other places.
Discussion: https://postgr.es/m/0184212d-1248-4f1f-a42d-f5cb1c1976d2%40tantorlabs.com
Author: Alena Rybakina <a.rybakina@postgrespro.ru>
Author: Andrei Lepikhov <lepihov@gmail.com>
Reviewed-by: Ivan Kush <ivan.kush@tantorlabs.com>
Reviewed-by: Alexander Korotkov <aekorotkov@gmail.com>
---
src/backend/optimizer/path/indxpath.c | 62 +-------------------
src/backend/optimizer/util/clauses.c | 81 +++++++++++++++++++++++++++
src/include/optimizer/optimizer.h | 5 ++
3 files changed, 88 insertions(+), 60 deletions(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 6386ce82253..8bb4c401f84 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -33,10 +33,8 @@
#include "optimizer/paths.h"
#include "optimizer/prep.h"
#include "optimizer/restrictinfo.h"
-#include "utils/array.h"
#include "utils/lsyscache.h"
#include "utils/selfuncs.h"
-#include "utils/syscache.h"
/* XXX see PartCollMatchesExprColl */
@@ -3304,7 +3302,6 @@ match_orclause_to_indexcol(PlannerInfo *root,
BoolExpr *orclause = (BoolExpr *) rinfo->orclause;
Node *indexExpr = NULL;
List *consts = NIL;
- Node *arrayNode = NULL;
ScalarArrayOpExpr *saopexpr = NULL;
Oid matchOpno = InvalidOid;
IndexClause *iclause;
@@ -3475,63 +3472,8 @@ match_orclause_to_indexcol(PlannerInfo *root,
return NULL;
}
- /*
- * Assemble an array from the list of constants. It seems more profitable
- * to build a const array. But in the presence of other nodes, we don't
- * have a specific value here and must employ an ArrayExpr instead.
- */
- if (haveNonConst)
- {
- ArrayExpr *arrayExpr = makeNode(ArrayExpr);
-
- /* array_collid will be set by parse_collate.c */
- arrayExpr->element_typeid = consttype;
- arrayExpr->array_typeid = arraytype;
- arrayExpr->multidims = false;
- arrayExpr->elements = consts;
- arrayExpr->location = -1;
-
- arrayNode = (Node *) arrayExpr;
- }
- else
- {
- int16 typlen;
- bool typbyval;
- char typalign;
- Datum *elems;
- int i = 0;
- ArrayType *arrayConst;
-
- get_typlenbyvalalign(consttype, &typlen, &typbyval, &typalign);
-
- elems = (Datum *) palloc(sizeof(Datum) * list_length(consts));
- foreach_node(Const, value, consts)
- {
- Assert(!value->constisnull);
-
- elems[i++] = value->constvalue;
- }
-
- arrayConst = construct_array(elems, i, consttype,
- typlen, typbyval, typalign);
- arrayNode = (Node *) makeConst(arraytype, -1, inputcollid,
- -1, PointerGetDatum(arrayConst),
- false, false);
-
- pfree(elems);
- list_free(consts);
- }
-
- /* Build the SAOP expression node */
- saopexpr = makeNode(ScalarArrayOpExpr);
- saopexpr->opno = matchOpno;
- saopexpr->opfuncid = get_opcode(matchOpno);
- saopexpr->hashfuncid = InvalidOid;
- saopexpr->negfuncid = InvalidOid;
- saopexpr->useOr = true;
- saopexpr->inputcollid = inputcollid;
- saopexpr->args = list_make2(indexExpr, arrayNode);
- saopexpr->location = -1;
+ saopexpr = make_SAOP_expr(matchOpno, indexExpr, consttype, inputcollid,
+ inputcollid, consts, haveNonConst);
/*
* Finally, build an IndexClause based on the SAOP node. Use
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 43dfecfb47f..92f347d0d24 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -40,7 +40,9 @@
#include "optimizer/planmain.h"
#include "parser/analyze.h"
#include "parser/parse_coerce.h"
+#include "parser/parse_collate.h"
#include "parser/parse_func.h"
+#include "parser/parse_oper.h"
#include "rewrite/rewriteHandler.h"
#include "rewrite/rewriteManip.h"
#include "tcop/tcopprot.h"
@@ -5439,3 +5441,82 @@ pull_paramids_walker(Node *node, Bitmapset **context)
}
return expression_tree_walker(node, pull_paramids_walker, context);
}
+
+/*
+ * Build ScalarArrayOpExpr on top of 'exprs.' 'haveNonConst' indicates
+ * whether at least one of the expressions is not Const. When it's false,
+ * the array constant is built directly; otherwise, we have to build a child
+ * ArrayExpr. The 'exprs' list gets freed if not directly used in the output
+ * expression tree.
+ */
+ScalarArrayOpExpr *
+make_SAOP_expr(Oid oper, Node *leftexpr, Oid coltype, Oid arraycollid,
+ Oid inputcollid, List *exprs, bool haveNonConst)
+{
+ Node *arrayNode = NULL;
+ ScalarArrayOpExpr *saopexpr = NULL;
+ Oid arraytype = get_array_type(coltype);
+
+ if (!OidIsValid(arraytype))
+ return NULL;
+
+ /*
+ * Assemble an array from the list of constants. It seems more profitable
+ * to build a const array. But in the presence of other nodes, we don't
+ * have a specific value here and must employ an ArrayExpr instead.
+ */
+ if (haveNonConst)
+ {
+ ArrayExpr *arrayExpr = makeNode(ArrayExpr);
+
+ /* array_collid will be set by parse_collate.c */
+ arrayExpr->element_typeid = coltype;
+ arrayExpr->array_typeid = arraytype;
+ arrayExpr->multidims = false;
+ arrayExpr->elements = exprs;
+ arrayExpr->location = -1;
+
+ arrayNode = (Node *) arrayExpr;
+ }
+ else
+ {
+ int16 typlen;
+ bool typbyval;
+ char typalign;
+ Datum *elems;
+ int i = 0;
+ ArrayType *arrayConst;
+
+ get_typlenbyvalalign(coltype, &typlen, &typbyval, &typalign);
+
+ elems = (Datum *) palloc(sizeof(Datum) * list_length(exprs));
+ foreach_node(Const, value, exprs)
+ {
+ Assert(!value->constisnull);
+
+ elems[i++] = value->constvalue;
+ }
+
+ arrayConst = construct_array(elems, i, coltype,
+ typlen, typbyval, typalign);
+ arrayNode = (Node *) makeConst(arraytype, -1, arraycollid,
+ -1, PointerGetDatum(arrayConst),
+ false, false);
+
+ pfree(elems);
+ list_free(exprs);
+ }
+
+ /* Build the SAOP expression node */
+ saopexpr = makeNode(ScalarArrayOpExpr);
+ saopexpr->opno = oper;
+ saopexpr->opfuncid = get_opcode(oper);
+ saopexpr->hashfuncid = InvalidOid;
+ saopexpr->negfuncid = InvalidOid;
+ saopexpr->useOr = true;
+ saopexpr->inputcollid = inputcollid;
+ saopexpr->args = list_make2(leftexpr, arrayNode);
+ saopexpr->location = -1;
+
+ return saopexpr;
+}
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index 78e05d88c8e..546828b54bd 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -158,6 +158,11 @@ extern List *expand_function_arguments(List *args, bool include_out_arguments,
Oid result_type,
struct HeapTupleData *func_tuple);
+extern ScalarArrayOpExpr *make_SAOP_expr(Oid oper, Node *leftexpr,
+ Oid coltype, Oid arraycollid,
+ Oid inputcollid, List *exprs,
+ bool haveNonConst);
+
/* in util/predtest.c: */
extern bool predicate_implied_by(List *predicate_list, List *clause_list,
--
2.39.5 (Apple Git-154)
From dff14085a1f840be527228846b42513d03f915b1 Mon Sep 17 00:00:00 2001
From: Alexander Korotkov <akorotkov@postgresql.org>
Date: Sat, 29 Mar 2025 12:35:51 +0200
Subject: [PATCH v8 2/2] Convert 'x IN (VALUES ...)' to 'x = ANY ...' then
appropriate
This commit implements the automatic conversion of 'x IN (VALUES ...)' into
ScalarArrayOpExpr. That simplifies the query tree, eliminating the appearance
of an unnecessary join.
Since VALUES describes a relational table, and the value of such a list is
a table row, the optimizer will likely face an underestimation problem due to
the inability to estimate cardinality through MCV statistics. The cardinality
evaluation mechanism can work with the array inclusion check operation.
If the array is small enough (< 100 elements), it will perform a statistical
evaluation element by element.
We perform the transformation in the convert_ANY_sublink_to_join() if VALUES
RTE is proper and the transformation is convertible. The conversion is only
possible for operations on scalar values, not rows.
Discussion: https://postgr.es/m/0184212d-1248-4f1f-a42d-f5cb1c1976d2%40tantorlabs.com
Author: Alena Rybakina <a.rybakina@postgrespro.ru>
Author: Andrei Lepikhov <lepihov@gmail.com>
Reviewed-by: Ivan Kush <ivan.kush@tantorlabs.com>
Reviewed-by: Alexander Korotkov <aekorotkov@gmail.com>
---
src/backend/optimizer/plan/subselect.c | 75 ++++
src/backend/optimizer/prep/prepjointree.c | 12 +
src/backend/optimizer/util/clauses.c | 14 +-
src/include/optimizer/subselect.h | 3 +
src/test/regress/expected/subselect.out | 446 ++++++++++++++++++++++
src/test/regress/sql/subselect.sql | 158 ++++++++
6 files changed, 703 insertions(+), 5 deletions(-)
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 8230cbea3c3..e0d1899c29b 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -1214,6 +1214,81 @@ inline_cte_walker(Node *node, inline_cte_walker_context *context)
return expression_tree_walker(node, inline_cte_walker, context);
}
+/*
+ * Attempt to transform 'testexpr' over the VALUES subquery into
+ * a ScalarArrayOpExpr.
+ *
+ * Return transformed ScalarArrayOpExpr or NULL if transformation isn't
+ * allowed.
+ */
+ScalarArrayOpExpr *
+convert_VALUES_to_ANY(PlannerInfo *root, Node *testexpr, Query *values)
+{
+ RangeTblEntry *rte;
+ Node *leftop;
+ Node *rightop;
+ Oid opno;
+ ListCell *lc;
+ Oid inputcollid;
+ bool haveNonConst = false;
+ List *exprs = NIL;
+
+ /*
+ * Check we have a binary operator over a single-column subquery with no
+ * joins and no LIMIT/OFFSET/ORDER BY clauses.
+ */
+ if (!IsA(testexpr, OpExpr) ||
+ list_length(((OpExpr *) testexpr)->args) != 2 ||
+ list_length(values->targetList) > 1 ||
+ values->limitCount != NULL ||
+ values->limitOffset != NULL ||
+ values->sortClause != NIL ||
+ list_length(values->rtable) != 1)
+ return NULL;
+
+ rte = linitial_node(RangeTblEntry, values->rtable);
+ leftop = linitial(((OpExpr *) testexpr)->args);
+ rightop = lsecond(((OpExpr *) testexpr)->args);
+ opno = ((OpExpr *) testexpr)->opno;
+ inputcollid = ((OpExpr *) testexpr)->inputcollid;
+
+ /*
+ * Also, check that only RTE corresponds to VALUES; the list of values has
+ * at least two items and no volatile functions.
+ */
+ if (rte->rtekind != RTE_VALUES ||
+ list_length(rte->values_lists) < 2 ||
+ contain_volatile_functions((Node *) rte->values_lists))
+ return NULL;
+
+ foreach(lc, rte->values_lists)
+ {
+ List *elem = lfirst(lc);
+ Node *value = linitial(elem);
+
+ /*
+ * Prepare an evaluation of the right side of the operator with
+ * substitution of the given value.
+ */
+ value = convert_testexpr(root, rightop, list_make1(value));
+
+ /*
+ * Try to evaluate constant expressions. We could get Const as a
+ * result.
+ */
+ value = eval_const_expressions(root, value);
+
+ if (!IsA(value, Const))
+ return NULL;
+
+ exprs = lappend(exprs, value);
+ }
+
+ /* Finally, build ScalarArrayOpExpr at the top of the 'exprs' list. */
+ return make_SAOP_expr(opno, leftop, exprType(rightop),
+ linitial_oid(rte->colcollations), inputcollid,
+ exprs, haveNonConst);
+}
/*
* convert_ANY_sublink_to_join: try to convert an ANY SubLink to a join
diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index d131a5bbc59..87dc6f56b57 100644
--- a/src/backend/optimizer/prep/prepjointree.c
+++ b/src/backend/optimizer/prep/prepjointree.c
@@ -664,6 +664,18 @@ pull_up_sublinks_qual_recurse(PlannerInfo *root, Node *node,
/* Is it a convertible ANY or EXISTS clause? */
if (sublink->subLinkType == ANY_SUBLINK)
{
+ ScalarArrayOpExpr *saop;
+
+ if ((saop = convert_VALUES_to_ANY(root,
+ sublink->testexpr,
+ (Query *) sublink->subselect)) != NULL)
+
+ /*
+ * The VALUES sequence was simplified. Nothing more to do
+ * here.
+ */
+ return (Node *) saop;
+
if ((j = convert_ANY_sublink_to_join(root, sublink,
available_rels1)) != NULL)
{
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 92f347d0d24..85e418e5c2c 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -5484,26 +5484,30 @@ make_SAOP_expr(Oid oper, Node *leftexpr, Oid coltype, Oid arraycollid,
bool typbyval;
char typalign;
Datum *elems;
+ bool *nulls;
int i = 0;
ArrayType *arrayConst;
+ int dims[1] = {list_length(exprs)};
+ int lbs[1] = {1};
get_typlenbyvalalign(coltype, &typlen, &typbyval, &typalign);
elems = (Datum *) palloc(sizeof(Datum) * list_length(exprs));
+ nulls = (bool *) palloc(sizeof(bool) * list_length(exprs));
foreach_node(Const, value, exprs)
{
- Assert(!value->constisnull);
-
- elems[i++] = value->constvalue;
+ elems[i] = value->constvalue;
+ nulls[i++] = value->constisnull;
}
- arrayConst = construct_array(elems, i, coltype,
- typlen, typbyval, typalign);
+ arrayConst = construct_md_array(elems, nulls, 1, dims, lbs,
+ coltype, typlen, typbyval, typalign);
arrayNode = (Node *) makeConst(arraytype, -1, arraycollid,
-1, PointerGetDatum(arrayConst),
false, false);
pfree(elems);
+ pfree(nulls);
list_free(exprs);
}
diff --git a/src/include/optimizer/subselect.h b/src/include/optimizer/subselect.h
index 8b9ab6e5792..48507eb4bca 100644
--- a/src/include/optimizer/subselect.h
+++ b/src/include/optimizer/subselect.h
@@ -17,6 +17,9 @@
#include "nodes/plannodes.h"
extern void SS_process_ctes(PlannerInfo *root);
+extern ScalarArrayOpExpr *convert_VALUES_to_ANY(PlannerInfo *root,
+ Node *testexpr,
+ Query *values);
extern JoinExpr *convert_ANY_sublink_to_join(PlannerInfo *root,
SubLink *sublink,
Relids available_rels);
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index d0db8a412ff..c6867720a69 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -2652,3 +2652,449 @@ ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
Filter: (odd = b.odd)
(16 rows)
+--
+-- Test VALUES to ARRAY (VtA) transformation
+--
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek, (VALUES(147, 'RFAAAA'), (931, 'VJAAAA')) AS v (i, j)
+ WHERE onek.unique1 = v.i AND onek.stringu1 = v.j;
+ QUERY PLAN
+-------------------------------------------------------------
+ Nested Loop
+ -> Values Scan on "*VALUES*"
+ -> Index Scan using onek_stringu1 on onek
+ Index Cond: (stringu1 = ("*VALUES*".column2)::text)
+ Filter: ("*VALUES*".column1 = unique1)
+(5 rows)
+
+SELECT * FROM onek,
+ (VALUES ((SELECT i FROM
+ (VALUES(10000), (2), (389), (1000), (2000), ((SELECT 10029))) AS foo(i)
+ ORDER BY i ASC LIMIT 1))) bar (i)
+ WHERE onek.unique1 = bar.i;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 | i
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------+---
+ 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx | 2
+(1 row)
+
+-- Forbid VTA transformation for a composite argument
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+ WHERE (unique1,ten) IN (VALUES (1,1), (20,0), (99,9), (17,99))
+ ORDER BY unique1;
+ QUERY PLAN
+-----------------------------------------------------------------
+ Sort
+ Sort Key: onek.unique1
+ -> Nested Loop
+ -> HashAggregate
+ Group Key: "*VALUES*".column1, "*VALUES*".column2
+ -> Values Scan on "*VALUES*"
+ -> Index Scan using onek_unique1 on onek
+ Index Cond: (unique1 = "*VALUES*".column1)
+ Filter: ("*VALUES*".column2 = ten)
+(9 rows)
+
+-- Values to Array transformation
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+ WHERE unique1 IN (VALUES(10000), (2), (389), (1000), (2000), (10029))
+ ORDER BY unique1;
+ QUERY PLAN
+----------------------------------------------------------------------------------------
+ Sort
+ Sort Key: unique1
+ -> Bitmap Heap Scan on onek
+ Recheck Cond: (unique1 = ANY ('{10000,2,389,1000,2000,10029}'::integer[]))
+ -> Bitmap Index Scan on onek_unique1
+ Index Cond: (unique1 = ANY ('{10000,2,389,1000,2000,10029}'::integer[]))
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+ WHERE unique1 IN (VALUES(1200), (1));
+ QUERY PLAN
+-------------------------------------------------------------
+ Bitmap Heap Scan on onek
+ Recheck Cond: (unique1 = ANY ('{1200,1}'::integer[]))
+ -> Bitmap Index Scan on onek_unique1
+ Index Cond: (unique1 = ANY ('{1200,1}'::integer[]))
+(4 rows)
+
+-- TODO: Recursively evaluate constant queries.
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+ WHERE unique1 IN (SELECT x*x FROM (VALUES(1200), (1)) AS x(x));
+ QUERY PLAN
+---------------------------------------------------------------------------
+ Nested Loop
+ -> Unique
+ -> Sort
+ Sort Key: (("*VALUES*".column1 * "*VALUES*".column1))
+ -> Values Scan on "*VALUES*"
+ -> Index Scan using onek_unique1 on onek
+ Index Cond: (unique1 = ("*VALUES*".column1 * "*VALUES*".column1))
+(7 rows)
+
+-- transformation doesn't apply because values RTE exists in subquery
+EXPLAIN (COSTS OFF)
+select * FROM onek,
+ (VALUES ((select i FROM
+ (VALUES(10000), (2), (389), (1000), (2000), (10029)) as foo(i)
+ ORDER BY i ASC LIMIT 1))) bar (i)
+ WHERE onek.unique1 = bar.i;
+ QUERY PLAN
+-------------------------------------------------
+ Index Scan using onek_unique1 on onek
+ Index Cond: (unique1 = (InitPlan 2).col1)
+ InitPlan 1
+ -> Limit
+ -> Sort
+ Sort Key: "*VALUES*".column1
+ -> Values Scan on "*VALUES*"
+ InitPlan 2
+ -> Limit
+ -> Sort
+ Sort Key: "*VALUES*_1".column1
+ -> Values Scan on "*VALUES*_1"
+(12 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT unique1, stringu1 FROM onek WHERE stringu1::name IN (VALUES('RFAAAA'), ('VJAAAA'));
+ QUERY PLAN
+------------------------------------------------------------------
+ Bitmap Heap Scan on onek
+ Recheck Cond: (stringu1 = ANY ('{RFAAAA,VJAAAA}'::text[]))
+ -> Bitmap Index Scan on onek_stringu1
+ Index Cond: (stringu1 = ANY ('{RFAAAA,VJAAAA}'::text[]))
+(4 rows)
+
+-- transformation doesn't apply because of type differences
+EXPLAIN (COSTS OFF)
+SELECT unique1, stringu1 FROM onek WHERE stringu1::text IN (VALUES('RFAAAA'), ('VJAAAA'));
+ QUERY PLAN
+----------------------------------------------------------------
+ Seq Scan on onek
+ Filter: ((stringu1)::text = ANY ('{RFAAAA,VJAAAA}'::text[]))
+(2 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * from onek WHERE unique1 in (VALUES(1200::bigint), (1));
+ QUERY PLAN
+------------------------------------------------------------
+ Bitmap Heap Scan on onek
+ Recheck Cond: (unique1 = ANY ('{1200,1}'::bigint[]))
+ -> Bitmap Index Scan on onek_unique1
+ Index Cond: (unique1 = ANY ('{1200,1}'::bigint[]))
+(4 rows)
+
+-- Recursive VTA transformation
+EXPLAIN (COSTS OFF)
+SELECT * FROM (
+ SELECT ten FROM onek
+ WHERE unique1 IN (VALUES(10000), (2), (389), (1000), (2000), (10029))
+ OFFSET 0
+);
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Bitmap Heap Scan on onek
+ Recheck Cond: (unique1 = ANY ('{10000,2,389,1000,2000,10029}'::integer[]))
+ -> Bitmap Index Scan on onek_unique1
+ Index Cond: (unique1 = ANY ('{10000,2,389,1000,2000,10029}'::integer[]))
+(4 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT (SELECT avg(ten::integer) FROM onek
+WHERE unique1 IN (VALUES(10000), (2), (389), (1000), (2000), (10029)));
+ QUERY PLAN
+------------------------------------------------------------------------------------------------
+ Result
+ InitPlan 1
+ -> Aggregate
+ -> Bitmap Heap Scan on onek
+ Recheck Cond: (unique1 = ANY ('{10000,2,389,1000,2000,10029}'::integer[]))
+ -> Bitmap Index Scan on onek_unique1
+ Index Cond: (unique1 = ANY ('{10000,2,389,1000,2000,10029}'::integer[]))
+(7 rows)
+
+-- VTA shouldn't depend on the side of the join probing with the VALUES expression.
+EXPLAIN (COSTS OFF)
+SELECT c.oid,c.relname FROM pg_class c JOIN pg_am a USING (oid)
+WHERE a.oid IN (VALUES (1), (2));
+ QUERY PLAN
+---------------------------------------------------------
+ Nested Loop
+ -> Seq Scan on pg_am a
+ Filter: (oid = ANY ('{1,2}'::oid[]))
+ -> Index Scan using pg_class_oid_index on pg_class c
+ Index Cond: (oid = a.oid)
+(5 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT c.oid,c.relname FROM pg_class c JOIN pg_am a USING (oid)
+WHERE c.oid IN (VALUES (1), (2));
+ QUERY PLAN
+---------------------------------------------------------------
+ Hash Join
+ Hash Cond: (a.oid = c.oid)
+ -> Seq Scan on pg_am a
+ -> Hash
+ -> Index Scan using pg_class_oid_index on pg_class c
+ Index Cond: (oid = ANY ('{1,2}'::oid[]))
+(6 rows)
+
+-- Complexity of test expression doesn't matter
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE sin(two)+four IN (VALUES (sin(0.5)), (2));
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on onek
+ Filter: ((sin((two)::double precision) + (four)::double precision) = ANY ('{0.479425538604203,2}'::double precision[]))
+(2 rows)
+
+EXPLAIN (COSTS OFF)
+-- VTA Doesn't allow NULLs in the list - may be until a better solution.
+SELECT ten FROM onek WHERE sin(two)+four IN (VALUES (sin(0.5)), (NULL), (2));
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on onek
+ Filter: ((sin((two)::double precision) + (four)::double precision) = ANY ('{0.479425538604203,NULL,2}'::double precision[]))
+(2 rows)
+
+PREPARE test (int,numeric, text) AS
+ SELECT ten FROM onek WHERE sin(two)*four/($3::real) IN (VALUES (sin($2)), (2), ($1));
+EXPLAIN (COSTS OFF) EXECUTE test(42, 3.14, '-1.5');
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on onek
+ Filter: (((sin((two)::double precision) * (four)::double precision) / '-1.5'::real) = ANY ('{0.0015926529164868282,2,42}'::double precision[]))
+(2 rows)
+
+EXPLAIN (COSTS OFF) EXECUTE test(NULL, 3.14, NULL);
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+EXPLAIN (COSTS OFF) EXECUTE test(NULL, 3.14, '-1.5');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on onek
+ Filter: (((sin((two)::double precision) * (four)::double precision) / '-1.5'::real) = ANY ('{0.0015926529164868282,2,NULL}'::double precision[]))
+(2 rows)
+
+PREPARE test1 (int,numeric, text) AS
+ SELECT ten FROM onek
+ WHERE sin(two)*four/($3::real) IN (VALUES (sin($2)), (2), ($2), ($2), ($1));
+-- VTA forbidden because sin($1) can't be evaluated to Const.
+EXPLAIN (COSTS OFF) EXECUTE test1(NULL, 2, '2');
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on onek
+ Filter: (((sin((two)::double precision) * (four)::double precision) / '2'::real) = ANY ('{0.9092974268256817,2,2,2,NULL}'::double precision[]))
+(2 rows)
+
+PREPARE test2 (int,numeric, text) AS
+ SELECT ten FROM onek
+ WHERE sin(two)*four/($3::real) IN (VALUES (2), ($2), ($2), ($1));
+-- VTA forbidden because of unresolved casting of numeric parameter to common type
+EXPLAIN (COSTS OFF) EXECUTE test2(2, 2, '2');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on onek
+ Filter: (((sin((two)::double precision) * (four)::double precision) / '2'::real) = ANY ('{2,2,2,2}'::double precision[]))
+(2 rows)
+
+PREPARE test3 (int,int, text) AS
+ SELECT ten FROM onek
+ WHERE sin(two)*four/($3::real) IN (VALUES (2), ($2), ($2), ($1));
+EXPLAIN (COSTS OFF) EXECUTE test3(2, 2, '2');
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------
+ Seq Scan on onek
+ Filter: (((sin((two)::double precision) * (four)::double precision) / '2'::real) = ANY ('{2,2,2,2}'::double precision[]))
+(2 rows)
+
+-- Be careful in case of sort_specification clauses like LIMIT, OFFSET etc.
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (1), (2) OFFSET 1);
+ QUERY PLAN
+----------------------------------------------------
+ Nested Loop
+ -> HashAggregate
+ Group Key: "*VALUES*".column1
+ -> Limit
+ -> Values Scan on "*VALUES*"
+ -> Index Scan using onek_unique1 on onek
+ Index Cond: (unique1 = "*VALUES*".column1)
+(7 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (1), (2) ORDER BY 1);
+ QUERY PLAN
+----------------------------------------------------
+ Nested Loop
+ -> Unique
+ -> Sort
+ Sort Key: "*VALUES*".column1
+ -> Sort
+ Sort Key: "*VALUES*".column1
+ -> Values Scan on "*VALUES*"
+ -> Index Scan using onek_unique1 on onek
+ Index Cond: (unique1 = "*VALUES*".column1)
+(9 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (1), (2) LIMIT 1);
+ QUERY PLAN
+----------------------------------------------------
+ Nested Loop
+ -> HashAggregate
+ Group Key: "*VALUES*".column1
+ -> Limit
+ -> Values Scan on "*VALUES*"
+ -> Index Scan using onek_unique1 on onek
+ Index Cond: (unique1 = "*VALUES*".column1)
+(7 rows)
+
+SELECT oid,relname FROM pg_class WHERE oid IN (VALUES (sin(0.5)), (2)); -- ERROR
+ERROR: operator does not exist: oid = double precision
+LINE 1: SELECT oid,relname FROM pg_class WHERE oid IN (VALUES (sin(0...
+ ^
+HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (sin(0.5)), (2));
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Seq Scan on onek
+ Filter: ((unique1)::double precision = ANY ('{0.479425538604203,2}'::double precision[]))
+(2 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t
+WHERE unique1 IN (VALUES (0), ((2 IN (SELECT unique2 FROM onek c
+ WHERE c.unique2 = t.unique1))::integer));
+ QUERY PLAN
+------------------------------------------------------------
+ Nested Loop Semi Join
+ -> Seq Scan on onek t
+ -> Values Scan on "*VALUES*"
+ Filter: (t.unique1 = column1)
+ SubPlan 1
+ -> Index Only Scan using onek_unique2 on onek c
+ Index Cond: (unique2 = t.unique1)
+(7 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t
+WHERE unique1 IN (VALUES (0), ((2 IN (SELECT unique2 FROM onek c
+ WHERE c.unique2 IN (VALUES (sin(0.5)), (2))))::integer));
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ -> Unique
+ -> Sort
+ Sort Key: "*VALUES*".column1
+ -> Values Scan on "*VALUES*"
+ SubPlan 1
+ -> Index Only Scan using onek_unique2 on onek c
+ Filter: ((unique2)::double precision = ANY ('{0.479425538604203,2}'::double precision[]))
+ -> Index Scan using onek_unique1 on onek t
+ Index Cond: (unique1 = "*VALUES*".column1)
+(10 rows)
+
+-- Doesn't allow the VtA dispite of possibility. XXX: should we improve it later?
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE unique1 IN (VALUES (0), ((2 IN (
+ (VALUES (1), (3))))::integer)
+);
+ QUERY PLAN
+-------------------------------------------------------
+ Nested Loop
+ -> Unique
+ -> Sort
+ Sort Key: "*VALUES*".column1
+ -> Values Scan on "*VALUES*"
+ SubPlan 1
+ -> Values Scan on "*VALUES*_1"
+ -> Index Scan using onek_unique1 on onek t
+ Index Cond: (unique1 = "*VALUES*".column1)
+(9 rows)
+
+-- The VtA works. Do we need to constify the 'SELECT 3' subquery?
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE unique1 IN (VALUES (0), ((2 IN
+ (SELECT (3)))::integer)
+);
+ QUERY PLAN
+----------------------------------------------------
+ Nested Loop
+ -> Unique
+ -> Sort
+ Sort Key: "*VALUES*".column1
+ -> Values Scan on "*VALUES*"
+ SubPlan 1
+ -> Result
+ -> Index Scan using onek_unique1 on onek t
+ Index Cond: (unique1 = "*VALUES*".column1)
+(9 rows)
+
+-- Alow to transformation and hold conversion between types of colemns and
+-- declared type of column pointed in RTE
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE unique1 IN (0, 1.0 IN (0, 3)::integer);
+ QUERY PLAN
+----------------------------------------------------------
+ Bitmap Heap Scan on onek t
+ Recheck Cond: (unique1 = ANY ('{0,0}'::integer[]))
+ -> Bitmap Index Scan on onek_unique1
+ Index Cond: (unique1 = ANY ('{0,0}'::integer[]))
+(4 rows)
+
+EXPLAIN
+SELECT ten FROM onek t WHERE 1 IN ((VALUES (1), (3)));
+ QUERY PLAN
+----------------------------------------------------------
+ Seq Scan on onek t (cost=0.00..45.00 rows=1000 width=4)
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE 1.0::integer IN ((VALUES (1), (3)));
+ QUERY PLAN
+--------------------
+ Seq Scan on onek t
+(1 row)
+
+-- Don't allow transformation because of imcompatibility of types
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE 1.0 IN ((VALUES (1), (3))::integer);
+ QUERY PLAN
+---------------------------------------------------------
+ Result
+ One-Time Filter: (1.0 = ((InitPlan 1).col1)::numeric)
+ InitPlan 1
+ -> Values Scan on "*VALUES*"
+ -> Seq Scan on onek t
+(5 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE 1.0 IN (VALUES (1), (3));
+ QUERY PLAN
+--------------------
+ Seq Scan on onek t
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek t1, lateral (SELECT * FROM onek t2 WHERE t2.ten IN (values (t1.ten), (1)));
+ QUERY PLAN
+--------------------------------------------------
+ Nested Loop
+ -> Seq Scan on onek t1
+ -> Hash Semi Join
+ Hash Cond: (t2.ten = "*VALUES*".column1)
+ -> Seq Scan on onek t2
+ -> Hash
+ -> Values Scan on "*VALUES*"
+(7 rows)
+
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index 6ed3636a9e4..088619f0ffc 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -1202,3 +1202,161 @@ WHERE a.thousand < 750;
explain (costs off)
SELECT * FROM tenk1 A LEFT JOIN tenk2 B
ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
+
+--
+-- Test VALUES to ARRAY (VtA) transformation
+--
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek, (VALUES(147, 'RFAAAA'), (931, 'VJAAAA')) AS v (i, j)
+ WHERE onek.unique1 = v.i AND onek.stringu1 = v.j;
+
+SELECT * FROM onek,
+ (VALUES ((SELECT i FROM
+ (VALUES(10000), (2), (389), (1000), (2000), ((SELECT 10029))) AS foo(i)
+ ORDER BY i ASC LIMIT 1))) bar (i)
+ WHERE onek.unique1 = bar.i;
+
+-- Forbid VTA transformation for a composite argument
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+ WHERE (unique1,ten) IN (VALUES (1,1), (20,0), (99,9), (17,99))
+ ORDER BY unique1;
+
+-- Values to Array transformation
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+ WHERE unique1 IN (VALUES(10000), (2), (389), (1000), (2000), (10029))
+ ORDER BY unique1;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+ WHERE unique1 IN (VALUES(1200), (1));
+
+-- TODO: Recursively evaluate constant queries.
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek
+ WHERE unique1 IN (SELECT x*x FROM (VALUES(1200), (1)) AS x(x));
+
+-- transformation doesn't apply because values RTE exists in subquery
+EXPLAIN (COSTS OFF)
+select * FROM onek,
+ (VALUES ((select i FROM
+ (VALUES(10000), (2), (389), (1000), (2000), (10029)) as foo(i)
+ ORDER BY i ASC LIMIT 1))) bar (i)
+ WHERE onek.unique1 = bar.i;
+
+EXPLAIN (COSTS OFF)
+SELECT unique1, stringu1 FROM onek WHERE stringu1::name IN (VALUES('RFAAAA'), ('VJAAAA'));
+
+-- transformation doesn't apply because of type differences
+EXPLAIN (COSTS OFF)
+SELECT unique1, stringu1 FROM onek WHERE stringu1::text IN (VALUES('RFAAAA'), ('VJAAAA'));
+
+EXPLAIN (COSTS OFF)
+SELECT * from onek WHERE unique1 in (VALUES(1200::bigint), (1));
+
+-- Recursive VTA transformation
+EXPLAIN (COSTS OFF)
+SELECT * FROM (
+ SELECT ten FROM onek
+ WHERE unique1 IN (VALUES(10000), (2), (389), (1000), (2000), (10029))
+ OFFSET 0
+);
+
+EXPLAIN (COSTS OFF)
+SELECT (SELECT avg(ten::integer) FROM onek
+WHERE unique1 IN (VALUES(10000), (2), (389), (1000), (2000), (10029)));
+
+-- VTA shouldn't depend on the side of the join probing with the VALUES expression.
+EXPLAIN (COSTS OFF)
+SELECT c.oid,c.relname FROM pg_class c JOIN pg_am a USING (oid)
+WHERE a.oid IN (VALUES (1), (2));
+EXPLAIN (COSTS OFF)
+SELECT c.oid,c.relname FROM pg_class c JOIN pg_am a USING (oid)
+WHERE c.oid IN (VALUES (1), (2));
+
+-- Complexity of test expression doesn't matter
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE sin(two)+four IN (VALUES (sin(0.5)), (2));
+EXPLAIN (COSTS OFF)
+
+-- VTA Doesn't allow NULLs in the list - may be until a better solution.
+SELECT ten FROM onek WHERE sin(two)+four IN (VALUES (sin(0.5)), (NULL), (2));
+
+PREPARE test (int,numeric, text) AS
+ SELECT ten FROM onek WHERE sin(two)*four/($3::real) IN (VALUES (sin($2)), (2), ($1));
+EXPLAIN (COSTS OFF) EXECUTE test(42, 3.14, '-1.5');
+EXPLAIN (COSTS OFF) EXECUTE test(NULL, 3.14, NULL);
+EXPLAIN (COSTS OFF) EXECUTE test(NULL, 3.14, '-1.5');
+PREPARE test1 (int,numeric, text) AS
+ SELECT ten FROM onek
+ WHERE sin(two)*four/($3::real) IN (VALUES (sin($2)), (2), ($2), ($2), ($1));
+-- VTA forbidden because sin($1) can't be evaluated to Const.
+EXPLAIN (COSTS OFF) EXECUTE test1(NULL, 2, '2');
+
+PREPARE test2 (int,numeric, text) AS
+ SELECT ten FROM onek
+ WHERE sin(two)*four/($3::real) IN (VALUES (2), ($2), ($2), ($1));
+-- VTA forbidden because of unresolved casting of numeric parameter to common type
+EXPLAIN (COSTS OFF) EXECUTE test2(2, 2, '2');
+PREPARE test3 (int,int, text) AS
+ SELECT ten FROM onek
+ WHERE sin(two)*four/($3::real) IN (VALUES (2), ($2), ($2), ($1));
+
+EXPLAIN (COSTS OFF) EXECUTE test3(2, 2, '2');
+
+-- Be careful in case of sort_specification clauses like LIMIT, OFFSET etc.
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (1), (2) OFFSET 1);
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (1), (2) ORDER BY 1);
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (1), (2) LIMIT 1);
+
+SELECT oid,relname FROM pg_class WHERE oid IN (VALUES (sin(0.5)), (2)); -- ERROR
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek WHERE unique1 IN (VALUES (sin(0.5)), (2));
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t
+WHERE unique1 IN (VALUES (0), ((2 IN (SELECT unique2 FROM onek c
+ WHERE c.unique2 = t.unique1))::integer));
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t
+WHERE unique1 IN (VALUES (0), ((2 IN (SELECT unique2 FROM onek c
+ WHERE c.unique2 IN (VALUES (sin(0.5)), (2))))::integer));
+
+-- Doesn't allow the VtA dispite of possibility. XXX: should we improve it later?
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE unique1 IN (VALUES (0), ((2 IN (
+ (VALUES (1), (3))))::integer)
+);
+
+-- The VtA works. Do we need to constify the 'SELECT 3' subquery?
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE unique1 IN (VALUES (0), ((2 IN
+ (SELECT (3)))::integer)
+);
+
+-- Alow to transformation and hold conversion between types of colemns and
+-- declared type of column pointed in RTE
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE unique1 IN (0, 1.0 IN (0, 3)::integer);
+
+EXPLAIN
+SELECT ten FROM onek t WHERE 1 IN ((VALUES (1), (3)));
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE 1.0::integer IN ((VALUES (1), (3)));
+
+-- Don't allow transformation because of imcompatibility of types
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE 1.0 IN ((VALUES (1), (3))::integer);
+
+EXPLAIN (COSTS OFF)
+SELECT ten FROM onek t WHERE 1.0 IN (VALUES (1), (3));
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM onek t1, lateral (SELECT * FROM onek t2 WHERE t2.ten IN (values (t1.ten), (1)));
--
2.39.5 (Apple Git-154)
