This is an automated email from the ASF dual-hosted git repository.
dongjoon pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new 9eca58e [SPARK-28334][SQL][TEST] Port select.sql
9eca58e is described below
commit 9eca58ed3e8ba5ba4e21637f88069f69c2337304
Author: Yuming Wang <[email protected]>
AuthorDate: Thu Jul 11 13:54:15 2019 -0700
[SPARK-28334][SQL][TEST] Port select.sql
## What changes were proposed in this pull request?
This PR is to port select.sql from PostgreSQL regression tests.
https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/select.sql
The expected results can be found in the link:
https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/expected/select.out
When porting the test cases, found four PostgreSQL specific features that
do not exist in Spark SQL:
[SPARK-28010](https://issues.apache.org/jira/browse/SPARK-28010): Support
ORDER BY ... USING syntax
[SPARK-28329](https://issues.apache.org/jira/browse/SPARK-28329): Support
SELECT INTO syntax
[SPARK-28330](https://issues.apache.org/jira/browse/SPARK-28330): Enhance
query limit
[SPARK-28296](https://issues.apache.org/jira/browse/SPARK-28296): Improved
VALUES support
Also, found one inconsistent behavior:
[SPARK-28333](https://issues.apache.org/jira/browse/SPARK-28333): `NULLS
FIRST` for `DESC` and `NULLS LAST` for `ASC`
## How was this patch tested?
N/A
Closes #25096 from wangyum/SPARK-28334.
Authored-by: Yuming Wang <[email protected]>
Signed-off-by: Dongjoon Hyun <[email protected]>
---
.../resources/sql-tests/inputs/pgSQL/select.sql | 285 +++++++++++
.../sql-tests/results/pgSQL/select.sql.out | 543 +++++++++++++++++++++
2 files changed, 828 insertions(+)
diff --git a/sql/core/src/test/resources/sql-tests/inputs/pgSQL/select.sql
b/sql/core/src/test/resources/sql-tests/inputs/pgSQL/select.sql
new file mode 100644
index 0000000..1f83d6c
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/pgSQL/select.sql
@@ -0,0 +1,285 @@
+--
+-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
+--
+--
+-- SELECT
+-- Test int8 64-bit integers.
+--
https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/select.sql
+--
+create or replace temporary view onek2 as select * from onek;
+create or replace temporary view INT8_TBL as select * from values
+ (cast(trim(' 123 ') as bigint), cast(trim(' 456') as bigint)),
+ (cast(trim('123 ') as bigint),cast('4567890123456789' as bigint)),
+ (cast('4567890123456789' as bigint),cast('123' as bigint)),
+ (cast(+4567890123456789 as bigint),cast('4567890123456789' as bigint)),
+ (cast('+4567890123456789' as bigint),cast('-4567890123456789' as bigint))
+ as INT8_TBL(q1, q2);
+
+-- btree index
+-- awk '{if($1<10){print;}else{next;}}' onek.data | sort +0n -1
+--
+SELECT * FROM onek
+ WHERE onek.unique1 < 10
+ ORDER BY onek.unique1;
+
+-- [SPARK-28010] Support ORDER BY ... USING syntax
+--
+-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
+--
+SELECT onek.unique1, onek.stringu1 FROM onek
+ WHERE onek.unique1 < 20
+ ORDER BY unique1 DESC;
+
+--
+-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
+--
+SELECT onek.unique1, onek.stringu1 FROM onek
+ WHERE onek.unique1 > 980
+ ORDER BY stringu1 ASC;
+
+--
+-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
+-- sort +1d -2 +0nr -1
+--
+SELECT onek.unique1, onek.string4 FROM onek
+ WHERE onek.unique1 > 980
+ ORDER BY string4 ASC, unique1 DESC;
+
+--
+-- awk '{if($1>980){print $1,$16;}else{next;}}' onek.data |
+-- sort +1dr -2 +0n -1
+--
+SELECT onek.unique1, onek.string4 FROM onek
+ WHERE onek.unique1 > 980
+ ORDER BY string4 DESC, unique1 ASC;
+
+--
+-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
+-- sort +0nr -1 +1d -2
+--
+SELECT onek.unique1, onek.string4 FROM onek
+ WHERE onek.unique1 < 20
+ ORDER BY unique1 DESC, string4 ASC;
+
+--
+-- awk '{if($1<20){print $1,$16;}else{next;}}' onek.data |
+-- sort +0n -1 +1dr -2
+--
+SELECT onek.unique1, onek.string4 FROM onek
+ WHERE onek.unique1 < 20
+ ORDER BY unique1 ASC, string4 DESC;
+
+--
+-- test partial btree indexes
+--
+-- As of 7.2, planner probably won't pick an indexscan without stats,
+-- so ANALYZE first. Also, we want to prevent it from picking a bitmapscan
+-- followed by sort, because that could hide index ordering problems.
+--
+-- ANALYZE onek2;
+
+-- SET enable_seqscan TO off;
+-- SET enable_bitmapscan TO off;
+-- SET enable_sort TO off;
+
+--
+-- awk '{if($1<10){print $0;}else{next;}}' onek.data | sort +0n -1
+--
+SELECT onek2.* FROM onek2 WHERE onek2.unique1 < 10;
+
+--
+-- awk '{if($1<20){print $1,$14;}else{next;}}' onek.data | sort +0nr -1
+--
+SELECT onek2.unique1, onek2.stringu1 FROM onek2
+ WHERE onek2.unique1 < 20
+ ORDER BY unique1 DESC;
+
+--
+-- awk '{if($1>980){print $1,$14;}else{next;}}' onek.data | sort +1d -2
+--
+SELECT onek2.unique1, onek2.stringu1 FROM onek2
+ WHERE onek2.unique1 > 980;
+
+-- RESET enable_seqscan;
+-- RESET enable_bitmapscan;
+-- RESET enable_sort;
+
+-- [SPARK-28329] SELECT INTO syntax
+-- SELECT two, stringu1, ten, string4
+-- INTO TABLE tmp
+-- FROM onek;
+CREATE TABLE tmp USING parquet AS
+SELECT two, stringu1, ten, string4
+FROM onek;
+
+-- Skip the person table because there is a point data type that we don't
support.
+--
+-- awk '{print $1,$2;}' person.data |
+-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |
+-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |
+-- awk 'BEGIN{FS=" ";}{if(NF!=2){print $4,$5;}else{print;}}' -
stud_emp.data
+--
+-- SELECT name, age FROM person*; ??? check if different
+-- SELECT p.name, p.age FROM person* p;
+
+--
+-- awk '{print $1,$2;}' person.data |
+-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - emp.data |
+-- awk '{if(NF!=2){print $3,$2;}else{print;}}' - student.data |
+-- awk 'BEGIN{FS=" ";}{if(NF!=1){print $4,$5;}else{print;}}' -
stud_emp.data |
+-- sort +1nr -2
+--
+-- SELECT p.name, p.age FROM person* p ORDER BY age DESC, name;
+
+-- [SPARK-28330] Enhance query limit
+--
+-- Test some cases involving whole-row Var referencing a subquery
+--
+select foo.* from (select 1) as foo;
+select foo.* from (select null) as foo;
+select foo.* from (select 'xyzzy',1,null) as foo;
+
+--
+-- Test VALUES lists
+--
+select * from onek, values(147, 'RFAAAA'), (931, 'VJAAAA') as v (i, j)
+ WHERE onek.unique1 = v.i and onek.stringu1 = v.j;
+
+-- [SPARK-28296] Improved VALUES support
+-- a more complex case
+-- looks like we're coding lisp :-)
+-- 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;
+
+-- try VALUES in a subquery
+-- select * from onek
+-- where (unique1,ten) in (values (1,1), (20,0), (99,9), (17,99))
+-- order by unique1;
+
+-- VALUES is also legal as a standalone query or a set-operation member
+VALUES (1,2), (3,4+4), (7,77.7);
+
+VALUES (1,2), (3,4+4), (7,77.7)
+UNION ALL
+SELECT 2+2, 57
+UNION ALL
+TABLE int8_tbl;
+
+--
+-- Test ORDER BY options
+--
+
+CREATE OR REPLACE TEMPORARY VIEW foo AS
+SELECT * FROM (values(42),(3),(10),(7),(null),(null),(1)) as foo (f1);
+
+-- [SPARK-28333] NULLS FIRST for DESC and NULLS LAST for ASC
+SELECT * FROM foo ORDER BY f1;
+SELECT * FROM foo ORDER BY f1 ASC; -- same thing
+SELECT * FROM foo ORDER BY f1 NULLS FIRST;
+SELECT * FROM foo ORDER BY f1 DESC;
+SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
+
+-- check if indexscans do the right things
+-- CREATE INDEX fooi ON foo (f1);
+-- SET enable_sort = false;
+
+-- SELECT * FROM foo ORDER BY f1;
+-- SELECT * FROM foo ORDER BY f1 NULLS FIRST;
+-- SELECT * FROM foo ORDER BY f1 DESC;
+-- SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
+
+-- DROP INDEX fooi;
+-- CREATE INDEX fooi ON foo (f1 DESC);
+
+-- SELECT * FROM foo ORDER BY f1;
+-- SELECT * FROM foo ORDER BY f1 NULLS FIRST;
+-- SELECT * FROM foo ORDER BY f1 DESC;
+-- SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
+
+-- DROP INDEX fooi;
+-- CREATE INDEX fooi ON foo (f1 DESC NULLS LAST);
+
+-- SELECT * FROM foo ORDER BY f1;
+-- SELECT * FROM foo ORDER BY f1 NULLS FIRST;
+-- SELECT * FROM foo ORDER BY f1 DESC;
+-- SELECT * FROM foo ORDER BY f1 DESC NULLS LAST;
+
+--
+-- Test planning of some cases with partial indexes
+--
+
+-- partial index is usable
+-- explain (costs off)
+-- select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
+select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
+-- actually run the query with an analyze to use the partial index
+-- explain (costs off, analyze on, timing off, summary off)
+-- select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
+-- explain (costs off)
+-- select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
+select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA';
+-- partial index predicate implies clause, so no need for retest
+-- explain (costs off)
+-- select * from onek2 where unique2 = 11 and stringu1 < 'B';
+select * from onek2 where unique2 = 11 and stringu1 < 'B';
+-- explain (costs off)
+-- select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
+-- but if it's an update target, must retest anyway
+-- explain (costs off)
+-- select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update;
+-- select unique2 from onek2 where unique2 = 11 and stringu1 < 'B' for update;
+-- partial index is not applicable
+-- explain (costs off)
+-- select unique2 from onek2 where unique2 = 11 and stringu1 < 'C';
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'C';
+-- partial index implies clause, but bitmap scan must recheck predicate anyway
+-- SET enable_indexscan TO off;
+-- explain (costs off)
+-- select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B';
+-- RESET enable_indexscan;
+-- check multi-index cases too
+-- explain (costs off)
+-- select unique1, unique2 from onek2
+-- where (unique2 = 11 or unique1 = 0) and stringu1 < 'B';
+select unique1, unique2 from onek2
+ where (unique2 = 11 or unique1 = 0) and stringu1 < 'B';
+-- explain (costs off)
+-- select unique1, unique2 from onek2
+-- where (unique2 = 11 and stringu1 < 'B') or unique1 = 0;
+select unique1, unique2 from onek2
+ where (unique2 = 11 and stringu1 < 'B') or unique1 = 0;
+
+--
+-- Test some corner cases that have been known to confuse the planner
+--
+
+-- ORDER BY on a constant doesn't really need any sorting
+SELECT 1 AS x ORDER BY x;
+
+-- But ORDER BY on a set-valued expression does
+-- create function sillysrf(int) returns setof int as
+-- 'values (1),(10),(2),($1)' language sql immutable;
+
+-- select sillysrf(42);
+-- select sillysrf(-1) order by 1;
+
+-- drop function sillysrf(int);
+
+-- X = X isn't a no-op, it's effectively X IS NOT NULL assuming = is strict
+-- (see bug #5084)
+select * from (values (2),(null),(1)) v(k) where k = k order by k;
+select * from (values (2),(null),(1)) v(k) where k = k;
+
+-- Test partitioned tables with no partitions, which should be handled the
+-- same as the non-inheritance case when expanding its RTE.
+-- create table list_parted_tbl (a int,b int) partition by list (a);
+-- create table list_parted_tbl1 partition of list_parted_tbl
+-- for values in (1) partition by list(b);
+-- explain (costs off) select * from list_parted_tbl;
+-- drop table list_parted_tbl;
+drop table tmp;
diff --git a/sql/core/src/test/resources/sql-tests/results/pgSQL/select.sql.out
b/sql/core/src/test/resources/sql-tests/results/pgSQL/select.sql.out
new file mode 100644
index 0000000..797f808
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/pgSQL/select.sql.out
@@ -0,0 +1,543 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 37
+
+
+-- !query 0
+create or replace temporary view onek2 as select * from onek
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+create or replace temporary view INT8_TBL as select * from values
+ (cast(trim(' 123 ') as bigint), cast(trim(' 456') as bigint)),
+ (cast(trim('123 ') as bigint),cast('4567890123456789' as bigint)),
+ (cast('4567890123456789' as bigint),cast('123' as bigint)),
+ (cast(+4567890123456789 as bigint),cast('4567890123456789' as bigint)),
+ (cast('+4567890123456789' as bigint),cast('-4567890123456789' as bigint))
+ as INT8_TBL(q1, q2)
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+SELECT * FROM onek
+ WHERE onek.unique1 < 10
+ ORDER BY onek.unique1
+-- !query 2 schema
+struct<unique1:int,unique2:int,two:int,four:int,ten:int,twenty:int,hundred:int,thousand:int,twothousand:int,fivethous:int,tenthous:int,odd:int,even:int,stringu1:string,stringu2:string,string4:string>
+-- !query 2 output
+0 998 0 0 0 0 0 0 0 0
0 0 1 AAAAAA KMBAAA OOOOxx
+1 214 1 1 1 1 1 1 1 1
1 2 3 BAAAAA GIAAAA OOOOxx
+2 326 0 2 2 2 2 2 2 2
2 4 5 CAAAAA OMAAAA OOOOxx
+3 431 1 3 3 3 3 3 3 3
3 6 7 DAAAAA PQAAAA VVVVxx
+4 833 0 0 4 4 4 4 4 4
4 8 9 EAAAAA BGBAAA HHHHxx
+5 541 1 1 5 5 5 5 5 5
5 10 11 FAAAAA VUAAAA HHHHxx
+6 978 0 2 6 6 6 6 6 6
6 12 13 GAAAAA QLBAAA OOOOxx
+7 647 1 3 7 7 7 7 7 7
7 14 15 HAAAAA XYAAAA VVVVxx
+8 653 0 0 8 8 8 8 8 8
8 16 17 IAAAAA DZAAAA HHHHxx
+9 49 1 1 9 9 9 9 9 9
9 18 19 JAAAAA XBAAAA HHHHxx
+
+
+-- !query 3
+SELECT onek.unique1, onek.stringu1 FROM onek
+ WHERE onek.unique1 < 20
+ ORDER BY unique1 DESC
+-- !query 3 schema
+struct<unique1:int,stringu1:string>
+-- !query 3 output
+19 TAAAAA
+18 SAAAAA
+17 RAAAAA
+16 QAAAAA
+15 PAAAAA
+14 OAAAAA
+13 NAAAAA
+12 MAAAAA
+11 LAAAAA
+10 KAAAAA
+9 JAAAAA
+8 IAAAAA
+7 HAAAAA
+6 GAAAAA
+5 FAAAAA
+4 EAAAAA
+3 DAAAAA
+2 CAAAAA
+1 BAAAAA
+0 AAAAAA
+
+
+-- !query 4
+SELECT onek.unique1, onek.stringu1 FROM onek
+ WHERE onek.unique1 > 980
+ ORDER BY stringu1 ASC
+-- !query 4 schema
+struct<unique1:int,stringu1:string>
+-- !query 4 output
+988 AMAAAA
+989 BMAAAA
+990 CMAAAA
+991 DMAAAA
+992 EMAAAA
+993 FMAAAA
+994 GMAAAA
+995 HMAAAA
+996 IMAAAA
+997 JMAAAA
+998 KMAAAA
+999 LMAAAA
+981 TLAAAA
+982 ULAAAA
+983 VLAAAA
+984 WLAAAA
+985 XLAAAA
+986 YLAAAA
+987 ZLAAAA
+
+
+-- !query 5
+SELECT onek.unique1, onek.string4 FROM onek
+ WHERE onek.unique1 > 980
+ ORDER BY string4 ASC, unique1 DESC
+-- !query 5 schema
+struct<unique1:int,string4:string>
+-- !query 5 output
+999 AAAAxx
+995 AAAAxx
+983 AAAAxx
+982 AAAAxx
+981 AAAAxx
+998 HHHHxx
+997 HHHHxx
+993 HHHHxx
+990 HHHHxx
+986 HHHHxx
+996 OOOOxx
+991 OOOOxx
+988 OOOOxx
+987 OOOOxx
+985 OOOOxx
+994 VVVVxx
+992 VVVVxx
+989 VVVVxx
+984 VVVVxx
+
+
+-- !query 6
+SELECT onek.unique1, onek.string4 FROM onek
+ WHERE onek.unique1 > 980
+ ORDER BY string4 DESC, unique1 ASC
+-- !query 6 schema
+struct<unique1:int,string4:string>
+-- !query 6 output
+984 VVVVxx
+989 VVVVxx
+992 VVVVxx
+994 VVVVxx
+985 OOOOxx
+987 OOOOxx
+988 OOOOxx
+991 OOOOxx
+996 OOOOxx
+986 HHHHxx
+990 HHHHxx
+993 HHHHxx
+997 HHHHxx
+998 HHHHxx
+981 AAAAxx
+982 AAAAxx
+983 AAAAxx
+995 AAAAxx
+999 AAAAxx
+
+
+-- !query 7
+SELECT onek.unique1, onek.string4 FROM onek
+ WHERE onek.unique1 < 20
+ ORDER BY unique1 DESC, string4 ASC
+-- !query 7 schema
+struct<unique1:int,string4:string>
+-- !query 7 output
+19 OOOOxx
+18 VVVVxx
+17 HHHHxx
+16 OOOOxx
+15 VVVVxx
+14 AAAAxx
+13 OOOOxx
+12 AAAAxx
+11 OOOOxx
+10 AAAAxx
+9 HHHHxx
+8 HHHHxx
+7 VVVVxx
+6 OOOOxx
+5 HHHHxx
+4 HHHHxx
+3 VVVVxx
+2 OOOOxx
+1 OOOOxx
+0 OOOOxx
+
+
+-- !query 8
+SELECT onek.unique1, onek.string4 FROM onek
+ WHERE onek.unique1 < 20
+ ORDER BY unique1 ASC, string4 DESC
+-- !query 8 schema
+struct<unique1:int,string4:string>
+-- !query 8 output
+0 OOOOxx
+1 OOOOxx
+2 OOOOxx
+3 VVVVxx
+4 HHHHxx
+5 HHHHxx
+6 OOOOxx
+7 VVVVxx
+8 HHHHxx
+9 HHHHxx
+10 AAAAxx
+11 OOOOxx
+12 AAAAxx
+13 OOOOxx
+14 AAAAxx
+15 VVVVxx
+16 OOOOxx
+17 HHHHxx
+18 VVVVxx
+19 OOOOxx
+
+
+-- !query 9
+SELECT onek2.* FROM onek2 WHERE onek2.unique1 < 10
+-- !query 9 schema
+struct<unique1:int,unique2:int,two:int,four:int,ten:int,twenty:int,hundred:int,thousand:int,twothousand:int,fivethous:int,tenthous:int,odd:int,even:int,stringu1:string,stringu2:string,string4:string>
+-- !query 9 output
+0 998 0 0 0 0 0 0 0 0
0 0 1 AAAAAA KMBAAA OOOOxx
+1 214 1 1 1 1 1 1 1 1
1 2 3 BAAAAA GIAAAA OOOOxx
+2 326 0 2 2 2 2 2 2 2
2 4 5 CAAAAA OMAAAA OOOOxx
+3 431 1 3 3 3 3 3 3 3
3 6 7 DAAAAA PQAAAA VVVVxx
+4 833 0 0 4 4 4 4 4 4
4 8 9 EAAAAA BGBAAA HHHHxx
+5 541 1 1 5 5 5 5 5 5
5 10 11 FAAAAA VUAAAA HHHHxx
+6 978 0 2 6 6 6 6 6 6
6 12 13 GAAAAA QLBAAA OOOOxx
+7 647 1 3 7 7 7 7 7 7
7 14 15 HAAAAA XYAAAA VVVVxx
+8 653 0 0 8 8 8 8 8 8
8 16 17 IAAAAA DZAAAA HHHHxx
+9 49 1 1 9 9 9 9 9 9
9 18 19 JAAAAA XBAAAA HHHHxx
+
+
+-- !query 10
+SELECT onek2.unique1, onek2.stringu1 FROM onek2
+ WHERE onek2.unique1 < 20
+ ORDER BY unique1 DESC
+-- !query 10 schema
+struct<unique1:int,stringu1:string>
+-- !query 10 output
+19 TAAAAA
+18 SAAAAA
+17 RAAAAA
+16 QAAAAA
+15 PAAAAA
+14 OAAAAA
+13 NAAAAA
+12 MAAAAA
+11 LAAAAA
+10 KAAAAA
+9 JAAAAA
+8 IAAAAA
+7 HAAAAA
+6 GAAAAA
+5 FAAAAA
+4 EAAAAA
+3 DAAAAA
+2 CAAAAA
+1 BAAAAA
+0 AAAAAA
+
+
+-- !query 11
+SELECT onek2.unique1, onek2.stringu1 FROM onek2
+ WHERE onek2.unique1 > 980
+-- !query 11 schema
+struct<unique1:int,stringu1:string>
+-- !query 11 output
+981 TLAAAA
+982 ULAAAA
+983 VLAAAA
+984 WLAAAA
+985 XLAAAA
+986 YLAAAA
+987 ZLAAAA
+988 AMAAAA
+989 BMAAAA
+990 CMAAAA
+991 DMAAAA
+992 EMAAAA
+993 FMAAAA
+994 GMAAAA
+995 HMAAAA
+996 IMAAAA
+997 JMAAAA
+998 KMAAAA
+999 LMAAAA
+
+
+-- !query 12
+CREATE TABLE tmp USING parquet AS
+SELECT two, stringu1, ten, string4
+FROM onek
+-- !query 12 schema
+struct<>
+-- !query 12 output
+
+
+
+-- !query 13
+select foo.* from (select 1) as foo
+-- !query 13 schema
+struct<1:int>
+-- !query 13 output
+1
+
+
+-- !query 14
+select foo.* from (select null) as foo
+-- !query 14 schema
+struct<NULL:null>
+-- !query 14 output
+NULL
+
+
+-- !query 15
+select foo.* from (select 'xyzzy',1,null) as foo
+-- !query 15 schema
+struct<xyzzy:string,1:int,NULL:null>
+-- !query 15 output
+xyzzy 1 NULL
+
+
+-- !query 16
+select * from onek, values(147, 'RFAAAA'), (931, 'VJAAAA') as v (i, j)
+ WHERE onek.unique1 = v.i and onek.stringu1 = v.j
+-- !query 16 schema
+struct<unique1:int,unique2:int,two:int,four:int,ten:int,twenty:int,hundred:int,thousand:int,twothousand:int,fivethous:int,tenthous:int,odd:int,even:int,stringu1:string,stringu2:string,string4:string,i:int,j:string>
+-- !query 16 output
+147 0 1 3 7 7 7 47 147 147
147 14 15 RFAAAA AAAAAA AAAAxx 147 RFAAAA
+931 1 1 3 1 11 1 31 131 431
931 2 3 VJAAAA BAAAAA HHHHxx 931 VJAAAA
+
+
+-- !query 17
+VALUES (1,2), (3,4+4), (7,77.7)
+-- !query 17 schema
+struct<col1:int,col2:decimal(11,1)>
+-- !query 17 output
+1 2
+3 8
+7 77.7
+
+
+-- !query 18
+VALUES (1,2), (3,4+4), (7,77.7)
+UNION ALL
+SELECT 2+2, 57
+UNION ALL
+TABLE int8_tbl
+-- !query 18 schema
+struct<col1:bigint,col2:decimal(21,1)>
+-- !query 18 output
+1 2
+123 456
+123 4567890123456789
+3 8
+4 57
+4567890123456789 -4567890123456789
+4567890123456789 123
+4567890123456789 4567890123456789
+7 77.7
+
+
+-- !query 19
+CREATE OR REPLACE TEMPORARY VIEW foo AS
+SELECT * FROM (values(42),(3),(10),(7),(null),(null),(1)) as foo (f1)
+-- !query 19 schema
+struct<>
+-- !query 19 output
+
+
+
+-- !query 20
+SELECT * FROM foo ORDER BY f1
+-- !query 20 schema
+struct<f1:int>
+-- !query 20 output
+NULL
+NULL
+1
+3
+7
+10
+42
+
+
+-- !query 21
+SELECT * FROM foo ORDER BY f1 ASC
+-- !query 21 schema
+struct<f1:int>
+-- !query 21 output
+NULL
+NULL
+1
+3
+7
+10
+42
+
+
+-- !query 22
+-- same thing
+SELECT * FROM foo ORDER BY f1 NULLS FIRST
+-- !query 22 schema
+struct<f1:int>
+-- !query 22 output
+NULL
+NULL
+1
+3
+7
+10
+42
+
+
+-- !query 23
+SELECT * FROM foo ORDER BY f1 DESC
+-- !query 23 schema
+struct<f1:int>
+-- !query 23 output
+42
+10
+7
+3
+1
+NULL
+NULL
+
+
+-- !query 24
+SELECT * FROM foo ORDER BY f1 DESC NULLS LAST
+-- !query 24 schema
+struct<f1:int>
+-- !query 24 output
+42
+10
+7
+3
+1
+NULL
+NULL
+
+
+-- !query 25
+select * from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'
+-- !query 25 schema
+struct<unique1:int,unique2:int,two:int,four:int,ten:int,twenty:int,hundred:int,thousand:int,twothousand:int,fivethous:int,tenthous:int,odd:int,even:int,stringu1:string,stringu2:string,string4:string>
+-- !query 25 output
+494 11 0 2 4 14 4 94 94 494
494 8 9 ATAAAA LAAAAA VVVVxx
+
+
+-- !query 26
+select unique2 from onek2 where unique2 = 11 and stringu1 = 'ATAAAA'
+-- !query 26 schema
+struct<unique2:int>
+-- !query 26 output
+11
+
+
+-- !query 27
+select * from onek2 where unique2 = 11 and stringu1 < 'B'
+-- !query 27 schema
+struct<unique1:int,unique2:int,two:int,four:int,ten:int,twenty:int,hundred:int,thousand:int,twothousand:int,fivethous:int,tenthous:int,odd:int,even:int,stringu1:string,stringu2:string,string4:string>
+-- !query 27 output
+494 11 0 2 4 14 4 94 94 494
494 8 9 ATAAAA LAAAAA VVVVxx
+
+
+-- !query 28
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'
+-- !query 28 schema
+struct<unique2:int>
+-- !query 28 output
+11
+
+
+-- !query 29
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'C'
+-- !query 29 schema
+struct<unique2:int>
+-- !query 29 output
+11
+
+
+-- !query 30
+select unique2 from onek2 where unique2 = 11 and stringu1 < 'B'
+-- !query 30 schema
+struct<unique2:int>
+-- !query 30 output
+11
+
+
+-- !query 31
+select unique1, unique2 from onek2
+ where (unique2 = 11 or unique1 = 0) and stringu1 < 'B'
+-- !query 31 schema
+struct<unique1:int,unique2:int>
+-- !query 31 output
+0 998
+494 11
+
+
+-- !query 32
+select unique1, unique2 from onek2
+ where (unique2 = 11 and stringu1 < 'B') or unique1 = 0
+-- !query 32 schema
+struct<unique1:int,unique2:int>
+-- !query 32 output
+0 998
+494 11
+
+
+-- !query 33
+SELECT 1 AS x ORDER BY x
+-- !query 33 schema
+struct<x:int>
+-- !query 33 output
+1
+
+
+-- !query 34
+select * from (values (2),(null),(1)) v(k) where k = k order by k
+-- !query 34 schema
+struct<k:int>
+-- !query 34 output
+1
+2
+
+
+-- !query 35
+select * from (values (2),(null),(1)) v(k) where k = k
+-- !query 35 schema
+struct<k:int>
+-- !query 35 output
+1
+2
+
+
+-- !query 36
+drop table tmp
+-- !query 36 schema
+struct<>
+-- !query 36 output
+
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
