This is an automated email from the ASF dual-hosted git repository.
wenchen pushed a commit to branch branch-3.0
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/branch-3.0 by this push:
new 06d3110 [SPARK-31119][SQL] Add interval value support for extract
expression as extract source
06d3110 is described below
commit 06d31104120a6f0ce249090864a4f452af0b675a
Author: Kent Yao <[email protected]>
AuthorDate: Wed Mar 18 12:29:39 2020 +0800
[SPARK-31119][SQL] Add interval value support for extract expression as
extract source
### What changes were proposed in this pull request?
```
<extract expression> ::= EXTRACT <left paren> <extract field> FROM <extract
source> <right paren>
<extract source> ::= <datetime value expression> | <interval value
expression>
```
We now only support datetime values as extract source for `extract`
expression but it's alternative function `date_part` supports both datetime and
interval.
This pr adds interval value support for `extract` expression as extract
source
### Why are the changes needed?
For ANSI compliance and the semantic consistency between extract and
`date_part`, we support intervals for extract expressions.
### Does this PR introduce any user-facing change?
yes, in the `extract(abc from xyz)` expression, the `xyz` can be intervals
### How was this patch tested?
add unit tests
Closes #27876 from yaooqinn/SPARK-31119.
Authored-by: Kent Yao <[email protected]>
Signed-off-by: Wenchen Fan <[email protected]>
(cherry picked from commit f1d27cdd91d0d5d107add662f7c35fc4a5dd278c)
Signed-off-by: Wenchen Fan <[email protected]>
---
.../spark/sql/catalyst/parser/AstBuilder.scala | 8 +-
.../benchmarks/ExtractBenchmark-jdk11-results.txt | 207 ++++++------
sql/core/benchmarks/ExtractBenchmark-results.txt | 207 ++++++------
.../test/resources/sql-tests/inputs/extract.sql | 121 +++----
.../resources/sql-tests/results/extract.sql.out | 358 +++++++++++----------
.../sql-tests/results/postgreSQL/date.sql.out | 16 +-
.../sql/execution/benchmark/ExtractBenchmark.scala | 16 +-
7 files changed, 484 insertions(+), 449 deletions(-)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
index 3c23ecb..b94fd1d 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/parser/AstBuilder.scala
@@ -1545,12 +1545,8 @@ class AstBuilder(conf: SQLConf) extends
SqlBaseBaseVisitor[AnyRef] with Logging
* Create a Extract expression.
*/
override def visitExtract(ctx: ExtractContext): Expression = withOrigin(ctx)
{
- val fieldStr = ctx.field.getText
- val source = expression(ctx.source)
- val extractField = DatePart.parseExtractField(fieldStr, source, {
- throw new ParseException(s"Literals of type '$fieldStr' are currently
not supported.", ctx)
- })
- new DatePart(Literal(fieldStr), expression(ctx.source), extractField)
+ val arguments = Seq(Literal(ctx.field.getText), expression(ctx.source))
+ UnresolvedFunction("date_part", arguments, isDistinct = false)
}
/**
diff --git a/sql/core/benchmarks/ExtractBenchmark-jdk11-results.txt
b/sql/core/benchmarks/ExtractBenchmark-jdk11-results.txt
index 0873215..8c56c0b 100644
--- a/sql/core/benchmarks/ExtractBenchmark-jdk11-results.txt
+++ b/sql/core/benchmarks/ExtractBenchmark-jdk11-results.txt
@@ -2,118 +2,137 @@ Java HotSpot(TM) 64-Bit Server VM 11.0.5+10-LTS on Mac OS X
10.15.3
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke extract for timestamp: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to timestamp 343 348
5 29.1 34.3 1.0X
-MILLENNIUM of timestamp 888 899
14 11.3 88.8 0.4X
-CENTURY of timestamp 835 846
15 12.0 83.5 0.4X
-DECADE of timestamp 824 834
9 12.1 82.4 0.4X
-YEAR of timestamp 818 824
10 12.2 81.8 0.4X
-ISOYEAR of timestamp 879 890
17 11.4 87.9 0.4X
-QUARTER of timestamp 965 1073
96 10.4 96.5 0.4X
-MONTH of timestamp 787 793
8 12.7 78.7 0.4X
-WEEK of timestamp 1277 1341
57 7.8 127.7 0.3X
-DAY of timestamp 780 785
8 12.8 78.0 0.4X
-DAYOFWEEK of timestamp 956 962
7 10.5 95.6 0.4X
-DOW of timestamp 995 1034
36 10.1 99.5 0.3X
-ISODOW of timestamp 885 898
11 11.3 88.5 0.4X
-DOY of timestamp 808 810
3 12.4 80.8 0.4X
-HOUR of timestamp 622 627
7 16.1 62.2 0.6X
-MINUTE of timestamp 629 636
8 15.9 62.9 0.5X
-SECOND of timestamp 800 819
30 12.5 80.0 0.4X
-MILLISECONDS of timestamp 743 755
21 13.5 74.3 0.5X
-MICROSECONDS of timestamp 692 730
53 14.5 69.2 0.5X
-EPOCH of timestamp 773 798
40 12.9 77.3 0.4X
+cast to timestamp 311 331
18 32.2 31.1 1.0X
+MILLENNIUM of timestamp 873 893
17 11.4 87.3 0.4X
+CENTURY of timestamp 869 873
5 11.5 86.9 0.4X
+DECADE of timestamp 851 872
23 11.7 85.1 0.4X
+YEAR of timestamp 841 856
14 11.9 84.1 0.4X
+ISOYEAR of timestamp 927 938
12 10.8 92.7 0.3X
+QUARTER of timestamp 959 963
6 10.4 95.9 0.3X
+MONTH of timestamp 852 864
18 11.7 85.2 0.4X
+WEEK of timestamp 1124 1252
112 8.9 112.4 0.3X
+DAY of timestamp 848 867
19 11.8 84.8 0.4X
+DAYOFWEEK of timestamp 977 987
16 10.2 97.7 0.3X
+DOW of timestamp 945 964
18 10.6 94.5 0.3X
+ISODOW of timestamp 924 929
5 10.8 92.4 0.3X
+DOY of timestamp 852 906
67 11.7 85.2 0.4X
+HOUR of timestamp 665 671
5 15.0 66.5 0.5X
+MINUTE of timestamp 655 670
15 15.3 65.5 0.5X
+SECOND of timestamp 757 763
7 13.2 75.7 0.4X
+MILLISECONDS of timestamp 745 761
14 13.4 74.5 0.4X
+MICROSECONDS of timestamp 691 697
7 14.5 69.1 0.5X
+EPOCH of timestamp 794 806
12 12.6 79.4 0.4X
Java HotSpot(TM) 64-Bit Server VM 11.0.5+10-LTS on Mac OS X 10.15.3
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke date_part for timestamp: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to timestamp 303 323
24 33.0 30.3 1.0X
-MILLENNIUM of timestamp 841 852
9 11.9 84.1 0.4X
-CENTURY of timestamp 818 821
4 12.2 81.8 0.4X
-DECADE of timestamp 821 836
15 12.2 82.1 0.4X
-YEAR of timestamp 858 928
61 11.7 85.8 0.4X
-ISOYEAR of timestamp 933 939
6 10.7 93.3 0.3X
-QUARTER of timestamp 979 1004
26 10.2 97.9 0.3X
-MONTH of timestamp 755 784
25 13.2 75.5 0.4X
-WEEK of timestamp 1189 1228
64 8.4 118.9 0.3X
-DAY of timestamp 768 770
4 13.0 76.8 0.4X
-DAYOFWEEK of timestamp 918 948
27 10.9 91.8 0.3X
-DOW of timestamp 933 983
53 10.7 93.3 0.3X
-ISODOW of timestamp 884 928
61 11.3 88.4 0.3X
-DOY of timestamp 787 797
12 12.7 78.7 0.4X
-HOUR of timestamp 611 664
58 16.4 61.1 0.5X
-MINUTE of timestamp 616 622
5 16.2 61.6 0.5X
-SECOND of timestamp 732 748
14 13.7 73.2 0.4X
-MILLISECONDS of timestamp 705 716
17 14.2 70.5 0.4X
-MICROSECONDS of timestamp 642 664
33 15.6 64.2 0.5X
-EPOCH of timestamp 760 781
18 13.2 76.0 0.4X
+cast to timestamp 273 274
1 36.6 27.3 1.0X
+MILLENNIUM of timestamp 860 875
14 11.6 86.0 0.3X
+CENTURY of timestamp 854 866
11 11.7 85.4 0.3X
+DECADE of timestamp 855 863
10 11.7 85.5 0.3X
+YEAR of timestamp 833 837
4 12.0 83.3 0.3X
+ISOYEAR of timestamp 1022 1036
18 9.8 102.2 0.3X
+QUARTER of timestamp 1067 1132
58 9.4 106.7 0.3X
+MONTH of timestamp 855 861
5 11.7 85.5 0.3X
+WEEK of timestamp 1312 1325
17 7.6 131.2 0.2X
+DAY of timestamp 837 847
10 12.0 83.7 0.3X
+DAYOFWEEK of timestamp 965 969
4 10.4 96.5 0.3X
+DOW of timestamp 981 1038
56 10.2 98.1 0.3X
+ISODOW of timestamp 942 959
20 10.6 94.2 0.3X
+DOY of timestamp 994 1021
24 10.1 99.4 0.3X
+HOUR of timestamp 690 695
7 14.5 69.0 0.4X
+MINUTE of timestamp 764 788
21 13.1 76.4 0.4X
+SECOND of timestamp 776 795
34 12.9 77.6 0.4X
+MILLISECONDS of timestamp 863 879
14 11.6 86.3 0.3X
+MICROSECONDS of timestamp 689 695
7 14.5 68.9 0.4X
+EPOCH of timestamp 797 865
69 12.5 79.7 0.3X
Java HotSpot(TM) 64-Bit Server VM 11.0.5+10-LTS on Mac OS X 10.15.3
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke extract for date: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to date 712 786
120 14.0 71.2 1.0X
-MILLENNIUM of date 846 888
73 11.8 84.6 0.8X
-CENTURY of date 781 792
11 12.8 78.1 0.9X
-DECADE of date 770 824
48 13.0 77.0 0.9X
-YEAR of date 804 832
27 12.4 80.4 0.9X
-ISOYEAR of date 909 931
24 11.0 90.9 0.8X
-QUARTER of date 957 975
22 10.4 95.7 0.7X
-MONTH of date 789 794
7 12.7 78.9 0.9X
-WEEK of date 1141 1165
29 8.8 114.1 0.6X
-DAY of date 784 800
22 12.8 78.4 0.9X
-DAYOFWEEK of date 907 916
14 11.0 90.7 0.8X
-DOW of date 931 958
25 10.7 93.1 0.8X
-ISODOW of date 852 857
7 11.7 85.2 0.8X
-DOY of date 831 870
45 12.0 83.1 0.9X
-HOUR of date 1574 1598
26 6.4 157.4 0.5X
-MINUTE of date 1525 1590
58 6.6 152.5 0.5X
-SECOND of date 1728 1739
10 5.8 172.8 0.4X
-MILLISECONDS of date 1715 1727
10 5.8 171.5 0.4X
-MICROSECONDS of date 1559 1669
95 6.4 155.9 0.5X
-EPOCH of date 1766 1774
9 5.7 176.6 0.4X
+cast to date 763 774
13 13.1 76.3 1.0X
+MILLENNIUM of date 857 911
73 11.7 85.7 0.9X
+CENTURY of date 869 884
15 11.5 86.9 0.9X
+DECADE of date 860 868
7 11.6 86.0 0.9X
+YEAR of date 848 852
7 11.8 84.8 0.9X
+ISOYEAR of date 1028 1046
22 9.7 102.8 0.7X
+QUARTER of date 979 986
7 10.2 97.9 0.8X
+MONTH of date 857 862
4 11.7 85.7 0.9X
+WEEK of date 1218 1247
29 8.2 121.8 0.6X
+DAY of date 855 868
11 11.7 85.5 0.9X
+DAYOFWEEK of date 987 1081
82 10.1 98.7 0.8X
+DOW of date 986 996
9 10.1 98.6 0.8X
+ISODOW of date 938 947
10 10.7 93.8 0.8X
+DOY of date 877 890
11 11.4 87.7 0.9X
+HOUR of date 1656 1668
12 6.0 165.6 0.5X
+MINUTE of date 1652 1666
15 6.1 165.2 0.5X
+SECOND of date 1752 1776
23 5.7 175.2 0.4X
+MILLISECONDS of date 1757 1766
11 5.7 175.7 0.4X
+MICROSECONDS of date 1682 1691
14 5.9 168.2 0.5X
+EPOCH of date 1706 1721
14 5.9 170.6 0.4X
Java HotSpot(TM) 64-Bit Server VM 11.0.5+10-LTS on Mac OS X 10.15.3
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke date_part for date: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to date 803 825
23 12.5 80.3 1.0X
-MILLENNIUM of date 918 924
8 10.9 91.8 0.9X
-CENTURY of date 855 879
25 11.7 85.5 0.9X
-DECADE of date 777 795
19 12.9 77.7 1.0X
-YEAR of date 772 779
9 13.0 77.2 1.0X
-ISOYEAR of date 903 927
21 11.1 90.3 0.9X
-QUARTER of date 983 1339
381 10.2 98.3 0.8X
-MONTH of date 764 780
20 13.1 76.4 1.1X
-WEEK of date 1239 1316
112 8.1 123.9 0.6X
-DAY of date 769 857
91 13.0 76.9 1.0X
-DAYOFWEEK of date 900 965
61 11.1 90.0 0.9X
-DOW of date 990 1041
62 10.1 99.0 0.8X
-ISODOW of date 974 977
5 10.3 97.4 0.8X
-DOY of date 862 918
50 11.6 86.2 0.9X
-HOUR of date 1491 1517
25 6.7 149.1 0.5X
-MINUTE of date 1514 1520
9 6.6 151.4 0.5X
-SECOND of date 1657 1677
27 6.0 165.7 0.5X
-MILLISECONDS of date 1646 1669
26 6.1 164.6 0.5X
-MICROSECONDS of date 1538 1556
23 6.5 153.8 0.5X
-EPOCH of date 1699 1718
28 5.9 169.9 0.5X
+cast to date 771 779
10 13.0 77.1 1.0X
+MILLENNIUM of date 839 858
22 11.9 83.9 0.9X
+CENTURY of date 853 872
22 11.7 85.3 0.9X
+DECADE of date 871 878
6 11.5 87.1 0.9X
+YEAR of date 853 863
9 11.7 85.3 0.9X
+ISOYEAR of date 1011 1021
11 9.9 101.1 0.8X
+QUARTER of date 981 988
7 10.2 98.1 0.8X
+MONTH of date 859 863
5 11.6 85.9 0.9X
+WEEK of date 1148 1159
12 8.7 114.8 0.7X
+DAY of date 852 855
2 11.7 85.2 0.9X
+DAYOFWEEK of date 937 952
12 10.7 93.7 0.8X
+DOW of date 953 956
3 10.5 95.3 0.8X
+ISODOW of date 899 916
16 11.1 89.9 0.9X
+DOY of date 857 865
7 11.7 85.7 0.9X
+HOUR of date 1644 1662
18 6.1 164.4 0.5X
+MINUTE of date 1633 1644
10 6.1 163.3 0.5X
+SECOND of date 1719 1727
9 5.8 171.9 0.4X
+MILLISECONDS of date 1721 1733
11 5.8 172.1 0.4X
+MICROSECONDS of date 1656 1680
34 6.0 165.6 0.5X
+EPOCH of date 1721 1736
15 5.8 172.1 0.4X
+
+Java HotSpot(TM) 64-Bit Server VM 11.0.5+10-LTS on Mac OS X 10.15.3
+Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
+Invoke extract for interval: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
+------------------------------------------------------------------------------------------------------------------------
+cast to interval 939 947
9 10.7 93.9 1.0X
+MILLENNIUM of interval 953 960
6 10.5 95.3 1.0X
+CENTURY of interval 971 979
10 10.3 97.1 1.0X
+DECADE of interval 962 969
7 10.4 96.2 1.0X
+YEAR of interval 965 973
10 10.4 96.5 1.0X
+QUARTER of interval 967 979
15 10.3 96.7 1.0X
+MONTH of interval 951 979
26 10.5 95.1 1.0X
+DAY of interval 945 970
25 10.6 94.5 1.0X
+HOUR of interval 963 970
9 10.4 96.3 1.0X
+MINUTE of interval 972 991
24 10.3 97.2 1.0X
+SECOND of interval 1053 1057
8 9.5 105.3 0.9X
+MILLISECONDS of interval 1042 1048
11 9.6 104.2 0.9X
+MICROSECONDS of interval 963 967
4 10.4 96.3 1.0X
+EPOCH of interval 1051 1071
22 9.5 105.1 0.9X
Java HotSpot(TM) 64-Bit Server VM 11.0.5+10-LTS on Mac OS X 10.15.3
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke date_part for interval: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to interval 971 976
5 10.3 97.1 1.0X
-MILLENNIUM of interval 983 1009
23 10.2 98.3 1.0X
-CENTURY of interval 972 984
12 10.3 97.2 1.0X
-DECADE of interval 954 962
10 10.5 95.4 1.0X
-YEAR of interval 954 973
17 10.5 95.4 1.0X
-QUARTER of interval 1009 1020
11 9.9 100.9 1.0X
-MONTH of interval 946 963
16 10.6 94.6 1.0X
-DAY of interval 952 963
12 10.5 95.2 1.0X
-HOUR of interval 948 960
11 10.5 94.8 1.0X
-MINUTE of interval 1035 1040
4 9.7 103.5 0.9X
-SECOND of interval 1085 1105
31 9.2 108.5 0.9X
-MILLISECONDS of interval 1065 1088
24 9.4 106.5 0.9X
-MICROSECONDS of interval 992 1007
17 10.1 99.2 1.0X
-EPOCH of interval 1087 1103
25 9.2 108.7 0.9X
+cast to interval 944 952
8 10.6 94.4 1.0X
+MILLENNIUM of interval 955 960
5 10.5 95.5 1.0X
+CENTURY of interval 958 972
12 10.4 95.8 1.0X
+DECADE of interval 962 967
6 10.4 96.2 1.0X
+YEAR of interval 958 978
18 10.4 95.8 1.0X
+QUARTER of interval 979 998
19 10.2 97.9 1.0X
+MONTH of interval 949 963
12 10.5 94.9 1.0X
+DAY of interval 958 971
11 10.4 95.8 1.0X
+HOUR of interval 947 962
15 10.6 94.7 1.0X
+MINUTE of interval 989 1006
15 10.1 98.9 1.0X
+SECOND of interval 1061 1064
4 9.4 106.1 0.9X
+MILLISECONDS of interval 1042 1061
18 9.6 104.2 0.9X
+MICROSECONDS of interval 982 992
12 10.2 98.2 1.0X
+EPOCH of interval 1047 1057
13 9.5 104.7 0.9X
diff --git a/sql/core/benchmarks/ExtractBenchmark-results.txt
b/sql/core/benchmarks/ExtractBenchmark-results.txt
index 36bdbd0..94cf0bc 100644
--- a/sql/core/benchmarks/ExtractBenchmark-results.txt
+++ b/sql/core/benchmarks/ExtractBenchmark-results.txt
@@ -2,118 +2,137 @@ Java HotSpot(TM) 64-Bit Server VM 1.8.0_231-b11 on Mac OS X
10.15.3
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke extract for timestamp: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to timestamp 287 308
19 34.8 28.7 1.0X
-MILLENNIUM of timestamp 896 918
20 11.2 89.6 0.3X
-CENTURY of timestamp 849 856
7 11.8 84.9 0.3X
-DECADE of timestamp 765 777
17 13.1 76.5 0.4X
-YEAR of timestamp 754 756
2 13.3 75.4 0.4X
-ISOYEAR of timestamp 843 849
5 11.9 84.3 0.3X
-QUARTER of timestamp 867 873
9 11.5 86.7 0.3X
-MONTH of timestamp 758 762
4 13.2 75.8 0.4X
-WEEK of timestamp 1049 1054
6 9.5 104.9 0.3X
-DAY of timestamp 750 763
11 13.3 75.0 0.4X
-DAYOFWEEK of timestamp 890 918
25 11.2 89.0 0.3X
-DOW of timestamp 879 887
8 11.4 87.9 0.3X
-ISODOW of timestamp 862 869
11 11.6 86.2 0.3X
-DOY of timestamp 811 868
55 12.3 81.1 0.4X
-HOUR of timestamp 627 638
11 16.0 62.7 0.5X
-MINUTE of timestamp 600 606
6 16.7 60.0 0.5X
-SECOND of timestamp 743 799
51 13.5 74.3 0.4X
-MILLISECONDS of timestamp 723 737
22 13.8 72.3 0.4X
-MICROSECONDS of timestamp 648 653
5 15.4 64.8 0.4X
-EPOCH of timestamp 780 800
17 12.8 78.0 0.4X
+cast to timestamp 275 307
28 36.3 27.5 1.0X
+MILLENNIUM of timestamp 911 924
20 11.0 91.1 0.3X
+CENTURY of timestamp 846 850
5 11.8 84.6 0.3X
+DECADE of timestamp 744 761
21 13.4 74.4 0.4X
+YEAR of timestamp 750 764
15 13.3 75.0 0.4X
+ISOYEAR of timestamp 815 828
11 12.3 81.5 0.3X
+QUARTER of timestamp 882 895
12 11.3 88.2 0.3X
+MONTH of timestamp 739 760
19 13.5 73.9 0.4X
+WEEK of timestamp 1058 1082
23 9.4 105.8 0.3X
+DAY of timestamp 722 730
8 13.8 72.2 0.4X
+DAYOFWEEK of timestamp 860 907
58 11.6 86.0 0.3X
+DOW of timestamp 853 860
7 11.7 85.3 0.3X
+ISODOW of timestamp 829 835
8 12.1 82.9 0.3X
+DOY of timestamp 757 772
20 13.2 75.7 0.4X
+HOUR of timestamp 586 594
7 17.1 58.6 0.5X
+MINUTE of timestamp 577 584
8 17.3 57.7 0.5X
+SECOND of timestamp 810 827
17 12.4 81.0 0.3X
+MILLISECONDS of timestamp 687 704
16 14.6 68.7 0.4X
+MICROSECONDS of timestamp 628 632
6 15.9 62.8 0.4X
+EPOCH of timestamp 750 761
12 13.3 75.0 0.4X
Java HotSpot(TM) 64-Bit Server VM 1.8.0_231-b11 on Mac OS X 10.15.3
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke date_part for timestamp: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to timestamp 238 248
12 42.0 23.8 1.0X
-MILLENNIUM of timestamp 862 875
12 11.6 86.2 0.3X
-CENTURY of timestamp 833 847
22 12.0 83.3 0.3X
-DECADE of timestamp 759 765
7 13.2 75.9 0.3X
-YEAR of timestamp 744 755
15 13.4 74.4 0.3X
-ISOYEAR of timestamp 937 1019
73 10.7 93.7 0.3X
-QUARTER of timestamp 1011 1091
69 9.9 101.1 0.2X
-MONTH of timestamp 846 888
40 11.8 84.6 0.3X
-WEEK of timestamp 1210 1239
41 8.3 121.0 0.2X
-DAY of timestamp 932 979
41 10.7 93.2 0.3X
-DAYOFWEEK of timestamp 1174 1200
42 8.5 117.4 0.2X
-DOW of timestamp 1131 1172
37 8.8 113.1 0.2X
-ISODOW of timestamp 896 903
7 11.2 89.6 0.3X
-DOY of timestamp 805 818
12 12.4 80.5 0.3X
-HOUR of timestamp 596 597
2 16.8 59.6 0.4X
-MINUTE of timestamp 582 597
17 17.2 58.2 0.4X
-SECOND of timestamp 697 709
16 14.4 69.7 0.3X
-MILLISECONDS of timestamp 700 710
10 14.3 70.0 0.3X
-MICROSECONDS of timestamp 612 631
21 16.3 61.2 0.4X
-EPOCH of timestamp 755 760
7 13.2 75.5 0.3X
+cast to timestamp 238 242
4 42.0 23.8 1.0X
+MILLENNIUM of timestamp 811 835
25 12.3 81.1 0.3X
+CENTURY of timestamp 799 823
21 12.5 79.9 0.3X
+DECADE of timestamp 733 743
12 13.6 73.3 0.3X
+YEAR of timestamp 714 717
3 14.0 71.4 0.3X
+ISOYEAR of timestamp 874 900
24 11.4 87.4 0.3X
+QUARTER of timestamp 903 910
11 11.1 90.3 0.3X
+MONTH of timestamp 774 808
44 12.9 77.4 0.3X
+WEEK of timestamp 1053 1064
12 9.5 105.3 0.2X
+DAY of timestamp 755 770
14 13.2 75.5 0.3X
+DAYOFWEEK of timestamp 831 841
9 12.0 83.1 0.3X
+DOW of timestamp 871 898
23 11.5 87.1 0.3X
+ISODOW of timestamp 832 887
59 12.0 83.2 0.3X
+DOY of timestamp 755 767
13 13.2 75.5 0.3X
+HOUR of timestamp 580 597
27 17.3 58.0 0.4X
+MINUTE of timestamp 572 589
15 17.5 57.2 0.4X
+SECOND of timestamp 716 730
12 14.0 71.6 0.3X
+MILLISECONDS of timestamp 716 762
57 14.0 71.6 0.3X
+MICROSECONDS of timestamp 600 610
12 16.7 60.0 0.4X
+EPOCH of timestamp 751 779
44 13.3 75.1 0.3X
Java HotSpot(TM) 64-Bit Server VM 1.8.0_231-b11 on Mac OS X 10.15.3
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke extract for date: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to date 633 641
7 15.8 63.3 1.0X
-MILLENNIUM of date 824 845
26 12.1 82.4 0.8X
-CENTURY of date 864 878
13 11.6 86.4 0.7X
-DECADE of date 746 763
17 13.4 74.6 0.8X
-YEAR of date 752 785
39 13.3 75.2 0.8X
-ISOYEAR of date 900 905
5 11.1 90.0 0.7X
-QUARTER of date 906 930
23 11.0 90.6 0.7X
-MONTH of date 747 752
6 13.4 74.7 0.8X
-WEEK of date 1089 1100
17 9.2 108.9 0.6X
-DAY of date 795 803
13 12.6 79.5 0.8X
-DAYOFWEEK of date 937 944
10 10.7 93.7 0.7X
-DOW of date 927 1003
66 10.8 92.7 0.7X
-ISODOW of date 977 980
7 10.2 97.7 0.6X
-DOY of date 827 877
45 12.1 82.7 0.8X
-HOUR of date 1525 1547
34 6.6 152.5 0.4X
-MINUTE of date 1473 1499
23 6.8 147.3 0.4X
-SECOND of date 1600 1615
15 6.2 160.0 0.4X
-MILLISECONDS of date 1666 1765
156 6.0 166.6 0.4X
-MICROSECONDS of date 1554 1627
127 6.4 155.4 0.4X
-EPOCH of date 1615 1646
27 6.2 161.5 0.4X
+cast to date 629 634
6 15.9 62.9 1.0X
+MILLENNIUM of date 826 912
97 12.1 82.6 0.8X
+CENTURY of date 797 840
54 12.6 79.7 0.8X
+DECADE of date 734 737
4 13.6 73.4 0.9X
+YEAR of date 721 768
78 13.9 72.1 0.9X
+ISOYEAR of date 884 892
13 11.3 88.4 0.7X
+QUARTER of date 894 949
69 11.2 89.4 0.7X
+MONTH of date 726 729
2 13.8 72.6 0.9X
+WEEK of date 1013 1023
9 9.9 101.3 0.6X
+DAY of date 727 747
26 13.8 72.7 0.9X
+DAYOFWEEK of date 828 832
6 12.1 82.8 0.8X
+DOW of date 846 850
5 11.8 84.6 0.7X
+ISODOW of date 803 810
7 12.5 80.3 0.8X
+DOY of date 751 761
11 13.3 75.1 0.8X
+HOUR of date 1372 1376
5 7.3 137.2 0.5X
+MINUTE of date 1379 1398
20 7.3 137.9 0.5X
+SECOND of date 1530 1542
10 6.5 153.0 0.4X
+MILLISECONDS of date 1532 1541
8 6.5 153.2 0.4X
+MICROSECONDS of date 1442 1462
23 6.9 144.2 0.4X
+EPOCH of date 1573 1594
20 6.4 157.3 0.4X
Java HotSpot(TM) 64-Bit Server VM 1.8.0_231-b11 on Mac OS X 10.15.3
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke date_part for date: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to date 676 690
20 14.8 67.6 1.0X
-MILLENNIUM of date 868 882
16 11.5 86.8 0.8X
-CENTURY of date 875 880
8 11.4 87.5 0.8X
-DECADE of date 762 776
16 13.1 76.2 0.9X
-YEAR of date 788 800
11 12.7 78.8 0.9X
-ISOYEAR of date 903 917
12 11.1 90.3 0.7X
-QUARTER of date 983 1018
40 10.2 98.3 0.7X
-MONTH of date 836 857
19 12.0 83.6 0.8X
-WEEK of date 1137 1168
28 8.8 113.7 0.6X
-DAY of date 768 817
82 13.0 76.8 0.9X
-DAYOFWEEK of date 890 926
36 11.2 89.0 0.8X
-DOW of date 1007 1033
39 9.9 100.7 0.7X
-ISODOW of date 962 969
7 10.4 96.2 0.7X
-DOY of date 797 882
80 12.6 79.7 0.8X
-HOUR of date 1449 1482
29 6.9 144.9 0.5X
-MINUTE of date 1536 1610
69 6.5 153.6 0.4X
-SECOND of date 1675 1823
128 6.0 167.5 0.4X
-MILLISECONDS of date 1605 1622
18 6.2 160.5 0.4X
-MICROSECONDS of date 1481 1504
32 6.8 148.1 0.5X
-EPOCH of date 1656 1843
296 6.0 165.6 0.4X
+cast to date 622 633
14 16.1 62.2 1.0X
+MILLENNIUM of date 793 814
20 12.6 79.3 0.8X
+CENTURY of date 798 809
12 12.5 79.8 0.8X
+DECADE of date 735 740
6 13.6 73.5 0.8X
+YEAR of date 714 732
23 14.0 71.4 0.9X
+ISOYEAR of date 881 886
4 11.3 88.1 0.7X
+QUARTER of date 916 925
14 10.9 91.6 0.7X
+MONTH of date 732 737
5 13.7 73.2 0.8X
+WEEK of date 1022 1034
10 9.8 102.2 0.6X
+DAY of date 722 745
26 13.8 72.2 0.9X
+DAYOFWEEK of date 831 849
17 12.0 83.1 0.7X
+DOW of date 853 858
4 11.7 85.3 0.7X
+ISODOW of date 824 825
1 12.1 82.4 0.8X
+DOY of date 749 752
4 13.3 74.9 0.8X
+HOUR of date 1422 1425
3 7.0 142.2 0.4X
+MINUTE of date 1379 1394
13 7.3 137.9 0.5X
+SECOND of date 1525 1536
9 6.6 152.5 0.4X
+MILLISECONDS of date 1542 1555
14 6.5 154.2 0.4X
+MICROSECONDS of date 1446 1449
2 6.9 144.6 0.4X
+EPOCH of date 1589 1621
35 6.3 158.9 0.4X
+
+Java HotSpot(TM) 64-Bit Server VM 1.8.0_231-b11 on Mac OS X 10.15.3
+Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
+Invoke extract for interval: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
+------------------------------------------------------------------------------------------------------------------------
+cast to interval 913 921
8 11.0 91.3 1.0X
+MILLENNIUM of interval 976 983
12 10.2 97.6 0.9X
+CENTURY of interval 976 979
3 10.2 97.6 0.9X
+DECADE of interval 975 987
10 10.3 97.5 0.9X
+YEAR of interval 964 968
5 10.4 96.4 0.9X
+QUARTER of interval 987 997
12 10.1 98.7 0.9X
+MONTH of interval 974 983
11 10.3 97.4 0.9X
+DAY of interval 959 962
3 10.4 95.9 1.0X
+HOUR of interval 972 986
13 10.3 97.2 0.9X
+MINUTE of interval 985 988
4 10.2 98.5 0.9X
+SECOND of interval 1128 1141
13 8.9 112.8 0.8X
+MILLISECONDS of interval 1083 1086
3 9.2 108.3 0.8X
+MICROSECONDS of interval 963 969
5 10.4 96.3 0.9X
+EPOCH of interval 1094 1116
23 9.1 109.4 0.8X
Java HotSpot(TM) 64-Bit Server VM 1.8.0_231-b11 on Mac OS X 10.15.3
Intel(R) Core(TM) i9-9980HK CPU @ 2.40GHz
Invoke date_part for interval: Best Time(ms) Avg Time(ms)
Stdev(ms) Rate(M/s) Per Row(ns) Relative
------------------------------------------------------------------------------------------------------------------------
-cast to interval 919 959
45 10.9 91.9 1.0X
-MILLENNIUM of interval 959 1001
36 10.4 95.9 1.0X
-CENTURY of interval 974 998
21 10.3 97.4 0.9X
-DECADE of interval 975 989
13 10.3 97.5 0.9X
-YEAR of interval 987 1012
24 10.1 98.7 0.9X
-QUARTER of interval 1014 1031
25 9.9 101.4 0.9X
-MONTH of interval 978 1018
45 10.2 97.8 0.9X
-DAY of interval 1012 1095
92 9.9 101.2 0.9X
-HOUR of interval 1129 1143
14 8.9 112.9 0.8X
-MINUTE of interval 1038 1060
25 9.6 103.8 0.9X
-SECOND of interval 1062 1289
356 9.4 106.2 0.9X
-MILLISECONDS of interval 1142 1226
83 8.8 114.2 0.8X
-MICROSECONDS of interval 1038 1129
79 9.6 103.8 0.9X
-EPOCH of interval 1090 1127
34 9.2 109.0 0.8X
+cast to interval 950 971
23 10.5 95.0 1.0X
+MILLENNIUM of interval 989 1000
11 10.1 98.9 1.0X
+CENTURY of interval 984 990
6 10.2 98.4 1.0X
+DECADE of interval 962 978
14 10.4 96.2 1.0X
+YEAR of interval 945 963
16 10.6 94.5 1.0X
+QUARTER of interval 985 998
17 10.2 98.5 1.0X
+MONTH of interval 970 976
9 10.3 97.0 1.0X
+DAY of interval 953 962
8 10.5 95.3 1.0X
+HOUR of interval 942 956
17 10.6 94.2 1.0X
+MINUTE of interval 975 993
16 10.3 97.5 1.0X
+SECOND of interval 1110 1122
12 9.0 111.0 0.9X
+MILLISECONDS of interval 1056 1074
22 9.5 105.6 0.9X
+MICROSECONDS of interval 939 960
36 10.7 93.9 1.0X
+EPOCH of interval 1071 1093
21 9.3 107.1 0.9X
diff --git a/sql/core/src/test/resources/sql-tests/inputs/extract.sql
b/sql/core/src/test/resources/sql-tests/inputs/extract.sql
index 2d6cad8..4bcc15c 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/extract.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/extract.sql
@@ -1,43 +1,43 @@
-CREATE TEMPORARY VIEW t AS select '2011-05-06 07:08:09.1234567' as c;
+CREATE TEMPORARY VIEW t AS select '2011-05-06 07:08:09.1234567' as c, interval
10 year 20 month 30 day 40 hour 50 minute 6.7890 second as i;
-select extract(millennium from c) from t;
-select extract(millennia from c) from t;
-select extract(mil from c) from t;
-select extract(mils from c) from t;
+select extract(millennium from c), extract(millennium from i) from t;
+select extract(millennia from c), extract(millennia from i) from t;
+select extract(mil from c), extract(mil from i) from t;
+select extract(mils from c), extract(mils from i) from t;
-select extract(century from c) from t;
-select extract(centuries from c) from t;
-select extract(c from c) from t;
-select extract(cent from c) from t;
+select extract(century from c), extract(century from i) from t;
+select extract(centuries from c), extract(centuries from i) from t;
+select extract(c from c), extract(c from i) from t;
+select extract(cent from c), extract(cent from i) from t;
-select extract(decade from c) from t;
-select extract(decades from c) from t;
-select extract(dec from c) from t;
-select extract(decs from c) from t;
+select extract(decade from c), extract(decade from i) from t;
+select extract(decades from c), extract(decades from i) from t;
+select extract(dec from c), extract(dec from i) from t;
+select extract(decs from c), extract(decs from i) from t;
-select extract(year from c) from t;
-select extract(y from c) from t;
-select extract(years from c) from t;
-select extract(yr from c) from t;
-select extract(yrs from c) from t;
+select extract(year from c), extract(year from i) from t;
+select extract(y from c), extract(y from i) from t;
+select extract(years from c), extract(years from i) from t;
+select extract(yr from c), extract(yr from i) from t;
+select extract(yrs from c), extract(yrs from i) from t;
select extract(isoyear from c) from t;
-select extract(quarter from c) from t;
-select extract(qtr from c) from t;
+select extract(quarter from c), extract(quarter from i) from t;
+select extract(qtr from c), extract(qtr from i) from t;
-select extract(month from c) from t;
-select extract(mon from c) from t;
-select extract(mons from c) from t;
-select extract(months from c) from t;
+select extract(month from c), extract(month from i) from t;
+select extract(mon from c), extract(mon from i) from t;
+select extract(mons from c), extract(mons from i) from t;
+select extract(months from c), extract(months from i) from t;
select extract(week from c) from t;
select extract(w from c) from t;
select extract(weeks from c) from t;
-select extract(day from c) from t;
-select extract(d from c) from t;
-select extract(days from c) from t;
+select extract(day from c), extract(day from i) from t;
+select extract(d from c), extract(d from i) from t;
+select extract(days from c), extract(days from i) from t;
select extract(dayofweek from c) from t;
@@ -47,38 +47,39 @@ select extract(isodow from c) from t;
select extract(doy from c) from t;
-select extract(hour from c) from t;
-select extract(h from c) from t;
-select extract(hours from c) from t;
-select extract(hr from c) from t;
-select extract(hrs from c) from t;
-
-select extract(minute from c) from t;
-select extract(m from c) from t;
-select extract(min from c) from t;
-select extract(mins from c) from t;
-select extract(minutes from c) from t;
-
-select extract(second from c) from t;
-select extract(s from c) from t;
-select extract(sec from c) from t;
-select extract(seconds from c) from t;
-select extract(secs from c) from t;
-
-select extract(milliseconds from c) from t;
-select extract(msec from c) from t;
-select extract(msecs from c) from t;
-select extract(millisecon from c) from t;
-select extract(mseconds from c) from t;
-select extract(ms from c) from t;
-
-select extract(microseconds from c) from t;
-select extract(usec from c) from t;
-select extract(usecs from c) from t;
-select extract(useconds from c) from t;
-select extract(microsecon from c) from t;
-select extract(us from c) from t;
-
-select extract(epoch from c) from t;
+select extract(hour from c), extract(hour from i) from t;
+select extract(h from c), extract(h from i) from t;
+select extract(hours from c), extract(hours from i) from t;
+select extract(hr from c), extract(hr from i) from t;
+select extract(hrs from c), extract(hrs from i) from t;
+
+select extract(minute from c), extract(minute from i) from t;
+select extract(m from c), extract(m from i) from t;
+select extract(min from c), extract(min from i) from t;
+select extract(mins from c), extract(mins from i) from t;
+select extract(minutes from c), extract(minutes from i) from t;
+
+select extract(second from c), extract(second from i) from t;
+select extract(s from c), extract(s from i) from t;
+select extract(sec from c), extract(sec from i) from t;
+select extract(seconds from c), extract(seconds from i) from t;
+select extract(secs from c), extract(secs from i) from t;
+
+select extract(milliseconds from c), extract(milliseconds from i) from t;
+select extract(msec from c), extract(msec from i) from t;
+select extract(msecs from c), extract(msecs from i) from t;
+select extract(millisecon from c), extract(millisecon from i) from t;
+select extract(mseconds from c), extract(mseconds from i) from t;
+select extract(ms from c), extract(ms from i) from t;
+
+select extract(microseconds from c), extract(microseconds from i) from t;
+select extract(usec from c), extract(usec from i) from t;
+select extract(usecs from c), extract(usecs from i) from t;
+select extract(useconds from c), extract(useconds from i) from t;
+select extract(microsecon from c), extract(microsecon from i) from t;
+select extract(us from c), extract(us from i) from t;
+
+select extract(epoch from c), extract(epoch from i) from t;
select extract(not_supported from c) from t;
+select extract(not_supported from i) from t;
diff --git a/sql/core/src/test/resources/sql-tests/results/extract.sql.out
b/sql/core/src/test/resources/sql-tests/results/extract.sql.out
index 583459f9..38c8490 100644
--- a/sql/core/src/test/resources/sql-tests/results/extract.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/extract.sql.out
@@ -1,9 +1,9 @@
-- Automatically generated by SQLQueryTestSuite
--- Number of queries: 64
+-- Number of queries: 65
-- !query
-CREATE TEMPORARY VIEW t AS select '2011-05-06 07:08:09.1234567' as c
+CREATE TEMPORARY VIEW t AS select '2011-05-06 07:08:09.1234567' as c, interval
10 year 20 month 30 day 40 hour 50 minute 6.7890 second as i
-- !query schema
struct<>
-- !query output
@@ -11,201 +11,201 @@ struct<>
-- !query
-select extract(millennium from c) from t
+select extract(millennium from c), extract(millennium from i) from t
-- !query schema
-struct<date_part('millennium', `c`):int>
+struct<date_part('millennium', t.`c`):int,date_part('millennium', t.`i`):int>
-- !query output
-3
+3 0
-- !query
-select extract(millennia from c) from t
+select extract(millennia from c), extract(millennia from i) from t
-- !query schema
-struct<date_part('millennia', `c`):int>
+struct<date_part('millennia', t.`c`):int,date_part('millennia', t.`i`):int>
-- !query output
-3
+3 0
-- !query
-select extract(mil from c) from t
+select extract(mil from c), extract(mil from i) from t
-- !query schema
-struct<date_part('mil', `c`):int>
+struct<date_part('mil', t.`c`):int,date_part('mil', t.`i`):int>
-- !query output
-3
+3 0
-- !query
-select extract(mils from c) from t
+select extract(mils from c), extract(mils from i) from t
-- !query schema
-struct<date_part('mils', `c`):int>
+struct<date_part('mils', t.`c`):int,date_part('mils', t.`i`):int>
-- !query output
-3
+3 0
-- !query
-select extract(century from c) from t
+select extract(century from c), extract(century from i) from t
-- !query schema
-struct<date_part('century', `c`):int>
+struct<date_part('century', t.`c`):int,date_part('century', t.`i`):int>
-- !query output
-21
+21 0
-- !query
-select extract(centuries from c) from t
+select extract(centuries from c), extract(centuries from i) from t
-- !query schema
-struct<date_part('centuries', `c`):int>
+struct<date_part('centuries', t.`c`):int,date_part('centuries', t.`i`):int>
-- !query output
-21
+21 0
-- !query
-select extract(c from c) from t
+select extract(c from c), extract(c from i) from t
-- !query schema
-struct<date_part('c', `c`):int>
+struct<date_part('c', t.`c`):int,date_part('c', t.`i`):int>
-- !query output
-21
+21 0
-- !query
-select extract(cent from c) from t
+select extract(cent from c), extract(cent from i) from t
-- !query schema
-struct<date_part('cent', `c`):int>
+struct<date_part('cent', t.`c`):int,date_part('cent', t.`i`):int>
-- !query output
-21
+21 0
-- !query
-select extract(decade from c) from t
+select extract(decade from c), extract(decade from i) from t
-- !query schema
-struct<date_part('decade', `c`):int>
+struct<date_part('decade', t.`c`):int,date_part('decade', t.`i`):int>
-- !query output
-201
+201 1
-- !query
-select extract(decades from c) from t
+select extract(decades from c), extract(decades from i) from t
-- !query schema
-struct<date_part('decades', `c`):int>
+struct<date_part('decades', t.`c`):int,date_part('decades', t.`i`):int>
-- !query output
-201
+201 1
-- !query
-select extract(dec from c) from t
+select extract(dec from c), extract(dec from i) from t
-- !query schema
-struct<date_part('dec', `c`):int>
+struct<date_part('dec', t.`c`):int,date_part('dec', t.`i`):int>
-- !query output
-201
+201 1
-- !query
-select extract(decs from c) from t
+select extract(decs from c), extract(decs from i) from t
-- !query schema
-struct<date_part('decs', `c`):int>
+struct<date_part('decs', t.`c`):int,date_part('decs', t.`i`):int>
-- !query output
-201
+201 1
-- !query
-select extract(year from c) from t
+select extract(year from c), extract(year from i) from t
-- !query schema
-struct<date_part('year', `c`):int>
+struct<date_part('year', t.`c`):int,date_part('year', t.`i`):int>
-- !query output
-2011
+2011 11
-- !query
-select extract(y from c) from t
+select extract(y from c), extract(y from i) from t
-- !query schema
-struct<date_part('y', `c`):int>
+struct<date_part('y', t.`c`):int,date_part('y', t.`i`):int>
-- !query output
-2011
+2011 11
-- !query
-select extract(years from c) from t
+select extract(years from c), extract(years from i) from t
-- !query schema
-struct<date_part('years', `c`):int>
+struct<date_part('years', t.`c`):int,date_part('years', t.`i`):int>
-- !query output
-2011
+2011 11
-- !query
-select extract(yr from c) from t
+select extract(yr from c), extract(yr from i) from t
-- !query schema
-struct<date_part('yr', `c`):int>
+struct<date_part('yr', t.`c`):int,date_part('yr', t.`i`):int>
-- !query output
-2011
+2011 11
-- !query
-select extract(yrs from c) from t
+select extract(yrs from c), extract(yrs from i) from t
-- !query schema
-struct<date_part('yrs', `c`):int>
+struct<date_part('yrs', t.`c`):int,date_part('yrs', t.`i`):int>
-- !query output
-2011
+2011 11
-- !query
select extract(isoyear from c) from t
-- !query schema
-struct<date_part('isoyear', `c`):int>
+struct<date_part('isoyear', t.`c`):int>
-- !query output
2011
-- !query
-select extract(quarter from c) from t
+select extract(quarter from c), extract(quarter from i) from t
-- !query schema
-struct<date_part('quarter', `c`):int>
+struct<date_part('quarter', t.`c`):int,date_part('quarter', t.`i`):tinyint>
-- !query output
-2
+2 3
-- !query
-select extract(qtr from c) from t
+select extract(qtr from c), extract(qtr from i) from t
-- !query schema
-struct<date_part('qtr', `c`):int>
+struct<date_part('qtr', t.`c`):int,date_part('qtr', t.`i`):tinyint>
-- !query output
-2
+2 3
-- !query
-select extract(month from c) from t
+select extract(month from c), extract(month from i) from t
-- !query schema
-struct<date_part('month', `c`):int>
+struct<date_part('month', t.`c`):int,date_part('month', t.`i`):tinyint>
-- !query output
-5
+5 8
-- !query
-select extract(mon from c) from t
+select extract(mon from c), extract(mon from i) from t
-- !query schema
-struct<date_part('mon', `c`):int>
+struct<date_part('mon', t.`c`):int,date_part('mon', t.`i`):tinyint>
-- !query output
-5
+5 8
-- !query
-select extract(mons from c) from t
+select extract(mons from c), extract(mons from i) from t
-- !query schema
-struct<date_part('mons', `c`):int>
+struct<date_part('mons', t.`c`):int,date_part('mons', t.`i`):tinyint>
-- !query output
-5
+5 8
-- !query
-select extract(months from c) from t
+select extract(months from c), extract(months from i) from t
-- !query schema
-struct<date_part('months', `c`):int>
+struct<date_part('months', t.`c`):int,date_part('months', t.`i`):tinyint>
-- !query output
-5
+5 8
-- !query
select extract(week from c) from t
-- !query schema
-struct<date_part('week', `c`):int>
+struct<date_part('week', t.`c`):int>
-- !query output
18
@@ -213,7 +213,7 @@ struct<date_part('week', `c`):int>
-- !query
select extract(w from c) from t
-- !query schema
-struct<date_part('w', `c`):int>
+struct<date_part('w', t.`c`):int>
-- !query output
18
@@ -221,39 +221,39 @@ struct<date_part('w', `c`):int>
-- !query
select extract(weeks from c) from t
-- !query schema
-struct<date_part('weeks', `c`):int>
+struct<date_part('weeks', t.`c`):int>
-- !query output
18
-- !query
-select extract(day from c) from t
+select extract(day from c), extract(day from i) from t
-- !query schema
-struct<date_part('day', `c`):int>
+struct<date_part('day', t.`c`):int,date_part('day', t.`i`):int>
-- !query output
-6
+6 30
-- !query
-select extract(d from c) from t
+select extract(d from c), extract(d from i) from t
-- !query schema
-struct<date_part('d', `c`):int>
+struct<date_part('d', t.`c`):int,date_part('d', t.`i`):int>
-- !query output
-6
+6 30
-- !query
-select extract(days from c) from t
+select extract(days from c), extract(days from i) from t
-- !query schema
-struct<date_part('days', `c`):int>
+struct<date_part('days', t.`c`):int,date_part('days', t.`i`):int>
-- !query output
-6
+6 30
-- !query
select extract(dayofweek from c) from t
-- !query schema
-struct<date_part('dayofweek', `c`):int>
+struct<date_part('dayofweek', t.`c`):int>
-- !query output
6
@@ -261,7 +261,7 @@ struct<date_part('dayofweek', `c`):int>
-- !query
select extract(dow from c) from t
-- !query schema
-struct<date_part('dow', `c`):int>
+struct<date_part('dow', t.`c`):int>
-- !query output
5
@@ -269,7 +269,7 @@ struct<date_part('dow', `c`):int>
-- !query
select extract(isodow from c) from t
-- !query schema
-struct<date_part('isodow', `c`):int>
+struct<date_part('isodow', t.`c`):int>
-- !query output
5
@@ -277,233 +277,233 @@ struct<date_part('isodow', `c`):int>
-- !query
select extract(doy from c) from t
-- !query schema
-struct<date_part('doy', `c`):int>
+struct<date_part('doy', t.`c`):int>
-- !query output
126
-- !query
-select extract(hour from c) from t
+select extract(hour from c), extract(hour from i) from t
-- !query schema
-struct<date_part('hour', `c`):int>
+struct<date_part('hour', t.`c`):int,date_part('hour', t.`i`):bigint>
-- !query output
-7
+7 40
-- !query
-select extract(h from c) from t
+select extract(h from c), extract(h from i) from t
-- !query schema
-struct<date_part('h', `c`):int>
+struct<date_part('h', t.`c`):int,date_part('h', t.`i`):bigint>
-- !query output
-7
+7 40
-- !query
-select extract(hours from c) from t
+select extract(hours from c), extract(hours from i) from t
-- !query schema
-struct<date_part('hours', `c`):int>
+struct<date_part('hours', t.`c`):int,date_part('hours', t.`i`):bigint>
-- !query output
-7
+7 40
-- !query
-select extract(hr from c) from t
+select extract(hr from c), extract(hr from i) from t
-- !query schema
-struct<date_part('hr', `c`):int>
+struct<date_part('hr', t.`c`):int,date_part('hr', t.`i`):bigint>
-- !query output
-7
+7 40
-- !query
-select extract(hrs from c) from t
+select extract(hrs from c), extract(hrs from i) from t
-- !query schema
-struct<date_part('hrs', `c`):int>
+struct<date_part('hrs', t.`c`):int,date_part('hrs', t.`i`):bigint>
-- !query output
-7
+7 40
-- !query
-select extract(minute from c) from t
+select extract(minute from c), extract(minute from i) from t
-- !query schema
-struct<date_part('minute', `c`):int>
+struct<date_part('minute', t.`c`):int,date_part('minute', t.`i`):tinyint>
-- !query output
-8
+8 50
-- !query
-select extract(m from c) from t
+select extract(m from c), extract(m from i) from t
-- !query schema
-struct<date_part('m', `c`):int>
+struct<date_part('m', t.`c`):int,date_part('m', t.`i`):tinyint>
-- !query output
-8
+8 50
-- !query
-select extract(min from c) from t
+select extract(min from c), extract(min from i) from t
-- !query schema
-struct<date_part('min', `c`):int>
+struct<date_part('min', t.`c`):int,date_part('min', t.`i`):tinyint>
-- !query output
-8
+8 50
-- !query
-select extract(mins from c) from t
+select extract(mins from c), extract(mins from i) from t
-- !query schema
-struct<date_part('mins', `c`):int>
+struct<date_part('mins', t.`c`):int,date_part('mins', t.`i`):tinyint>
-- !query output
-8
+8 50
-- !query
-select extract(minutes from c) from t
+select extract(minutes from c), extract(minutes from i) from t
-- !query schema
-struct<date_part('minutes', `c`):int>
+struct<date_part('minutes', t.`c`):int,date_part('minutes', t.`i`):tinyint>
-- !query output
-8
+8 50
-- !query
-select extract(second from c) from t
+select extract(second from c), extract(second from i) from t
-- !query schema
-struct<date_part('second', `c`):decimal(8,6)>
+struct<date_part('second', t.`c`):decimal(8,6),date_part('second',
t.`i`):decimal(8,6)>
-- !query output
-9.123456
+9.123456 6.789000
-- !query
-select extract(s from c) from t
+select extract(s from c), extract(s from i) from t
-- !query schema
-struct<date_part('s', `c`):decimal(8,6)>
+struct<date_part('s', t.`c`):decimal(8,6),date_part('s', t.`i`):decimal(8,6)>
-- !query output
-9.123456
+9.123456 6.789000
-- !query
-select extract(sec from c) from t
+select extract(sec from c), extract(sec from i) from t
-- !query schema
-struct<date_part('sec', `c`):decimal(8,6)>
+struct<date_part('sec', t.`c`):decimal(8,6),date_part('sec',
t.`i`):decimal(8,6)>
-- !query output
-9.123456
+9.123456 6.789000
-- !query
-select extract(seconds from c) from t
+select extract(seconds from c), extract(seconds from i) from t
-- !query schema
-struct<date_part('seconds', `c`):decimal(8,6)>
+struct<date_part('seconds', t.`c`):decimal(8,6),date_part('seconds',
t.`i`):decimal(8,6)>
-- !query output
-9.123456
+9.123456 6.789000
-- !query
-select extract(secs from c) from t
+select extract(secs from c), extract(secs from i) from t
-- !query schema
-struct<date_part('secs', `c`):decimal(8,6)>
+struct<date_part('secs', t.`c`):decimal(8,6),date_part('secs',
t.`i`):decimal(8,6)>
-- !query output
-9.123456
+9.123456 6.789000
-- !query
-select extract(milliseconds from c) from t
+select extract(milliseconds from c), extract(milliseconds from i) from t
-- !query schema
-struct<date_part('milliseconds', `c`):decimal(8,3)>
+struct<date_part('milliseconds', t.`c`):decimal(8,3),date_part('milliseconds',
t.`i`):decimal(8,3)>
-- !query output
-9123.456
+9123.456 6789.000
-- !query
-select extract(msec from c) from t
+select extract(msec from c), extract(msec from i) from t
-- !query schema
-struct<date_part('msec', `c`):decimal(8,3)>
+struct<date_part('msec', t.`c`):decimal(8,3),date_part('msec',
t.`i`):decimal(8,3)>
-- !query output
-9123.456
+9123.456 6789.000
-- !query
-select extract(msecs from c) from t
+select extract(msecs from c), extract(msecs from i) from t
-- !query schema
-struct<date_part('msecs', `c`):decimal(8,3)>
+struct<date_part('msecs', t.`c`):decimal(8,3),date_part('msecs',
t.`i`):decimal(8,3)>
-- !query output
-9123.456
+9123.456 6789.000
-- !query
-select extract(millisecon from c) from t
+select extract(millisecon from c), extract(millisecon from i) from t
-- !query schema
-struct<date_part('millisecon', `c`):decimal(8,3)>
+struct<date_part('millisecon', t.`c`):decimal(8,3),date_part('millisecon',
t.`i`):decimal(8,3)>
-- !query output
-9123.456
+9123.456 6789.000
-- !query
-select extract(mseconds from c) from t
+select extract(mseconds from c), extract(mseconds from i) from t
-- !query schema
-struct<date_part('mseconds', `c`):decimal(8,3)>
+struct<date_part('mseconds', t.`c`):decimal(8,3),date_part('mseconds',
t.`i`):decimal(8,3)>
-- !query output
-9123.456
+9123.456 6789.000
-- !query
-select extract(ms from c) from t
+select extract(ms from c), extract(ms from i) from t
-- !query schema
-struct<date_part('ms', `c`):decimal(8,3)>
+struct<date_part('ms', t.`c`):decimal(8,3),date_part('ms', t.`i`):decimal(8,3)>
-- !query output
-9123.456
+9123.456 6789.000
-- !query
-select extract(microseconds from c) from t
+select extract(microseconds from c), extract(microseconds from i) from t
-- !query schema
-struct<date_part('microseconds', `c`):int>
+struct<date_part('microseconds', t.`c`):int,date_part('microseconds',
t.`i`):bigint>
-- !query output
-9123456
+9123456 6789000
-- !query
-select extract(usec from c) from t
+select extract(usec from c), extract(usec from i) from t
-- !query schema
-struct<date_part('usec', `c`):int>
+struct<date_part('usec', t.`c`):int,date_part('usec', t.`i`):bigint>
-- !query output
-9123456
+9123456 6789000
-- !query
-select extract(usecs from c) from t
+select extract(usecs from c), extract(usecs from i) from t
-- !query schema
-struct<date_part('usecs', `c`):int>
+struct<date_part('usecs', t.`c`):int,date_part('usecs', t.`i`):bigint>
-- !query output
-9123456
+9123456 6789000
-- !query
-select extract(useconds from c) from t
+select extract(useconds from c), extract(useconds from i) from t
-- !query schema
-struct<date_part('useconds', `c`):int>
+struct<date_part('useconds', t.`c`):int,date_part('useconds', t.`i`):bigint>
-- !query output
-9123456
+9123456 6789000
-- !query
-select extract(microsecon from c) from t
+select extract(microsecon from c), extract(microsecon from i) from t
-- !query schema
-struct<date_part('microsecon', `c`):int>
+struct<date_part('microsecon', t.`c`):int,date_part('microsecon',
t.`i`):bigint>
-- !query output
-9123456
+9123456 6789000
-- !query
-select extract(us from c) from t
+select extract(us from c), extract(us from i) from t
-- !query schema
-struct<date_part('us', `c`):int>
+struct<date_part('us', t.`c`):int,date_part('us', t.`i`):bigint>
-- !query output
-9123456
+9123456 6789000
-- !query
-select extract(epoch from c) from t
+select extract(epoch from c), extract(epoch from i) from t
-- !query schema
-struct<date_part('epoch', `c`):decimal(20,6)>
+struct<date_part('epoch', t.`c`):decimal(20,6),date_part('epoch',
t.`i`):decimal(18,6)>
-- !query output
-1304665689.123456
+1304665689.123456 370608606.789000
-- !query
@@ -511,10 +511,14 @@ select extract(not_supported from c) from t
-- !query schema
struct<>
-- !query output
-org.apache.spark.sql.catalyst.parser.ParseException
+org.apache.spark.sql.AnalysisException
+Literals of type 'not_supported' are currently not supported for the string
type.;; line 1 pos 7
-Literals of type 'not_supported' are currently not supported.(line 1, pos 7)
-== SQL ==
-select extract(not_supported from c) from t
--------^^^
+-- !query
+select extract(not_supported from i) from t
+-- !query schema
+struct<>
+-- !query output
+org.apache.spark.sql.AnalysisException
+Literals of type 'not_supported' are currently not supported for the interval
type.;; line 1 pos 7
diff --git
a/sql/core/src/test/resources/sql-tests/results/postgreSQL/date.sql.out
b/sql/core/src/test/resources/sql-tests/results/postgreSQL/date.sql.out
index ed27317..e5a7c43 100755
--- a/sql/core/src/test/resources/sql-tests/results/postgreSQL/date.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/postgreSQL/date.sql.out
@@ -590,7 +590,7 @@ struct<date_part('EPOCH', TIMESTAMP '1970-01-01
00:00:00'):decimal(20,6)>
-- !query
SELECT EXTRACT(CENTURY FROM TO_DATE('0101-12-31 BC', 'yyyy-MM-dd G'))
-- !query schema
-struct<date_part('CENTURY', TO_DATE('0101-12-31 BC', 'yyyy-MM-dd G')):int>
+struct<date_part('CENTURY', to_date('0101-12-31 BC', 'yyyy-MM-dd G')):int>
-- !query output
-2
@@ -598,7 +598,7 @@ struct<date_part('CENTURY', TO_DATE('0101-12-31 BC',
'yyyy-MM-dd G')):int>
-- !query
SELECT EXTRACT(CENTURY FROM TO_DATE('0100-12-31 BC', 'yyyy-MM-dd G'))
-- !query schema
-struct<date_part('CENTURY', TO_DATE('0100-12-31 BC', 'yyyy-MM-dd G')):int>
+struct<date_part('CENTURY', to_date('0100-12-31 BC', 'yyyy-MM-dd G')):int>
-- !query output
-1
@@ -606,7 +606,7 @@ struct<date_part('CENTURY', TO_DATE('0100-12-31 BC',
'yyyy-MM-dd G')):int>
-- !query
SELECT EXTRACT(CENTURY FROM TO_DATE('0001-12-31 BC', 'yyyy-MM-dd G'))
-- !query schema
-struct<date_part('CENTURY', TO_DATE('0001-12-31 BC', 'yyyy-MM-dd G')):int>
+struct<date_part('CENTURY', to_date('0001-12-31 BC', 'yyyy-MM-dd G')):int>
-- !query output
-1
@@ -670,7 +670,7 @@ true
-- !query
SELECT EXTRACT(MILLENNIUM FROM TO_DATE('0001-12-31 BC', 'yyyy-MM-dd G'))
-- !query schema
-struct<date_part('MILLENNIUM', TO_DATE('0001-12-31 BC', 'yyyy-MM-dd G')):int>
+struct<date_part('MILLENNIUM', to_date('0001-12-31 BC', 'yyyy-MM-dd G')):int>
-- !query output
-1
@@ -750,7 +750,7 @@ struct<date_part('DECADE', DATE '0009-12-31'):int>
-- !query
SELECT EXTRACT(DECADE FROM TO_DATE('0001-01-01 BC', 'yyyy-MM-dd G'))
-- !query schema
-struct<date_part('DECADE', TO_DATE('0001-01-01 BC', 'yyyy-MM-dd G')):int>
+struct<date_part('DECADE', to_date('0001-01-01 BC', 'yyyy-MM-dd G')):int>
-- !query output
0
@@ -758,7 +758,7 @@ struct<date_part('DECADE', TO_DATE('0001-01-01 BC',
'yyyy-MM-dd G')):int>
-- !query
SELECT EXTRACT(DECADE FROM TO_DATE('0002-12-31 BC', 'yyyy-MM-dd G'))
-- !query schema
-struct<date_part('DECADE', TO_DATE('0002-12-31 BC', 'yyyy-MM-dd G')):int>
+struct<date_part('DECADE', to_date('0002-12-31 BC', 'yyyy-MM-dd G')):int>
-- !query output
-1
@@ -766,7 +766,7 @@ struct<date_part('DECADE', TO_DATE('0002-12-31 BC',
'yyyy-MM-dd G')):int>
-- !query
SELECT EXTRACT(DECADE FROM TO_DATE('0011-01-01 BC', 'yyyy-MM-dd G'))
-- !query schema
-struct<date_part('DECADE', TO_DATE('0011-01-01 BC', 'yyyy-MM-dd G')):int>
+struct<date_part('DECADE', to_date('0011-01-01 BC', 'yyyy-MM-dd G')):int>
-- !query output
-1
@@ -774,7 +774,7 @@ struct<date_part('DECADE', TO_DATE('0011-01-01 BC',
'yyyy-MM-dd G')):int>
-- !query
SELECT EXTRACT(DECADE FROM TO_DATE('0012-12-31 BC', 'yyyy-MM-dd G'))
-- !query schema
-struct<date_part('DECADE', TO_DATE('0012-12-31 BC', 'yyyy-MM-dd G')):int>
+struct<date_part('DECADE', to_date('0012-12-31 BC', 'yyyy-MM-dd G')):int>
-- !query output
-2
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/execution/benchmark/ExtractBenchmark.scala
b/sql/core/src/test/scala/org/apache/spark/sql/execution/benchmark/ExtractBenchmark.scala
index f1315d4..285211e 100644
---
a/sql/core/src/test/scala/org/apache/spark/sql/execution/benchmark/ExtractBenchmark.scala
+++
b/sql/core/src/test/scala/org/apache/spark/sql/execution/benchmark/ExtractBenchmark.scala
@@ -84,8 +84,6 @@ object ExtractBenchmark extends SqlBasedBenchmark {
}
}
- private case class Settings(fields: Seq[String], func: Seq[String], iterNum:
Long)
-
override def runBenchmarkSuite(mainArgs: Array[String]): Unit = {
val N = 10000000L
val datetimeFields = Seq(
@@ -100,18 +98,16 @@ object ExtractBenchmark extends SqlBasedBenchmark {
"HOUR", "MINUTE", "SECOND",
"MILLISECONDS", "MICROSECONDS", "EPOCH")
val settings = Map(
- "timestamp" -> Settings(datetimeFields, Seq("extract", "date_part"), N),
- "date" -> Settings(datetimeFields, Seq("extract", "date_part"), N),
- "interval" -> Settings(intervalFields, Seq("date_part"), N))
+ "timestamp" -> datetimeFields,
+ "date" -> datetimeFields,
+ "interval" -> intervalFields)
- for {
- (dataType, Settings(fields, funcs, iterNum)) <- settings
- func <- funcs} {
+ for {(dataType, fields) <- settings; func <- Seq("extract", "date_part")} {
val benchmark = new Benchmark(s"Invoke $func for $dataType", N, output =
output)
- run(benchmark, iterNum, s"cast to $dataType", castExpr(dataType))
- fields.foreach(run(benchmark, func, iterNum, _, dataType))
+ run(benchmark, N, s"cast to $dataType", castExpr(dataType))
+ fields.foreach(run(benchmark, func, N, _, dataType))
benchmark.run()
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
