You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@spark.apache.org by do...@apache.org on 2019/07/06 01:30:45 UTC
[spark] branch master updated: [SPARK-28020][SQL][TEST] Port
date.sql
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 51379b7 [SPARK-28020][SQL][TEST] Port date.sql
51379b7 is described below
commit 51379b731db4523d5e10ad9f2c063e445ae10e72
Author: Yuming Wang <yu...@ebay.com>
AuthorDate: Fri Jul 5 18:30:25 2019 -0700
[SPARK-28020][SQL][TEST] Port date.sql
## What changes were proposed in this pull request?
This PR is to port date.sql from PostgreSQL regression tests. https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/date.sql
The expected results can be found in the link: https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/expected/date.out
When porting the test cases, found four PostgreSQL specific features that do not exist in Spark SQL:
[SPARK-28017](https://issues.apache.org/jira/browse/SPARK-28017): Enhance EXTRACT/DATE_TRUNC
[SPARK-28141](https://issues.apache.org/jira/browse/SPARK-28141): Date type can not accept special values
[SPARK-28253](https://issues.apache.org/jira/browse/SPARK-28253): Date type have different low value and high value
[SPARK-28259](https://issues.apache.org/jira/browse/SPARK-28259): Date/Time Output Styles and Date Order Conventions
Also, found a bug:
[SPARK-28015](https://issues.apache.org/jira/browse/SPARK-28015): Invalid date formats should throw an exception
Also, found a inconsistent behavior:
[SPARK-27923](https://issues.apache.org/jira/browse/SPARK-27923): Invalid date throw an exception bug Spark SQL returns `NULL`, for example: https://github.com/postgres/postgres/blob/30bcebbdcf23eb8b78e553c4b3b5eb847410ef19/src/test/regress/expected/date.out#L13-L14
## How was this patch tested?
N/A
Closes #24850 from wangyum/SPARK-28020.
Authored-by: Yuming Wang <yu...@ebay.com>
Signed-off-by: Dongjoon Hyun <dh...@apple.com>
---
.../test/resources/sql-tests/inputs/pgSQL/date.sql | 364 +++++++++++++++++
.../resources/sql-tests/results/pgSQL/date.sql.out | 443 +++++++++++++++++++++
2 files changed, 807 insertions(+)
diff --git a/sql/core/src/test/resources/sql-tests/inputs/pgSQL/date.sql b/sql/core/src/test/resources/sql-tests/inputs/pgSQL/date.sql
new file mode 100644
index 0000000..e694380
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/inputs/pgSQL/date.sql
@@ -0,0 +1,364 @@
+--
+-- Portions Copyright (c) 1996-2019, PostgreSQL Global Development Group
+--
+--
+-- DATE
+-- https://github.com/postgres/postgres/blob/REL_12_BETA2/src/test/regress/sql/date.sql
+
+CREATE TABLE DATE_TBL (f1 date) USING parquet;
+
+INSERT INTO DATE_TBL VALUES ('1957-04-09');
+INSERT INTO DATE_TBL VALUES ('1957-06-13');
+INSERT INTO DATE_TBL VALUES ('1996-02-28');
+INSERT INTO DATE_TBL VALUES ('1996-02-29');
+INSERT INTO DATE_TBL VALUES ('1996-03-01');
+INSERT INTO DATE_TBL VALUES ('1996-03-02');
+INSERT INTO DATE_TBL VALUES ('1997-02-28');
+-- [SPARK-27923] Skip invalid date: 1997-02-29
+-- INSERT INTO DATE_TBL VALUES ('1997-02-29');
+INSERT INTO DATE_TBL VALUES ('1997-03-01');
+INSERT INTO DATE_TBL VALUES ('1997-03-02');
+INSERT INTO DATE_TBL VALUES ('2000-04-01');
+INSERT INTO DATE_TBL VALUES ('2000-04-02');
+INSERT INTO DATE_TBL VALUES ('2000-04-03');
+INSERT INTO DATE_TBL VALUES ('2038-04-08');
+INSERT INTO DATE_TBL VALUES ('2039-04-09');
+INSERT INTO DATE_TBL VALUES ('2040-04-10');
+
+SELECT f1 AS `Fifteen` FROM DATE_TBL;
+
+SELECT f1 AS `Nine` FROM DATE_TBL WHERE f1 < '2000-01-01';
+
+SELECT f1 AS `Three` FROM DATE_TBL
+ WHERE f1 BETWEEN '2000-01-01' AND '2001-01-01';
+
+-- Skip the formats that we do not supported. Please check [SPARK-8995] for all supported formats
+--
+-- Check all the documented input formats
+--
+-- [SPARK-28259] Date/Time Output Styles and Date Order Conventions
+-- SET datestyle TO iso; -- display results in ISO
+
+-- SET datestyle TO ymd;
+
+-- SELECT date 'January 8, 1999';
+SELECT date '1999-01-08';
+SELECT date '1999-01-18';
+-- SELECT date '1/8/1999';
+-- SELECT date '1/18/1999';
+-- SELECT date '18/1/1999';
+-- SELECT date '01/02/03';
+-- SELECT date '19990108';
+-- SELECT date '990108';
+-- SELECT date '1999.008';
+-- SELECT date 'J2451187';
+-- SELECT date 'January 8, 99 BC';
+
+-- SELECT date '99-Jan-08';
+-- SELECT date '1999-Jan-08';
+-- SELECT date '08-Jan-99';
+-- SELECT date '08-Jan-1999';
+-- SELECT date 'Jan-08-99';
+-- SELECT date 'Jan-08-1999';
+-- SELECT date '99-08-Jan';
+-- SELECT date '1999-08-Jan';
+
+-- SELECT date '99 Jan 08';
+SELECT date '1999 Jan 08';
+-- SELECT date '08 Jan 99';
+-- SELECT date '08 Jan 1999';
+-- SELECT date 'Jan 08 99';
+-- SELECT date 'Jan 08 1999';
+-- SELECT date '99 08 Jan';
+SELECT date '1999 08 Jan';
+
+-- SELECT date '99-01-08';
+SELECT date '1999-01-08';
+-- SELECT date '08-01-99';
+-- SELECT date '08-01-1999';
+-- SELECT date '01-08-99';
+-- SELECT date '01-08-1999';
+-- SELECT date '99-08-01';
+SELECT date '1999-08-01';
+
+-- SELECT date '99 01 08';
+SELECT date '1999 01 08';
+-- SELECT date '08 01 99';
+-- SELECT date '08 01 1999';
+-- SELECT date '01 08 99';
+-- SELECT date '01 08 1999';
+-- SELECT date '99 08 01';
+SELECT date '1999 08 01';
+
+-- SET datestyle TO dmy;
+
+-- SELECT date 'January 8, 1999';
+SELECT date '1999-01-08';
+-- SELECT date '1999-01-18';
+-- SELECT date '1/8/1999';
+-- SELECT date '1/18/1999';
+-- SELECT date '18/1/1999';
+-- SELECT date '01/02/03';
+-- SELECT date '19990108';
+-- SELECT date '990108';
+-- SELECT date '1999.008';
+-- SELECT date 'J2451187';
+-- SELECT date 'January 8, 99 BC';
+
+-- SELECT date '99-Jan-08';
+-- SELECT date '1999-Jan-08';
+-- SELECT date '08-Jan-99';
+-- SELECT date '08-Jan-1999';
+-- SELECT date 'Jan-08-99';
+-- SELECT date 'Jan-08-1999';
+-- SELECT date '99-08-Jan';
+-- SELECT date '1999-08-Jan';
+
+-- SELECT date '99 Jan 08';
+SELECT date '1999 Jan 08';
+-- SELECT date '08 Jan 99';
+-- SELECT date '08 Jan 1999';
+-- SELECT date 'Jan 08 99';
+-- SELECT date 'Jan 08 1999';
+-- SELECT date '99 08 Jan';
+SELECT date '1999 08 Jan';
+
+-- SELECT date '99-01-08';
+SELECT date '1999-01-08';
+-- SELECT date '08-01-99';
+-- SELECT date '08-01-1999';
+-- SELECT date '01-08-99';
+-- SELECT date '01-08-1999';
+-- SELECT date '99-08-01';
+SELECT date '1999-08-01';
+
+-- SELECT date '99 01 08';
+SELECT date '1999 01 08';
+-- SELECT date '08 01 99';
+-- SELECT date '08 01 1999';
+-- SELECT date '01 08 99';
+-- SELECT date '01 08 1999';
+-- SELECT date '99 08 01';
+SELECT date '1999 08 01';
+
+-- SET datestyle TO mdy;
+
+-- SELECT date 'January 8, 1999';
+SELECT date '1999-01-08';
+SELECT date '1999-01-18';
+-- SELECT date '1/8/1999';
+-- SELECT date '1/18/1999';
+-- SELECT date '18/1/1999';
+-- SELECT date '01/02/03';
+-- SELECT date '19990108';
+-- SELECT date '990108';
+-- SELECT date '1999.008';
+-- SELECT date 'J2451187';
+-- SELECT date 'January 8, 99 BC';
+
+-- SELECT date '99-Jan-08';
+-- SELECT date '1999-Jan-08';
+-- SELECT date '08-Jan-99';
+-- SELECT date '08-Jan-1999';
+-- SELECT date 'Jan-08-99';
+-- SELECT date 'Jan-08-1999';
+-- SELECT date '99-08-Jan';
+-- SELECT date '1999-08-Jan';
+
+-- SELECT date '99 Jan 08';
+SELECT date '1999 Jan 08';
+-- SELECT date '08 Jan 99';
+-- SELECT date '08 Jan 1999';
+-- SELECT date 'Jan 08 99';
+-- SELECT date 'Jan 08 1999';
+-- SELECT date '99 08 Jan';
+SELECT date '1999 08 Jan';
+
+-- SELECT date '99-01-08';
+SELECT date '1999-01-08';
+-- SELECT date '08-01-99';
+-- SELECT date '08-01-1999';
+-- SELECT date '01-08-99';
+-- SELECT date '01-08-1999';
+-- SELECT date '99-08-01';
+SELECT date '1999-08-01';
+
+-- SELECT date '99 01 08';
+SELECT date '1999 01 08';
+-- SELECT date '08 01 99';
+-- SELECT date '08 01 1999';
+-- SELECT date '01 08 99';
+-- SELECT date '01 08 1999';
+-- SELECT date '99 08 01';
+SELECT date '1999 08 01';
+
+-- [SPARK-28253] Date type have different low value and high value
+-- Check upper and lower limits of date range
+SELECT date '4714-11-24 BC';
+SELECT date '4714-11-23 BC'; -- out of range
+SELECT date '5874897-12-31';
+SELECT date '5874898-01-01'; -- out of range
+
+-- RESET datestyle;
+
+--
+-- Simple math
+-- Leave most of it for the horology tests
+--
+
+SELECT f1 - date '2000-01-01' AS `Days From 2K` FROM DATE_TBL;
+
+-- [SPARK-28141] Date type can not accept special values
+-- SELECT f1 - date 'epoch' AS "Days From Epoch" FROM DATE_TBL;
+
+-- SELECT date 'yesterday' - date 'today' AS "One day";
+
+-- SELECT date 'today' - date 'tomorrow' AS "One day";
+
+-- SELECT date 'yesterday' - date 'tomorrow' AS "Two days";
+
+-- SELECT date 'tomorrow' - date 'today' AS "One day";
+
+-- SELECT date 'today' - date 'yesterday' AS "One day";
+
+-- SELECT date 'tomorrow' - date 'yesterday' AS "Two days";
+
+-- [SPARK-28017] Enhance date EXTRACT
+--
+-- test extract!
+--
+-- epoch
+--
+-- SELECT EXTRACT(EPOCH FROM DATE '1970-01-01'); -- 0
+-- SELECT EXTRACT(EPOCH FROM TIMESTAMP '1970-01-01'); -- 0
+-- SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '1970-01-01+00'); -- 0
+--
+-- century
+--
+-- SELECT EXTRACT(CENTURY FROM DATE '0101-12-31 BC'); -- -2
+-- SELECT EXTRACT(CENTURY FROM DATE '0100-12-31 BC'); -- -1
+-- SELECT EXTRACT(CENTURY FROM DATE '0001-12-31 BC'); -- -1
+-- SELECT EXTRACT(CENTURY FROM DATE '0001-01-01'); -- 1
+-- SELECT EXTRACT(CENTURY FROM DATE '0001-01-01 AD'); -- 1
+-- SELECT EXTRACT(CENTURY FROM DATE '1900-12-31'); -- 19
+-- SELECT EXTRACT(CENTURY FROM DATE '1901-01-01'); -- 20
+-- SELECT EXTRACT(CENTURY FROM DATE '2000-12-31'); -- 20
+-- SELECT EXTRACT(CENTURY FROM DATE '2001-01-01'); -- 21
+-- SELECT EXTRACT(CENTURY FROM CURRENT_DATE)>=21 AS True; -- true
+--
+-- millennium
+--
+-- SELECT EXTRACT(MILLENNIUM FROM DATE '0001-12-31 BC'); -- -1
+-- SELECT EXTRACT(MILLENNIUM FROM DATE '0001-01-01 AD'); -- 1
+-- SELECT EXTRACT(MILLENNIUM FROM DATE '1000-12-31'); -- 1
+-- SELECT EXTRACT(MILLENNIUM FROM DATE '1001-01-01'); -- 2
+-- SELECT EXTRACT(MILLENNIUM FROM DATE '2000-12-31'); -- 2
+-- SELECT EXTRACT(MILLENNIUM FROM DATE '2001-01-01'); -- 3
+-- next test to be fixed on the turn of the next millennium;-)
+-- SELECT EXTRACT(MILLENNIUM FROM CURRENT_DATE); -- 3
+--
+-- decade
+--
+-- SELECT EXTRACT(DECADE FROM DATE '1994-12-25'); -- 199
+-- SELECT EXTRACT(DECADE FROM DATE '0010-01-01'); -- 1
+-- SELECT EXTRACT(DECADE FROM DATE '0009-12-31'); -- 0
+-- SELECT EXTRACT(DECADE FROM DATE '0001-01-01 BC'); -- 0
+-- SELECT EXTRACT(DECADE FROM DATE '0002-12-31 BC'); -- -1
+-- SELECT EXTRACT(DECADE FROM DATE '0011-01-01 BC'); -- -1
+-- SELECT EXTRACT(DECADE FROM DATE '0012-12-31 BC'); -- -2
+--
+-- some other types:
+--
+-- on a timestamp.
+-- SELECT EXTRACT(CENTURY FROM NOW())>=21 AS True; -- true
+-- SELECT EXTRACT(CENTURY FROM TIMESTAMP '1970-03-20 04:30:00.00000'); -- 20
+-- on an interval
+-- SELECT EXTRACT(CENTURY FROM INTERVAL '100 y'); -- 1
+-- SELECT EXTRACT(CENTURY FROM INTERVAL '99 y'); -- 0
+-- SELECT EXTRACT(CENTURY FROM INTERVAL '-99 y'); -- 0
+-- SELECT EXTRACT(CENTURY FROM INTERVAL '-100 y'); -- -1
+
+-- [SPARK-28017] Enhance date DATE_TRUNC
+--
+-- test trunc function!
+--
+-- SELECT DATE_TRUNC('MILLENNIUM', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1001
+-- SELECT DATE_TRUNC('MILLENNIUM', DATE '1970-03-20'); -- 1001-01-01
+-- SELECT DATE_TRUNC('CENTURY', TIMESTAMP '1970-03-20 04:30:00.00000'); -- 1901
+-- SELECT DATE_TRUNC('CENTURY', DATE '1970-03-20'); -- 1901
+-- SELECT DATE_TRUNC('CENTURY', DATE '2004-08-10'); -- 2001-01-01
+-- SELECT DATE_TRUNC('CENTURY', DATE '0002-02-04'); -- 0001-01-01
+-- SELECT DATE_TRUNC('CENTURY', DATE '0055-08-10 BC'); -- 0100-01-01 BC
+-- SELECT DATE_TRUNC('DECADE', DATE '1993-12-25'); -- 1990-01-01
+-- SELECT DATE_TRUNC('DECADE', DATE '0004-12-25'); -- 0001-01-01 BC
+-- SELECT DATE_TRUNC('DECADE', DATE '0002-12-31 BC'); -- 0011-01-01 BC
+
+-- [SPARK-28141] Date type can not accept special values
+--
+-- test infinity
+--
+-- select 'infinity'::date, '-infinity'::date;
+-- select 'infinity'::date > 'today'::date as t;
+-- select '-infinity'::date < 'today'::date as t;
+-- select isfinite('infinity'::date), isfinite('-infinity'::date), isfinite('today'::date);
+--
+-- oscillating fields from non-finite date/timestamptz:
+--
+-- SELECT EXTRACT(HOUR FROM DATE 'infinity'); -- NULL
+-- SELECT EXTRACT(HOUR FROM DATE '-infinity'); -- NULL
+-- SELECT EXTRACT(HOUR FROM TIMESTAMP 'infinity'); -- NULL
+-- SELECT EXTRACT(HOUR FROM TIMESTAMP '-infinity'); -- NULL
+-- SELECT EXTRACT(HOUR FROM TIMESTAMPTZ 'infinity'); -- NULL
+-- SELECT EXTRACT(HOUR FROM TIMESTAMPTZ '-infinity'); -- NULL
+-- all possible fields
+-- SELECT EXTRACT(MICROSECONDS FROM DATE 'infinity'); -- NULL
+-- SELECT EXTRACT(MILLISECONDS FROM DATE 'infinity'); -- NULL
+-- SELECT EXTRACT(SECOND FROM DATE 'infinity'); -- NULL
+-- SELECT EXTRACT(MINUTE FROM DATE 'infinity'); -- NULL
+-- SELECT EXTRACT(HOUR FROM DATE 'infinity'); -- NULL
+-- SELECT EXTRACT(DAY FROM DATE 'infinity'); -- NULL
+-- SELECT EXTRACT(MONTH FROM DATE 'infinity'); -- NULL
+-- SELECT EXTRACT(QUARTER FROM DATE 'infinity'); -- NULL
+-- SELECT EXTRACT(WEEK FROM DATE 'infinity'); -- NULL
+-- SELECT EXTRACT(DOW FROM DATE 'infinity'); -- NULL
+-- SELECT EXTRACT(ISODOW FROM DATE 'infinity'); -- NULL
+-- SELECT EXTRACT(DOY FROM DATE 'infinity'); -- NULL
+-- SELECT EXTRACT(TIMEZONE FROM DATE 'infinity'); -- NULL
+-- SELECT EXTRACT(TIMEZONE_M FROM DATE 'infinity'); -- NULL
+-- SELECT EXTRACT(TIMEZONE_H FROM DATE 'infinity'); -- NULL
+--
+-- monotonic fields from non-finite date/timestamptz:
+--
+-- SELECT EXTRACT(EPOCH FROM DATE 'infinity'); -- Infinity
+-- SELECT EXTRACT(EPOCH FROM DATE '-infinity'); -- -Infinity
+-- SELECT EXTRACT(EPOCH FROM TIMESTAMP 'infinity'); -- Infinity
+-- SELECT EXTRACT(EPOCH FROM TIMESTAMP '-infinity'); -- -Infinity
+-- SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ 'infinity'); -- Infinity
+-- SELECT EXTRACT(EPOCH FROM TIMESTAMPTZ '-infinity'); -- -Infinity
+-- all possible fields
+-- SELECT EXTRACT(YEAR FROM DATE 'infinity'); -- Infinity
+-- SELECT EXTRACT(DECADE FROM DATE 'infinity'); -- Infinity
+-- SELECT EXTRACT(CENTURY FROM DATE 'infinity'); -- Infinity
+-- SELECT EXTRACT(MILLENNIUM FROM DATE 'infinity'); -- Infinity
+-- SELECT EXTRACT(JULIAN FROM DATE 'infinity'); -- Infinity
+-- SELECT EXTRACT(ISOYEAR FROM DATE 'infinity'); -- Infinity
+-- SELECT EXTRACT(EPOCH FROM DATE 'infinity'); -- Infinity
+--
+-- wrong fields from non-finite date:
+--
+-- SELECT EXTRACT(MICROSEC FROM DATE 'infinity'); -- ERROR: timestamp units "microsec" not recognized
+-- SELECT EXTRACT(UNDEFINED FROM DATE 'infinity'); -- ERROR: timestamp units "undefined" not supported
+
+-- skip test constructors
+-- test constructors
+-- select make_date(2013, 7, 15);
+-- select make_date(-44, 3, 15);
+-- select make_time(8, 20, 0.0);
+-- should fail
+-- select make_date(2013, 2, 30);
+-- select make_date(2013, 13, 1);
+-- select make_date(2013, 11, -1);
+-- select make_time(10, 55, 100.1);
+-- select make_time(24, 0, 2.1);
+
+DROP TABLE DATE_TBL;
diff --git a/sql/core/src/test/resources/sql-tests/results/pgSQL/date.sql.out b/sql/core/src/test/resources/sql-tests/results/pgSQL/date.sql.out
new file mode 100644
index 0000000..a0630b9
--- /dev/null
+++ b/sql/core/src/test/resources/sql-tests/results/pgSQL/date.sql.out
@@ -0,0 +1,443 @@
+-- Automatically generated by SQLQueryTestSuite
+-- Number of queries: 48
+
+
+-- !query 0
+CREATE TABLE DATE_TBL (f1 date) USING parquet
+-- !query 0 schema
+struct<>
+-- !query 0 output
+
+
+
+-- !query 1
+INSERT INTO DATE_TBL VALUES ('1957-04-09')
+-- !query 1 schema
+struct<>
+-- !query 1 output
+
+
+
+-- !query 2
+INSERT INTO DATE_TBL VALUES ('1957-06-13')
+-- !query 2 schema
+struct<>
+-- !query 2 output
+
+
+
+-- !query 3
+INSERT INTO DATE_TBL VALUES ('1996-02-28')
+-- !query 3 schema
+struct<>
+-- !query 3 output
+
+
+
+-- !query 4
+INSERT INTO DATE_TBL VALUES ('1996-02-29')
+-- !query 4 schema
+struct<>
+-- !query 4 output
+
+
+
+-- !query 5
+INSERT INTO DATE_TBL VALUES ('1996-03-01')
+-- !query 5 schema
+struct<>
+-- !query 5 output
+
+
+
+-- !query 6
+INSERT INTO DATE_TBL VALUES ('1996-03-02')
+-- !query 6 schema
+struct<>
+-- !query 6 output
+
+
+
+-- !query 7
+INSERT INTO DATE_TBL VALUES ('1997-02-28')
+-- !query 7 schema
+struct<>
+-- !query 7 output
+
+
+
+-- !query 8
+INSERT INTO DATE_TBL VALUES ('1997-03-01')
+-- !query 8 schema
+struct<>
+-- !query 8 output
+
+
+
+-- !query 9
+INSERT INTO DATE_TBL VALUES ('1997-03-02')
+-- !query 9 schema
+struct<>
+-- !query 9 output
+
+
+
+-- !query 10
+INSERT INTO DATE_TBL VALUES ('2000-04-01')
+-- !query 10 schema
+struct<>
+-- !query 10 output
+
+
+
+-- !query 11
+INSERT INTO DATE_TBL VALUES ('2000-04-02')
+-- !query 11 schema
+struct<>
+-- !query 11 output
+
+
+
+-- !query 12
+INSERT INTO DATE_TBL VALUES ('2000-04-03')
+-- !query 12 schema
+struct<>
+-- !query 12 output
+
+
+
+-- !query 13
+INSERT INTO DATE_TBL VALUES ('2038-04-08')
+-- !query 13 schema
+struct<>
+-- !query 13 output
+
+
+
+-- !query 14
+INSERT INTO DATE_TBL VALUES ('2039-04-09')
+-- !query 14 schema
+struct<>
+-- !query 14 output
+
+
+
+-- !query 15
+INSERT INTO DATE_TBL VALUES ('2040-04-10')
+-- !query 15 schema
+struct<>
+-- !query 15 output
+
+
+
+-- !query 16
+SELECT f1 AS `Fifteen` FROM DATE_TBL
+-- !query 16 schema
+struct<Fifteen:date>
+-- !query 16 output
+1957-04-09
+1957-06-13
+1996-02-28
+1996-02-29
+1996-03-01
+1996-03-02
+1997-02-28
+1997-03-01
+1997-03-02
+2000-04-01
+2000-04-02
+2000-04-03
+2038-04-08
+2039-04-09
+2040-04-10
+
+
+-- !query 17
+SELECT f1 AS `Nine` FROM DATE_TBL WHERE f1 < '2000-01-01'
+-- !query 17 schema
+struct<Nine:date>
+-- !query 17 output
+1957-04-09
+1957-06-13
+1996-02-28
+1996-02-29
+1996-03-01
+1996-03-02
+1997-02-28
+1997-03-01
+1997-03-02
+
+
+-- !query 18
+SELECT f1 AS `Three` FROM DATE_TBL
+ WHERE f1 BETWEEN '2000-01-01' AND '2001-01-01'
+-- !query 18 schema
+struct<Three:date>
+-- !query 18 output
+2000-04-01
+2000-04-02
+2000-04-03
+
+
+-- !query 19
+SELECT date '1999-01-08'
+-- !query 19 schema
+struct<DATE '1999-01-08':date>
+-- !query 19 output
+1999-01-08
+
+
+-- !query 20
+SELECT date '1999-01-18'
+-- !query 20 schema
+struct<DATE '1999-01-18':date>
+-- !query 20 output
+1999-01-18
+
+
+-- !query 21
+SELECT date '1999 Jan 08'
+-- !query 21 schema
+struct<DATE '1999-01-01':date>
+-- !query 21 output
+1999-01-01
+
+
+-- !query 22
+SELECT date '1999 08 Jan'
+-- !query 22 schema
+struct<DATE '1999-01-01':date>
+-- !query 22 output
+1999-01-01
+
+
+-- !query 23
+SELECT date '1999-01-08'
+-- !query 23 schema
+struct<DATE '1999-01-08':date>
+-- !query 23 output
+1999-01-08
+
+
+-- !query 24
+SELECT date '1999-08-01'
+-- !query 24 schema
+struct<DATE '1999-08-01':date>
+-- !query 24 output
+1999-08-01
+
+
+-- !query 25
+SELECT date '1999 01 08'
+-- !query 25 schema
+struct<DATE '1999-01-01':date>
+-- !query 25 output
+1999-01-01
+
+
+-- !query 26
+SELECT date '1999 08 01'
+-- !query 26 schema
+struct<DATE '1999-01-01':date>
+-- !query 26 output
+1999-01-01
+
+
+-- !query 27
+SELECT date '1999-01-08'
+-- !query 27 schema
+struct<DATE '1999-01-08':date>
+-- !query 27 output
+1999-01-08
+
+
+-- !query 28
+SELECT date '1999 Jan 08'
+-- !query 28 schema
+struct<DATE '1999-01-01':date>
+-- !query 28 output
+1999-01-01
+
+
+-- !query 29
+SELECT date '1999 08 Jan'
+-- !query 29 schema
+struct<DATE '1999-01-01':date>
+-- !query 29 output
+1999-01-01
+
+
+-- !query 30
+SELECT date '1999-01-08'
+-- !query 30 schema
+struct<DATE '1999-01-08':date>
+-- !query 30 output
+1999-01-08
+
+
+-- !query 31
+SELECT date '1999-08-01'
+-- !query 31 schema
+struct<DATE '1999-08-01':date>
+-- !query 31 output
+1999-08-01
+
+
+-- !query 32
+SELECT date '1999 01 08'
+-- !query 32 schema
+struct<DATE '1999-01-01':date>
+-- !query 32 output
+1999-01-01
+
+
+-- !query 33
+SELECT date '1999 08 01'
+-- !query 33 schema
+struct<DATE '1999-01-01':date>
+-- !query 33 output
+1999-01-01
+
+
+-- !query 34
+SELECT date '1999-01-08'
+-- !query 34 schema
+struct<DATE '1999-01-08':date>
+-- !query 34 output
+1999-01-08
+
+
+-- !query 35
+SELECT date '1999-01-18'
+-- !query 35 schema
+struct<DATE '1999-01-18':date>
+-- !query 35 output
+1999-01-18
+
+
+-- !query 36
+SELECT date '1999 Jan 08'
+-- !query 36 schema
+struct<DATE '1999-01-01':date>
+-- !query 36 output
+1999-01-01
+
+
+-- !query 37
+SELECT date '1999 08 Jan'
+-- !query 37 schema
+struct<DATE '1999-01-01':date>
+-- !query 37 output
+1999-01-01
+
+
+-- !query 38
+SELECT date '1999-01-08'
+-- !query 38 schema
+struct<DATE '1999-01-08':date>
+-- !query 38 output
+1999-01-08
+
+
+-- !query 39
+SELECT date '1999-08-01'
+-- !query 39 schema
+struct<DATE '1999-08-01':date>
+-- !query 39 output
+1999-08-01
+
+
+-- !query 40
+SELECT date '1999 01 08'
+-- !query 40 schema
+struct<DATE '1999-01-01':date>
+-- !query 40 output
+1999-01-01
+
+
+-- !query 41
+SELECT date '1999 08 01'
+-- !query 41 schema
+struct<DATE '1999-01-01':date>
+-- !query 41 output
+1999-01-01
+
+
+-- !query 42
+SELECT date '4714-11-24 BC'
+-- !query 42 schema
+struct<DATE '4714-11-24':date>
+-- !query 42 output
+4714-11-24
+
+
+-- !query 43
+SELECT date '4714-11-23 BC'
+-- !query 43 schema
+struct<DATE '4714-11-23':date>
+-- !query 43 output
+4714-11-23
+
+
+-- !query 44
+-- out of range
+SELECT date '5874897-12-31'
+-- !query 44 schema
+struct<>
+-- !query 44 output
+org.apache.spark.sql.catalyst.parser.ParseException
+
+Cannot parse the DATE value: 5874897-12-31(line 2, pos 7)
+
+== SQL ==
+-- out of range
+SELECT date '5874897-12-31'
+-------^^^
+
+
+-- !query 45
+SELECT date '5874898-01-01'
+-- !query 45 schema
+struct<>
+-- !query 45 output
+org.apache.spark.sql.catalyst.parser.ParseException
+
+Cannot parse the DATE value: 5874898-01-01(line 1, pos 7)
+
+== SQL ==
+SELECT date '5874898-01-01'
+-------^^^
+
+
+-- !query 46
+-- out of range
+
+
+
+SELECT f1 - date '2000-01-01' AS `Days From 2K` FROM DATE_TBL
+-- !query 46 schema
+struct<Days From 2K:int>
+-- !query 46 output
+-1035
+-1036
+-1037
+-1400
+-1401
+-1402
+-1403
+-15542
+-15607
+13977
+14343
+14710
+91
+92
+93
+
+
+-- !query 47
+DROP TABLE DATE_TBL
+-- !query 47 schema
+struct<>
+-- !query 47 output
+
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@spark.apache.org
For additional commands, e-mail: commits-help@spark.apache.org