This is an automated email from the ASF dual-hosted git repository.
mbudiu pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new 7ce986fbc6 [CALCITE-6620] VALUES created by RelBuilder do not have a
homogeneous type
7ce986fbc6 is described below
commit 7ce986fbc635722f3390b81dcdfe24bbd82ddb3d
Author: Mihai Budiu <[email protected]>
AuthorDate: Tue Oct 8 15:01:40 2024 -0700
[CALCITE-6620] VALUES created by RelBuilder do not have a homogeneous type
Signed-off-by: Mihai Budiu <[email protected]>
---
.../java/org/apache/calcite/tools/RelBuilder.java | 31 ++++-
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 12 +-
.../org/apache/calcite/test/RelBuilderTest.java | 16 +++
core/src/test/resources/sql/agg.iq | 128 ++++++++++-----------
core/src/test/resources/sql/join.iq | 4 +-
core/src/test/resources/sql/outer.iq | 2 +-
core/src/test/resources/sql/winagg.iq | 4 +-
7 files changed, 121 insertions(+), 76 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
index 4c7357da81..31c8e27d43 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -2886,6 +2886,31 @@ public class RelBuilder {
return ((AggCallPlus) c).op().kind == SqlKind.GROUP_ID;
}
+ /** Given a list of literals and a target row type, make the literals
+ * respectively match the fields types of the row.
+ *
+ * @param rowType Type expected for values
+ * @param values A list of literals that should match the rowType */
+ private List<RexLiteral> convertLiteralTypes(
+ RelDataType rowType, List<RexLiteral> values) {
+ assert values.size() == rowType.getFieldCount()
+ : "List of literals of size " + values.size() + " does not match
expected type " + rowType;
+
+ List<RelDataTypeField> fields = rowType.getFieldList();
+ List<RexLiteral> constants = new ArrayList<>();
+
+ for (int i = 0; i < values.size(); i++) {
+ RexLiteral vi = values.get(i);
+ RelDataType type = fields.get(i).getType();
+ RexNode e = cluster.getRexBuilder().makeAbstractCast(type, vi, false);
+ RexNode simplified = simplifier.simplify(e);
+ assert simplified instanceof RexLiteral
+ : "Could not simplify expression to literal" + simplified;
+ constants.add((RexLiteral) simplified);
+ }
+ return ImmutableList.copyOf(constants);
+ }
+
private RelBuilder setOp(boolean all, SqlKind kind, int n) {
List<RelNode> inputs = new ArrayList<>();
for (int i = 0; i < n; i++) {
@@ -2917,7 +2942,11 @@ public class RelBuilder {
requireNonNull(rowType, () -> "leastRestrictive(" + inputTypes + ")");
final List<List<RexLiteral>> tuples = new ArrayList<>();
for (RelNode input : inputs) {
- tuples.addAll(((Values) input).tuples);
+ ImmutableList<ImmutableList<RexLiteral>> literals = ((Values)
input).tuples;
+ for (ImmutableList<RexLiteral> l : literals) {
+ List<RexLiteral> converted = convertLiteralTypes(rowType, l);
+ tuples.add(converted);
+ }
}
final List<List<RexLiteral>> tuples2 =
all ? tuples : Util.distinctList(tuples);
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index fd72b876b0..67fd701f22 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -7765,7 +7765,7 @@ class RelToSqlConverterTest {
final String expectedDefault = "INSERT INTO \"SCOTT\".\"DEPT\""
+ " (\"DEPTNO\", \"DNAME\", \"LOC\")\n"
+ "VALUES (1, 'Fred', 'San Francisco'),\n"
- + "(2, 'Eric', 'Washington')";
+ + "(2, 'Eric', 'Washington ')";
final String expectedDefaultX = "INSERT INTO \"SCOTT\".\"DEPT\""
+ " (\"DEPTNO\", \"DNAME\", \"LOC\")\n"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
@@ -7775,7 +7775,7 @@ class RelToSqlConverterTest {
+ "FROM (VALUES (0)) AS \"t\" (\"ZERO\")";
final String expectedHive = "INSERT INTO `SCOTT`.`DEPT` (`DEPTNO`,
`DNAME`, `LOC`)\n"
+ "VALUES (1, 'Fred', 'San Francisco'),\n"
- + "(2, 'Eric', 'Washington')";
+ + "(2, 'Eric', 'Washington ')";
final String expectedHiveX = "INSERT INTO `SCOTT`.`DEPT` (`DEPTNO`,
`DNAME`, `LOC`)\n"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
+ "UNION ALL\n"
@@ -7783,7 +7783,7 @@ class RelToSqlConverterTest {
final String expectedMysql = "INSERT INTO `SCOTT`.`DEPT`"
+ " (`DEPTNO`, `DNAME`, `LOC`)\n"
+ "VALUES (1, 'Fred', 'San Francisco'),\n"
- + "(2, 'Eric', 'Washington')";
+ + "(2, 'Eric', 'Washington ')";
final String expectedMysqlX = "INSERT INTO `SCOTT`.`DEPT`"
+ " (`DEPTNO`, `DNAME`, `LOC`)\n"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
@@ -7792,7 +7792,7 @@ class RelToSqlConverterTest {
final String expectedOracle = "INSERT INTO \"SCOTT\".\"DEPT\""
+ " (\"DEPTNO\", \"DNAME\", \"LOC\")\n"
+ "VALUES (1, 'Fred', 'San Francisco'),\n"
- + "(2, 'Eric', 'Washington')";
+ + "(2, 'Eric', 'Washington ')";
final String expectedOracleX = "INSERT INTO \"SCOTT\".\"DEPT\""
+ " (\"DEPTNO\", \"DNAME\", \"LOC\")\n"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
@@ -7803,7 +7803,7 @@ class RelToSqlConverterTest {
final String expectedMssql = "INSERT INTO [SCOTT].[DEPT]"
+ " ([DEPTNO], [DNAME], [LOC])\n"
+ "VALUES (1, 'Fred', 'San Francisco'),\n"
- + "(2, 'Eric', 'Washington')";
+ + "(2, 'Eric', 'Washington ')";
final String expectedMssqlX = "INSERT INTO [SCOTT].[DEPT]"
+ " ([DEPTNO], [DNAME], [LOC])\n"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
@@ -7814,7 +7814,7 @@ class RelToSqlConverterTest {
final String expectedCalcite = "INSERT INTO \"SCOTT\".\"DEPT\""
+ " (\"DEPTNO\", \"DNAME\", \"LOC\")\n"
+ "VALUES (1, 'Fred', 'San Francisco'),\n"
- + "(2, 'Eric', 'Washington')";
+ + "(2, 'Eric', 'Washington ')";
final String expectedCalciteX = "INSERT INTO \"SCOTT\".\"DEPT\""
+ " (\"DEPTNO\", \"DNAME\", \"LOC\")\n"
+ "SELECT 1, 'Fred', 'San Francisco'\n"
diff --git a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
index 2f0293feb5..28dfba9656 100644
--- a/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
+++ b/core/src/test/java/org/apache/calcite/test/RelBuilderTest.java
@@ -18,6 +18,8 @@ package org.apache.calcite.test;
import org.apache.calcite.adapter.enumerable.EnumerableConvention;
import org.apache.calcite.adapter.enumerable.EnumerableRules;
import org.apache.calcite.adapter.java.ReflectiveSchema;
+import org.apache.calcite.config.CalciteConnectionProperty;
+import org.apache.calcite.config.Lex;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.plan.Contexts;
import org.apache.calcite.plan.Convention;
@@ -231,6 +233,20 @@ public class RelBuilderTest {
hasTree("LogicalTableScan(table=[[scott, EMP]])\n"));
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6620";>[CALCITE-6620]
+ * VALUES created by RelBuilder do not have a homogeneous type</a>. */
+ @Test void differentTypeValues() {
+ CalciteAssert.that()
+ .with(CalciteConnectionProperty.LEX, Lex.JAVA)
+ .with(CalciteConnectionProperty.FORCE_DECORRELATE, false)
+ .withSchema("s", new ReflectiveSchema(new HrSchema()))
+ .query("SELECT * FROM (VALUES (1, 2, 3), (CAST(5E0 AS REAL), 5E0,
NULL))")
+ .explainContains("PLAN=EnumerableValues(tuples=[[{ 1.0E0, 2.0E0, 3 }, "
+ + "{ 5.0E0, 5.0E0, null }]])")
+ .returnsOrdered("EXPR$0=1.0; EXPR$1=2.0; EXPR$2=3", "EXPR$0=5.0;
EXPR$1=5.0; EXPR$2=null");
+ }
+
@Test void testScanQualifiedTable() {
// Equivalent SQL:
// SELECT *
diff --git a/core/src/test/resources/sql/agg.iq
b/core/src/test/resources/sql/agg.iq
index 733ae7a9c0..411cbe38f8 100644
--- a/core/src/test/resources/sql/agg.iq
+++ b/core/src/test/resources/sql/agg.iq
@@ -3205,34 +3205,34 @@ LIMIT 500;
!use post
select gender, json_objectagg(ename: deptno absent on null) from emp group by
gender;
-+--------+-------------------------------------------------------+
-| GENDER | EXPR$1 |
-+--------+-------------------------------------------------------+
-| F | {"Eve":50,"Grace":60,"Susan":30,"Alice":30,"Jane":10} |
-| M | {"Adam":50,"Bob":10,"Eric":20} |
-+--------+-------------------------------------------------------+
++--------+----------------------------------------------------------+
+| GENDER | EXPR$1 |
++--------+----------------------------------------------------------+
+| F | {"Jane ":10,"Grace":60,"Susan":30,"Alice":30,"Eve ":50} |
+| M | {"Bob ":10,"Adam ":50,"Eric ":20} |
++--------+----------------------------------------------------------+
(2 rows)
!ok
select gender, json_arrayagg(json_object('ename': ename, 'deptno': deptno)
format json) from emp group by gender;
-+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| GENDER | EXPR$1
|
-+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| F |
[{"ename":"Jane","deptno":10},{"ename":"Susan","deptno":30},{"ename":"Alice","deptno":30},{"ename":"Eve","deptno":50},{"ename":"Grace","deptno":60},{"ename":"Wilma","deptno":null}]
|
-| M |
[{"ename":"Bob","deptno":10},{"ename":"Eric","deptno":20},{"ename":"Adam","deptno":50}]
|
-+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
++--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+| GENDER | EXPR$1
|
++--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+| F | [{"ename":"Jane
","deptno":10},{"ename":"Susan","deptno":30},{"ename":"Alice","deptno":30},{"ename":"Eve
","deptno":50},{"ename":"Grace","deptno":60},{"ename":"Wilma","deptno":null}]
|
+| M | [{"ename":"Bob ","deptno":10},{"ename":"Eric
","deptno":20},{"ename":"Adam ","deptno":50}]
|
++--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
(2 rows)
!ok
select gender, json_arrayagg(json_object('ename': ename, 'deptno': deptno))
from emp group by gender;
-+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| GENDER | EXPR$1
|
-+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
-| F |
[{"ename":"Jane","deptno":10},{"ename":"Susan","deptno":30},{"ename":"Alice","deptno":30},{"ename":"Eve","deptno":50},{"ename":"Grace","deptno":60},{"ename":"Wilma","deptno":null}]
|
-| M |
[{"ename":"Bob","deptno":10},{"ename":"Eric","deptno":20},{"ename":"Adam","deptno":50}]
|
-+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
++--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+| GENDER | EXPR$1
|
++--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
+| F | [{"ename":"Jane
","deptno":10},{"ename":"Susan","deptno":30},{"ename":"Alice","deptno":30},{"ename":"Eve
","deptno":50},{"ename":"Grace","deptno":60},{"ename":"Wilma","deptno":null}]
|
+| M | [{"ename":"Bob ","deptno":10},{"ename":"Eric
","deptno":20},{"ename":"Adam ","deptno":50}]
|
++--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
(2 rows)
!ok
@@ -3241,10 +3241,10 @@ select json_object('deptno': deptno, 'employees':
json_arrayagg(json_object('ena
+-------------------------------------------------------------------------------------------+
| EXPR$0
|
+-------------------------------------------------------------------------------------------+
-|
{"employees":[{"ename":"Adam","gender":"M"},{"ename":"Eve","gender":"F"}],"deptno":50}
|
-| {"employees":[{"ename":"Eric","gender":"M"}],"deptno":20}
|
+| {"employees":[{"ename":"Adam ","gender":"M"},{"ename":"Eve
","gender":"F"}],"deptno":50} |
+| {"employees":[{"ename":"Eric ","gender":"M"}],"deptno":20}
|
| {"employees":[{"ename":"Grace","gender":"F"}],"deptno":60}
|
-|
{"employees":[{"ename":"Jane","gender":"F"},{"ename":"Bob","gender":"M"}],"deptno":10}
|
+| {"employees":[{"ename":"Jane ","gender":"F"},{"ename":"Bob
","gender":"M"}],"deptno":10} |
|
{"employees":[{"ename":"Susan","gender":"F"},{"ename":"Alice","gender":"F"}],"deptno":30}
|
| {"employees":[{"ename":"Wilma","gender":"F"}],"deptno":null}
|
+-------------------------------------------------------------------------------------------+
@@ -3256,10 +3256,10 @@ select json_object('deptno': deptno, 'employees':
json_arrayagg(json_object('ena
+-------------------------------------------------------------------------------------------+
| EXPR$0
|
+-------------------------------------------------------------------------------------------+
-|
{"employees":[{"ename":"Adam","gender":"M"},{"ename":"Eve","gender":"F"}],"deptno":50}
|
-| {"employees":[{"ename":"Eric","gender":"M"}],"deptno":20}
|
+| {"employees":[{"ename":"Adam ","gender":"M"},{"ename":"Eve
","gender":"F"}],"deptno":50} |
+| {"employees":[{"ename":"Eric ","gender":"M"}],"deptno":20}
|
| {"employees":[{"ename":"Grace","gender":"F"}],"deptno":60}
|
-|
{"employees":[{"ename":"Jane","gender":"F"},{"ename":"Bob","gender":"M"}],"deptno":10}
|
+| {"employees":[{"ename":"Jane ","gender":"F"},{"ename":"Bob
","gender":"M"}],"deptno":10} |
|
{"employees":[{"ename":"Susan","gender":"F"},{"ename":"Alice","gender":"F"}],"deptno":30}
|
| {"employees":[{"ename":"Wilma","gender":"F"}],"deptno":null}
|
+-------------------------------------------------------------------------------------------+
@@ -3308,53 +3308,53 @@ select gender,
json_objectagg(ename: deptno),
json_objectagg(ename: deptno absent on null)
from emp group by gender;
-+--------+--------------------------------------------------------------------+-------------------------------------------------------+
-| GENDER | EXPR$1
| EXPR$2 |
-+--------+--------------------------------------------------------------------+-------------------------------------------------------+
-| F | {"Eve":50,"Grace":60,"Wilma":null,"Susan":30,"Alice":30,"Jane":10}
| {"Eve":50,"Grace":60,"Susan":30,"Alice":30,"Jane":10} |
-| M | {"Adam":50,"Bob":10,"Eric":20}
| {"Adam":50,"Bob":10,"Eric":20} |
-+--------+--------------------------------------------------------------------+-------------------------------------------------------+
++--------+-----------------------------------------------------------------------+----------------------------------------------------------+
+| GENDER | EXPR$1
| EXPR$2 |
++--------+-----------------------------------------------------------------------+----------------------------------------------------------+
+| F | {"Jane ":10,"Grace":60,"Wilma":null,"Susan":30,"Alice":30,"Eve
":50} | {"Jane ":10,"Grace":60,"Susan":30,"Alice":30,"Eve ":50} |
+| M | {"Bob ":10,"Adam ":50,"Eric ":20}
| {"Bob ":10,"Adam ":50,"Eric ":20} |
++--------+-----------------------------------------------------------------------+----------------------------------------------------------+
(2 rows)
!ok
EnumerableAggregate(group=[{2}], EXPR$1=[JSON_OBJECTAGG_NULL_ON_NULL($0, $1)],
EXPR$2=[JSON_OBJECTAGG_ABSENT_ON_NULL($0, $1)])
- EnumerableValues(tuples=[[{ 'Jane', 10, 'F' }, { 'Bob', 10, 'M' }, { 'Eric',
20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam', 50, 'M' }, {
'Eve', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
+ EnumerableValues(tuples=[[{ 'Jane ', 10, 'F' }, { 'Bob ', 10, 'M' }, {
'Eric ', 20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam ', 50,
'M' }, { 'Eve ', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
!plan
select listagg(ename) as combined_name from emp;
-+------------------------------------------------+
-| COMBINED_NAME |
-+------------------------------------------------+
-| Jane,Bob,Eric,Susan,Alice,Adam,Eve,Grace,Wilma |
-+------------------------------------------------+
++-------------------------------------------------------+
+| COMBINED_NAME |
++-------------------------------------------------------+
+| Jane ,Bob ,Eric ,Susan,Alice,Adam ,Eve ,Grace,Wilma |
++-------------------------------------------------------+
(1 row)
!ok
select listagg(ename) within group(order by gender, ename) as combined_name
from emp;
-+------------------------------------------------+
-| COMBINED_NAME |
-+------------------------------------------------+
-| Alice,Eve,Grace,Jane,Susan,Wilma,Adam,Bob,Eric |
-+------------------------------------------------+
++-------------------------------------------------------+
+| COMBINED_NAME |
++-------------------------------------------------------+
+| Alice,Eve ,Grace,Jane ,Susan,Wilma,Adam ,Bob ,Eric |
++-------------------------------------------------------+
(1 row)
!ok
EnumerableAggregate(group=[{}], COMBINED_NAME=[LISTAGG($0) WITHIN GROUP ([2,
0])])
- EnumerableValues(tuples=[[{ 'Jane', 10, 'F' }, { 'Bob', 10, 'M' }, { 'Eric',
20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam', 50, 'M' }, {
'Eve', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
+ EnumerableValues(tuples=[[{ 'Jane ', 10, 'F' }, { 'Bob ', 10, 'M' }, {
'Eric ', 20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam ', 50,
'M' }, { 'Eve ', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
!plan
select
listagg(ename) within group(order by deptno, ename) as default_listagg_sep,
listagg(ename, '; ') within group(order by deptno, ename desc) as
custom_listagg_sep
from emp group by gender;
-+----------------------------------+---------------------------------------+
-| DEFAULT_LISTAGG_SEP | CUSTOM_LISTAGG_SEP |
-+----------------------------------+---------------------------------------+
-| Bob,Eric,Adam | Bob; Eric; Adam |
-| Jane,Alice,Susan,Eve,Grace,Wilma | Jane; Susan; Alice; Eve; Grace; Wilma |
-+----------------------------------+---------------------------------------+
++-------------------------------------+------------------------------------------+
+| DEFAULT_LISTAGG_SEP | CUSTOM_LISTAGG_SEP
|
++-------------------------------------+------------------------------------------+
+| Bob ,Eric ,Adam | Bob ; Eric ; Adam
|
+| Jane ,Alice,Susan,Eve ,Grace,Wilma | Jane ; Susan; Alice; Eve ; Grace;
Wilma |
++-------------------------------------+------------------------------------------+
(2 rows)
!ok
@@ -3368,11 +3368,11 @@ select
group_concat(distinct ename order by ename) as combined_name,
group_concat(ename order by ename separator ';') as separated_name
from emp;
-+------------------------------------------------+------------------------------------------------+
-| COMBINED_NAME | SEPARATED_NAME
|
-+------------------------------------------------+------------------------------------------------+
-| Adam,Alice,Bob,Eric,Eve,Grace,Jane,Susan,Wilma |
Adam;Alice;Bob;Eric;Eve;Grace;Jane;Susan;Wilma |
-+------------------------------------------------+------------------------------------------------+
++-------------------------------------------------------+-------------------------------------------------------+
+| COMBINED_NAME | SEPARATED_NAME
|
++-------------------------------------------------------+-------------------------------------------------------+
+| Adam ,Alice,Bob ,Eric ,Eve ,Grace,Jane ,Susan,Wilma | Adam ;Alice;Bob
;Eric ;Eve ;Grace;Jane ;Susan;Wilma |
++-------------------------------------------------------+-------------------------------------------------------+
(1 row)
!ok
@@ -3381,11 +3381,11 @@ from emp;
select
group_concat(deptno, ename order by ename) as combined_name
from emp;
-+-----------------------------------------------+
-| COMBINED_NAME |
-+-----------------------------------------------+
-| 50Alice30Bob10Eric20Eve50Grace60Jane10Susan30 |
-+-----------------------------------------------+
++-----------------------------------------------------+
+| COMBINED_NAME |
++-----------------------------------------------------+
+| 50Alice30Bob 10Eric 20Eve 50Grace60Jane 10Susan30 |
++-----------------------------------------------------+
(1 row)
!ok
@@ -3406,12 +3406,12 @@ select
string_agg(ename order by deptno, ename) as default_string_agg_sep,
string_agg(ename, '; ' order by deptno, ename desc) as custom_string_agg_sep
from emp group by gender;
-+----------------------------------+---------------------------------------+
-| DEFAULT_STRING_AGG_SEP | CUSTOM_STRING_AGG_SEP |
-+----------------------------------+---------------------------------------+
-| Bob,Eric,Adam | Bob; Eric; Adam |
-| Jane,Alice,Susan,Eve,Grace,Wilma | Jane; Susan; Alice; Eve; Grace; Wilma |
-+----------------------------------+---------------------------------------+
++-------------------------------------+------------------------------------------+
+| DEFAULT_STRING_AGG_SEP | CUSTOM_STRING_AGG_SEP
|
++-------------------------------------+------------------------------------------+
+| Bob ,Eric ,Adam | Bob ; Eric ; Adam
|
+| Jane ,Alice,Susan,Eve ,Grace,Wilma | Jane ; Susan; Alice; Eve ; Grace;
Wilma |
++-------------------------------------+------------------------------------------+
(2 rows)
!ok
diff --git a/core/src/test/resources/sql/join.iq
b/core/src/test/resources/sql/join.iq
index e8fa546a39..6330a8d746 100644
--- a/core/src/test/resources/sql/join.iq
+++ b/core/src/test/resources/sql/join.iq
@@ -110,7 +110,7 @@ on emp.deptno = dept.deptno or emp.ename = dept.dname;
!ok
EnumerableNestedLoopJoin(condition=[OR(=($1, $3), =(CAST($0):CHAR(11) NOT
NULL, $4))], joinType=[inner])
- EnumerableValues(tuples=[[{ 'Jane', 10, 'F' }, { 'Bob', 10, 'M' }, { 'Eric',
20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam', 50, 'M' }, {
'Eve', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
+ EnumerableValues(tuples=[[{ 'Jane ', 10, 'F' }, { 'Bob ', 10, 'M' }, {
'Eric ', 20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam ', 50,
'M' }, { 'Eve ', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, {
30, 'Engineering' }, { 40, 'Empty ' }]])
!plan
@@ -138,7 +138,7 @@ on emp.deptno = dept.deptno or emp.ename = dept.dname;
# Cannot be decomposed into an equi-join; plan uses EnumerableNestedLoopJoin
EnumerableNestedLoopJoin(condition=[OR(=($1, $3), =(CAST($0):CHAR(11) NOT
NULL, $4))], joinType=[left])
- EnumerableValues(tuples=[[{ 'Jane', 10, 'F' }, { 'Bob', 10, 'M' }, { 'Eric',
20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam', 50, 'M' }, {
'Eve', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
+ EnumerableValues(tuples=[[{ 'Jane ', 10, 'F' }, { 'Bob ', 10, 'M' }, {
'Eric ', 20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam ', 50,
'M' }, { 'Eve ', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, {
30, 'Engineering' }, { 40, 'Empty ' }]])
!plan
diff --git a/core/src/test/resources/sql/outer.iq
b/core/src/test/resources/sql/outer.iq
index 858596a2e4..b0848dae21 100644
--- a/core/src/test/resources/sql/outer.iq
+++ b/core/src/test/resources/sql/outer.iq
@@ -245,7 +245,7 @@ select * from (select * from emp where gender ='F') as emp
full join dept on emp
!ok
EnumerableNestedLoopJoin(condition=[=(-($1, $3), 0)], joinType=[full])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=['F'], expr#4=[=($t2, $t3)],
proj#0..2=[{exprs}], $condition=[$t4])
- EnumerableValues(tuples=[[{ 'Jane', 10, 'F' }, { 'Bob', 10, 'M' }, {
'Eric', 20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam', 50,
'M' }, { 'Eve', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
+ EnumerableValues(tuples=[[{ 'Jane ', 10, 'F' }, { 'Bob ', 10, 'M' }, {
'Eric ', 20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam ', 50,
'M' }, { 'Eve ', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
EnumerableValues(tuples=[[{ 10, 'Sales ' }, { 20, 'Marketing ' }, {
30, 'Engineering' }, { 40, 'Empty ' }]])
!plan
diff --git a/core/src/test/resources/sql/winagg.iq
b/core/src/test/resources/sql/winagg.iq
index 64e4a0d26c..762e8a351e 100644
--- a/core/src/test/resources/sql/winagg.iq
+++ b/core/src/test/resources/sql/winagg.iq
@@ -761,7 +761,7 @@ select emp."ENAME", emp."DEPTNO",
from emp order by emp."ENAME";
EnumerableSort(sort0=[$0], dir0=[ASC])
EnumerableWindow(window#0=[window(aggs [NTH_VALUE($1, $2), NTH_VALUE($1,
$3), NTH_VALUE($1, $4), NTH_VALUE($1, $5), NTH_VALUE($1, $6)])])
- EnumerableValues(tuples=[[{ 'Jane', 10 }, { 'Bob', 10 }, { 'Eric', 20 }, {
'Susan', 30 }, { 'Alice', 30 }, { 'Adam', 50 }, { 'Eve', 50 }, { 'Grace', 60 },
{ 'Wilma', null }]])
+ EnumerableValues(tuples=[[{ 'Jane ', 10 }, { 'Bob ', 10 }, { 'Eric ', 20
}, { 'Susan', 30 }, { 'Alice', 30 }, { 'Adam ', 50 }, { 'Eve ', 50 }, {
'Grace', 60 }, { 'Wilma', null }]])
!plan
+-------+--------+-------------+--------------+-------------+--------------+-------------+
| ENAME | DEPTNO | first_value | second_value | fifth_value | eighth_value |
tenth_value |
@@ -1006,7 +1006,7 @@ select gender, count(*) over(partition by gender order by
ename) as count1 from
EnumerableCalc(expr#0..3=[{inputs}], GENDER=[$t2], $1=[$t3])
EnumerableWindow(window#0=[window(partition {2} order by [0] aggs
[COUNT()])])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=['F'], expr#4=[=($t2, $t3)],
proj#0..2=[{exprs}], $condition=[$t4])
- EnumerableValues(tuples=[[{ 'Jane', 10, 'F' }, { 'Bob', 10, 'M' }, {
'Eric', 20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam', 50,
'M' }, { 'Eve', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
+ EnumerableValues(tuples=[[{ 'Jane ', 10, 'F' }, { 'Bob ', 10, 'M' }, {
'Eric ', 20, 'M' }, { 'Susan', 30, 'F' }, { 'Alice', 30, 'F' }, { 'Adam ', 50,
'M' }, { 'Eve ', 50, 'F' }, { 'Grace', 60, 'F' }, { 'Wilma', null, 'F' }]])
!plan
+--------+--------+
| GENDER | COUNT1 |
