You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2019/10/05 02:47:16 UTC
[calcite] 02/02: [CALCITE-3361] Add 'lenientOperatorLookup'
connection property
This is an automated email from the ASF dual-hosted git repository.
jhyde pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/calcite.git
commit 35c5f57d7db2b4745086178602ca69195ffb858e
Author: Julian Hyde <jh...@apache.org>
AuthorDate: Wed Sep 18 22:45:43 2019 -0700
[CALCITE-3361] Add 'lenientOperatorLookup' connection property
Add a test (redshift.iq) that parses and validates a SQL statement with
every built-in Redshift function, per the Redshift documentation.
The test uses lenientOperatorLookup so that unknown functions do not
cause validation errors (as long as they have ordinary function syntax).
Test cases are commented out if the feature is not supported (e.g. the
APPROXIMATE keyword). If a function is built into Redshift but not
known to Calcite, its test will use '!explain-validated-on' rather than
'!ok'.
In parser, increase the lookahead for JoinType() so that we can parse
LEFT SEMI JOIN. This allows us to enable BabelQuidemTest, which was
previously disabled.
---
.../org/apache/calcite/test/BabelQuidemTest.java | 35 +-
.../java/org/apache/calcite/test/BabelTest.java | 40 +-
babel/src/test/resources/sql/redshift.iq | 2199 ++++++++++++++++++++
babel/src/test/resources/sql/select.iq | 2 +-
core/src/main/codegen/templates/Parser.jj | 9 +-
.../calcite/config/CalciteConnectionConfig.java | 2 +
.../config/CalciteConnectionConfigImpl.java | 5 +
.../calcite/config/CalciteConnectionProperty.java | 6 +-
.../java/org/apache/calcite/sql/SqlFunction.java | 22 +-
.../calcite/sql/validate/SqlValidatorImpl.java | 3 +-
10 files changed, 2279 insertions(+), 44 deletions(-)
diff --git a/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java b/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java
index 4c2e304..4bc7df3 100644
--- a/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java
+++ b/babel/src/test/java/org/apache/calcite/test/BabelQuidemTest.java
@@ -16,8 +16,6 @@
*/
package org.apache.calcite.test;
-import org.apache.calcite.config.CalciteConnectionConfig;
-import org.apache.calcite.config.CalciteConnectionConfigImpl;
import org.apache.calcite.config.CalciteConnectionProperty;
import org.apache.calcite.jdbc.CalciteConnection;
import org.apache.calcite.materialize.MaterializationService;
@@ -41,7 +39,6 @@ import net.hydromatic.quidem.Command;
import net.hydromatic.quidem.CommandHandler;
import net.hydromatic.quidem.Quidem;
-import org.junit.Ignore;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.junit.runners.Parameterized;
@@ -49,7 +46,6 @@ import org.junit.runners.Parameterized;
import java.sql.Connection;
import java.util.Collection;
import java.util.List;
-import java.util.Properties;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
@@ -58,7 +54,6 @@ import java.util.regex.Pattern;
* Unit tests for the Babel SQL parser.
*/
@RunWith(Parameterized.class)
-@Ignore
public class BabelQuidemTest extends QuidemTest {
/** Creates a BabelQuidemTest. Public per {@link Parameterized}. */
@SuppressWarnings("WeakerAccess")
@@ -100,8 +95,27 @@ public class BabelQuidemTest extends QuidemTest {
switch (name) {
case "babel":
return BabelTest.connect();
+ case "scott-babel":
+ return CalciteAssert.that()
+ .with(CalciteAssert.Config.SCOTT)
+ .with(CalciteConnectionProperty.PARSER_FACTORY,
+ SqlBabelParserImpl.class.getName() + "#FACTORY")
+ .with(CalciteConnectionProperty.CONFORMANCE,
+ SqlConformanceEnum.BABEL)
+ .connect();
+ case "scott-redshift":
+ return CalciteAssert.that()
+ .with(CalciteAssert.Config.SCOTT)
+ .with(CalciteConnectionProperty.FUN, "standard,postgresql,oracle")
+ .with(CalciteConnectionProperty.PARSER_FACTORY,
+ SqlBabelParserImpl.class.getName() + "#FACTORY")
+ .with(CalciteConnectionProperty.CONFORMANCE,
+ SqlConformanceEnum.BABEL)
+ .with(CalciteConnectionProperty.LENIENT_OPERATOR_LOOKUP, true)
+ .connect();
+ default:
+ return super.connect(name, reference);
}
- return super.connect(name, reference);
}
};
}
@@ -130,13 +144,6 @@ public class BabelQuidemTest extends QuidemTest {
SqlParser.configBuilder()
.setParserFactory(SqlBabelParserImpl.FACTORY);
- // use Babel conformance for validation
- final Properties properties = new Properties();
- properties.setProperty(CalciteConnectionProperty.CONFORMANCE.name(),
- SqlConformanceEnum.BABEL.name());
- final CalciteConnectionConfig connectionConfig =
- new CalciteConnectionConfigImpl(properties);
-
// extract named schema from connection and use it in planner
final CalciteConnection calciteConnection =
x.connection().unwrap(CalciteConnection.class);
@@ -149,7 +156,7 @@ public class BabelQuidemTest extends QuidemTest {
Frameworks.newConfigBuilder()
.defaultSchema(schema)
.parserConfig(parserConfig.build())
- .context(Contexts.of(connectionConfig));
+ .context(Contexts.of(calciteConnection.config()));
// parse, validate and un-parse
final Quidem.SqlCommand sqlCommand = x.previousSqlCommand();
diff --git a/babel/src/test/java/org/apache/calcite/test/BabelTest.java b/babel/src/test/java/org/apache/calcite/test/BabelTest.java
index e04b1b6..f98ff2d 100644
--- a/babel/src/test/java/org/apache/calcite/test/BabelTest.java
+++ b/babel/src/test/java/org/apache/calcite/test/BabelTest.java
@@ -30,6 +30,7 @@ import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
+import java.util.Properties;
import java.util.function.UnaryOperator;
import static org.hamcrest.core.Is.is;
@@ -42,6 +43,25 @@ public class BabelTest {
static final String URL = "jdbc:calcite:";
+ private static UnaryOperator<CalciteAssert.PropBuilder> useParserFactory() {
+ return propBuilder ->
+ propBuilder.set(CalciteConnectionProperty.PARSER_FACTORY,
+ SqlBabelParserImpl.class.getName() + "#FACTORY");
+ }
+
+ private static UnaryOperator<CalciteAssert.PropBuilder> useLibraryList(
+ String libraryList) {
+ return propBuilder ->
+ propBuilder.set(CalciteConnectionProperty.FUN, libraryList);
+ }
+
+ private static UnaryOperator<CalciteAssert.PropBuilder> useLenientOperatorLookup(
+ boolean lenient) {
+ return propBuilder ->
+ propBuilder.set(CalciteConnectionProperty.LENIENT_OPERATOR_LOOKUP,
+ Boolean.toString(lenient));
+ }
+
@Rule
public ExpectedException thrown = ExpectedException.none();
@@ -51,21 +71,17 @@ public class BabelTest {
static Connection connect(UnaryOperator<CalciteAssert.PropBuilder> propBuild)
throws SQLException {
- final CalciteAssert.PropBuilder propBuilder =
- CalciteAssert.propBuilder()
- .set(CalciteConnectionProperty.PARSER_FACTORY,
- SqlBabelParserImpl.class.getName() + "#FACTORY");
- return DriverManager.getConnection(URL,
- propBuild.apply(propBuilder).build());
- }
-
- private Connection connectWithFun(String libraryList) throws SQLException {
- return connect(propBuilder ->
- propBuilder.set(CalciteConnectionProperty.FUN, libraryList));
+ final CalciteAssert.PropBuilder propBuilder = CalciteAssert.propBuilder();
+ final Properties info =
+ propBuild.andThen(useParserFactory())
+ .andThen(useLenientOperatorLookup(true))
+ .apply(propBuilder)
+ .build();
+ return DriverManager.getConnection(URL, info);
}
@Test public void testInfixCast() throws SQLException {
- try (Connection connection = connectWithFun("standard,postgresql");
+ try (Connection connection = connect(useLibraryList("standard,postgresql"));
Statement statement = connection.createStatement()) {
checkInfixCast(statement, "integer", Types.INTEGER);
checkInfixCast(statement, "varchar", Types.VARCHAR);
diff --git a/babel/src/test/resources/sql/redshift.iq b/babel/src/test/resources/sql/redshift.iq
new file mode 100755
index 0000000..8c337bc
--- /dev/null
+++ b/babel/src/test/resources/sql/redshift.iq
@@ -0,0 +1,2199 @@
+# redshift.iq - Babel test for Redshift dialect of SQL
+#
+# Licensed to the Apache Software Foundation (ASF) under one or more
+# contributor license agreements. See the NOTICE file distributed with
+# this work for additional information regarding copyright ownership.
+# The ASF licenses this file to you under the Apache License, Version 2.0
+# (the "License"); you may not use this file except in compliance with
+# the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing, software
+# distributed under the License is distributed on an "AS IS" BASIS,
+# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+# See the License for the specific language governing permissions and
+# limitations under the License.
+#
+# The following functions have ordinary syntax and are not defined in Calcite.
+# We call them using '!explain-validated-on calcite', which generates a plan;
+# if we tried to execute, using '!ok', the command would fail. Ideally we would
+# define each of these in Calcite's Redshift (or PostgreSQL) operator table,
+# and we could execute the functions.
+# get_bit
+# get_byte
+# set_bit
+# set_byte
+# to_ascii
+# isfinite
+# now()
+# date_cmp
+# date_cmp_timestamp
+# date_cmp_timestamptz
+# date_part_year
+# date_trunc
+# interval_cmp
+# getdate()
+# nvl2
+# age
+# add_months
+# trunc
+# to_hex
+# random
+# pow
+# log
+# dlog10
+# dlog1
+# checksum
+# cbrt
+# to_timestamp
+# timezone
+# bpcharcmp
+# btrim
+# charindex
+# bttext_pattern_cmp
+# crc32
+# func_sha1
+# convert_timezone
+#
+# The following functions work correctly, and can be executed, but we use
+# '!explain-validated-on' because the results of execution differ each time:
+# current_date
+# current_time
+# current_timestamp
+# localtime
+# localtimestamp
+#
+# lenientAgg - aggregate functions not in operator table:
+# median
+# bool_and
+# bool_or
+# percentile_cont
+# percentile_disc
+# cume_dist
+# percent_rank
+# ratio_to_report
+#
+# approximate - keyword before aggregate functions, e.g. 'approximate avg(x)'
+#
+# atTimeZone - AT TIME ZONE operator
+#
+# firstValueRespectNulls - RESPECT NULLS in FIRST_VALUE and LAST_VALUE
+#
+# leadIgnoreNulls - allow IGNORE NULLS in LEAD, LAG, NTH_VALUE
+#
+# nvlVarArgs - NVL with more than 2 arguments
+#
+# coerce - apply CONVERT_TIMEZONE to CHAR rather than TIMESTAMP last arg,
+# or with only 2 args,
+# apply DATEADD to CHAR rather than DATE
+# apply CONVERT_TIMEZONE to CHAR rather than DATE
+#
+# dateAdd - "No match found for function signature DATEADD(<INTERVAL_YEAR_MONTH>,
+# <NUMERIC>, <DATE>)" due to "month" argument;
+# similarly DATEDIFF and DATE_PART;
+# similarly 'm' and 'qtr' arguments
+#
+# sysdate - operator with no parens:
+# SYSDATE
+# CURRENT_USER_ID
+#
+# emptyParens - Allow nilary built-in functions to be called with empty parens:
+# PI
+# CURRENT_SCHEMA
+#
+# position - Non-ANSI position function; similarly CONVERT
+#
+# pluralInterval - allow INTERVAL '2' DAYS as well as INTERVAL '2' DAY; [CALCITE-3383]
+#
+# TODO:
+# * Why does CONCAT not work?
+#
+!use scott-redshift
+!set outputformat csv
+
+# 1 Leader Node–Only Functions
+# The following SQL functions are leader-node only functions and are not
+# supported on the compute nodes:
+#
+# 1.1 System information functions
+# CURRENT_SCHEMA
+# CURRENT_SCHEMAS
+# HAS_DATABASE_PRIVILEGE
+# HAS_SCHEMA_PRIVILEGE
+# HAS_TABLE_PRIVILEGE
+
+# 1.2.1 Date functions
+# AGE
+# CURRENT_TIME
+# CURRENT_TIMESTAMP
+# LOCALTIME
+# ISFINITE
+# NOW
+
+# 1.2.2 String functions
+
+# ASCII
+select ascii('xyz');
+EXPR$0
+120
+!ok
+
+# GET_BIT
+select get_bit(CAST('FFFF' as BINARY), 1);
+SELECT "GET_BIT"(CAST('FFFF' AS BINARY), 1)
+!explain-validated-on calcite
+
+# GET_BYTE
+select get_byte(CAST('FFFF' as BINARY), 1);
+SELECT "GET_BYTE"(CAST('FFFF' AS BINARY), 1)
+!explain-validated-on calcite
+
+# SET_BIT
+select set_bit(CAST('FFFF' as BINARY), 1, 61);
+SELECT "SET_BIT"(CAST('FFFF' AS BINARY), 1, 61)
+!explain-validated-on calcite
+
+# SET_BYTE
+select set_byte(CAST('FFFF' as BINARY), 1, 61);
+SELECT "SET_BYTE"(CAST('FFFF' AS BINARY), 1, 61)
+!explain-validated-on calcite
+
+# TO_ASCII
+select to_ascii(120);
+SELECT "TO_ASCII"(120)
+!explain-validated-on calcite
+
+# 2 Compute Node–Only Functions
+#
+# The following SQL functions must execute only on the compute nodes.
+#
+# LISTAGG
+# MEDIAN
+# PERCENTILE_CONT
+# PERCENTILE_DISC and APPROXIMATE PERCENTILE_DISC
+
+# 3 Aggregate Functions
+
+# APPROXIMATE PERCENTILE_DISC
+!if (approximate) {
+select approximate percentile_disc(0.5) within group (order by sal) from emp group by deptno;
+!ok
+!}
+
+# AVG
+select avg(sal) from emp;
+EXPR$0
+2073.214285714286
+!ok
+
+# COUNT
+!if (approximate) {
+select approximate count(distinct sal) from emp;
+!ok
+!}
+
+select count(*) from emp;
+EXPR$0
+14
+!ok
+
+select count(sal) from emp;
+EXPR$0
+14
+!ok
+
+select count(all sal) from emp;
+EXPR$0
+14
+!ok
+
+select count(distinct sal) from emp;
+EXPR$0
+12
+!ok
+
+# LISTAGG
+select listagg(empno) from emp group by deptno;
+EXPR$0
+7369,7566,7788,7876,7902
+7499,7521,7654,7698,7844,7900
+7782,7839,7934
+!ok
+
+select listagg(empno) within group (order by sal) from emp group by deptno;
+EXPR$0
+7369,7876,7566,7788,7902
+7900,7521,7654,7844,7499,7698
+7934,7782,7839
+!ok
+
+select listagg(empno, ',') from emp group by deptno;
+EXPR$0
+7369,7566,7788,7876,7902
+7499,7521,7654,7698,7844,7900
+7782,7839,7934
+!ok
+
+# MAX
+select max(distinct sal) from emp;
+EXPR$0
+5000.00
+!ok
+
+select max(all sal) from emp;
+EXPR$0
+5000.00
+!ok
+
+select max(sal) from emp;
+EXPR$0
+5000.00
+!ok
+
+# MEDIAN
+!if (lenientAgg) {
+select median(sal) from emp;
+!ok
+!}
+
+# MIN
+select min(distinct sal) from emp;
+EXPR$0
+800.00
+!ok
+
+select min(all sal) from emp;
+EXPR$0
+800.00
+!ok
+
+select min(sal) from emp;
+EXPR$0
+800.00
+!ok
+
+# PERCENTILE_CONT
+!if (lenientAgg) {
+select percentile_cont(0.6) within group (order by sal) from emp group by deptno;
+!ok
+!}
+
+# PERCENTILE_DISC
+!if (lenientAgg) {
+select percentile_disc(0.6) within group (order by sal) from emp group by deptno;
+!ok
+!}
+
+# STDDEV_SAMP and STDDEV_POP
+select stddev_samp(sal) from emp;
+EXPR$0
+1182.503223516271873450023122131824493408203125
+!ok
+
+select stddev_pop(sal) from emp;
+EXPR$0
+1139.488618295281639802851714193820953369140625
+!ok
+
+# SUM
+select sum(sal) from emp;
+EXPR$0
+29025.00
+!ok
+
+select sum(distinct sal) from emp;
+EXPR$0
+24775.00
+!ok
+
+# VAR_SAMP and VAR_POP
+select var_samp(sal) from emp;
+EXPR$0
+1398313.873626374
+!ok
+
+select var_samp(distinct sal) from emp;
+EXPR$0
+1512779.356060606
+!ok
+
+select var_samp(all sal) from emp;
+EXPR$0
+1398313.873626374
+!ok
+
+select var_pop(sal) from emp;
+EXPR$0
+1298434.31122449
+!ok
+
+# 4 Bit-Wise Aggregate Functions
+
+# BIT_AND
+select bit_and(deptno) from emp;
+EXPR$0
+0
+!ok
+
+# BIT_OR
+select bit_or(deptno) from emp;
+EXPR$0
+30
+!ok
+
+# BOOL_AND operates on a single Boolean or integer column or expression
+!if (lenientAgg) {
+select bool_and(deptno < 20) from emp;
+!ok
+!}
+
+!if (lenientAgg) {
+select bool_and(deptno) from emp;
+!ok
+!}
+
+!if (lenientAgg) {
+select bool_and(distinct deptno) from emp;
+!ok
+!}
+
+# BOOL_OR operates on a single Boolean or integer column or expression
+!if (lenientAgg) {
+select bool_or(deptno < 20) from emp;
+!ok
+!}
+
+!if (lenientAgg) {
+select bool_or(deptno) from emp;
+!ok
+!}
+
+# 5 Window and ranking functions
+
+# 5.1 Window functions:
+
+# AVG
+select empno, avg(sal) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1;
+EMPNO, EXPR$1
+7499, 1600.00
+7521, 1425.00
+7654, 1366.666666666667
+7698, 1737.50
+7844, 1690.00
+7900, 1566.666666666667
+!ok
+
+# COUNT
+select empno, count(comm) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1;
+EMPNO, EXPR$1
+7499, 1
+7521, 2
+7654, 3
+7698, 3
+7844, 4
+7900, 4
+!ok
+
+# CUME_DIST
+!if (lenientAgg) {
+select empno, cume_dist() over (order by sal rows unbounded preceding) from emp where deptno = 30 order by 1;
+!ok
+!}
+
+# FIRST_VALUE
+select empno, first_value(sal) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1;
+EMPNO, EXPR$1
+7499, 1600.00
+7521, 1600.00
+7654, 1600.00
+7698, 1600.00
+7844, 1600.00
+7900, 1600.00
+!ok
+
+!if (firstValueRespectNulls) {
+select empno, first_value(sal respect nulls) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1;
+!ok
+!}
+
+# LAG
+select empno, lag(sal) respect nulls over (order by empno) from emp where deptno = 30 order by 1;
+EMPNO, EXPR$1
+7499, null
+7521, 1600.00
+7654, 1250.00
+7698, 1250.00
+7844, 2850.00
+7900, 1500.00
+!ok
+
+select empno, lag(sal, 2) respect nulls over (order by empno) from emp where deptno = 30 order by 1;
+EMPNO, EXPR$1
+7499, null
+7521, null
+7654, 1600.00
+7698, 1250.00
+7844, 1250.00
+7900, 2850.00
+!ok
+
+# LAST_VALUE
+!if (firstValueRespectNulls) {
+select empno, last_value(sal) over (order by empno rows unbounded preceding) from emp order by 1;
+!ok
+!}
+
+!if (firstValueRespectNulls) {
+select empno, last_value(sal respect nulls) over (order by empno rows unbounded preceding) from emp order by 1;
+!ok
+!}
+
+# LEAD
+!if (leadIgnoreNulls) {
+select empno, lead(sal, 2) ignore nulls over (order by empno) from emp order by 1;
+!ok
+!}
+
+# MAX
+select empno, max(comm) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1;
+EMPNO, EXPR$1
+7499, 300.00
+7521, 500.00
+7654, 1400.00
+7698, 1400.00
+7844, 1400.00
+7900, 1400.00
+!ok
+
+# MEDIAN
+!if (lenientAgg) {
+select empno, median(comm) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1;
+!ok
+!}
+
+# MIN
+select empno, min(comm) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1;
+EMPNO, EXPR$1
+7499, 300.00
+7521, 300.00
+7654, 300.00
+7698, 300.00
+7844, 0.00
+7900, 0.00
+!ok
+
+# NTH_VALUE
+!if (leadIgnoreNulls) {
+select empno, nth_value(sal, 2) ignore nulls over (order by empno rows unbounded preceding) from emp order by 1;
+!ok
+!}
+
+# PERCENTILE_CONT
+!if (lenientAgg) {
+select percentile_cont(0.6) within group (order by sal) over () from emp;
+!ok
+!}
+
+!if (lenientAgg) {
+select percentile_cont(0.6) within group (order by sal) over (partition by deptno) from emp;
+!ok
+!}
+
+# PERCENTILE_DISC
+!if (lenientAgg) {
+select percentile_disc(0.6) within group (order by sal) over () from emp;
+!ok
+!}
+
+!if (lenientAgg) {
+select percentile_disc(0.6) within group (order by sal) over (partition by deptno) from emp;
+!ok
+!}
+
+# RATIO_TO_REPORT
+!if (lenientAgg) {
+select ratio_to_report(sal) over () from emp where deptno = 30;
+!ok
+!}
+
+!if (lenientAgg) {
+select deptno, ratio_to_report(sal) over (partition by deptno) from emp;
+!ok
+!}
+
+# STDDEV_POP
+select empno, stddev_pop(comm) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1;
+EMPNO, EXPR$1
+7499, 0
+7521, 100
+7654, 478.42333648024424519462627358734607696533203125
+7698, 478.42333648024424519462627358734607696533203125
+7844, 522.0153254455275373402400873601436614990234375
+7900, 522.0153254455275373402400873601436614990234375
+!ok
+
+# STDDEV_SAMP (synonym for STDDEV)
+select empno, stddev_samp(comm) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1;
+EMPNO, EXPR$1
+7499, null
+7521, 141.421356237309510106570087373256683349609375
+7654, 585.9465277082316561063635163009166717529296875
+7698, 585.9465277082316561063635163009166717529296875
+7844, 602.7713773341707792496890760958194732666015625
+7900, 602.7713773341707792496890760958194732666015625
+!ok
+
+select empno, stddev(comm) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1;
+EMPNO, EXPR$1
+7499, null
+7521, 141.421356237309510106570087373256683349609375
+7654, 585.9465277082316561063635163009166717529296875
+7698, 585.9465277082316561063635163009166717529296875
+7844, 602.7713773341707792496890760958194732666015625
+7900, 602.7713773341707792496890760958194732666015625
+!ok
+
+# SUM
+select empno, sum(comm) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1;
+EMPNO, EXPR$1
+7499, 300.00
+7521, 800.00
+7654, 2200.00
+7698, 2200.00
+7844, 2200.00
+7900, 2200.00
+!ok
+
+# VAR_POP
+select empno, var_pop(comm) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1;
+EMPNO, EXPR$1
+7499, 0.0000
+7521, 10000.0000
+7654, 228888.888888889
+7698, 228888.888888889
+7844, 272500.0000
+7900, 272500.0000
+!ok
+
+# VAR_SAMP (synonym for VARIANCE)
+select empno, var_samp(comm) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1;
+EMPNO, EXPR$1
+7499, null
+7521, 20000.0000
+7654, 343333.3333333335
+7698, 343333.3333333335
+7844, 363333.3333333333
+7900, 363333.3333333333
+!ok
+
+select empno, variance(comm) over (order by empno rows unbounded preceding) from emp where deptno = 30 order by 1;
+EMPNO, EXPR$1
+7499, null
+7521, 20000.0000
+7654, 343333.3333333335
+7698, 343333.3333333335
+7844, 363333.3333333333
+7900, 363333.3333333333
+!ok
+
+# 5.2 Ranking functions
+
+# DENSE_RANK
+select dense_rank() over () from emp where deptno = 30;
+EXPR$0
+6
+6
+6
+6
+6
+6
+!ok
+
+select dense_rank() over (partition by deptno) from emp;
+EXPR$0
+3
+3
+3
+5
+5
+5
+5
+5
+6
+6
+6
+6
+6
+6
+!ok
+
+select dense_rank() over (partition by deptno order by sal) from emp;
+EXPR$0
+1
+1
+1
+2
+2
+2
+2
+3
+3
+3
+4
+4
+4
+5
+!ok
+
+# NTILE
+select ntile(4) over (order by sal desc) from emp;
+EXPR$0
+1
+1
+1
+1
+2
+2
+2
+3
+3
+3
+3
+4
+4
+4
+!ok
+
+# PERCENT_RANK
+!if (lenientAgg) {
+select percent_rank() over () from emp;
+!ok
+!}
+
+!if (lenientAgg) {
+select percent_rank() over (partition by deptno) from emp;
+!ok
+!}
+
+!if (lenientAgg) {
+select percent_rank() over (partition by deptno order by sal) from emp;
+!ok
+!}
+
+# RANK
+select rank() over () from emp;
+EXPR$0
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+14
+!ok
+
+select rank() over (partition by deptno) from emp;
+EXPR$0
+3
+3
+3
+5
+5
+5
+5
+5
+6
+6
+6
+6
+6
+6
+!ok
+
+select rank() over (partition by deptno order by sal) from emp;
+EXPR$0
+1
+1
+1
+2
+2
+2
+2
+3
+3
+4
+4
+4
+5
+6
+!ok
+
+# ROW_NUMBER
+select row_number() over () from emp;
+EXPR$0
+1
+10
+11
+12
+13
+14
+2
+3
+4
+5
+6
+7
+8
+9
+!ok
+
+# 6 Conditional Expressions
+
+# CASE
+select case when deptno < 20 then 'x' else 'y' end from emp;
+EXPR$0
+y
+y
+y
+y
+y
+y
+y
+y
+y
+y
+y
+x
+x
+x
+!ok
+
+select case when deptno < 20 then 'x' end from emp;
+EXPR$0
+null
+null
+null
+null
+null
+null
+null
+null
+null
+null
+null
+x
+x
+x
+!ok
+
+select case deptno when 10 then 'x' when 20 then 'y' end from emp;
+EXPR$0
+null
+null
+null
+null
+null
+null
+x
+x
+x
+y
+y
+y
+y
+y
+!ok
+
+select case deptno when 10 then 'x' when 20 then 'y' else 'z' end from emp;
+EXPR$0
+x
+x
+x
+y
+y
+y
+y
+y
+z
+z
+z
+z
+z
+z
+!ok
+
+# COALESCE is a synonym for NVL
+select coalesce(1, 2, 3);
+EXPR$0
+1
+!ok
+
+# DECODE
+select decode(deptno, 10, 'x', 20, 'y', 'z') from emp;
+EXPR$0
+x
+x
+x
+y
+y
+y
+y
+y
+z
+z
+z
+z
+z
+z
+!ok
+
+# GREATEST and LEAST
+select greatest(deptno) from emp where deptno = 30;
+EXPR$0
+30
+30
+30
+30
+30
+30
+!ok
+
+select greatest(deptno, empno) from emp where deptno = 30;
+EXPR$0
+7499
+7521
+7654
+7698
+7844
+7900
+!ok
+
+select greatest(deptno, empno, sal) from emp where deptno = 30;
+EXPR$0
+7499
+7521
+7654
+7698
+7844
+7900
+!ok
+
+select least(deptno, empno) from emp where deptno = 30;
+EXPR$0
+30
+30
+30
+30
+30
+30
+!ok
+
+# NVL
+select nvl(1, 2);
+EXPR$0
+1
+!ok
+
+!if (nvlVarArgs) {
+select nvl(1, 2, 3);
+!ok
+!}
+
+select nvl(comm, sal) from emp where deptno = 30;
+EXPR$0
+0.00
+1400.00
+2850.00
+300.00
+500.00
+950.00
+!ok
+
+# NVL2
+select nvl2(comm, sal, sal + 10) from emp where deptno = 30;
+SELECT "NVL2"("EMP"."COMM", "EMP"."SAL", "EMP"."SAL" + 10)
+FROM "scott"."EMP" AS "EMP"
+WHERE "EMP"."DEPTNO" = 30
+!explain-validated-on calcite
+
+# NULLIF
+select nullif(comm, sal) from emp;
+EXPR$0
+0.00
+1400.00
+300.00
+500.00
+null
+null
+null
+null
+null
+null
+null
+null
+null
+null
+!ok
+
+# 7 Date and Time Functions
+
+# 7.1 Deprecated leader-node only functions
+
+# The following date functions are deprecated because they execute only on
+# the leader node.
+
+# AGE. Use DATEDIFF Function instead.
+select age('2017-01-01','2011-06-24');
+SELECT "AGE"('2017-01-01', '2011-06-24')
+!explain-validated-on calcite
+
+# CURRENT_TIME. Use GETDATE Function or SYSDATE instead.
+select current_time;
+SELECT CURRENT_TIME AS CURRENT_TIME
+!explain-validated-on calcite
+
+select current_time(2);
+SELECT CURRENT_TIME(2)
+!explain-validated-on calcite
+
+# CURRENT_TIMESTAMP. Use GETDATE Function or SYSDATE instead.
+select current_timestamp;
+SELECT CURRENT_TIMESTAMP AS CURRENT_TIMESTAMP
+!explain-validated-on calcite
+
+select current_timestamp(2);
+SELECT CURRENT_TIMESTAMP(2)
+!explain-validated-on calcite
+
+# LOCALTIME. Use GETDATE Function or SYSDATE instead.
+select localtime;
+SELECT LOCALTIME AS LOCALTIME
+!explain-validated-on calcite
+
+select localtime(2);
+SELECT LOCALTIME(2)
+!explain-validated-on calcite
+
+# LOCALTIMESTAMP. Use GETDATE Function or SYSDATE instead.
+select localtimestamp;
+SELECT LOCALTIMESTAMP AS LOCALTIMESTAMP
+!explain-validated-on calcite
+
+# ISFINITE
+select isfinite(date '2002-09-17');
+SELECT "ISFINITE"(DATE '2002-09-17')
+!explain-validated-on calcite
+
+# NOW. Use GETDATE Function or SYSDATE instead.
+select now();
+SELECT "NOW"()
+!explain-validated-on calcite
+
+# 7.2 Date and Time functions
+
+# ADD_MONTHS ({date|timestamp}, integer) returns TIMESTAMP
+# Adds the specified number of months to a date or time stamp.
+# If the date you are adding to is the last day of the month, the result is
+# always the last day of the result month, regardless of the length of the month.
+
+select add_months('2008-03-31',1);
+SELECT "ADD_MONTHS"('2008-03-31', 1)
+!explain-validated-on calcite
+
+-- returns '2008-04-30'
+select add_months(date '2008-03-31',1);
+SELECT "ADD_MONTHS"(DATE '2008-03-31', 1)
+!explain-validated-on calcite
+
+-- returns '2008-05-31'
+select add_months(date '2008-04-30',1);
+SELECT "ADD_MONTHS"(DATE '2008-04-30', 1)
+!explain-validated-on calcite
+
+select add_months(date '2008-03-31',-1);
+SELECT "ADD_MONTHS"(DATE '2008-03-31', -1)
+!explain-validated-on calcite
+
+select add_months(timestamp '2008-03-31 12:23:34',1);
+SELECT "ADD_MONTHS"(TIMESTAMP '2008-03-31 12:23:34', 1)
+!explain-validated-on calcite
+
+# AT TIME ZONE 'timezone' returns TIMESTAMP
+# Specifies which time zone to use with a TIMESTAMP or TIMESTAMPTZ expression.
+!if (atTimeZone) {
+-- returns '2001-02-16 19:38:40-08'
+SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
+!ok
+!}
+
+!if (atTimeZone) {
+-- returns '2001-02-16 18:38:40'
+SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
+!ok
+!}
+
+# CONVERT_TIMEZONE (['timezone',] 'timezone', timestamp) returns TIMESTAMP
+# Converts a time stamp from one time zone to another.
+-- returns '2008-03-05 09:25:29'
+select convert_timezone('EST', 'PST', timestamp '2008-03-05 12:25:29');
+SELECT "CONVERT_TIMEZONE"('EST', 'PST', TIMESTAMP '2008-03-05 12:25:29')
+!explain-validated-on calcite
+
+!if (coerce) {
+-- returns '2008-03-05 09:25:29'
+select convert_timezone('EST', 'PST', '20080305 12:25:29');
+!ok
+!}
+
+!if (coerce) {
+-- returns '2013-02-01 03:00:00'
+select convert_timezone('America/New_York', '2013-02-01 08:00:00');
+!ok
+!}
+
+!if (coerce) {
+-- returns '2014-05-17 10:00:00'
+select CONVERT_TIMEZONE('GMT','GMT+2','2014-05-17 12:00:00');
+!ok
+!}
+
+# CURRENT_DATE returns DATE
+# Returns a date in the current session time zone (UTC by default) for the start
+# of the current transaction.
+select current_date;
+SELECT CURRENT_DATE AS CURRENT_DATE
+!explain-validated-on calcite
+
+# DATE_CMP (date1, date2) returns INTEGER
+# Compares two dates and returns 0 if the dates are identical, 1 if date1 is
+# greater, and -1 if date2 is greater.
+-- returns -1
+select date_cmp('2008-01-01', '2008-01-04');
+SELECT "DATE_CMP"('2008-01-01', '2008-01-04')
+!explain-validated-on calcite
+
+-- returns 0
+select date_cmp(date '2008-01-04', '2008-01-04');
+SELECT "DATE_CMP"(DATE '2008-01-04', '2008-01-04')
+!explain-validated-on calcite
+
+-- returns 1
+select date_cmp(date '2008-01-05', date '2008-01-04');
+SELECT "DATE_CMP"(DATE '2008-01-05', DATE '2008-01-04')
+!explain-validated-on calcite
+
+# DATE_CMP_TIMESTAMP (date, timestamp) returns INTEGER
+# Compares a date to a time and returns 0 if the values are identical, 1 if date
+# is greater and -1 if timestamp is greater.
+-- returns -1
+select date_cmp_timestamp('2008-01-01', '2008-01-04 00:00:00');
+SELECT "DATE_CMP_TIMESTAMP"('2008-01-01', '2008-01-04 00:00:00')
+!explain-validated-on calcite
+
+-- returns 0
+select date_cmp_timestamp(date '2008-01-04', '2008-01-04 00:00:00');
+SELECT "DATE_CMP_TIMESTAMP"(DATE '2008-01-04', '2008-01-04 00:00:00')
+!explain-validated-on calcite
+
+-- I presume that this returns -1, but doc does not specify
+select date_cmp_timestamp(date '2008-01-04', '2008-01-04 01:23:45');
+SELECT "DATE_CMP_TIMESTAMP"(DATE '2008-01-04', '2008-01-04 01:23:45')
+!explain-validated-on calcite
+
+-- returns 1
+select date_cmp_timestamp(date '2008-01-05', timestamp '2008-01-04 00:00:00');
+SELECT "DATE_CMP_TIMESTAMP"(DATE '2008-01-05', TIMESTAMP '2008-01-04 00:00:00')
+!explain-validated-on calcite
+
+# DATE_CMP_TIMESTAMPTZ (date, timestamptz) returns INTEGER
+# Compares a date and a time stamp with time zone and returns 0 if the values
+# are identical, 1 if date is greater and -1 if timestamptz is greater.
+!if (atTimeZone) {
+-- returns -1
+select date_cmp_timestamptz('2008-01-01', '2008-01-04 00:00:00' at time zone 'gmt');
+!ok
+!}
+
+# DATE_PART_YEAR (date) returns INTEGER
+# Extracts the year from a date.
+-- returns 2008
+select date_part_year('2008-01-05');
+SELECT "DATE_PART_YEAR"('2008-01-05')
+!explain-validated-on calcite
+
+select date_part_year(date '2008-01-05');
+SELECT "DATE_PART_YEAR"(DATE '2008-01-05')
+!explain-validated-on calcite
+
+# DATEADD (datepart, interval, {date|timestamp}) returns TIMESTAMP
+# Increments a date or time by a specified interval.
+# The DATEADD(month, ...) and ADD_MONTHS functions handle dates that fall at the
+# ends of months differently.
+!if (dateAdd) {
+-- returns '2009-08-28 00:00:00'
+select dateadd(month,18,date '2008-02-28');
+!ok
+!}
+
+!if (dateAdd) {
+-- as above, for uses char rather than date
+select dateadd(month,18,'2008-02-28');
+!ok
+!}
+
+!if (dateAdd) {
+-- returns '2004-05-30 00:00:00' (ADD_MONTHS would return '2004-05-31')
+select dateadd(month,1,date '2004-04-30');
+!ok
+!}
+
+!if (dateAdd) {
+-- returns '2008-02-28 00:30:00' ('m' stands for minutes, not months)
+select dateadd(m,18,date '2008-02-28');
+!ok
+!}
+
+!if (dateAdd) {
+-- returns '2017-02-28 00:00:00'
+select dateadd(month,12,date '2016-02-29');
+!ok
+!}
+
+!if (dateAdd) {
+-- returns '2017-03-01 00:00:00'
+select dateadd(year, 1, date '2016-02-29');
+!ok
+!}
+
+# DATEDIFF (datepart, {date|time}, {date|timestamp}) returns BIGINT
+# Returns the difference between two dates or times for a given date part, such
+# as a day or month.
+!if (dateAdd) {
+-- returns 52
+select datediff(week,date '2009-01-01',date '2009-12-31') as numweeks;
+!ok
+!}
+
+!if (dateAdd) {
+-- as above but CHAR rather than DATE
+select datediff(week,'2009-01-01','2009-12-31') as numweeks;
+!ok
+
+select datediff(week,date '2009-01-01','2009-12-31') as numweeks;
+!ok
+
+select datediff(week,'2009-01-01',date '2009-12-31') as numweeks;
+!ok
+!}
+
+!if (dateAdd) {
+-- returns 40 or more
+select datediff(qtr, date '1998-07-01', current_date);
+!ok
+!}
+
+!if (dateAdd) {
+select datediff(hours, date '1998-07-01', current_date);
+!ok
+!}
+
+!if (dateAdd) {
+select datediff(day, date '1998-07-01', current_date);
+!ok
+!}
+
+# DATE_PART (datepart, {date|time}) returns DOUBLE
+# Extracts a date part value from date or time.
+!if (dateAdd) {
+-- returns 25
+select date_part(w, timestamp '2008-06-17 09:44:54');
+!ok
+!}
+
+!if (dateAdd) {
+select date_part(w, timestamp '2008-06-17 09:44:54');
+!ok
+!}
+
+!if (dateAdd) {
+-- returns 8
+select date_part(minute, timestamp '2009-01-01 02:08:01');
+!ok
+!}
+
+!if (dateAdd) {
+select date_part(minute, time '02:08:01');
+!ok
+!}
+
+!if (dateAdd) {
+select date_part(minute, date '2009-01-01');
+!ok
+!}
+
+# DATE_TRUNC ('datepart', timestamp) returns TIMESTAMP
+# Truncates a time stamp based on a date part.
+-- returns '2008-09-01'
+select date_trunc('week', date '2008-09-07');
+SELECT "DATE_TRUNC"('week', DATE '2008-09-07')
+!explain-validated-on calcite
+
+-- as above, but CHAR rather than DATE
+select date_trunc('week', '2008-09-07');
+SELECT "DATE_TRUNC"('week', '2008-09-07')
+!explain-validated-on calcite
+
+-- returns '2008-09-08'
+select date_trunc('week', date '2008-09-08');
+SELECT "DATE_TRUNC"('week', DATE '2008-09-08')
+!explain-validated-on calcite
+
+-- returns '2008-09-08'
+select date_trunc('week', date '2008-09-09');
+SELECT "DATE_TRUNC"('week', DATE '2008-09-09')
+!explain-validated-on calcite
+
+# EXTRACT (datepart FROM {TIMESTAMP 'literal' | timestamp}) returns DOUBLE
+# Extracts a date part from a timestamp or literal.
+-- returns 8
+select extract(minute from timestamp '2009-09-09 12:08:43');
+EXPR$0
+8
+!ok
+
+!if (coerce) {
+-- as above, but CHAR rather than TIMESTAMP
+select extract(minute from '2009-09-09 12:08:43');
+!ok
+!}
+
+# GETDATE() returns TIMESTAMP
+# Returns the current date and time in the current session time zone (UTC by
+# default). The parentheses are required.
+select getdate();
+SELECT "GETDATE"()
+!explain-validated-on calcite
+
+# INTERVAL_CMP (interval1, interval2) returns INTEGER
+# Compares two intervals and returns 0 if the intervals are equal, 1 if
+# interval1 is greater, and -1 if interval2 is greater.
+!if (pluralInterval) {
+-- returns -1
+select interval_cmp(interval '3' days,interval '1');
+!explain-validated-on calcite
+!}
+
+-- as above but CHAR rather than INTERVAL
+select interval_cmp('3 days','1 year');
+SELECT "INTERVAL_CMP"('3 days', '1 year')
+!explain-validated-on calcite
+
+-- returns 0
+select interval_cmp('7 days','1 week');
+SELECT "INTERVAL_CMP"('7 days', '1 week')
+!explain-validated-on calcite
+
+-- should this return 0 or 1?
+select interval_cmp('366 days','1 year');
+SELECT "INTERVAL_CMP"('366 days', '1 year')
+!explain-validated-on calcite
+
+-- should this return -1, 0 or 1?
+select interval_cmp('30 days','1 month');
+SELECT "INTERVAL_CMP"('30 days', '1 month')
+!explain-validated-on calcite
+
+# LAST_DAY(date) returns DATE
+# Returns the date of the last day of the month that contains date.
+# Always returns DATE, even if argument is TIMESTAMP.
+-- returns '2004-01-31'
+select last_day(date '2004-01-25');
+EXPR$0
+2004-01-31
+!ok
+
+-- returns '2004-01-31'
+select last_day(timestamp '2004-01-25 12:23:45');
+EXPR$0
+2004-01-31
+!ok
+
+# MONTHS_BETWEEN (date, date) returns FLOAT8
+# Returns the number of months between two dates.
+-- returns -2
+select months_between('1969-01-18', '1969-03-18') as months;
+SELECT "MONTHS_BETWEEN"('1969-01-18', '1969-03-18') AS "MONTHS"
+!explain-validated-on calcite
+
+# NEXT_DAY (date, day) returns DATE
+# Returns the date of the first instance of day that is later than date.
+-- returns '2014-08-26'
+select next_day('2014-08-20','Tuesday');
+SELECT "NEXT_DAY"('2014-08-20', 'Tuesday')
+!explain-validated-on calcite
+
+-- returns '2014-08-26'
+select next_day('2014-08-20','Tu');
+SELECT "NEXT_DAY"('2014-08-20', 'Tu')
+!explain-validated-on calcite
+
+-- not valid ('T' could be 'Tue' or 'Thu')
+select next_day('2014-08-20','T');
+SELECT "NEXT_DAY"('2014-08-20', 'T')
+!explain-validated-on calcite
+
+-- returns '2014-08-22'
+select next_day('2014-08-20','F');
+SELECT "NEXT_DAY"('2014-08-20', 'F')
+!explain-validated-on calcite
+
+# SYSDATE returns TIMESTAMP
+# Returns the date and time in the current session time zone (UTC by default)
+# for the start of the current transaction.
+!if (sysdate) {
+select sysdate;
+!ok
+!}
+
+# TIMEOFDAY() returns VARCHAR
+# Returns the current weekday, date, and time in the current session time zone
+# (UTC by default) as a string value.
+-- returns something like 'Thu Sep 19 22:53:50.333525 2013 UTC'
+select timeofday();
+SELECT "TIMEOFDAY"()
+!explain-validated-on calcite
+
+# TIMESTAMP_CMP (timestamp1, timestamp2) returns INTEGER
+# Compares two timestamps and returns 0 if the timestamps are equal, 1 if
+# timestamp1 is greater, and -1 if timestamp2 is greater.
+-- returns -1
+select timestamp_cmp('2008-01-01 00:00:00', '2008-01-04 12:34:56');
+SELECT "TIMESTAMP_CMP"('2008-01-01 00:00:00', '2008-01-04 12:34:56')
+!explain-validated-on calcite
+
+# TIMESTAMP_CMP_DATE (timestamp, date) returns INTEGER
+# Compares a timestamp to a date and returns 0 if the values are equal, 1 if
+# timestamp is greater, and -1 if date is greater.
+-- returns -1
+select timestamp_cmp_date('2008-01-01 00:00:00', '2008-01-04');
+SELECT "TIMESTAMP_CMP_DATE"('2008-01-01 00:00:00', '2008-01-04')
+!explain-validated-on calcite
+
+# TIMESTAMP_CMP_TIMESTAMPTZ (timestamp, timestamptz) returns INTEGER
+# Compares a timestamp with a time stamp with time zone and returns 0 if the
+# values are equal, 1 if timestamp is greater, and -1 if timestamptz is greater.
+-- returns -1
+select timestamp_cmp_timestamptz('2008-01-01 00:00:00', '2008-01-04 00:00:00');
+SELECT "TIMESTAMP_CMP_TIMESTAMPTZ"('2008-01-01 00:00:00', '2008-01-04 00:00:00')
+!explain-validated-on calcite
+
+# TIMESTAMPTZ_CMP (timestamptz1, timestamptz2) returns INTEGER
+# Compares two timestamp with time zone values and returns 0 if the values are
+# equal, 1 if timestamptz1 is greater, and -1 if timestamptz2 is greater.
+-- returns -1
+select timestamptz_cmp('2008-01-01 00:00:00', '2008-01-04 00:00:00');
+SELECT "TIMESTAMPTZ_CMP"('2008-01-01 00:00:00', '2008-01-04 00:00:00')
+!explain-validated-on calcite
+
+# TIMESTAMPTZ_CMP_DATE (timestamptz, date) returns INTEGER
+# Compares the value of a time stamp with time zone and a date and returns 0 if
+# the values are equal, 1 if timestamptz is greater, and -1 if date is greater.
+-- returns -1
+select timestamptz_cmp_date('2008-01-01 00:00:00', '2008-01-04');
+SELECT "TIMESTAMPTZ_CMP_DATE"('2008-01-01 00:00:00', '2008-01-04')
+!explain-validated-on calcite
+
+# TIMESTAMPTZ_CMP_TIMESTAMP (timestamptz, timestamp) returns INTEGER
+# Compares a timestamp with time zone with a time stamp and returns 0 if the
+# values are equal, 1 if timestamptz is greater, and -1 if timestamp is greater.
+-- returns -1
+select timestamptz_cmp_timestamp('2008-01-01 00:00:00', '2008-01-04');
+SELECT "TIMESTAMPTZ_CMP_TIMESTAMP"('2008-01-01 00:00:00', '2008-01-04')
+!explain-validated-on calcite
+
+# TIMEZONE ('timezone', timestamp | timestamptz ) returns TIMESTAMP or TIMESTAMPTZ
+# Returns a time stamp or time stamp with time zone for the specified time zone
+# and time stamp value.
+select timezone('Africa/Kampala', '2008-01-01 00:00:00');
+SELECT "TIMEZONE"('Africa/Kampala', '2008-01-01 00:00:00')
+!explain-validated-on calcite
+
+# TO_TIMESTAMP ('timestamp', 'format') returns TIMESTAMPTZ
+# Returns a time stamp with time zone for the specified time stamp and time zone
+# format.
+select to_timestamp('05 Dec 2000', 'DD Mon YYYY');
+SELECT "TO_TIMESTAMP"('05 Dec 2000', 'DD Mon YYYY')
+!explain-validated-on calcite
+
+# TRUNC(timestamp) returns DATE
+# Truncates a time stamp and returns a date.
+-- returns '2011-07-21'
+select trunc(timestamp '2011-07-21 10:32:38.248109');
+SELECT "TRUNC"(TIMESTAMP '2011-07-21 10:32:38.248109')
+!explain-validated-on calcite
+
+# 8 Math functions
+
+# ABS
+select abs(2);
+EXPR$0
+2
+!ok
+
+select -abs(-pi);
+EXPR$0
+-3.141592653589793
+!ok
+
+# ACOS
+select acos(0);
+EXPR$0
+1.5707963267948966
+!ok
+
+# ASIN
+select asin(0);
+EXPR$0
+0.0
+!ok
+
+# ATAN
+select atan(0);
+EXPR$0
+0.0
+!ok
+
+# ATAN2
+select atan2(2,2) * 4 as pi;
+PI
+3.141592653589793
+!ok
+
+# CBRT
+select cbrt(-8);
+SELECT "CBRT"(-8)
+!explain-validated-on calcite
+
+# CEILING (or CEIL)
+select ceiling(10.5);
+EXPR$0
+11
+!ok
+
+select ceiling(-10.5);
+EXPR$0
+-10
+!ok
+
+select ceil(pi);
+EXPR$0
+4.0
+!ok
+
+# CHECKSUM
+select checksum(comm) from emp;
+SELECT "CHECKSUM"("EMP"."COMM")
+FROM "scott"."EMP" AS "EMP"
+!explain-validated-on calcite
+
+# COS
+select cos(180);
+EXPR$0
+-0.5984600690578581
+!ok
+
+# COT
+select cot(45);
+EXPR$0
+0.6173696237835551
+!ok
+
+# DEGREES
+select degrees(pi);
+EXPR$0
+180.0
+!ok
+
+# DEXP
+select dexp(0);
+SELECT "DEXP"(0)
+!explain-validated-on calcite
+
+# DLOG1 is a synonym for LN
+select dlog1(10);
+SELECT "DLOG1"(10)
+!explain-validated-on calcite
+
+# DLOG10
+select dlog10(100);
+SELECT "DLOG10"(100)
+!explain-validated-on calcite
+
+# EXP
+select exp(0);
+EXPR$0
+1.0
+!ok
+
+# FLOOR
+select floor(10.5);
+EXPR$0
+10
+!ok
+
+select floor(-10.5);
+EXPR$0
+-11
+!ok
+
+# LN
+select ln(1);
+EXPR$0
+0.0
+!ok
+
+# LOG
+select log(1000);
+SELECT "LOG"(1000)
+!explain-validated-on calcite
+
+# MOD
+select mod(-50, 17);
+EXPR$0
+-16
+!ok
+
+# PI
+# In standard SQL you write 'pi', but for redshift you write 'pi()'
+!if (emptyParens) {
+select pi();
+!ok
+!}
+
+# POWER
+select power(2, 3);
+EXPR$0
+8.0
+!ok
+
+select pow(-2, 3);
+SELECT "POW"(-2, 3)
+!explain-validated-on calcite
+
+# RADIANS
+select radians(180);
+EXPR$0
+3.141592653589793
+!ok
+
+# RANDOM
+select random();
+SELECT "RANDOM"()
+!explain-validated-on calcite
+
+# ROUND
+select round(pi);
+EXPR$0
+3.0
+!ok
+
+select round(pi, 2);
+EXPR$0
+3.14
+!ok
+
+select round(-pi, 2);
+EXPR$0
+-3.14
+!ok
+
+# SIN
+select sin(-90);
+EXPR$0
+-0.8939966636005579
+!ok
+
+# SIGN
+select sign(23);
+EXPR$0
+1
+!ok
+
+# SQRT
+select sqrt(225);
+EXPR$0
+15.0
+!ok
+
+# TAN
+select tan(45);
+EXPR$0
+1.6197751905438615
+!ok
+
+# TO_HEX
+select to_hex(2147676847);
+SELECT "TO_HEX"(2147676847)
+!explain-validated-on calcite
+
+# TRUNC
+select trunc(111.86);
+SELECT "TRUNC"(111.86)
+!explain-validated-on calcite
+
+select trunc(-111.86, 1);
+SELECT "TRUNC"(-111.86, 1)
+!explain-validated-on calcite
+
+# 9 String functions
+
+# || (Concatenation) Operator
+select 'a' || 'b';
+EXPR$0
+ab
+!ok
+
+# BPCHARCMP
+select ename, dname, bpcharcmp(ename, dname) from emp join dept using (deptno);
+SELECT "EMP"."ENAME", "DEPT"."DNAME", "BPCHARCMP"("EMP"."ENAME", "DEPT"."DNAME")
+FROM "scott"."EMP" AS "EMP"
+ INNER JOIN "scott"."DEPT" AS "DEPT" USING ("DEPTNO")
+!explain-validated-on calcite
+
+# BTRIM
+select btrim(' abc ');
+SELECT "BTRIM"(' abc ')
+!explain-validated-on calcite
+
+select btrim('xyzaxyzbxyzcxyz', 'xyz');
+SELECT "BTRIM"('xyzaxyzbxyzcxyz', 'xyz')
+!explain-validated-on calcite
+
+# BTTEXT_PATTERN_CMP is a synonym for BPCHARCMP
+select ename, dname, bttext_pattern_cmp(ename, dname) from emp join dept using (deptno);
+SELECT "EMP"."ENAME", "DEPT"."DNAME", "BTTEXT_PATTERN_CMP"("EMP"."ENAME", "DEPT"."DNAME")
+FROM "scott"."EMP" AS "EMP"
+ INNER JOIN "scott"."DEPT" AS "DEPT" USING ("DEPTNO")
+!explain-validated-on calcite
+
+# CHAR_LENGTH is a synonym for LEN
+select char_length('abc');
+EXPR$0
+3
+!ok
+
+# CHARACTER_LENGTH is a synonym for LEN
+select character_length('abc');
+EXPR$0
+3
+!ok
+
+# CHARINDEX
+select charindex('dog', 'fish');
+SELECT "CHARINDEX"('dog', 'fish')
+!explain-validated-on calcite
+
+select charindex('fish', 'dogfish');
+SELECT "CHARINDEX"('fish', 'dogfish')
+!explain-validated-on calcite
+
+# CHR
+select chr(65);
+EXPR$0
+A
+!ok
+
+# CONCAT (Oracle Compatibility Function)
+select concat('a', 'b');
+SELECT "CONCAT"('a', 'b')
+!explain-validated-on calcite
+
+# CRC32
+-- returns 'f2726906'
+select crc32('Amazon Redshift');
+SELECT "CRC32"('Amazon Redshift')
+!explain-validated-on calcite
+
+# FUNC_SHA1
+select func_sha1('Amazon Redshift');
+SELECT "FUNC_SHA1"('Amazon Redshift')
+!explain-validated-on calcite
+
+# INITCAP
+-- returns 'Nibh.Enim@Egestas.Ca'
+select initcap('nibh.enim@egestas.ca');
+EXPR$0
+Nibh.Enim@Egestas.Ca
+!ok
+
+# LEFT and RIGHT
+-- returns 'Chica'
+select left('Chicago', 5);
+EXPR$0
+Chica
+!ok
+
+-- returns 'icago'
+select right('Chicago', 5);
+EXPR$0
+icago
+!ok
+
+# LEN is a synonym for LENGTH
+select len('gth');
+SELECT "LEN"('gth')
+!explain-validated-on calcite
+
+# LENGTH
+select length('ily');
+SELECT "LENGTH"('ily')
+!explain-validated-on calcite
+
+-- returns 8 (cf OCTET_LENGTH)
+select length('français');
+SELECT "LENGTH"(u&'fran\00e7ais')
+!explain-validated-on calcite
+
+# LOWER
+select lower('Manhattan');
+EXPR$0
+manhattan
+!ok
+
+# LPAD and RPAD
+select lpad('cat', 7);
+SELECT "LPAD"('cat', 7)
+!explain-validated-on calcite
+
+-- returns 'eeriness'
+select rpad(lpad('rine', 6, 'e'), 8, 's');
+SELECT "RPAD"("LPAD"('rine', 6, 'e'), 8, 's')
+!explain-validated-on calcite
+
+select rpad('cat', 7);
+SELECT "RPAD"('cat', 7)
+!explain-validated-on calcite
+
+# LTRIM
+-- returns 'kery'
+select ltrim('bakery', 'abc');
+SELECT "LTRIM"('bakery', 'abc')
+!explain-validated-on calcite
+
+# MD5
+-- returns 'f7415e33f972c03abd4f3fed36748f7a'
+select md5('Amazon Redshift');
+EXPR$0
+f7415e33f972c03abd4f3fed36748f7a
+!ok
+
+# OCTET_LENGTH
+-- returns 9 (cf LENGTH)
+select octet_length('français');
+SELECT "OCTET_LENGTH"(u&'fran\00e7ais')
+!explain-validated-on calcite
+
+# POSITION is a synonym for STRPOS
+!if (position) {
+select position('fish', 'dogfish');
+!ok
+!}
+
+# QUOTE_IDENT
+-- returns '"ab cd"'
+select quote_ident('ab cd');
+SELECT "QUOTE_IDENT"('ab cd')
+!explain-validated-on calcite
+
+-- returns 'EMP'
+select quote_ident('EMP');
+SELECT "QUOTE_IDENT"('EMP')
+!explain-validated-on calcite
+
+# QUOTE_LITERAL
+-- returns "'it''s a literal'"
+select quote_literal('it''s a literal');
+SELECT "QUOTE_LITERAL"('it''s a literal')
+!explain-validated-on calcite
+
+# REGEXP_COUNT
+-- returns 8
+select regexp_count('abcdefghijklmnopqrstuvwxyz', '[a-z]{3}');
+SELECT "REGEXP_COUNT"('abcdefghijklmnopqrstuvwxyz', '[a-z]{3}')
+!explain-validated-on calcite
+
+# REGEXP_INSTR ( source_string, pattern [, position [, occurrence] [, option
+# [, parameters ] ] ] ] )
+select regexp_instr('The Home Depot Center', '[cC]ent(er|re)$');
+SELECT "REGEXP_INSTR"('The Home Depot Center', '[cC]ent(er|re)$')
+!explain-validated-on calcite
+
+# REGEXP_REPLACE ( source_string, pattern [, replace_string [ , position ] ] )
+select regexp_replace('DonecFri@semperpretiumneque.com', '@.*\\.(org|gov|com)$');
+SELECT "REGEXP_REPLACE"('DonecFri@semperpretiumneque.com', '@.*\\.(org|gov|com)$')
+!explain-validated-on calcite
+
+# REGEXP_SUBSTR ( source_string, pattern [, position [, occurrence
+# [, parameters ] ] ] )
+select regexp_substr('Suspendisse.tristique@nonnisiAenean.edu','@[^.]*');
+SELECT "REGEXP_SUBSTR"('Suspendisse.tristique@nonnisiAenean.edu', '@[^.]*')
+!explain-validated-on calcite
+
+# REPEAT
+select repeat('ba', 3);
+EXPR$0
+bababa
+!ok
+
+# REPLACE
+select replace('catching catfish', 'cat', 'dog');
+EXPR$0
+dogching dogfish
+!ok
+
+# REPLICATE is a synonym for REPEAT
+select replicate('ba', 3);
+SELECT "REPLICATE"('ba', 3)
+!explain-validated-on calcite
+
+# REVERSE
+select reverse('ab c');
+SELECT "REVERSE"('ab c')
+!explain-validated-on calcite
+
+# RTRIM
+-- returns 'baker'
+select rtrim('bakery', 'xyz');
+SELECT "RTRIM"('bakery', 'xyz')
+!explain-validated-on calcite
+
+# SPLIT_PART
+-- returns '03'
+select split_part('2008-03-05', '-', 2);
+SELECT "SPLIT_PART"('2008-03-05', '-', 2)
+!explain-validated-on calcite
+
+# STRPOS is a synonym for CHARINDEX and POSITION
+select strpos('fish', 'dogfish');
+SELECT "STRPOS"('fish', 'dogfish')
+!explain-validated-on calcite
+
+# STRTOL
+-- returns 2882343476
+select strtol('abcd1234',16);
+SELECT "STRTOL"('abcd1234', 16)
+!explain-validated-on calcite
+
+-- returns 53
+select strtol('110101', 2);
+SELECT "STRTOL"('110101', 2)
+!explain-validated-on calcite
+
+# SUBSTRING
+-- returns 'pill'
+select substring('caterpillar',6,4);
+EXPR$0
+pill
+!ok
+
+-- returns 'pillar'
+select substring('caterpillar',6,8);
+EXPR$0
+pillar
+!ok
+
+-- returns 'pill'
+select substring('caterpillar' from 6 for 4);
+EXPR$0
+pill
+!ok
+
+# TEXTLEN is a synonym for LEN
+select textlen('abc');
+SELECT "TEXTLEN"('abc')
+!explain-validated-on calcite
+
+# TRANSLATE ( expression, characters_to_replace, characters_to_substitute )
+-- returns 'most tin'
+select translate('mint tea', 'inea', 'osin');
+EXPR$0
+most tin
+!ok
+
+# TRIM( [ BOTH ] ['characters' FROM ] string ] )
+-- returns 'dog'
+select trim('"' FROM '"dog"');
+EXPR$0
+dog
+!ok
+
+# UPPER
+select upper('Pop');
+EXPR$0
+POP
+!ok
+
+# 10 JSON Functions
+
+# IS_VALID_JSON
+select is_valid_json('{"a":2}');
+SELECT "IS_VALID_JSON"('{"a":2}')
+!explain-validated-on calcite
+
+# IS_VALID_JSON_ARRAY
+-- returns true
+select is_valid_json_array('[]');
+SELECT "IS_VALID_JSON_ARRAY"('[]')
+!explain-validated-on calcite
+
+-- returns false
+select is_valid_json_array('{}');
+SELECT "IS_VALID_JSON_ARRAY"('{}')
+!explain-validated-on calcite
+
+# JSON_ARRAY_LENGTH('json_array' [, null_if_invalid ] )
+-- returns 3
+select json_array_length('[2,3,[4,5]]');
+SELECT "JSON_ARRAY_LENGTH"('[2,3,[4,5]]')
+!explain-validated-on calcite
+
+-- returns null
+select json_array_length('[2,3', true);
+SELECT "JSON_ARRAY_LENGTH"('[2,3', TRUE)
+!explain-validated-on calcite
+
+# JSON_EXTRACT_ARRAY_ELEMENT_TEXT('json string', pos [, null_if_invalid ] )
+-- returns '113'
+select json_extract_array_element_text('[111,112,113]', 2);
+SELECT "JSON_EXTRACT_ARRAY_ELEMENT_TEXT"('[111,112,113]', 2)
+!explain-validated-on calcite
+
+# JSON_EXTRACT_PATH_TEXT('json_string', 'path_elem' [,'path_elem'[, ...] ] [, null_if_invalid ] )
+-- returns 'star'
+select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}}','f4', 'f6');
+SELECT "JSON_EXTRACT_PATH_TEXT"('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}}', 'f4', 'f6')
+!explain-validated-on calcite
+
+# 11 Data Type Formatting Functions
+
+# CAST and CONVERT
+select cast(stddev_samp(sal) as dec(14, 2)) from emp;
+EXPR$0
+1182.503223516271873450023122131824493408203125
+!ok
+
+select 123.456::decimal(8,4);
+EXPR$0
+123.456
+!ok
+
+!if (position) {
+select convert(integer, comm) from emp;
+!ok
+!}
+
+select cast(comm as integer) from emp where deptno = 30 order by empno;
+EXPR$0
+300
+500
+1400
+null
+0
+null
+!ok
+
+select comm::integer from emp where deptno = 30 order by empno;
+EXPR$0
+300
+500
+1400
+null
+0
+null
+!ok
+
+# TO_CHAR
+-- returns '23:15:59'
+select to_char(timestamp '2009-12-31 23:15:59','HH24:MI:SS');
+SELECT "TO_CHAR"(TIMESTAMP '2009-12-31 23:15:59', 'HH24:MI:SS')
+!explain-validated-on calcite
+
+-- returns '125.80-'
+select to_char(-125.8, '999D99S');
+SELECT "TO_CHAR"(-125.8, '999D99S')
+!explain-validated-on calcite
+
+# TO_DATE
+-- returns '2001-10-02'
+select to_date ('02 Oct 2001', 'DD Mon YYYY');
+SELECT "TO_DATE"('02 Oct 2001', 'DD Mon YYYY')
+!explain-validated-on calcite
+
+# TO_NUMBER
+-- returns -12454.8
+select to_number('12,454.8-', '99G999D9S');
+SELECT "TO_NUMBER"('12,454.8-', '99G999D9S')
+!explain-validated-on calcite
+
+# 12 System Administration Functions
+
+# CHANGE_QUERY_PRIORITY(query_id, priority)
+select change_query_priority(1076, 'Critical');
+SELECT "CHANGE_QUERY_PRIORITY"(1076, 'Critical')
+!explain-validated-on calcite
+
+# CHANGE_SESSION_PRIORITY(pid, priority)
+select change_session_priority(30311, 'Lowest');
+SELECT "CHANGE_SESSION_PRIORITY"(30311, 'Lowest')
+!explain-validated-on calcite
+
+# CHANGE_USER_PRIORITY(user_name, priority)
+-- returns 'Succeeded to change user priority. Changed user (analysis_user) priority to lowest.'
+select change_user_priority('analysis_user', 'lowest');
+SELECT "CHANGE_USER_PRIORITY"('analysis_user', 'lowest')
+!explain-validated-on calcite
+
+# CURRENT_SETTING('parameter')
+-- returns 'unset'
+select current_setting('query_group');
+SELECT "CURRENT_SETTING"('query_group')
+!explain-validated-on calcite
+
+# PG_CANCEL_BACKEND(pid)
+select pg_cancel_backend(802);
+SELECT "PG_CANCEL_BACKEND"(802)
+!explain-validated-on calcite
+
+# PG_TERMINATE_BACKEND(pid)
+select pg_terminate_backend(8585);
+SELECT "PG_TERMINATE_BACKEND"(8585)
+!explain-validated-on calcite
+
+# SET_CONFIG('parameter', 'new_value' , is_local)
+-- returns 'test'
+select set_config('query_group', 'test', true);
+SELECT "SET_CONFIG"('query_group', 'test', TRUE)
+!explain-validated-on calcite
+
+# 13 System Information Functions
+
+# CURRENT_DATABASE
+!if (emptyParens) {
+select current_database();
+!ok
+!}
+
+# CURRENT_SCHEMA
+!if (emptyParens) {
+select current_schema();
+!ok
+!}
+
+# CURRENT_SCHEMAS(include_implicit)
+select current_schemas(false);
+SELECT "CURRENT_SCHEMAS"(FALSE)
+!explain-validated-on calcite
+
+# CURRENT_USER
+select current_user;
+CURRENT_USER
+sa
+!ok
+
+# CURRENT_USER_ID
+!if (sysdate) {
+select current_user_id;
+!ok
+!}
+
+# HAS_DATABASE_PRIVILEGE( [ user, ] database, privilege)
+select has_database_privilege('guest', 'tickit', 'temp');
+SELECT "HAS_DATABASE_PRIVILEGE"('guest', 'tickit', 'temp')
+!explain-validated-on calcite
+
+# HAS_SCHEMA_PRIVILEGE( [ user, ] schema, privilege)
+select has_schema_privilege('guest', 'public', 'create');
+SELECT "HAS_SCHEMA_PRIVILEGE"('guest', 'public', 'create')
+!explain-validated-on calcite
+
+# HAS_TABLE_PRIVILEGE( [ user, ] table, privilege)
+select has_table_privilege('guest', 'listing', 'select');
+SELECT "HAS_TABLE_PRIVILEGE"('guest', 'listing', 'select')
+!explain-validated-on calcite
+
+# PG_BACKEND_PID
+select pg_backend_pid();
+SELECT "PG_BACKEND_PID"()
+!explain-validated-on calcite
+
+# PG_GET_COLS
+select pg_get_cols('sales_vw');
+SELECT "PG_GET_COLS"('sales_vw')
+!explain-validated-on calcite
+
+# PG_GET_LATE_BINDING_VIEW_COLS
+select pg_get_late_binding_view_cols();
+SELECT "PG_GET_LATE_BINDING_VIEW_COLS"()
+!explain-validated-on calcite
+
+# PG_LAST_COPY_COUNT
+select pg_last_copy_count();
+SELECT "PG_LAST_COPY_COUNT"()
+!explain-validated-on calcite
+
+# PG_LAST_COPY_ID
+select pg_last_copy_id();
+SELECT "PG_LAST_COPY_ID"()
+!explain-validated-on calcite
+
+# PG_LAST_UNLOAD_ID
+select pg_last_unload_id();
+SELECT "PG_LAST_UNLOAD_ID"()
+!explain-validated-on calcite
+
+# PG_LAST_QUERY_ID
+select pg_last_query_id();
+SELECT "PG_LAST_QUERY_ID"()
+!explain-validated-on calcite
+
+# PG_LAST_UNLOAD_COUNT
+select pg_last_unload_count();
+SELECT "PG_LAST_UNLOAD_COUNT"()
+!explain-validated-on calcite
+
+# SESSION_USER
+select session_user;
+SESSION_USER
+sa
+!ok
+
+# SLICE_NUM
+# Returns an integer corresponding to the slice number in the cluster where the
+# data for a row is located.
+select slice_num();
+SELECT "SLICE_NUM"()
+!explain-validated-on calcite
+
+# USER
+# Synonym for CURRENT_USER
+select user;
+USER
+sa
+!ok
+
+# VERSION
+# Returns details about the currently installed release,
+# with specific Amazon Redshift version information at the end.
+select version();
+SELECT "VERSION"()
+!explain-validated-on calcite
+
+# End redshift.iq
diff --git a/babel/src/test/resources/sql/select.iq b/babel/src/test/resources/sql/select.iq
index d429c6c..4e9f822 100755
--- a/babel/src/test/resources/sql/select.iq
+++ b/babel/src/test/resources/sql/select.iq
@@ -15,7 +15,7 @@
# See the License for the specific language governing permissions and
# limitations under the License.
#
-!use scott
+!use scott-babel
!set outputformat mysql
# ORDER BY column not in SELECT clause
diff --git a/core/src/main/codegen/templates/Parser.jj b/core/src/main/codegen/templates/Parser.jj
index 917b92f..a063aa5 100644
--- a/core/src/main/codegen/templates/Parser.jj
+++ b/core/src/main/codegen/templates/Parser.jj
@@ -1626,6 +1626,11 @@ SqlLiteral JoinType() :
}
{
(
+ LOOKAHEAD(3) // required for "LEFT SEMI JOIN" in Babel
+<#list parser.joinTypes as method>
+ joinType = ${method}()
+ |
+</#list>
<JOIN> { joinType = JoinType.INNER; }
|
<INNER> <JOIN> { joinType = JoinType.INNER; }
@@ -1637,10 +1642,6 @@ SqlLiteral JoinType() :
<FULL> [ <OUTER> ] <JOIN> { joinType = JoinType.FULL; }
|
<CROSS> <JOIN> { joinType = JoinType.CROSS; }
-<#list parser.joinTypes as method>
- |
- joinType = ${method}()
-</#list>
)
{
return joinType.symbol(getPos());
diff --git a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfig.java b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfig.java
index 1fef597..fdc3379 100644
--- a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfig.java
+++ b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfig.java
@@ -76,6 +76,8 @@ public interface CalciteConnectionConfig extends ConnectionConfig {
String locale();
/** @see CalciteConnectionProperty#TYPE_COERCION */
boolean typeCoercion();
+ /** @see CalciteConnectionProperty#LENIENT_OPERATOR_LOOKUP */
+ boolean lenientOperatorLookup();
}
// End CalciteConnectionConfig.java
diff --git a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
index 779a84b..8353dc5 100644
--- a/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
+++ b/core/src/main/java/org/apache/calcite/config/CalciteConnectionConfigImpl.java
@@ -174,6 +174,11 @@ public class CalciteConnectionConfigImpl extends ConnectionConfigImpl
return CalciteConnectionProperty.TYPE_COERCION.wrap(properties)
.getBoolean();
}
+
+ public boolean lenientOperatorLookup() {
+ return CalciteConnectionProperty.LENIENT_OPERATOR_LOOKUP.wrap(properties)
+ .getBoolean();
+ }
}
// End CalciteConnectionConfigImpl.java
diff --git a/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java b/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
index 92abf9a..876d0ea 100644
--- a/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
+++ b/core/src/main/java/org/apache/calcite/config/CalciteConnectionProperty.java
@@ -155,7 +155,11 @@ public enum CalciteConnectionProperty implements ConnectionProperty {
/** Whether to make implicit type coercion when type mismatch
* for validation, default true. */
- TYPE_COERCION("typeCoercion", Type.BOOLEAN, true, false);
+ TYPE_COERCION("typeCoercion", Type.BOOLEAN, true, false),
+
+ /** Whether to make create implicit functions if functions do not exist
+ * in the operator table, default false. */
+ LENIENT_OPERATOR_LOOKUP("lenientOperatorLookup", Type.BOOLEAN, false, false);
private final String camelName;
private final Type type;
diff --git a/core/src/main/java/org/apache/calcite/sql/SqlFunction.java b/core/src/main/java/org/apache/calcite/sql/SqlFunction.java
index 0a0c40b..05a910e 100644
--- a/core/src/main/java/org/apache/calcite/sql/SqlFunction.java
+++ b/core/src/main/java/org/apache/calcite/sql/SqlFunction.java
@@ -290,17 +290,17 @@ public class SqlFunction extends SqlOperator {
break validCoercionType;
}
}
- // if function doesn't exist within operator table and known function
- // handling is turned off then create a more permissive function
- if (function == null && validator.isLenientOperatorLookup()) {
- final SqlFunction x = (SqlFunction) call.getOperator();
- final SqlIdentifier identifier =
- Util.first(x.getSqlIdentifier(),
- new SqlIdentifier(x.getName(), SqlParserPos.ZERO));
- function = new SqlUnresolvedFunction(identifier, null,
- null, OperandTypes.VARIADIC, null, x.getFunctionType());
- break validCoercionType;
- }
+ }
+ // if function doesn't exist within operator table and known function
+ // handling is turned off then create a more permissive function
+ if (function == null && validator.isLenientOperatorLookup()) {
+ final SqlFunction x = (SqlFunction) call.getOperator();
+ final SqlIdentifier identifier =
+ Util.first(x.getSqlIdentifier(),
+ new SqlIdentifier(x.getName(), SqlParserPos.ZERO));
+ function = new SqlUnresolvedFunction(identifier, null,
+ null, OperandTypes.VARIADIC, null, x.getFunctionType());
+ break validCoercionType;
}
throw validator.handleUnresolvedFunction(call, this, argTypes,
argNames);
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
index 1553646..3540eec 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/SqlValidatorImpl.java
@@ -328,7 +328,8 @@ public class SqlValidatorImpl implements SqlValidatorWithHints {
groupFinder = new AggFinder(opTab, false, false, true, null, nameMatcher);
aggOrOverOrGroupFinder = new AggFinder(opTab, true, true, true, null,
nameMatcher);
- this.lenientOperatorLookup = false;
+ this.lenientOperatorLookup = catalogReader.getConfig() != null
+ && catalogReader.getConfig().lenientOperatorLookup();
this.enableTypeCoercion = catalogReader.getConfig() == null
|| catalogReader.getConfig().typeCoercion();
this.typeCoercion = TypeCoercions.getTypeCoercion(this, conformance);