You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@ignite.apache.org by tl...@apache.org on 2021/07/07 12:09:32 UTC
[ignite] branch sql-calcite updated: IGNITE-14953 Calcite. Sort out
test scripts 'function' (#9194)
This is an automated email from the ASF dual-hosted git repository.
tledkov pushed a commit to branch sql-calcite
in repository https://gitbox.apache.org/repos/asf/ignite.git
The following commit(s) were added to refs/heads/sql-calcite by this push:
new e48e232 IGNITE-14953 Calcite. Sort out test scripts 'function' (#9194)
e48e232 is described below
commit e48e2323210d957ac4e51cb7416bbd756232f8f4
Author: Taras Ledkov <tl...@gridgain.com>
AuthorDate: Wed Jul 7 15:08:57 2021 +0300
IGNITE-14953 Calcite. Sort out test scripts 'function' (#9194)
---
.../query/calcite/logical/SqlScriptRunner.java | 36 +-
.../calcite/src/test/sql/function/blob/base64.test | 76 +--
.../calcite/src/test/sql/function/blob/encode.test | 37 -
.../src/test/sql/function/date/date_part.test | 170 +++++
...{date_part_stats.test => date_part.test_ignore} | 91 +--
.../src/test/sql/function/date/test_date_part.test | 135 +---
...t_date_part.test => test_date_part.test_ignore} | 17 +-
.../test/sql/function/date/test_date_trunc.test | 143 ----
.../src/test/sql/function/date/test_extract.test | 47 +-
.../sql/function/date/test_extract.test_ignore | 113 ++++
.../sql/function/date/test_extract_edge_cases.test | 279 --------
...es.test => test_extract_edge_cases.test_ignore} | 2 +
.../test/sql/function/date/test_extract_month.test | 635 +----------------
.../function/date/test_extract_month.test_ignore | 753 +++++++++++++++++++++
.../test/sql/function/date/test_extract_year.test | 9 +-
.../src/test/sql/function/date/test_strftime.test | 101 ---
.../function/date/test_strftime_exhaustive.test | 354 ----------
.../test/sql/function/generic/test_between.test | 21 +-
.../src/test/sql/function/generic/test_case.test | 11 +-
.../src/test/sql/function/generic/test_if.test | 32 -
.../test/sql/function/generic/test_if_null.test | 24 -
.../src/test/sql/function/generic/test_in.test | 87 ++-
...arge_in.test_slow => test_large_in.test_ignore} | 4 +-
.../sql/function/generic/test_least_greatest.test | 18 +-
.../test/sql/function/generic/test_null_if.test | 23 +-
.../src/test/sql/function/generic/test_set.test | 39 --
.../src/test/sql/function/generic/test_stats.test | 64 --
.../sql/function/generic/test_table_param.test | 36 -
.../test/sql/function/interval/test_date_part.test | 75 --
...{test_extract.test => test_extract.test_ignore} | 4 +-
.../src/test/sql/function/nested/test_lambda.test | 44 --
.../test/sql/function/numeric/test_bit_count.test | 20 -
.../test/sql/function/numeric/test_factorial.test | 52 --
.../test/sql/function/numeric/test_floor_ceil.test | 29 +-
.../src/test/sql/function/numeric/test_gamma.test | 109 ---
.../sql/function/numeric/test_invalid_math.test | 27 +-
.../src/test/sql/function/numeric/test_mod.test | 15 +-
.../test/sql/function/numeric/test_pg_math.test | 36 +-
.../src/test/sql/function/numeric/test_pow.test | 15 +-
.../src/test/sql/function/numeric/test_random.test | 87 +--
.../src/test/sql/function/numeric/test_round.test | 7 +-
.../src/test/sql/function/numeric/test_trigo.test | 63 +-
.../sql/function/numeric/test_type_resolution.test | 3 -
.../src/test/sql/function/numeric/test_unary.test | 3 -
.../sql/function/operator/test_arithmetic.test | 87 ++-
.../operator/test_arithmetic_sqllogic.test | 3 -
.../sql/function/operator/test_bitwise_ops.test | 68 --
.../function/operator/test_bitwise_ops_types.test | 103 ---
.../sql/function/operator/test_comparison.test | 23 +-
.../sql/function/operator/test_conjunction.test | 93 ++-
.../calcite/src/test/sql/function/string/md5.test | 8 +-
...down.test => regex_filter_pushdown.test_ignore} | 6 +-
...egex_replace.test => regex_replace.test_ignore} | 4 +-
...{regex_search.test => regex_search.test_ignore} | 4 +-
.../test/sql/function/string/strip_accents.test | 37 -
.../src/test/sql/function/string/test_ascii.test | 3 -
.../test/sql/function/string/test_bit_length.test | 52 --
.../test/sql/function/string/test_caseconvert.test | 66 +-
...seconvert.test => test_caseconvert.test_ignore} | 72 +-
...icode.test => test_complex_unicode.test_ignore} | 4 +-
.../src/test/sql/function/string/test_concat.test | 22 +-
.../sql/function/string/test_concat_function.test | 26 +-
.../test/sql/function/string/test_concat_ws.test | 158 -----
.../test/sql/function/string/test_contains.test | 154 -----
.../sql/function/string/test_contains_utf8.test | 85 ---
.../src/test/sql/function/string/test_format.test | 95 ---
.../src/test/sql/function/string/test_glob.test | 245 -------
.../src/test/sql/function/string/test_ilike.test | 136 ----
.../src/test/sql/function/string/test_instr.test | 110 ---
.../test/sql/function/string/test_instr_utf8.test | 85 ---
.../src/test/sql/function/string/test_jaccard.test | 144 ----
.../src/test/sql/function/string/test_left.test | 48 +-
.../{test_left.test => test_left.test_ignore} | 20 +-
.../src/test/sql/function/string/test_length.test | 9 +-
.../{test_length.test => test_length.test_ignore} | 8 +-
.../test/sql/function/string/test_levenshtein.test | 273 --------
.../src/test/sql/function/string/test_like.test | 115 ++--
.../test/sql/function/string/test_like_escape.test | 35 +-
...ke_escape.test => test_like_escape.test_ignore} | 33 +-
.../test/sql/function/string/test_mismatches.test | 178 -----
.../src/test/sql/function/string/test_pad.test | 102 ---
.../src/test/sql/function/string/test_prefix.test | 241 -------
.../src/test/sql/function/string/test_printf.test | 185 -----
.../src/test/sql/function/string/test_repeat.test | 19 +-
.../{test_repeat.test => test_repeat.test_ignore} | 14 +-
...{test_replace.test => test_replace.test_ignore} | 6 +-
.../src/test/sql/function/string/test_reverse.test | 19 +-
...{test_reverse.test => test_reverse.test_ignore} | 14 +-
.../src/test/sql/function/string/test_right.test | 63 +-
.../{test_right.test => test_right.test_ignore} | 32 +-
.../test/sql/function/string/test_similar_to.test | 70 +-
...similar_to.test => test_similar_to.test_ignore} | 41 +-
.../function/string/test_string_split.test_slow | 360 ----------
.../test/sql/function/string/test_substring.test | 138 +---
...t_substring.test => test_substring.test_ignore} | 82 +--
...g_utf8.test => test_substring_utf8.test_ignore} | 4 +-
.../src/test/sql/function/string/test_suffix.test | 232 -------
.../src/test/sql/function/string/test_trim.test | 58 +-
.../src/test/sql/function/string/test_unicode.test | 52 --
.../src/test/sql/function/time/test_date_part.test | 100 ---
.../src/test/sql/function/time/test_extract.test | 40 +-
...{test_extract.test => test_extract.test_ignore} | 20 +-
.../test/sql/function/time/test_extract_stats.test | 46 --
.../src/test/sql/function/timestamp/age.test | 140 ----
.../test/sql/function/timestamp/current_time.test | 6 -
.../function/timestamp/current_time.test_ignore | 13 +
.../src/test/sql/function/timestamp/epoch.test | 14 -
.../test/sql/function/timestamp/test_extract.test | 45 +-
.../function/timestamp/test_extract.test_ignore | 101 +++
.../sql/function/timestamp/test_extract_ms.test | 22 +-
.../function/timestamp/test_extract_ms.test_ignore | 35 +
.../src/test/sql/function/timestamp/test_now.test | 36 -
.../timestamp/test_strftime_timestamp.test | 336 ---------
.../test/sql/function/timestamp/test_strptime.test | 199 ------
114 files changed, 1935 insertions(+), 7508 deletions(-)
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/SqlScriptRunner.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/SqlScriptRunner.java
index 291c1ac..5ef9035 100644
--- a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/SqlScriptRunner.java
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/SqlScriptRunner.java
@@ -27,6 +27,7 @@ import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
+import java.util.Locale;
import java.util.Map;
import java.util.NoSuchElementException;
import java.util.Set;
@@ -137,12 +138,19 @@ public class SqlScriptRunner {
/** */
String nextLine() throws IOException {
+ String ret = nextLineWithoutTrim();
+
+ return ret == null ? null : ret.trim();
+ }
+
+ /** */
+ String nextLineWithoutTrim() throws IOException {
if (r.ready()) {
String s = r.readLine();
lineNum++;
- return s.trim();
+ return s;
}
return null;
@@ -451,7 +459,7 @@ public class SqlScriptRunner {
}
// Read expected results
- String s = script.nextLine();
+ String s = script.nextLineWithoutTrim();
Matcher m = HASHING_PTRN.matcher(s);
if (m.matches()) {
@@ -501,7 +509,7 @@ public class SqlScriptRunner {
+ script.positionDescription() + "[row=\"" + s + "\", types=" + resTypes + ']', e);
}
- s = script.nextLine();
+ s = script.nextLineWithoutTrim();
}
}
}
@@ -530,7 +538,8 @@ public class SqlScriptRunner {
private void checkResultTuples(List<List<?>> res) {
if (expectedRes.size() != res.size()) {
throw new AssertionError("Invalid results rows count at: " + posDesc +
- ". [expected=" + expectedRes + ", actual=" + res + ']');
+ ". [expectedRows=" + expectedRes.size() + ", actualRows=" + res.size() +
+ ", expected=" + expectedRes + ", actual=" + res + ']');
}
for (int i = 0; i < expectedRes.size(); ++i) {
@@ -556,15 +565,22 @@ public class SqlScriptRunner {
throw new AssertionError(msg);
if (actual instanceof Number) {
- BigDecimal actDec = new BigDecimal(String.valueOf(actual));
- BigDecimal expDec = new BigDecimal(expectedStr);
+ if ("NaN".equals(expectedStr) || expectedStr.endsWith("Infinity")) {
+ if (!expectedStr.equals(String.valueOf(actual)))
+ throw new AssertionError(msg);
+ }
+ else {
+ BigDecimal actDec = new BigDecimal(String.valueOf(actual));
+ BigDecimal expDec = new BigDecimal(expectedStr);
- if (actDec.compareTo(expDec) != 0)
- throw new AssertionError(msg);
+ if (actDec.compareTo(expDec) != 0)
+ throw new AssertionError(msg);
+ }
}
else {
- if (!String.valueOf(expectedStr).equals(String.valueOf(actual)))
- throw new AssertionError(msg);
+ if (!String.valueOf(expectedStr).equals(String.valueOf(actual)) &&
+ !("(empty)".equals(expectedStr) && String.valueOf(actual).isEmpty()))
+ throw new AssertionError(msg);
}
}
diff --git a/modules/calcite/src/test/sql/function/blob/base64.test b/modules/calcite/src/test/sql/function/blob/base64.test
index 465573a..da29395 100644
--- a/modules/calcite/src/test/sql/function/blob/base64.test
+++ b/modules/calcite/src/test/sql/function/blob/base64.test
@@ -2,85 +2,15 @@
# description: Test blob base64 functions
# group: [blob]
-statement ok
-PRAGMA enable_verification
-
-# test base64 encoding
-query I
-SELECT base64(encode(''))
-----
-(empty)
-
-query I
-SELECT base64(encode('a'))
-----
-YQ==
-
-query I
-SELECT base64(encode('ab'))
-----
-YWI=
-
-query I
-SELECT base64(encode('abc'))
-----
-YWJj
-
-query I
-SELECT base64(encode('üäabcdef'))
-----
-w7zDpGFiY2RlZg==
-
-query I
-SELECT base64(encode('iJWERiuhjruhwuiehr8493231'))
-----
-aUpXRVJpdWhqcnVod3VpZWhyODQ5MzIzMQ==
-
-query I
-SELECT base64(encode('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890'))
-----
-YWJjZGVmZ2hpamtsbW5vcHFyc3R1dnd4eXpBQkNERUZHSElKS0xNTk9QUVJTVFVWV1hZWjEyMzQ1Njc4OTA=
-
-query I
-SELECT to_base64(encode('base64 encoded string'));
-----
-YmFzZTY0IGVuY29kZWQgc3RyaW5n
-
-# test base64 decoding round-trip
-query I
-SELECT from_base64(base64(encode('')))
-----
-(empty)
-
-query I
-SELECT from_base64(base64(encode('a')))
-----
-a
-
-query I
-SELECT from_base64(base64(encode('ab')))
-----
-ab
-
-query I
-SELECT from_base64(base64(encode('abc')))
-----
-abc
-
-query I
-SELECT from_base64(base64(encode('iJWERiuhjruhwuiehr8493231')))
-----
-iJWERiuhjruhwuiehr8493231
-
query I
-SELECT from_base64(base64(encode('abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890')))
+SELECT to_base64(NULL)
----
-abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890
+NULL
query I
select from_base64('AAAA');
----
-\x00\x00\x00
+000000
# malformed base64
# must be multiple of 4
diff --git a/modules/calcite/src/test/sql/function/blob/encode.test b/modules/calcite/src/test/sql/function/blob/encode.test
deleted file mode 100644
index 4103b06..0000000
--- a/modules/calcite/src/test/sql/function/blob/encode.test
+++ /dev/null
@@ -1,37 +0,0 @@
-# name: test/sql/function/blob/encode.test
-# description: Test blob encode/decode functions
-# group: [blob]
-
-statement ok
-PRAGMA enable_verification
-
-# test basic encode/decode usage
-query I
-SELECT encode('ü')
-----
-\xC3\xBC
-
-query I
-SELECT decode(encode('ü'))
-----
-ü
-
-query I
-SELECT decode('\xF0\x9F\xA6\x86'::BLOB)
-----
-🦆
-
-# test invalid decodes
-statement error
-SELECT decode('\xFF'::BLOB)
-
-statement error
-SELECT decode('\x00'::BLOB)
-
-query I
-SELECT decode(encode(a)) || a from (values ('hello'), ('world')) tbl(a);
-----
-hellohello
-worldworld
-
-
diff --git a/modules/calcite/src/test/sql/function/date/date_part.test b/modules/calcite/src/test/sql/function/date/date_part.test
new file mode 100644
index 0000000..57ad833
--- /dev/null
+++ b/modules/calcite/src/test/sql/function/date/date_part.test
@@ -0,0 +1,170 @@
+# name: test/sql/function/date/date_part_stats.test
+# description: Test date part stats on empty table
+# group: [date]
+
+statement ok
+CREATE TABLE dates(d DATE);
+
+# test stats propagation from empty table
+query I
+SELECT EXTRACT(year FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(month FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(day FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(decade FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(century FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(millennium FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(second FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(minute FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(hour FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(epoch FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(dow FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(isodow FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(week FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(doy FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(quarter FROM d) FROM dates;
+----
+
+query I
+SELECT DAYOFMONTH(d) FROM dates;
+----
+
+query I
+SELECT DAYOFWEEK(d) FROM dates;
+----
+
+statement ok
+INSERT INTO dates VALUES (DATE '1992-01-01'), (DATE '2000-12-31');
+
+query I
+SELECT EXTRACT(YEAR FROM d) as y FROM dates ORDER BY y;
+----
+1992
+2000
+
+query I
+SELECT EXTRACT(month FROM d) as m FROM dates ORDER BY m;
+----
+1
+12
+
+query I
+SELECT EXTRACT(day FROM d) as d FROM dates ORDER BY d;
+----
+1
+31
+
+query I
+SELECT EXTRACT(decade FROM d) as d FROM dates ORDER BY d;
+----
+199
+200
+
+query I
+SELECT EXTRACT(century FROM d) as c FROM dates ORDER BY c;
+----
+20
+20
+
+query I
+SELECT EXTRACT(millennium FROM d) as m FROM dates ORDER BY m;
+----
+2
+2
+
+query I
+SELECT EXTRACT(second FROM d) as s FROM dates ORDER BY s;
+----
+0
+0
+
+query I
+SELECT EXTRACT(minute FROM d) as m FROM dates ORDER BY m;
+----
+0
+0
+
+query I
+SELECT EXTRACT(hour FROM d) h FROM dates;
+----
+0
+0
+
+query I
+SELECT EXTRACT(dow FROM d) as d FROM dates ORDER BY d;
+----
+1
+4
+
+query I
+SELECT EXTRACT(isodow FROM d) as d FROM dates ORDER BY d;
+----
+3
+7
+
+query I
+SELECT EXTRACT(week FROM d) as w FROM dates ORDER BY w;
+----
+1
+52
+
+query I
+SELECT EXTRACT(doy FROM d) as d FROM dates ORDER BY d;
+----
+1
+366
+
+query I
+SELECT EXTRACT(quarter FROM d) as q FROM dates ORDER BY q;
+----
+1
+4
+
+
+query I
+SELECT DAYOFMONTH(d) as d FROM dates ORDER BY d;
+----
+1
+31
+
diff --git a/modules/calcite/src/test/sql/function/date/date_part_stats.test b/modules/calcite/src/test/sql/function/date/date_part.test_ignore
similarity index 57%
rename from modules/calcite/src/test/sql/function/date/date_part_stats.test
rename to modules/calcite/src/test/sql/function/date/date_part.test_ignore
index d2daca2..4e0ce92 100644
--- a/modules/calcite/src/test/sql/function/date/date_part_stats.test
+++ b/modules/calcite/src/test/sql/function/date/date_part.test_ignore
@@ -1,9 +1,7 @@
# name: test/sql/function/date/date_part_stats.test
# description: Test date part stats on empty table
# group: [date]
-
-statement ok
-PRAGMA enable_verification
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14955
statement ok
CREATE TABLE dates(d DATE);
@@ -94,90 +92,98 @@ SELECT WEEKOFYEAR(d) FROM dates;
----
statement ok
-PRAGMA disable_verification
-
-statement ok
INSERT INTO dates VALUES (DATE '1992-01-01'), (DATE '2000-12-31');
query I
-SELECT stats(EXTRACT(YEAR FROM d)) FROM dates LIMIT 1;
+SELECT EXTRACT(YEAR FROM d) as y FROM dates ORDER BY y;
----
-<REGEX>:.*1992.*2000.*
+1992
+2000
query I
-SELECT stats(EXTRACT(month FROM d)) FROM dates LIMIT 1;
+SELECT EXTRACT(month FROM d) as m FROM dates ORDER BY m;
----
-<REGEX>:.*1.*12.*
+1
+12
query I
-SELECT stats(EXTRACT(day FROM d)) FROM dates LIMIT 1;
+SELECT EXTRACT(day FROM d) as d FROM dates ORDER BY d;
----
-<REGEX>:.*1.*31.*
+1
+31
query I
-SELECT stats(EXTRACT(decade FROM d)) FROM dates LIMIT 1;
+SELECT EXTRACT(decade FROM d) as d FROM dates ORDER BY d;
----
-<REGEX>:.*199.*200.*
+199
+200
query I
-SELECT stats(EXTRACT(century FROM d)) FROM dates LIMIT 1;
+SELECT EXTRACT(century FROM d) as c FROM dates ORDER BY c;
----
-<REGEX>:.*20.*20.*
+20
+20
query I
-SELECT stats(EXTRACT(millennium FROM d)) FROM dates LIMIT 1;
+SELECT EXTRACT(millennium FROM d) as m FROM dates ORDER BY m;
----
-<REGEX>:.*2.*2.*
+2
+2
query I
-SELECT stats(EXTRACT(microseconds FROM d)) FROM dates LIMIT 1;
+SELECT EXTRACT(second FROM d) as s FROM dates ORDER BY s;
----
-<REGEX>:.*0.*60000000.*
+0
+0
query I
-SELECT stats(EXTRACT(milliseconds FROM d)) FROM dates LIMIT 1;
+SELECT EXTRACT(minute FROM d) as m FROM dates ORDER BY m;
----
-<REGEX>:.*0.*60000.*
+0
+0
query I
-SELECT stats(EXTRACT(second FROM d)) FROM dates LIMIT 1;
+SELECT EXTRACT(hour FROM d) h FROM dates;
----
-<REGEX>:.*0.*60.*
+0
+0
query I
-SELECT stats(EXTRACT(minute FROM d)) FROM dates LIMIT 1;
+SELECT EXTRACT(dow FROM d) as d FROM dates ORDER BY d;
----
-<REGEX>:.*0.*60.*
+1
+4
query I
-SELECT stats(EXTRACT(hour FROM d)) FROM dates LIMIT 1;
+SELECT EXTRACT(isodow FROM d) as d FROM dates ORDER BY d;
----
-<REGEX>:.*0.*24.*
+3
+7
query I
-SELECT stats(EXTRACT(dow FROM d)) FROM dates LIMIT 1;
+SELECT EXTRACT(week FROM d) as w FROM dates ORDER BY w;
----
-<REGEX>:.*0.*6.*
+1
+52
query I
-SELECT stats(EXTRACT(isodow FROM d)) FROM dates LIMIT 1;
+SELECT EXTRACT(doy FROM d) as d FROM dates ORDER BY d;
----
-<REGEX>:.*1.*7.*
+1
+366
query I
-SELECT stats(EXTRACT(week FROM d)) FROM dates LIMIT 1;
+SELECT EXTRACT(quarter FROM d) as q FROM dates ORDER BY q;
----
-<REGEX>:.*1.*54.*
+1
+4
-query I
-SELECT stats(EXTRACT(doy FROM d)) FROM dates LIMIT 1;
-----
-<REGEX>:.*1.*366.*
query I
-SELECT stats(EXTRACT(quarter FROM d)) FROM dates LIMIT 1;
+SELECT DAYOFMONTH(d) as d FROM dates ORDER BY d;
----
-<REGEX>:.*1.*4.*
+1
+31
query I
SELECT stats(YEARWEEK(d)) FROM dates LIMIT 1;
@@ -185,11 +191,6 @@ SELECT stats(YEARWEEK(d)) FROM dates LIMIT 1;
<REGEX>:.*199201.*200052.*
query I
-SELECT stats(DAYOFMONTH(d)) FROM dates LIMIT 1;
-----
-<REGEX>:.*1.*31.*
-
-query I
SELECT stats(WEEKDAY(d)) FROM dates LIMIT 1;
----
<REGEX>:.*0.*6.*
diff --git a/modules/calcite/src/test/sql/function/date/test_date_part.test b/modules/calcite/src/test/sql/function/date/test_date_part.test
index fe41122..f3c067d 100644
--- a/modules/calcite/src/test/sql/function/date/test_date_part.test
+++ b/modules/calcite/src/test/sql/function/date/test_date_part.test
@@ -3,86 +3,11 @@
# group: [date]
statement ok
-PRAGMA enable_verification
-
-statement ok
CREATE TABLE dates(d DATE, s VARCHAR);
statement ok
INSERT INTO dates VALUES ('1992-01-01', 'year'), ('1992-03-03', 'month'), ('1992-05-05', 'day');
-# test date_part with different combinations of constant/non-constant columns
-query I
-SELECT date_part(NULL::VARCHAR, NULL::TIMESTAMP) FROM dates;
-----
-NULL
-NULL
-NULL
-
-query I
-SELECT date_part(s, NULL::TIMESTAMP) FROM dates;
-----
-NULL
-NULL
-NULL
-
-# dates
-query I
-SELECT date_part(NULL, d) FROM dates;
-----
-NULL
-NULL
-NULL
-
-query I
-SELECT date_part(s, DATE '1992-01-01') FROM dates;
-----
-1992
-1
-1
-
-query I
-SELECT date_part('year', d) FROM dates;
-----
-1992
-1992
-1992
-
-query I
-SELECT date_part(s, d) FROM dates;
-----
-1992
-3
-5
-
-# timestamps
-query I
-SELECT date_part(NULL, d::TIMESTAMP) FROM dates;
-----
-NULL
-NULL
-NULL
-
-query I
-SELECT date_part(s, TIMESTAMP '1992-01-01') FROM dates;
-----
-1992
-1
-1
-
-query I
-SELECT date_part('year', d::TIMESTAMP) FROM dates;
-----
-1992
-1992
-1992
-
-query I
-SELECT date_part(s, d::TIMESTAMP) FROM dates;
-----
-1992
-3
-5
# last_day
query TTT
@@ -91,78 +16,30 @@ SELECT LAST_DAY(DATE '1900-02-12'), LAST_DAY(DATE '1992-02-12'), LAST_DAY(DATE '
1900-02-28 1992-02-29 2000-02-29
query T
-SELECT LAST_DAY(d) FROM dates;
+SELECT LAST_DAY(d) FROM dates ORDER BY d;
----
1992-01-31
1992-03-31
1992-05-31
query T
-SELECT LAST_DAY(d::timestamp) FROM dates;
+SELECT LAST_DAY(d::timestamp) FROM dates ORDER BY d;
----
1992-01-31
1992-03-31
1992-05-31
-# monthname
-query T
-SELECT MONTHNAME(d) FROM dates;
-----
-January
-March
-May
-
-# dayname
-query T
-SELECT DAYNAME(d) FROM dates;
-----
-Wednesday
-Tuesday
-Tuesday
-
-# yearweek
-query I
-SELECT YEARWEEK(d) FROM dates;
-----
-199201
-199209
-199218
-
# aliases
query I
-SELECT DAYOFMONTH(d) FROM dates;
+SELECT DAYOFMONTH(d) FROM dates ORDER BY d;
----
1
3
5
query I
-SELECT WEEKDAY(d) FROM dates;
+SELECT DAYOFWEEK(d) FROM dates ORDER BY d;
----
+4
+3
3
-2
-2
-
-query I
-SELECT WEEKOFYEAR(d) FROM dates;
-----
-1
-9
-18
-
-query IIIIIIIIIIII
-select
-date_part('quarter', DATE '1992-01-20'),
-date_part('quarter', DATE '1992-02-20'),
-date_part('quarter', DATE '1992-03-20'),
-date_part('quarter', DATE '1992-04-20'),
-date_part('quarter', DATE '1992-05-20'),
-date_part('quarter', DATE '1992-06-20'),
-date_part('quarter', DATE '1992-07-20'),
-date_part('quarter', DATE '1992-08-20'),
-date_part('quarter', DATE '1992-09-20'),
-date_part('quarter', DATE '1992-10-20'),
-date_part('quarter', DATE '1992-11-20'),
-date_part('quarter', DATE '1992-12-20')
-----
-1 1 1 2 2 2 3 3 3 4 4 4
diff --git a/modules/calcite/src/test/sql/function/date/test_date_part.test b/modules/calcite/src/test/sql/function/date/test_date_part.test_ignore
similarity index 87%
copy from modules/calcite/src/test/sql/function/date/test_date_part.test
copy to modules/calcite/src/test/sql/function/date/test_date_part.test_ignore
index fe41122..94d1466 100644
--- a/modules/calcite/src/test/sql/function/date/test_date_part.test
+++ b/modules/calcite/src/test/sql/function/date/test_date_part.test_ignore
@@ -1,9 +1,8 @@
# name: test/sql/function/date/test_date_part.test
# description: DATE_PART test
# group: [date]
-
-statement ok
-PRAGMA enable_verification
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14954
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14955
statement ok
CREATE TABLE dates(d DATE, s VARCHAR);
@@ -91,14 +90,14 @@ SELECT LAST_DAY(DATE '1900-02-12'), LAST_DAY(DATE '1992-02-12'), LAST_DAY(DATE '
1900-02-28 1992-02-29 2000-02-29
query T
-SELECT LAST_DAY(d) FROM dates;
+SELECT LAST_DAY(d) FROM dates ORDER BY d;
----
1992-01-31
1992-03-31
1992-05-31
query T
-SELECT LAST_DAY(d::timestamp) FROM dates;
+SELECT LAST_DAY(d::timestamp) FROM dates ORDER BY d;
----
1992-01-31
1992-03-31
@@ -130,18 +129,18 @@ SELECT YEARWEEK(d) FROM dates;
# aliases
query I
-SELECT DAYOFMONTH(d) FROM dates;
+SELECT DAYOFMONTH(d) FROM dates ORDER BY d;
----
1
3
5
query I
-SELECT WEEKDAY(d) FROM dates;
+SELECT DAYOFWEEK(d) FROM dates ORDER BY d;
----
+4
+3
3
-2
-2
query I
SELECT WEEKOFYEAR(d) FROM dates;
diff --git a/modules/calcite/src/test/sql/function/date/test_date_trunc.test b/modules/calcite/src/test/sql/function/date/test_date_trunc.test
deleted file mode 100644
index 39cec97..0000000
--- a/modules/calcite/src/test/sql/function/date/test_date_trunc.test
+++ /dev/null
@@ -1,143 +0,0 @@
-# name: test/sql/function/date/test_date_trunc.test
-# description: Test date truncate functionality
-# group: [date]
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE dates(d DATE, s VARCHAR);
-
-statement ok
-CREATE TABLE timestamps(d TIMESTAMP, s VARCHAR);
-
-statement ok
-INSERT INTO dates VALUES ('1992-12-02', 'year'), ('1993-03-03', 'month'), ('1994-05-05', 'day');
-
-statement ok
-INSERT INTO timestamps VALUES ('1992-02-02 02:02:03', 'millennium'), ('1992-02-02 02:02:03', 'century'), ('1992-02-02 02:02:03', 'decade'), ('1992-02-02 02:02:03', 'year'), ('1992-02-02 02:02:03', 'quarter'), ('1992-02-02 02:02:03', 'month'), ('1992-02-02 02:02:03', 'week'), ('1992-02-02 02:02:03', 'day'), ('1992-02-02 02:02:03', 'hour'), ('1992-02-02 02:02:03', 'minute'), ('1992-02-02 02:02:03', 'second'), ('1992-02-02 02:02:03', 'milliseconds'), ('1992-02-02 02:02:03', 'microseconds');
-
-# test date_trunc with different combinations of constant/non-constant columns on both dates and timestamps
-query T
-SELECT date_trunc(NULL::VARCHAR, NULL::TIMESTAMP) FROM dates;
-----
-NULL
-NULL
-NULL
-
-query T
-SELECT date_trunc(s, NULL::TIMESTAMP) FROM dates;
-----
-NULL
-NULL
-NULL
-
-query T
-SELECT date_trunc(NULL, d) FROM dates;
-----
-NULL
-NULL
-NULL
-
-query T
-SELECT date_trunc(NULL::VARCHAR, NULL::TIMESTAMP) FROM timestamps LIMIT 3;
-----
-NULL
-NULL
-NULL
-
-query T
-SELECT date_trunc(s, NULL::TIMESTAMP) FROM timestamps LIMIT 3;
-----
-NULL
-NULL
-NULL
-
-query T
-SELECT date_trunc(NULL, d) FROM timestamps LIMIT 3;
-----
-NULL
-NULL
-NULL
-
-# dates should be cast to timestamp
-query T
-SELECT date_trunc('month', DATE '1992-02-02') FROM dates LIMIT 1;
-----
-1992-02-01 00:00:00
-
-query T
-SELECT date_trunc(s, d) FROM dates;
-----
-1992-01-01 00:00:00
-1993-03-01 00:00:00
-1994-05-05 00:00:00
-
-# Timestamps should return timestamp type
-query T
-SELECT date_trunc('minute', TIMESTAMP '1992-02-02 04:03:02') FROM timestamps LIMIT 1;
-----
-1992-02-02 04:03:00
-
-# Test all truncate operators on timestamps
-query T
-SELECT date_trunc(s, d) FROM timestamps;
-----
-1000-01-01 00:00:00
-1900-01-01 00:00:00
-1990-01-01 00:00:00
-1992-01-01 00:00:00
-1992-01-01 00:00:00
-1992-02-01 00:00:00
-1992-01-27 00:00:00
-1992-02-02 00:00:00
-1992-02-02 02:00:00
-1992-02-02 02:02:00
-1992-02-02 02:02:03
-1992-02-02 02:02:03
-1992-02-02 02:02:03
-
-# Redo previous test but with casting to date first
-query T
-SELECT date_trunc(s, CAST(d as DATE)) FROM timestamps;
-----
-1000-01-01 00:00:00
-1900-01-01 00:00:00
-1990-01-01 00:00:00
-1992-01-01 00:00:00
-1992-01-01 00:00:00
-1992-02-01 00:00:00
-1992-01-27 00:00:00
-1992-02-02 00:00:00
-1992-02-02 00:00:00
-1992-02-02 00:00:00
-1992-02-02 00:00:00
-1992-02-02 00:00:00
-1992-02-02 00:00:00
-
-# Test week operator special cases
-query T
-SELECT date_trunc('week', TIMESTAMP '2020-01-01 04:03:02') FROM timestamps LIMIT 1;
-----
-2019-12-30 00:00:00
-
-query T
-SELECT date_trunc('week', TIMESTAMP '2019-01-06 04:03:02') FROM timestamps LIMIT 1;
-----
-2018-12-31 00:00:00
-
-# Test quarter operator more thoroughly
-query T
-SELECT date_trunc('quarter', TIMESTAMP '2020-12-02 04:03:02') FROM timestamps LIMIT 1;
-----
-2020-10-01 00:00:00
-
-query T
-SELECT date_trunc('quarter', TIMESTAMP '2019-01-06 04:03:02') FROM timestamps LIMIT 1;
-----
-2019-01-01 00:00:00
-
-# Unknown specifier should fail
-statement error
-SELECT date_trunc('epoch', TIMESTAMP '2019-01-06 04:03:02') FROM timestamps LIMIT 1;
-
diff --git a/modules/calcite/src/test/sql/function/date/test_extract.test b/modules/calcite/src/test/sql/function/date/test_extract.test
index bd814a4..bce0759 100644
--- a/modules/calcite/src/test/sql/function/date/test_extract.test
+++ b/modules/calcite/src/test/sql/function/date/test_extract.test
@@ -3,112 +3,91 @@
# group: [date]
statement ok
-PRAGMA enable_verification
-
-statement ok
CREATE TABLE dates(i DATE)
statement ok
-INSERT INTO dates VALUES ('1993-08-14'), (NULL)
+INSERT INTO dates VALUES (DATE '1993-08-14'), (NULL)
# extract various parts of the date
# year
query I
-SELECT EXTRACT(year FROM i) FROM dates
+SELECT EXTRACT(year FROM i) FROM dates ORDER BY i
----
1993
NULL
# month
query I
-SELECT EXTRACT(month FROM i) FROM dates
+SELECT EXTRACT(month FROM i) FROM dates ORDER BY i
----
8
NULL
# day
query I
-SELECT EXTRACT(day FROM i) FROM dates
+SELECT EXTRACT(day FROM i) FROM dates ORDER BY i
----
14
NULL
# decade
query I
-SELECT EXTRACT(decade FROM i) FROM dates
+SELECT EXTRACT(decade FROM i) FROM dates ORDER BY i
----
199
NULL
# century
query I
-SELECT EXTRACT(century FROM i) FROM dates
+SELECT EXTRACT(century FROM i) FROM dates ORDER BY i
----
20
NULL
-# day of the week (Sunday = 0, Saturday = 6)
-query I
-SELECT EXTRACT(DOW FROM i) FROM dates
-----
-6
-NULL
-
# day of the year (1 - 365/366)
query I
-SELECT EXTRACT(DOY FROM i) FROM dates
+SELECT EXTRACT(DOY FROM i) FROM dates ORDER BY i
----
226
NULL
# epoch
query I
-SELECT EXTRACT(epoch FROM i) FROM dates
+SELECT EXTRACT(epoch FROM i) FROM dates ORDER BY i
----
745286400
NULL
# isodow (Monday = 1, Sunday = 7)
query I
-SELECT EXTRACT(ISODOW FROM i) FROM dates
+SELECT EXTRACT(ISODOW FROM i) FROM dates ORDER BY i
----
6
NULL
# millenium (change of millenium is January 1, X001)
query I
-SELECT EXTRACT(millennium FROM i) FROM dates
+SELECT EXTRACT(millennium FROM i) FROM dates ORDER BY i
----
2
NULL
# timestamp variants all give 0 for date
query I
-SELECT EXTRACT(second FROM i) FROM dates
-----
-0
-NULL
-
-query I
-SELECT EXTRACT(minute FROM i) FROM dates
+SELECT EXTRACT(second FROM i) FROM dates ORDER BY i
----
0
NULL
query I
-SELECT EXTRACT(hour FROM i) FROM dates
+SELECT EXTRACT(minute FROM i) FROM dates ORDER BY i
----
0
NULL
query I
-SELECT EXTRACT(milliseconds FROM i) FROM dates
+SELECT EXTRACT(hour FROM i) FROM dates ORDER BY i
----
0
NULL
-query I
-SELECT EXTRACT(microsecond FROM i) FROM dates
-----
-0
-NULL
diff --git a/modules/calcite/src/test/sql/function/date/test_extract.test_ignore b/modules/calcite/src/test/sql/function/date/test_extract.test_ignore
new file mode 100644
index 0000000..5987614
--- /dev/null
+++ b/modules/calcite/src/test/sql/function/date/test_extract.test_ignore
@@ -0,0 +1,113 @@
+# name: test/sql/function/date/test_extract.test
+# description: Extract function
+# group: [date]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14960
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14955
+
+statement ok
+CREATE TABLE dates(i DATE)
+
+statement ok
+INSERT INTO dates VALUES (DATE '1993-08-14'), (NULL)
+
+# extract various parts of the date
+# year
+query I
+SELECT EXTRACT(year FROM i) FROM dates ORDER BY i
+----
+1993
+NULL
+
+# month
+query I
+SELECT EXTRACT(month FROM i) FROM dates ORDER BY i
+----
+8
+NULL
+
+# day
+query I
+SELECT EXTRACT(day FROM i) FROM dates ORDER BY i
+----
+14
+NULL
+
+# decade
+query I
+SELECT EXTRACT(decade FROM i) FROM dates ORDER BY i
+----
+199
+NULL
+
+# century
+query I
+SELECT EXTRACT(century FROM i) FROM dates ORDER BY i
+----
+20
+NULL
+
+# day of the week (Sunday = 0, Saturday = 6)
+query I
+SELECT EXTRACT(DOW FROM i) FROM dates ORDER BY i
+----
+6
+NULL
+
+# day of the year (1 - 365/366)
+query I
+SELECT EXTRACT(DOY FROM i) FROM dates ORDER BY i
+----
+226
+NULL
+
+# epoch
+query I
+SELECT EXTRACT(epoch FROM i) FROM dates ORDER BY i
+----
+745286400
+NULL
+
+# isodow (Monday = 1, Sunday = 7)
+query I
+SELECT EXTRACT(ISODOW FROM i) FROM dates ORDER BY i
+----
+6
+NULL
+
+# millenium (change of millenium is January 1, X001)
+query I
+SELECT EXTRACT(millennium FROM i) FROM dates ORDER BY i
+----
+2
+NULL
+
+# timestamp variants all give 0 for date
+query I
+SELECT EXTRACT(second FROM i) FROM dates ORDER BY i
+----
+0
+NULL
+
+query I
+SELECT EXTRACT(minute FROM i) FROM dates ORDER BY i
+----
+0
+NULL
+
+query I
+SELECT EXTRACT(hour FROM i) FROM dates ORDER BY i
+----
+0
+NULL
+
+query I
+SELECT EXTRACT(milliseconds FROM i) FROM dates ORDER BY i
+----
+0
+NULL
+
+query I
+SELECT EXTRACT(microsecond FROM i) FROM dates ORDER BY i
+----
+0
+NULL
diff --git a/modules/calcite/src/test/sql/function/date/test_extract_edge_cases.test b/modules/calcite/src/test/sql/function/date/test_extract_edge_cases.test
index 1db3179..52211a7 100644
--- a/modules/calcite/src/test/sql/function/date/test_extract_edge_cases.test
+++ b/modules/calcite/src/test/sql/function/date/test_extract_edge_cases.test
@@ -24,84 +24,6 @@ SELECT EXTRACT(millennium FROM cast('2001-10-10' AS DATE));
----
3
-# check DOW
-# start from the epoch and go up/down, every time the day should go up/down
-# one as well
-query I
-SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) + 0);
-----
-4
-
-query I
-SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) - 0);
-----
-4
-
-query I
-SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) + 1);
-----
-5
-
-query I
-SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) - 1);
-----
-3
-
-query I
-SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) + 2);
-----
-6
-
-query I
-SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) - 2);
-----
-2
-
-query I
-SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) + 3);
-----
-0
-
-query I
-SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) - 3);
-----
-1
-
-query I
-SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) + 4);
-----
-1
-
-query I
-SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) - 4);
-----
-0
-
-query I
-SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) + 5);
-----
-2
-
-query I
-SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) - 5);
-----
-6
-
-query I
-SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) + 6);
-----
-3
-
-query I
-SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) - 6);
-----
-5
-
-query I
-SELECT EXTRACT(dow FROM cast('1793-05-26' AS DATE));
-----
-0
-
query I
SELECT EXTRACT(isodow FROM cast('1793-05-26' AS DATE));
----
@@ -138,204 +60,3 @@ SELECT EXTRACT(week FROM cast('2010-01-01' AS DATE));
----
53
-# every 7 days the week number should go up by 7
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 0);
-----
-1
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 7);
-----
-2
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 14);
-----
-3
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 21);
-----
-4
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 28);
-----
-5
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 35);
-----
-6
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 42);
-----
-7
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 49);
-----
-8
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 56);
-----
-9
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 63);
-----
-10
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 70);
-----
-11
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 77);
-----
-12
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 84);
-----
-13
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 91);
-----
-14
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 98);
-----
-15
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 105);
-----
-16
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 112);
-----
-17
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 119);
-----
-18
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 126);
-----
-19
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 133);
-----
-20
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 140);
-----
-21
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 147);
-----
-22
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 154);
-----
-23
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 161);
-----
-24
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 168);
-----
-25
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 175);
-----
-26
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 182);
-----
-27
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 189);
-----
-28
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 196);
-----
-29
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 203);
-----
-30
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 210);
-----
-31
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 217);
-----
-32
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 224);
-----
-33
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 231);
-----
-34
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 238);
-----
-35
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 245);
-----
-36
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 252);
-----
-37
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 259);
-----
-38
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 266);
-----
-39
-
-query I
-SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE) + 273);
-----
-40
-
diff --git a/modules/calcite/src/test/sql/function/date/test_extract_edge_cases.test b/modules/calcite/src/test/sql/function/date/test_extract_edge_cases.test_ignore
similarity index 97%
copy from modules/calcite/src/test/sql/function/date/test_extract_edge_cases.test
copy to modules/calcite/src/test/sql/function/date/test_extract_edge_cases.test_ignore
index 1db3179..e424159 100644
--- a/modules/calcite/src/test/sql/function/date/test_extract_edge_cases.test
+++ b/modules/calcite/src/test/sql/function/date/test_extract_edge_cases.test_ignore
@@ -1,6 +1,8 @@
# name: test/sql/function/date/test_extract_edge_cases.test
# description: Extract function edge cases
# group: [date]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14961
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14960
# century changes in the year 1
query I
diff --git a/modules/calcite/src/test/sql/function/date/test_extract_month.test b/modules/calcite/src/test/sql/function/date/test_extract_month.test
index fe2ed87..fe88fa8 100644
--- a/modules/calcite/src/test/sql/function/date/test_extract_month.test
+++ b/modules/calcite/src/test/sql/function/date/test_extract_month.test
@@ -2,11 +2,8 @@
# description: Extract month function
# group: [date]
-statement ok
-PRAGMA enable_verification
-
query II
-select date '1992-01-01' + interval (i) days, month(date '1992-01-01' + interval (i) days) from range(0, 366) tbl(i);
+select date '1992-01-01' + interval (x) days, month(date '1992-01-01' + interval (x) days) from table(system_range(0, 17));
----
1992-01-01 1
1992-01-02 1
@@ -26,357 +23,9 @@ select date '1992-01-01' + interval (i) days, month(date '1992-01-01' + interval
1992-01-16 1
1992-01-17 1
1992-01-18 1
-1992-01-19 1
-1992-01-20 1
-1992-01-21 1
-1992-01-22 1
-1992-01-23 1
-1992-01-24 1
-1992-01-25 1
-1992-01-26 1
-1992-01-27 1
-1992-01-28 1
-1992-01-29 1
-1992-01-30 1
-1992-01-31 1
-1992-02-01 2
-1992-02-02 2
-1992-02-03 2
-1992-02-04 2
-1992-02-05 2
-1992-02-06 2
-1992-02-07 2
-1992-02-08 2
-1992-02-09 2
-1992-02-10 2
-1992-02-11 2
-1992-02-12 2
-1992-02-13 2
-1992-02-14 2
-1992-02-15 2
-1992-02-16 2
-1992-02-17 2
-1992-02-18 2
-1992-02-19 2
-1992-02-20 2
-1992-02-21 2
-1992-02-22 2
-1992-02-23 2
-1992-02-24 2
-1992-02-25 2
-1992-02-26 2
-1992-02-27 2
-1992-02-28 2
-1992-02-29 2
-1992-03-01 3
-1992-03-02 3
-1992-03-03 3
-1992-03-04 3
-1992-03-05 3
-1992-03-06 3
-1992-03-07 3
-1992-03-08 3
-1992-03-09 3
-1992-03-10 3
-1992-03-11 3
-1992-03-12 3
-1992-03-13 3
-1992-03-14 3
-1992-03-15 3
-1992-03-16 3
-1992-03-17 3
-1992-03-18 3
-1992-03-19 3
-1992-03-20 3
-1992-03-21 3
-1992-03-22 3
-1992-03-23 3
-1992-03-24 3
-1992-03-25 3
-1992-03-26 3
-1992-03-27 3
-1992-03-28 3
-1992-03-29 3
-1992-03-30 3
-1992-03-31 3
-1992-04-01 4
-1992-04-02 4
-1992-04-03 4
-1992-04-04 4
-1992-04-05 4
-1992-04-06 4
-1992-04-07 4
-1992-04-08 4
-1992-04-09 4
-1992-04-10 4
-1992-04-11 4
-1992-04-12 4
-1992-04-13 4
-1992-04-14 4
-1992-04-15 4
-1992-04-16 4
-1992-04-17 4
-1992-04-18 4
-1992-04-19 4
-1992-04-20 4
-1992-04-21 4
-1992-04-22 4
-1992-04-23 4
-1992-04-24 4
-1992-04-25 4
-1992-04-26 4
-1992-04-27 4
-1992-04-28 4
-1992-04-29 4
-1992-04-30 4
-1992-05-01 5
-1992-05-02 5
-1992-05-03 5
-1992-05-04 5
-1992-05-05 5
-1992-05-06 5
-1992-05-07 5
-1992-05-08 5
-1992-05-09 5
-1992-05-10 5
-1992-05-11 5
-1992-05-12 5
-1992-05-13 5
-1992-05-14 5
-1992-05-15 5
-1992-05-16 5
-1992-05-17 5
-1992-05-18 5
-1992-05-19 5
-1992-05-20 5
-1992-05-21 5
-1992-05-22 5
-1992-05-23 5
-1992-05-24 5
-1992-05-25 5
-1992-05-26 5
-1992-05-27 5
-1992-05-28 5
-1992-05-29 5
-1992-05-30 5
-1992-05-31 5
-1992-06-01 6
-1992-06-02 6
-1992-06-03 6
-1992-06-04 6
-1992-06-05 6
-1992-06-06 6
-1992-06-07 6
-1992-06-08 6
-1992-06-09 6
-1992-06-10 6
-1992-06-11 6
-1992-06-12 6
-1992-06-13 6
-1992-06-14 6
-1992-06-15 6
-1992-06-16 6
-1992-06-17 6
-1992-06-18 6
-1992-06-19 6
-1992-06-20 6
-1992-06-21 6
-1992-06-22 6
-1992-06-23 6
-1992-06-24 6
-1992-06-25 6
-1992-06-26 6
-1992-06-27 6
-1992-06-28 6
-1992-06-29 6
-1992-06-30 6
-1992-07-01 7
-1992-07-02 7
-1992-07-03 7
-1992-07-04 7
-1992-07-05 7
-1992-07-06 7
-1992-07-07 7
-1992-07-08 7
-1992-07-09 7
-1992-07-10 7
-1992-07-11 7
-1992-07-12 7
-1992-07-13 7
-1992-07-14 7
-1992-07-15 7
-1992-07-16 7
-1992-07-17 7
-1992-07-18 7
-1992-07-19 7
-1992-07-20 7
-1992-07-21 7
-1992-07-22 7
-1992-07-23 7
-1992-07-24 7
-1992-07-25 7
-1992-07-26 7
-1992-07-27 7
-1992-07-28 7
-1992-07-29 7
-1992-07-30 7
-1992-07-31 7
-1992-08-01 8
-1992-08-02 8
-1992-08-03 8
-1992-08-04 8
-1992-08-05 8
-1992-08-06 8
-1992-08-07 8
-1992-08-08 8
-1992-08-09 8
-1992-08-10 8
-1992-08-11 8
-1992-08-12 8
-1992-08-13 8
-1992-08-14 8
-1992-08-15 8
-1992-08-16 8
-1992-08-17 8
-1992-08-18 8
-1992-08-19 8
-1992-08-20 8
-1992-08-21 8
-1992-08-22 8
-1992-08-23 8
-1992-08-24 8
-1992-08-25 8
-1992-08-26 8
-1992-08-27 8
-1992-08-28 8
-1992-08-29 8
-1992-08-30 8
-1992-08-31 8
-1992-09-01 9
-1992-09-02 9
-1992-09-03 9
-1992-09-04 9
-1992-09-05 9
-1992-09-06 9
-1992-09-07 9
-1992-09-08 9
-1992-09-09 9
-1992-09-10 9
-1992-09-11 9
-1992-09-12 9
-1992-09-13 9
-1992-09-14 9
-1992-09-15 9
-1992-09-16 9
-1992-09-17 9
-1992-09-18 9
-1992-09-19 9
-1992-09-20 9
-1992-09-21 9
-1992-09-22 9
-1992-09-23 9
-1992-09-24 9
-1992-09-25 9
-1992-09-26 9
-1992-09-27 9
-1992-09-28 9
-1992-09-29 9
-1992-09-30 9
-1992-10-01 10
-1992-10-02 10
-1992-10-03 10
-1992-10-04 10
-1992-10-05 10
-1992-10-06 10
-1992-10-07 10
-1992-10-08 10
-1992-10-09 10
-1992-10-10 10
-1992-10-11 10
-1992-10-12 10
-1992-10-13 10
-1992-10-14 10
-1992-10-15 10
-1992-10-16 10
-1992-10-17 10
-1992-10-18 10
-1992-10-19 10
-1992-10-20 10
-1992-10-21 10
-1992-10-22 10
-1992-10-23 10
-1992-10-24 10
-1992-10-25 10
-1992-10-26 10
-1992-10-27 10
-1992-10-28 10
-1992-10-29 10
-1992-10-30 10
-1992-10-31 10
-1992-11-01 11
-1992-11-02 11
-1992-11-03 11
-1992-11-04 11
-1992-11-05 11
-1992-11-06 11
-1992-11-07 11
-1992-11-08 11
-1992-11-09 11
-1992-11-10 11
-1992-11-11 11
-1992-11-12 11
-1992-11-13 11
-1992-11-14 11
-1992-11-15 11
-1992-11-16 11
-1992-11-17 11
-1992-11-18 11
-1992-11-19 11
-1992-11-20 11
-1992-11-21 11
-1992-11-22 11
-1992-11-23 11
-1992-11-24 11
-1992-11-25 11
-1992-11-26 11
-1992-11-27 11
-1992-11-28 11
-1992-11-29 11
-1992-11-30 11
-1992-12-01 12
-1992-12-02 12
-1992-12-03 12
-1992-12-04 12
-1992-12-05 12
-1992-12-06 12
-1992-12-07 12
-1992-12-08 12
-1992-12-09 12
-1992-12-10 12
-1992-12-11 12
-1992-12-12 12
-1992-12-13 12
-1992-12-14 12
-1992-12-15 12
-1992-12-16 12
-1992-12-17 12
-1992-12-18 12
-1992-12-19 12
-1992-12-20 12
-1992-12-21 12
-1992-12-22 12
-1992-12-23 12
-1992-12-24 12
-1992-12-25 12
-1992-12-26 12
-1992-12-27 12
-1992-12-28 12
-1992-12-29 12
-1992-12-30 12
-1992-12-31 12
query II
-select date '1993-01-01' + interval (i) days, month(date '1993-01-01' + interval (i) days) from range(0, 366) tbl(i);
+select date '1993-01-01' + interval (x) days, month(date '1993-01-01' + interval (x) days) from table(system_range(0, 85));
----
1993-01-01 1
1993-01-02 1
@@ -464,283 +113,3 @@ select date '1993-01-01' + interval (i) days, month(date '1993-01-01' + interval
1993-03-25 3
1993-03-26 3
1993-03-27 3
-1993-03-28 3
-1993-03-29 3
-1993-03-30 3
-1993-03-31 3
-1993-04-01 4
-1993-04-02 4
-1993-04-03 4
-1993-04-04 4
-1993-04-05 4
-1993-04-06 4
-1993-04-07 4
-1993-04-08 4
-1993-04-09 4
-1993-04-10 4
-1993-04-11 4
-1993-04-12 4
-1993-04-13 4
-1993-04-14 4
-1993-04-15 4
-1993-04-16 4
-1993-04-17 4
-1993-04-18 4
-1993-04-19 4
-1993-04-20 4
-1993-04-21 4
-1993-04-22 4
-1993-04-23 4
-1993-04-24 4
-1993-04-25 4
-1993-04-26 4
-1993-04-27 4
-1993-04-28 4
-1993-04-29 4
-1993-04-30 4
-1993-05-01 5
-1993-05-02 5
-1993-05-03 5
-1993-05-04 5
-1993-05-05 5
-1993-05-06 5
-1993-05-07 5
-1993-05-08 5
-1993-05-09 5
-1993-05-10 5
-1993-05-11 5
-1993-05-12 5
-1993-05-13 5
-1993-05-14 5
-1993-05-15 5
-1993-05-16 5
-1993-05-17 5
-1993-05-18 5
-1993-05-19 5
-1993-05-20 5
-1993-05-21 5
-1993-05-22 5
-1993-05-23 5
-1993-05-24 5
-1993-05-25 5
-1993-05-26 5
-1993-05-27 5
-1993-05-28 5
-1993-05-29 5
-1993-05-30 5
-1993-05-31 5
-1993-06-01 6
-1993-06-02 6
-1993-06-03 6
-1993-06-04 6
-1993-06-05 6
-1993-06-06 6
-1993-06-07 6
-1993-06-08 6
-1993-06-09 6
-1993-06-10 6
-1993-06-11 6
-1993-06-12 6
-1993-06-13 6
-1993-06-14 6
-1993-06-15 6
-1993-06-16 6
-1993-06-17 6
-1993-06-18 6
-1993-06-19 6
-1993-06-20 6
-1993-06-21 6
-1993-06-22 6
-1993-06-23 6
-1993-06-24 6
-1993-06-25 6
-1993-06-26 6
-1993-06-27 6
-1993-06-28 6
-1993-06-29 6
-1993-06-30 6
-1993-07-01 7
-1993-07-02 7
-1993-07-03 7
-1993-07-04 7
-1993-07-05 7
-1993-07-06 7
-1993-07-07 7
-1993-07-08 7
-1993-07-09 7
-1993-07-10 7
-1993-07-11 7
-1993-07-12 7
-1993-07-13 7
-1993-07-14 7
-1993-07-15 7
-1993-07-16 7
-1993-07-17 7
-1993-07-18 7
-1993-07-19 7
-1993-07-20 7
-1993-07-21 7
-1993-07-22 7
-1993-07-23 7
-1993-07-24 7
-1993-07-25 7
-1993-07-26 7
-1993-07-27 7
-1993-07-28 7
-1993-07-29 7
-1993-07-30 7
-1993-07-31 7
-1993-08-01 8
-1993-08-02 8
-1993-08-03 8
-1993-08-04 8
-1993-08-05 8
-1993-08-06 8
-1993-08-07 8
-1993-08-08 8
-1993-08-09 8
-1993-08-10 8
-1993-08-11 8
-1993-08-12 8
-1993-08-13 8
-1993-08-14 8
-1993-08-15 8
-1993-08-16 8
-1993-08-17 8
-1993-08-18 8
-1993-08-19 8
-1993-08-20 8
-1993-08-21 8
-1993-08-22 8
-1993-08-23 8
-1993-08-24 8
-1993-08-25 8
-1993-08-26 8
-1993-08-27 8
-1993-08-28 8
-1993-08-29 8
-1993-08-30 8
-1993-08-31 8
-1993-09-01 9
-1993-09-02 9
-1993-09-03 9
-1993-09-04 9
-1993-09-05 9
-1993-09-06 9
-1993-09-07 9
-1993-09-08 9
-1993-09-09 9
-1993-09-10 9
-1993-09-11 9
-1993-09-12 9
-1993-09-13 9
-1993-09-14 9
-1993-09-15 9
-1993-09-16 9
-1993-09-17 9
-1993-09-18 9
-1993-09-19 9
-1993-09-20 9
-1993-09-21 9
-1993-09-22 9
-1993-09-23 9
-1993-09-24 9
-1993-09-25 9
-1993-09-26 9
-1993-09-27 9
-1993-09-28 9
-1993-09-29 9
-1993-09-30 9
-1993-10-01 10
-1993-10-02 10
-1993-10-03 10
-1993-10-04 10
-1993-10-05 10
-1993-10-06 10
-1993-10-07 10
-1993-10-08 10
-1993-10-09 10
-1993-10-10 10
-1993-10-11 10
-1993-10-12 10
-1993-10-13 10
-1993-10-14 10
-1993-10-15 10
-1993-10-16 10
-1993-10-17 10
-1993-10-18 10
-1993-10-19 10
-1993-10-20 10
-1993-10-21 10
-1993-10-22 10
-1993-10-23 10
-1993-10-24 10
-1993-10-25 10
-1993-10-26 10
-1993-10-27 10
-1993-10-28 10
-1993-10-29 10
-1993-10-30 10
-1993-10-31 10
-1993-11-01 11
-1993-11-02 11
-1993-11-03 11
-1993-11-04 11
-1993-11-05 11
-1993-11-06 11
-1993-11-07 11
-1993-11-08 11
-1993-11-09 11
-1993-11-10 11
-1993-11-11 11
-1993-11-12 11
-1993-11-13 11
-1993-11-14 11
-1993-11-15 11
-1993-11-16 11
-1993-11-17 11
-1993-11-18 11
-1993-11-19 11
-1993-11-20 11
-1993-11-21 11
-1993-11-22 11
-1993-11-23 11
-1993-11-24 11
-1993-11-25 11
-1993-11-26 11
-1993-11-27 11
-1993-11-28 11
-1993-11-29 11
-1993-11-30 11
-1993-12-01 12
-1993-12-02 12
-1993-12-03 12
-1993-12-04 12
-1993-12-05 12
-1993-12-06 12
-1993-12-07 12
-1993-12-08 12
-1993-12-09 12
-1993-12-10 12
-1993-12-11 12
-1993-12-12 12
-1993-12-13 12
-1993-12-14 12
-1993-12-15 12
-1993-12-16 12
-1993-12-17 12
-1993-12-18 12
-1993-12-19 12
-1993-12-20 12
-1993-12-21 12
-1993-12-22 12
-1993-12-23 12
-1993-12-24 12
-1993-12-25 12
-1993-12-26 12
-1993-12-27 12
-1993-12-28 12
-1993-12-29 12
-1993-12-30 12
-1993-12-31 12
-1994-01-01 1
diff --git a/modules/calcite/src/test/sql/function/date/test_extract_month.test_ignore b/modules/calcite/src/test/sql/function/date/test_extract_month.test_ignore
new file mode 100644
index 0000000..43afddd
--- /dev/null
+++ b/modules/calcite/src/test/sql/function/date/test_extract_month.test_ignore
@@ -0,0 +1,753 @@
+# name: test/sql/function/date/test_extract_month.test
+# description: Extract month function
+# group: [date]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14963
+
+query II
+select date '1992-01-01' + interval (18) days, month(date '1992-01-01' + interval (18) days);
+----
+1992-01-18 1
+
+query II
+select date '1992-01-01' + interval (x) days, month(date '1992-01-01' + interval (x) days) from table(system_range(0, 365));
+----
+1992-01-01 1
+1992-01-02 1
+1992-01-03 1
+1992-01-04 1
+1992-01-05 1
+1992-01-06 1
+1992-01-07 1
+1992-01-08 1
+1992-01-09 1
+1992-01-10 1
+1992-01-11 1
+1992-01-12 1
+1992-01-13 1
+1992-01-14 1
+1992-01-15 1
+1992-01-16 1
+1992-01-17 1
+1992-01-18 1
+1992-01-19 1
+1992-01-20 1
+1992-01-21 1
+1992-01-22 1
+1992-01-23 1
+1992-01-24 1
+1992-01-25 1
+1992-01-26 1
+1992-01-27 1
+1992-01-28 1
+1992-01-29 1
+1992-01-30 1
+1992-01-31 1
+1992-02-01 2
+1992-02-02 2
+1992-02-03 2
+1992-02-04 2
+1992-02-05 2
+1992-02-06 2
+1992-02-07 2
+1992-02-08 2
+1992-02-09 2
+1992-02-10 2
+1992-02-11 2
+1992-02-12 2
+1992-02-13 2
+1992-02-14 2
+1992-02-15 2
+1992-02-16 2
+1992-02-17 2
+1992-02-18 2
+1992-02-19 2
+1992-02-20 2
+1992-02-21 2
+1992-02-22 2
+1992-02-23 2
+1992-02-24 2
+1992-02-25 2
+1992-02-26 2
+1992-02-27 2
+1992-02-28 2
+1992-02-29 2
+1992-03-01 3
+1992-03-02 3
+1992-03-03 3
+1992-03-04 3
+1992-03-05 3
+1992-03-06 3
+1992-03-07 3
+1992-03-08 3
+1992-03-09 3
+1992-03-10 3
+1992-03-11 3
+1992-03-12 3
+1992-03-13 3
+1992-03-14 3
+1992-03-15 3
+1992-03-16 3
+1992-03-17 3
+1992-03-18 3
+1992-03-19 3
+1992-03-20 3
+1992-03-21 3
+1992-03-22 3
+1992-03-23 3
+1992-03-24 3
+1992-03-25 3
+1992-03-26 3
+1992-03-27 3
+1992-03-28 3
+1992-03-29 3
+1992-03-30 3
+1992-03-31 3
+1992-04-01 4
+1992-04-02 4
+1992-04-03 4
+1992-04-04 4
+1992-04-05 4
+1992-04-06 4
+1992-04-07 4
+1992-04-08 4
+1992-04-09 4
+1992-04-10 4
+1992-04-11 4
+1992-04-12 4
+1992-04-13 4
+1992-04-14 4
+1992-04-15 4
+1992-04-16 4
+1992-04-17 4
+1992-04-18 4
+1992-04-19 4
+1992-04-20 4
+1992-04-21 4
+1992-04-22 4
+1992-04-23 4
+1992-04-24 4
+1992-04-25 4
+1992-04-26 4
+1992-04-27 4
+1992-04-28 4
+1992-04-29 4
+1992-04-30 4
+1992-05-01 5
+1992-05-02 5
+1992-05-03 5
+1992-05-04 5
+1992-05-05 5
+1992-05-06 5
+1992-05-07 5
+1992-05-08 5
+1992-05-09 5
+1992-05-10 5
+1992-05-11 5
+1992-05-12 5
+1992-05-13 5
+1992-05-14 5
+1992-05-15 5
+1992-05-16 5
+1992-05-17 5
+1992-05-18 5
+1992-05-19 5
+1992-05-20 5
+1992-05-21 5
+1992-05-22 5
+1992-05-23 5
+1992-05-24 5
+1992-05-25 5
+1992-05-26 5
+1992-05-27 5
+1992-05-28 5
+1992-05-29 5
+1992-05-30 5
+1992-05-31 5
+1992-06-01 6
+1992-06-02 6
+1992-06-03 6
+1992-06-04 6
+1992-06-05 6
+1992-06-06 6
+1992-06-07 6
+1992-06-08 6
+1992-06-09 6
+1992-06-10 6
+1992-06-11 6
+1992-06-12 6
+1992-06-13 6
+1992-06-14 6
+1992-06-15 6
+1992-06-16 6
+1992-06-17 6
+1992-06-18 6
+1992-06-19 6
+1992-06-20 6
+1992-06-21 6
+1992-06-22 6
+1992-06-23 6
+1992-06-24 6
+1992-06-25 6
+1992-06-26 6
+1992-06-27 6
+1992-06-28 6
+1992-06-29 6
+1992-06-30 6
+1992-07-01 7
+1992-07-02 7
+1992-07-03 7
+1992-07-04 7
+1992-07-05 7
+1992-07-06 7
+1992-07-07 7
+1992-07-08 7
+1992-07-09 7
+1992-07-10 7
+1992-07-11 7
+1992-07-12 7
+1992-07-13 7
+1992-07-14 7
+1992-07-15 7
+1992-07-16 7
+1992-07-17 7
+1992-07-18 7
+1992-07-19 7
+1992-07-20 7
+1992-07-21 7
+1992-07-22 7
+1992-07-23 7
+1992-07-24 7
+1992-07-25 7
+1992-07-26 7
+1992-07-27 7
+1992-07-28 7
+1992-07-29 7
+1992-07-30 7
+1992-07-31 7
+1992-08-01 8
+1992-08-02 8
+1992-08-03 8
+1992-08-04 8
+1992-08-05 8
+1992-08-06 8
+1992-08-07 8
+1992-08-08 8
+1992-08-09 8
+1992-08-10 8
+1992-08-11 8
+1992-08-12 8
+1992-08-13 8
+1992-08-14 8
+1992-08-15 8
+1992-08-16 8
+1992-08-17 8
+1992-08-18 8
+1992-08-19 8
+1992-08-20 8
+1992-08-21 8
+1992-08-22 8
+1992-08-23 8
+1992-08-24 8
+1992-08-25 8
+1992-08-26 8
+1992-08-27 8
+1992-08-28 8
+1992-08-29 8
+1992-08-30 8
+1992-08-31 8
+1992-09-01 9
+1992-09-02 9
+1992-09-03 9
+1992-09-04 9
+1992-09-05 9
+1992-09-06 9
+1992-09-07 9
+1992-09-08 9
+1992-09-09 9
+1992-09-10 9
+1992-09-11 9
+1992-09-12 9
+1992-09-13 9
+1992-09-14 9
+1992-09-15 9
+1992-09-16 9
+1992-09-17 9
+1992-09-18 9
+1992-09-19 9
+1992-09-20 9
+1992-09-21 9
+1992-09-22 9
+1992-09-23 9
+1992-09-24 9
+1992-09-25 9
+1992-09-26 9
+1992-09-27 9
+1992-09-28 9
+1992-09-29 9
+1992-09-30 9
+1992-10-01 10
+1992-10-02 10
+1992-10-03 10
+1992-10-04 10
+1992-10-05 10
+1992-10-06 10
+1992-10-07 10
+1992-10-08 10
+1992-10-09 10
+1992-10-10 10
+1992-10-11 10
+1992-10-12 10
+1992-10-13 10
+1992-10-14 10
+1992-10-15 10
+1992-10-16 10
+1992-10-17 10
+1992-10-18 10
+1992-10-19 10
+1992-10-20 10
+1992-10-21 10
+1992-10-22 10
+1992-10-23 10
+1992-10-24 10
+1992-10-25 10
+1992-10-26 10
+1992-10-27 10
+1992-10-28 10
+1992-10-29 10
+1992-10-30 10
+1992-10-31 10
+1992-11-01 11
+1992-11-02 11
+1992-11-03 11
+1992-11-04 11
+1992-11-05 11
+1992-11-06 11
+1992-11-07 11
+1992-11-08 11
+1992-11-09 11
+1992-11-10 11
+1992-11-11 11
+1992-11-12 11
+1992-11-13 11
+1992-11-14 11
+1992-11-15 11
+1992-11-16 11
+1992-11-17 11
+1992-11-18 11
+1992-11-19 11
+1992-11-20 11
+1992-11-21 11
+1992-11-22 11
+1992-11-23 11
+1992-11-24 11
+1992-11-25 11
+1992-11-26 11
+1992-11-27 11
+1992-11-28 11
+1992-11-29 11
+1992-11-30 11
+1992-12-01 12
+1992-12-02 12
+1992-12-03 12
+1992-12-04 12
+1992-12-05 12
+1992-12-06 12
+1992-12-07 12
+1992-12-08 12
+1992-12-09 12
+1992-12-10 12
+1992-12-11 12
+1992-12-12 12
+1992-12-13 12
+1992-12-14 12
+1992-12-15 12
+1992-12-16 12
+1992-12-17 12
+1992-12-18 12
+1992-12-19 12
+1992-12-20 12
+1992-12-21 12
+1992-12-22 12
+1992-12-23 12
+1992-12-24 12
+1992-12-25 12
+1992-12-26 12
+1992-12-27 12
+1992-12-28 12
+1992-12-29 12
+1992-12-30 12
+1992-12-31 12
+
+query II
+select date '1993-01-01' + interval (i) days, month(date '1993-01-01' + interval (i) days) from range(0, 366) tbl(i);
+----
+1993-01-01 1
+1993-01-02 1
+1993-01-03 1
+1993-01-04 1
+1993-01-05 1
+1993-01-06 1
+1993-01-07 1
+1993-01-08 1
+1993-01-09 1
+1993-01-10 1
+1993-01-11 1
+1993-01-12 1
+1993-01-13 1
+1993-01-14 1
+1993-01-15 1
+1993-01-16 1
+1993-01-17 1
+1993-01-18 1
+1993-01-19 1
+1993-01-20 1
+1993-01-21 1
+1993-01-22 1
+1993-01-23 1
+1993-01-24 1
+1993-01-25 1
+1993-01-26 1
+1993-01-27 1
+1993-01-28 1
+1993-01-29 1
+1993-01-30 1
+1993-01-31 1
+1993-02-01 2
+1993-02-02 2
+1993-02-03 2
+1993-02-04 2
+1993-02-05 2
+1993-02-06 2
+1993-02-07 2
+1993-02-08 2
+1993-02-09 2
+1993-02-10 2
+1993-02-11 2
+1993-02-12 2
+1993-02-13 2
+1993-02-14 2
+1993-02-15 2
+1993-02-16 2
+1993-02-17 2
+1993-02-18 2
+1993-02-19 2
+1993-02-20 2
+1993-02-21 2
+1993-02-22 2
+1993-02-23 2
+1993-02-24 2
+1993-02-25 2
+1993-02-26 2
+1993-02-27 2
+1993-02-28 2
+1993-03-01 3
+1993-03-02 3
+1993-03-03 3
+1993-03-04 3
+1993-03-05 3
+1993-03-06 3
+1993-03-07 3
+1993-03-08 3
+1993-03-09 3
+1993-03-10 3
+1993-03-11 3
+1993-03-12 3
+1993-03-13 3
+1993-03-14 3
+1993-03-15 3
+1993-03-16 3
+1993-03-17 3
+1993-03-18 3
+1993-03-19 3
+1993-03-20 3
+1993-03-21 3
+1993-03-22 3
+1993-03-23 3
+1993-03-24 3
+1993-03-25 3
+1993-03-26 3
+1993-03-27 3
+1993-03-28 3
+1993-03-29 3
+1993-03-30 3
+1993-03-31 3
+1993-04-01 4
+1993-04-02 4
+1993-04-03 4
+1993-04-04 4
+1993-04-05 4
+1993-04-06 4
+1993-04-07 4
+1993-04-08 4
+1993-04-09 4
+1993-04-10 4
+1993-04-11 4
+1993-04-12 4
+1993-04-13 4
+1993-04-14 4
+1993-04-15 4
+1993-04-16 4
+1993-04-17 4
+1993-04-18 4
+1993-04-19 4
+1993-04-20 4
+1993-04-21 4
+1993-04-22 4
+1993-04-23 4
+1993-04-24 4
+1993-04-25 4
+1993-04-26 4
+1993-04-27 4
+1993-04-28 4
+1993-04-29 4
+1993-04-30 4
+1993-05-01 5
+1993-05-02 5
+1993-05-03 5
+1993-05-04 5
+1993-05-05 5
+1993-05-06 5
+1993-05-07 5
+1993-05-08 5
+1993-05-09 5
+1993-05-10 5
+1993-05-11 5
+1993-05-12 5
+1993-05-13 5
+1993-05-14 5
+1993-05-15 5
+1993-05-16 5
+1993-05-17 5
+1993-05-18 5
+1993-05-19 5
+1993-05-20 5
+1993-05-21 5
+1993-05-22 5
+1993-05-23 5
+1993-05-24 5
+1993-05-25 5
+1993-05-26 5
+1993-05-27 5
+1993-05-28 5
+1993-05-29 5
+1993-05-30 5
+1993-05-31 5
+1993-06-01 6
+1993-06-02 6
+1993-06-03 6
+1993-06-04 6
+1993-06-05 6
+1993-06-06 6
+1993-06-07 6
+1993-06-08 6
+1993-06-09 6
+1993-06-10 6
+1993-06-11 6
+1993-06-12 6
+1993-06-13 6
+1993-06-14 6
+1993-06-15 6
+1993-06-16 6
+1993-06-17 6
+1993-06-18 6
+1993-06-19 6
+1993-06-20 6
+1993-06-21 6
+1993-06-22 6
+1993-06-23 6
+1993-06-24 6
+1993-06-25 6
+1993-06-26 6
+1993-06-27 6
+1993-06-28 6
+1993-06-29 6
+1993-06-30 6
+1993-07-01 7
+1993-07-02 7
+1993-07-03 7
+1993-07-04 7
+1993-07-05 7
+1993-07-06 7
+1993-07-07 7
+1993-07-08 7
+1993-07-09 7
+1993-07-10 7
+1993-07-11 7
+1993-07-12 7
+1993-07-13 7
+1993-07-14 7
+1993-07-15 7
+1993-07-16 7
+1993-07-17 7
+1993-07-18 7
+1993-07-19 7
+1993-07-20 7
+1993-07-21 7
+1993-07-22 7
+1993-07-23 7
+1993-07-24 7
+1993-07-25 7
+1993-07-26 7
+1993-07-27 7
+1993-07-28 7
+1993-07-29 7
+1993-07-30 7
+1993-07-31 7
+1993-08-01 8
+1993-08-02 8
+1993-08-03 8
+1993-08-04 8
+1993-08-05 8
+1993-08-06 8
+1993-08-07 8
+1993-08-08 8
+1993-08-09 8
+1993-08-10 8
+1993-08-11 8
+1993-08-12 8
+1993-08-13 8
+1993-08-14 8
+1993-08-15 8
+1993-08-16 8
+1993-08-17 8
+1993-08-18 8
+1993-08-19 8
+1993-08-20 8
+1993-08-21 8
+1993-08-22 8
+1993-08-23 8
+1993-08-24 8
+1993-08-25 8
+1993-08-26 8
+1993-08-27 8
+1993-08-28 8
+1993-08-29 8
+1993-08-30 8
+1993-08-31 8
+1993-09-01 9
+1993-09-02 9
+1993-09-03 9
+1993-09-04 9
+1993-09-05 9
+1993-09-06 9
+1993-09-07 9
+1993-09-08 9
+1993-09-09 9
+1993-09-10 9
+1993-09-11 9
+1993-09-12 9
+1993-09-13 9
+1993-09-14 9
+1993-09-15 9
+1993-09-16 9
+1993-09-17 9
+1993-09-18 9
+1993-09-19 9
+1993-09-20 9
+1993-09-21 9
+1993-09-22 9
+1993-09-23 9
+1993-09-24 9
+1993-09-25 9
+1993-09-26 9
+1993-09-27 9
+1993-09-28 9
+1993-09-29 9
+1993-09-30 9
+1993-10-01 10
+1993-10-02 10
+1993-10-03 10
+1993-10-04 10
+1993-10-05 10
+1993-10-06 10
+1993-10-07 10
+1993-10-08 10
+1993-10-09 10
+1993-10-10 10
+1993-10-11 10
+1993-10-12 10
+1993-10-13 10
+1993-10-14 10
+1993-10-15 10
+1993-10-16 10
+1993-10-17 10
+1993-10-18 10
+1993-10-19 10
+1993-10-20 10
+1993-10-21 10
+1993-10-22 10
+1993-10-23 10
+1993-10-24 10
+1993-10-25 10
+1993-10-26 10
+1993-10-27 10
+1993-10-28 10
+1993-10-29 10
+1993-10-30 10
+1993-10-31 10
+1993-11-01 11
+1993-11-02 11
+1993-11-03 11
+1993-11-04 11
+1993-11-05 11
+1993-11-06 11
+1993-11-07 11
+1993-11-08 11
+1993-11-09 11
+1993-11-10 11
+1993-11-11 11
+1993-11-12 11
+1993-11-13 11
+1993-11-14 11
+1993-11-15 11
+1993-11-16 11
+1993-11-17 11
+1993-11-18 11
+1993-11-19 11
+1993-11-20 11
+1993-11-21 11
+1993-11-22 11
+1993-11-23 11
+1993-11-24 11
+1993-11-25 11
+1993-11-26 11
+1993-11-27 11
+1993-11-28 11
+1993-11-29 11
+1993-11-30 11
+1993-12-01 12
+1993-12-02 12
+1993-12-03 12
+1993-12-04 12
+1993-12-05 12
+1993-12-06 12
+1993-12-07 12
+1993-12-08 12
+1993-12-09 12
+1993-12-10 12
+1993-12-11 12
+1993-12-12 12
+1993-12-13 12
+1993-12-14 12
+1993-12-15 12
+1993-12-16 12
+1993-12-17 12
+1993-12-18 12
+1993-12-19 12
+1993-12-20 12
+1993-12-21 12
+1993-12-22 12
+1993-12-23 12
+1993-12-24 12
+1993-12-25 12
+1993-12-26 12
+1993-12-27 12
+1993-12-28 12
+1993-12-29 12
+1993-12-30 12
+1993-12-31 12
+1994-01-01 1
+
+
+[expected=[[1992-01-01, 1], [1992-01-02, 1], [1992-01-03, 1], [1992-01-04, 1], [1992-01-05, 1], [1992-01-06, 1], [1992-01-07, 1], [1992-01-08, 1], [1992-01-09, 1], [1992-01-10, 1], [1992-01-11, 1], [1992-01-12, 1], [1992-01-13, 1], [1992-01-14, 1], [1992-01-15, 1], [1992-01-16, 1], [1992-01-17, 1], [1992-01-18, 1], [1992-01-19, 1], [1992-01-20, 1], [1992-01-21, 1], [1992-01-22, 1], [1992-01-23, 1], [1992-01-24, 1], [1992-01-25, 1], [1992-01-26, 1], [1992-01-27, 1], [1992-01-28, 1], [1992 [...]
+actual=[[1992-01-01, 1], [1992-01-02, 1], [1992-01-03, 1], [1992-01-04, 1], [1992-01-05, 1], [1992-01-06, 1], [1992-01-07, 1], [1992-01-08, 1], [1992-01-09, 1], [1992-01-10, 1], [1992-01-11, 1], [1992-01-12, 1], [1992-01-13, 1], [1992-01-14, 1], [1992-01-15, 1], [1992-01-16, 1], [1992-01-17, 1], [1992-01-18, 1], [1992-01-18, 1], [1992-01-20, 1], [1992-01-21, 1], [1992-01-22, 1], [1992-01-23, 1], [1992-01-24, 1], [1992-01-25, 1], [1992-01-26, 1], [1992-01-27, 1], [1992-01-28, 1], [1992-01 [...]
\ No newline at end of file
diff --git a/modules/calcite/src/test/sql/function/date/test_extract_year.test b/modules/calcite/src/test/sql/function/date/test_extract_year.test
index c2b48e9..40bc363 100644
--- a/modules/calcite/src/test/sql/function/date/test_extract_year.test
+++ b/modules/calcite/src/test/sql/function/date/test_extract_year.test
@@ -3,13 +3,16 @@
# group: [date]
statement ok
-PRAGMA enable_verification
+CREATE TABLE dates (d DATE);
statement ok
-CREATE TABLE dates AS SELECT date '1970-01-01' + concat(i, ' years')::interval AS d from range(0, 430) tbl(i);
+CREATE TABLE dates2 (d DATE);
statement ok
-CREATE TABLE dates2 AS SELECT date '1970-01-01' + concat(i * 6, ' months')::interval AS d from range(0, 200) tbl(i);
+INSERT INTO dates SELECT date '1970-01-01' + INTERVAL(x) years AS d from table(system_range(0, 429));
+
+statement ok
+INSERT INTO dates2 SELECT date '1970-01-01' + INTERVAL(x * 6) months AS d from table(system_range(0, 199));
query I
SELECT EXTRACT(year FROM d) FROM dates ORDER BY 1
diff --git a/modules/calcite/src/test/sql/function/date/test_strftime.test b/modules/calcite/src/test/sql/function/date/test_strftime.test
deleted file mode 100644
index 9d6977e..0000000
--- a/modules/calcite/src/test/sql/function/date/test_strftime.test
+++ /dev/null
@@ -1,101 +0,0 @@
-# name: test/sql/function/date/test_strftime.test
-# description: Test strftime function
-# group: [date]
-
-statement ok
-PRAGMA enable_verification
-
-# simple single specifier
-query I
-SELECT strftime(DATE '1992-01-01', '%Y');
-----
-1992
-
-# some literals
-query I
-SELECT strftime(DATE '1992-01-01', '(%Y)');
-----
-(1992)
-
-# escapes
-query I
-SELECT strftime(DATE '1992-01-01', '%% %Y %%');
-----
-% 1992 %
-
-# many consecutive escapes
-query I
-SELECT strftime(DATE '1992-01-01', '%%%%%% %Y %%%%%%');
-----
-%%% 1992 %%%
-
-# multiple specifiers
-query I
-SELECT strftime(DATE '1992-02-01', '%d/%m/%Y');
-----
-01/02/1992
-
-# we can repeat the same specifier many times
-query I
-SELECT strftime(DATE '1992-02-01', '%Y %Y %Y %Y');
-----
-1992 1992 1992 1992
-
-# test on a table
-statement ok
-CREATE TABLE dates(d DATE);
-INSERT INTO dates VALUES ('1992-01-01'), ('1993-03-20'), (NULL);
-
-query I
-SELECT strftime(d, '%d/%m/%Y') FROM dates ORDER BY d;
-----
-NULL
-01/01/1992
-20/03/1993
-
-# null date
-query I
-SELECT strftime(NULL::DATE, '%d/%m/%Y') FROM dates ORDER BY d;
-----
-NULL
-NULL
-NULL
-
-# null format
-query I
-SELECT strftime(d, NULL) FROM dates ORDER BY d;
-----
-NULL
-NULL
-NULL
-
-# no specifiers, only constant
-query I
-SELECT strftime(DATE '1992-01-01', 'hello world');
-----
-hello world
-
-# non-constant format not supported
-statement error
-SELECT strftime(d, d::VARCHAR) FROM dates ORDER BY d;
-
-# unterminated escape
-statement error
-SELECT strftime(DATE '1992-01-01', '%');
-
-# unrecognized code
-statement error
-SELECT strftime(DATE '1992-01-01', '%R');
-
-# millisecond specifier %g
-query IIII
-select strftime(strptime('023', '%g'), '%g'), strftime(strptime('0', '%g'), '%g'), strftime(strptime('000', '%g'), '%g'), strftime(strptime('999', '%g'), '%g');
-----
-023
-000
-000
-999
-
-statement error
-SELECT strptime('-1', '%g');
-SELECT strptime('1000', '%g');
\ No newline at end of file
diff --git a/modules/calcite/src/test/sql/function/date/test_strftime_exhaustive.test b/modules/calcite/src/test/sql/function/date/test_strftime_exhaustive.test
deleted file mode 100644
index ed09172..0000000
--- a/modules/calcite/src/test/sql/function/date/test_strftime_exhaustive.test
+++ /dev/null
@@ -1,354 +0,0 @@
-# name: test/sql/function/date/test_strftime_exhaustive.test
-# description: Test all strftime % codes with the date type
-# group: [date]
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE dates(d DATE);
-INSERT INTO dates VALUES ('1992-01-01'), ('1993-03-20'), ('2020-08-09'), ('2020-08-10'), (NULL);
-
-# %a: Abbreviated weekday name
-query I
-SELECT strftime(d, '%a') FROM dates ORDER BY d;
-----
-NULL
-Wed
-Sat
-Sun
-Mon
-
-# %A: Full weekday name
-query I
-SELECT strftime(d, '%A') FROM dates ORDER BY d;
-----
-NULL
-Wednesday
-Saturday
-Sunday
-Monday
-
-# %w - Weekday as a decimal number.
-query I
-SELECT strftime(d, '%w') FROM dates ORDER BY d;
-----
-NULL
-3
-6
-0
-1
-
-# %d - Day of the month as a zero-padded decimal.
-query I
-SELECT strftime(d, '%d') FROM dates ORDER BY d;
-----
-NULL
-01
-20
-09
-10
-
-# %-d - Day of the month as a decimal number.
-query I
-SELECT strftime(d, '%-d') FROM dates ORDER BY d;
-----
-NULL
-1
-20
-9
-10
-
-# %b - Abbreviated month name.
-query I
-SELECT strftime(d, '%b') FROM dates ORDER BY d;
-----
-NULL
-Jan
-Mar
-Aug
-Aug
-
-# %h - alias for %b
-query I
-SELECT strftime(d, '%h') FROM dates ORDER BY d;
-----
-NULL
-Jan
-Mar
-Aug
-Aug
-
-# %B - Full month name
-query I
-SELECT strftime(d, '%B') FROM dates ORDER BY d;
-----
-NULL
-January
-March
-August
-August
-
-# %m - Month as a zero-padded decimal number
-query I
-SELECT strftime(d, '%m') FROM dates ORDER BY d;
-----
-NULL
-01
-03
-08
-08
-
-# %-m - Month as a decimal number. (1, 2, ..., 12)
-query I
-SELECT strftime(d, '%-m') FROM dates ORDER BY d;
-----
-NULL
-1
-3
-8
-8
-
-# %y - Year without century as a zero-padded decimal number.
-query I
-SELECT strftime(d, '%y') FROM dates ORDER BY d;
-----
-NULL
-92
-93
-20
-20
-
-# %-y - Year without century as a decimal number.
-query I
-SELECT strftime(d, '%-y') FROM dates ORDER BY d;
-----
-NULL
-92
-93
-20
-20
-
-query I
-SELECT strftime(DATE '2001-01-01', '%-y')
-----
-1
-
-# %Y - Year with century as a decimal number.
-query I
-SELECT strftime(d, '%Y') FROM dates ORDER BY d;
-----
-NULL
-1992
-1993
-2020
-2020
-
-# %H - Hour (24-hour clock) as a zero-padded decimal number.
-query I
-SELECT strftime(d, '%H') FROM dates ORDER BY d;
-----
-NULL
-00
-00
-00
-00
-
-# %-H - Hour (24-hour clock) as a decimal number. (0, 1, ..., 23)
-query I
-SELECT strftime(d, '%-H') FROM dates ORDER BY d;
-----
-NULL
-0
-0
-0
-0
-
-# %I - Hour (12-hour clock) as a zero-padded decimal number.
-query I
-SELECT strftime(d, '%I') FROM dates ORDER BY d;
-----
-NULL
-12
-12
-12
-12
-
-# %-I - Hour (12-hour clock) as a decimal number. (1, 2, ... 12)
-query I
-SELECT strftime(d, '%-I') FROM dates ORDER BY d;
-----
-NULL
-12
-12
-12
-12
-
-# %p - Locale’s AM or PM.
-query I
-SELECT strftime(d, '%p') FROM dates ORDER BY d;
-----
-NULL
-AM
-AM
-AM
-AM
-
-# %M - Minute as a zero-padded decimal number.
-query I
-SELECT strftime(d, '%M') FROM dates ORDER BY d;
-----
-NULL
-00
-00
-00
-00
-
-# %-M - Minute as a decimal number. (0, 1, ..., 59)
-query I
-SELECT strftime(d, '%-M') FROM dates ORDER BY d;
-----
-NULL
-0
-0
-0
-0
-
-# %S - Second as a zero-padded decimal number.
-query I
-SELECT strftime(d, '%S') FROM dates ORDER BY d;
-----
-NULL
-00
-00
-00
-00
-
-# %-S - Second as a decimal number. (0, 1, ..., 59)
-query I
-SELECT strftime(d, '%-S') FROM dates ORDER BY d;
-----
-NULL
-0
-0
-0
-0
-
-# %f - Microsecond as a decimal number, zero-padded on the left.
-query I
-SELECT strftime(d, '%f') FROM dates ORDER BY d;
-----
-NULL
-000000
-000000
-000000
-000000
-
-# %z - UTC offset in the form +HHMM or -HHMM.
-query I
-SELECT strftime(d, '%z') FROM dates ORDER BY d;
-----
-NULL
-(empty)
-(empty)
-(empty)
-(empty)
-
-# %Z - Time zone name.
-query I
-SELECT strftime(d, '%Z') FROM dates ORDER BY d;
-----
-NULL
-(empty)
-(empty)
-(empty)
-(empty)
-
-# %j - Day of the year as a zero-padded decimal number.
-query I
-SELECT strftime(d, '%j') FROM dates ORDER BY d;
-----
-NULL
-001
-079
-222
-223
-
-# %-j - Day of the year as a decimal number. (1, 2, ..., 366)
-query I
-SELECT strftime(d, '%-j') FROM dates ORDER BY d;
-----
-NULL
-1
-79
-222
-223
-
-# %U - Week number of the year (Sunday as the first day of the week).
-query I
-SELECT strftime(d, '%U') FROM dates ORDER BY d;
-----
-NULL
-00
-11
-32
-32
-
-# %W - Week number of the year (Monday as the first day of the week).
-query I
-SELECT strftime(d, '%W') FROM dates ORDER BY d;
-----
-NULL
-00
-11
-31
-32
-
-# %c - Locale’s appropriate date and time representation.
-query I
-SELECT strftime(d, '%c') FROM dates ORDER BY d;
-----
-NULL
-1992-01-01 00:00:00
-1993-03-20 00:00:00
-2020-08-09 00:00:00
-2020-08-10 00:00:00
-
-query I
-SELECT strftime(d, 'XX%cXX') FROM dates ORDER BY d;
-----
-NULL
-XX1992-01-01 00:00:00XX
-XX1993-03-20 00:00:00XX
-XX2020-08-09 00:00:00XX
-XX2020-08-10 00:00:00XX
-
-# %x - Locale’s appropriate date representation.
-query I
-SELECT strftime(d, '%x') FROM dates ORDER BY d;
-----
-NULL
-1992-01-01
-1993-03-20
-2020-08-09
-2020-08-10
-
-# X - Locale’s appropriate time representation.
-query I
-SELECT strftime(d, '%X') FROM dates ORDER BY d;
-----
-NULL
-00:00:00
-00:00:00
-00:00:00
-00:00:00
-
-
-query I
-SELECT strftime(d, 'XX%cXX%xXX%XXX') FROM dates ORDER BY d;
-----
-NULL
-XX1992-01-01 00:00:00XX1992-01-01XX00:00:00XX
-XX1993-03-20 00:00:00XX1993-03-20XX00:00:00XX
-XX2020-08-09 00:00:00XX2020-08-09XX00:00:00XX
-XX2020-08-10 00:00:00XX2020-08-10XX00:00:00XX
diff --git a/modules/calcite/src/test/sql/function/generic/test_between.test b/modules/calcite/src/test/sql/function/generic/test_between.test
index 08b8069..a9470a5 100644
--- a/modules/calcite/src/test/sql/function/generic/test_between.test
+++ b/modules/calcite/src/test/sql/function/generic/test_between.test
@@ -2,20 +2,17 @@
# description: Test between statement
# group: [generic]
-statement ok
-PRAGMA enable_verification
-
# test constant BETWEEN statement
# simple between
query T
SELECT 10 BETWEEN 10 AND 20
----
-1
+true
query T
SELECT 9 BETWEEN 10 AND 20
----
-0
+false
# now with NULL values
query T
@@ -26,7 +23,7 @@ NULL
query T
SELECT 30 BETWEEN NULL AND 20
----
-0
+false
query T
SELECT 10 BETWEEN 10 AND NULL
@@ -36,7 +33,7 @@ NULL
query T
SELECT 9 BETWEEN 10 AND NULL
----
-0
+false
query T
SELECT NULL BETWEEN 10 AND 20
@@ -68,24 +65,24 @@ INSERT INTO integers VALUES (1), (2), (3), (NULL)
query T
SELECT i BETWEEN 1 AND 2 FROM integers ORDER BY i
----
+true
+true
+false
NULL
-1
-1
-0
query T
SELECT i BETWEEN NULL AND 2 FROM integers ORDER BY i
----
NULL
NULL
+false
NULL
-0
query T
SELECT i BETWEEN 2 AND NULL FROM integers ORDER BY i
----
+false
NULL
-0
NULL
NULL
diff --git a/modules/calcite/src/test/sql/function/generic/test_case.test b/modules/calcite/src/test/sql/function/generic/test_case.test
index 1b8a895..a15d27d 100644
--- a/modules/calcite/src/test/sql/function/generic/test_case.test
+++ b/modules/calcite/src/test/sql/function/generic/test_case.test
@@ -3,16 +3,13 @@
# group: [generic]
statement ok
-PRAGMA enable_verification
-
-statement ok
CREATE TABLE test (a INTEGER, b INTEGER);
statement ok
-INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)
+INSERT INTO test VALUES (11, 22), (12, 21), (13, 22)
query I
-SELECT CASE WHEN test.a=11 THEN b ELSE NULL END FROM test
+SELECT CASE WHEN test.a=11 THEN b ELSE NULL END FROM test ORDER BY a
----
22
NULL
@@ -81,14 +78,14 @@ SELECT CASE WHEN 'hello' THEN b ELSE a END FROM test
# but only when cast cannot be performed
query I
-SELECT CASE WHEN 'true' THEN NULL ELSE b+1 END FROM test ORDER BY b
+SELECT CASE WHEN true THEN NULL ELSE b+1 END FROM test ORDER BY b
----
NULL
NULL
NULL
query I
-SELECT CASE WHEN 'false' THEN NULL ELSE b+1 END FROM test ORDER BY b
+SELECT CASE WHEN false THEN NULL ELSE b+1 END FROM test ORDER BY b
----
22
23
diff --git a/modules/calcite/src/test/sql/function/generic/test_if.test b/modules/calcite/src/test/sql/function/generic/test_if.test
deleted file mode 100644
index b4739f7..0000000
--- a/modules/calcite/src/test/sql/function/generic/test_if.test
+++ /dev/null
@@ -1,32 +0,0 @@
-# name: test/sql/function/generic/test_if.test
-# description: IF test
-# group: [generic]
-
-statement ok
-PRAGMA enable_verification
-
-query III
-SELECT IF(true, 1, 10), IF(false, 1, 10), IF(NULL, 1, 10)
-----
-1 10 10
-
-query III
-SELECT IF(true, 20, 2000), IF(false, 20, 2000), IF(NULL, 20, 2000)
-----
-20 2000 2000
-
-query RRR
-SELECT IF(true, 20.5, 2000), IF(false, 20, 2000.5), IF(NULL, 20, 2000.5)
-----
-20.500000 2000.500000 2000.500000
-
-query TTT
-SELECT IF(true, '2020-05-05'::date, '1996-11-05 10:11:56'::timestamp), IF(false, '2020-05-05'::date, '1996-11-05 10:11:56'::timestamp), IF(NULL, '2020-05-05'::date, '1996-11-05 10:11:56'::timestamp)
-----
-2020-05-05 00:00:00 1996-11-05 10:11:56 1996-11-05 10:11:56
-
-query TTT
-SELECT IF(true, 'true', 'false'), IF(false, 'true', 'false'), IF(NULL, 'true', 'false')
-----
-true false false
-
diff --git a/modules/calcite/src/test/sql/function/generic/test_if_null.test b/modules/calcite/src/test/sql/function/generic/test_if_null.test
deleted file mode 100644
index 3d3c584..0000000
--- a/modules/calcite/src/test/sql/function/generic/test_if_null.test
+++ /dev/null
@@ -1,24 +0,0 @@
-# name: test/sql/function/generic/test_if_null.test
-# description: IFNULL test
-# group: [generic]
-
-query III
-SELECT IFNULL(NULL, NULL), IFNULL(NULL, 10), IFNULL(1, 10)
-----
-NULL 10 1
-
-query IR
-SELECT IFNULL(NULL, 2000), IFNULL(20.5, 2000)
-----
-2000 20.500000
-
-query TT
-SELECT IFNULL(NULL, '1996-11-05 10:11:56'::timestamp), IFNULL('2020-05-05'::date, '1996-11-05 10:11:56'::timestamp)
-----
-1996-11-05 10:11:56 2020-05-05 00:00:00
-
-query TT
-SELECT IFNULL(NULL, 'not NULL'), IFNULL('NULL', 'not NULL')
-----
-not NULL NULL
-
diff --git a/modules/calcite/src/test/sql/function/generic/test_in.test b/modules/calcite/src/test/sql/function/generic/test_in.test
index 3723635..6fb0203 100644
--- a/modules/calcite/src/test/sql/function/generic/test_in.test
+++ b/modules/calcite/src/test/sql/function/generic/test_in.test
@@ -3,9 +3,6 @@
# group: [generic]
statement ok
-PRAGMA enable_verification
-
-statement ok
CREATE TABLE integers(i INTEGER)
statement ok
@@ -27,112 +24,112 @@ SELECT * FROM integers WHERE i IN (1, 2, 3, 4, 5, 6, 7, 8) ORDER BY i
query IT
SELECT i, i IN (1, 2, 3, 4, 5, 6, 7, 8) FROM integers ORDER BY i
----
+1 true
+2 true
+3 true
NULL NULL
-1 1
-2 1
-3 1
query IT
SELECT i, i NOT IN (1, 2, 3, 4, 5, 6, 7, 8) FROM integers ORDER BY i
----
+1 false
+2 false
+3 false
NULL NULL
-1 0
-2 0
-3 0
query IT
SELECT i, i IN (1, 2, NULL, 4, 5, 6, 7, 8) FROM integers ORDER BY i
----
-NULL NULL
-1 1
-2 1
+1 true
+2 true
3 NULL
+NULL NULL
query IT
SELECT i, i IN (i + 1) FROM integers ORDER BY i
----
+1 false
+2 false
+3 false
NULL NULL
-1 0
-2 0
-3 0
query IT
SELECT i, i IN (i + 1, 42, i) FROM integers ORDER BY i
----
+1 true
+2 true
+3 true
NULL NULL
-1 1
-2 1
-3 1
query IT
SELECT i, 1 IN (i - 1, i, i + 1) FROM integers ORDER BY i
----
+1 true
+2 true
+3 false
NULL NULL
-1 1
-2 1
-3 0
query IT
SELECT i, 1 NOT IN (i - 1, i, i + 1) FROM integers ORDER BY i
----
+1 false
+2 false
+3 true
NULL NULL
-1 0
-2 0
-3 1
query IT
SELECT i, i IN (11, 12, 13, 14, 15, 16, 17, 18, 1, i) FROM integers ORDER BY i
----
+1 true
+2 true
+3 true
NULL NULL
-1 1
-2 1
-3 1
query IT
SELECT i, i NOT IN (11, 12, 13, 14, 15, 16, 17, 18, 1, i) FROM integers ORDER BY i
----
+1 false
+2 false
+3 false
NULL NULL
-1 0
-2 0
-3 0
query IT
SELECT i, 1 IN (11, 12, 13, 14, 15, 16, 17, 18, 1, i) FROM integers ORDER BY i
----
-NULL 1
-1 1
-2 1
-3 1
+1 true
+2 true
+3 true
+NULL true
query IT
SELECT i, 1 NOT IN (11, 12, 13, 14, 15, 16, 17, 18, 1, i) FROM integers ORDER BY i
----
-NULL 0
-1 0
-2 0
-3 0
+1 false
+2 false
+3 false
+NULL false
query IT
SELECT i, i IN ((SELECT MAX(i) FROM integers), (SELECT MIN(i) FROM integers)) FROM integers ORDER BY i
----
+1 true
+2 false
+3 true
NULL NULL
-1 1
-2 0
-3 1
query III
SELECT i, (SELECT MAX(i) FROM integers WHERE i <> i1.i), (SELECT MIN(i) FROM integers WHERE i <= i1.i) FROM integers i1 ORDER BY i
----
-NULL NULL NULL
1 3 1
2 3 1
3 2 1
+NULL NULL NULL
query IT
SELECT i, i IN ((SELECT MAX(i) FROM integers WHERE i <> i1.i), (SELECT MIN(i) FROM integers WHERE i <= i1.i)) FROM integers i1 ORDER BY i
----
+1 true
+2 false
+3 false
NULL NULL
-1 1
-2 0
-3 0
diff --git a/modules/calcite/src/test/sql/function/generic/test_large_in.test_slow b/modules/calcite/src/test/sql/function/generic/test_large_in.test_ignore
similarity index 99%
rename from modules/calcite/src/test/sql/function/generic/test_large_in.test_slow
rename to modules/calcite/src/test/sql/function/generic/test_large_in.test_ignore
index 7e6e735..e13e092 100644
--- a/modules/calcite/src/test/sql/function/generic/test_large_in.test_slow
+++ b/modules/calcite/src/test/sql/function/generic/test_large_in.test_ignore
@@ -1,9 +1,7 @@
# name: test/sql/function/generic/test_large_in.test_slow
# description: Test large IN statement with varchar
# group: [generic]
-
-statement ok
-PRAGMA enable_verification
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14964
statement ok
CREATE TABLE strings(s VARCHAR)
diff --git a/modules/calcite/src/test/sql/function/generic/test_least_greatest.test b/modules/calcite/src/test/sql/function/generic/test_least_greatest.test
index cf2c089..7c54014 100644
--- a/modules/calcite/src/test/sql/function/generic/test_least_greatest.test
+++ b/modules/calcite/src/test/sql/function/generic/test_least_greatest.test
@@ -2,9 +2,6 @@
# description: Test LEAST/GREATEST
# group: [generic]
-statement ok
-PRAGMA enable_verification
-
# scalar usage
query I
SELECT LEAST(1)
@@ -69,12 +66,6 @@ SELECT LEAST(DATE '1992-01-01', DATE '1994-02-02', DATE '1991-01-01', NULL)
----
NULL
-# test mix of types
-query T
-SELECT LEAST(DATE '1992-01-01', 'hello', 123)
-----
-123
-
# tables
statement ok
CREATE TABLE t1(i INTEGER, j INTEGER)
@@ -117,16 +108,9 @@ SELECT LEAST(i, j), GREATEST(i, j) FROM t1 WHERE j IS NOT NULL ORDER BY i
1 2
3 7
-# row ids
-query II
-SELECT LEAST(rowid + 10, i, j), GREATEST(i, rowid + 4, j) FROM t1 WHERE j IS NOT NULL ORDER BY i
-----
-1 5
-3 7
-
# generated strings
query T
-SELECT LEAST(REPEAT(i::VARCHAR, 20), j::VARCHAR) FROM t1
+SELECT LEAST(REPEAT(i::VARCHAR, 20), j::VARCHAR) FROM t1 ORDER BY i
----
NULL
1
diff --git a/modules/calcite/src/test/sql/function/generic/test_null_if.test b/modules/calcite/src/test/sql/function/generic/test_null_if.test
index 1d1c161..c708545 100644
--- a/modules/calcite/src/test/sql/function/generic/test_null_if.test
+++ b/modules/calcite/src/test/sql/function/generic/test_null_if.test
@@ -2,9 +2,6 @@
# description: Test NULL IF statement
# group: [generic]
-statement ok
-PRAGMA enable_verification
-
# NULL IF
query T
SELECT NULLIF(NULLIF ('hello', 'world'), 'blabla');
@@ -13,23 +10,23 @@ hello
# NULL IF with subquery
statement ok
-CREATE TABLE test (a STRING);
+CREATE TABLE test (a VARCHAR);
statement ok
-INSERT INTO test VALUES ('hello'), ('world'), ('test')
+INSERT INTO test VALUES ('aaa'), ('bbb'), ('ccc')
statement ok
-CREATE TABLE test2 (a STRING, b STRING);
+CREATE TABLE test2 (a VARCHAR, b VARCHAR);
statement ok
INSERT INTO test2 VALUES ('blabla', 'b'), ('blabla2', 'c'), ('blabla3', 'd')
query T
-SELECT NULLIF(NULLIF ((SELECT a FROM test LIMIT 1 offset 1), a), b) FROM test2
+SELECT NULLIF(NULLIF((SELECT MIN(a) FROM test), a), b) FROM test2
----
-world
-world
-world
+aaa
+aaa
+aaa
statement ok
DROP TABLE test;
@@ -39,14 +36,14 @@ statement ok
CREATE TABLE test3 (a INTEGER, b INTEGER);
statement ok
-INSERT INTO test3 VALUES (11, 22), (13, 22), (12, 21)
+INSERT INTO test3 VALUES (11, 22), (12, 21), (13, 22)
query T
-SELECT NULLIF(CAST(a AS VARCHAR), 11) FROM test3;
+SELECT NULLIF(CAST(a AS VARCHAR), 11) FROM test3 ORDER BY a;
----
NULL
-13
12
+13
query IT
SELECT a, CASE WHEN a>11 THEN CAST(a AS VARCHAR) ELSE CAST(b AS VARCHAR) END FROM test3 ORDER BY 1;
diff --git a/modules/calcite/src/test/sql/function/generic/test_set.test b/modules/calcite/src/test/sql/function/generic/test_set.test
deleted file mode 100644
index 28d732d..0000000
--- a/modules/calcite/src/test/sql/function/generic/test_set.test
+++ /dev/null
@@ -1,39 +0,0 @@
-# name: test/sql/function/generic/test_set.test
-# description: Test stats function
-# group: [generic]
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-SET a = 42;
-
-query I
-SELECT CURRENT_SETTING('a');
-----
-42
-
-statement ok
-SET a = 'asdf';
-
-query I
-SELECT CURRENT_SETTING('a');
-----
-asdf
-
-statement ok
-SET b = asdf;
-
-query I
-SELECT CURRENT_SETTING('b');
-----
-asdf
-
-statement error
-SET a = NULL;
-
-statement error
-RESET a;
-
-statement error
-RESET ALL;
diff --git a/modules/calcite/src/test/sql/function/generic/test_stats.test b/modules/calcite/src/test/sql/function/generic/test_stats.test
deleted file mode 100644
index d04e798..0000000
--- a/modules/calcite/src/test/sql/function/generic/test_stats.test
+++ /dev/null
@@ -1,64 +0,0 @@
-# name: test/sql/function/generic/test_stats.test
-# description: Test stats function
-# group: [generic]
-
-# scalar stats
-query I
-SELECT STATS(5);
-----
-<REGEX>:.*5.*5.*
-
-query I
-SELECT STATS(7);
-----
-<REGEX>:.*7.*7.*
-
-query I
-SELECT STATS('hello');
-----
-<REGEX>:.*hello.*hello.*
-
-query I
-SELECT STATS('1234567ü');
-----
-<REGEX>:.*1234567.*1234567.*
-
-# arithmetic
-query I
-SELECT STATS(5+2);
-----
-<REGEX>:.*7.*7.*
-
-# non-scalar stats
-statement ok
-CREATE TABLE integers(i INTEGER);
-
-statement ok
-INSERT INTO integers VALUES (1), (2), (3);
-
-# read stats
-query I
-SELECT STATS(i) FROM integers LIMIT 1;
-----
-<REGEX>:.*1.*3.*
-
-# arithmetic
-query I
-SELECT STATS(i+2) FROM integers LIMIT 1;
-----
-<REGEX>:.*3.*5.*
-
-query I
-SELECT STATS(i-5) FROM integers LIMIT 1;
-----
-<REGEX>:.*-4.*-2.*
-
-query I
-SELECT STATS(i*2) FROM integers LIMIT 1;
-----
-<REGEX>:.*2.*6.*
-
-query I
-SELECT STATS(i*-1) FROM integers LIMIT 1;
-----
-<REGEX>:.*-3.*-1.*
diff --git a/modules/calcite/src/test/sql/function/generic/test_table_param.test b/modules/calcite/src/test/sql/function/generic/test_table_param.test
deleted file mode 100644
index eaf7299..0000000
--- a/modules/calcite/src/test/sql/function/generic/test_table_param.test
+++ /dev/null
@@ -1,36 +0,0 @@
-# name: test/sql/function/generic/test_table_param.test
-# description: Test stats function
-# group: [generic]
-
-# scalar stats
-
-statement ok
-pragma enable_verification
-
-statement ok
-create table a (i double, j double);
-
-statement ok
-insert into a values (1, 10), (42, 420);
-
-statement ok
-EXPLAIN SELECT * FROM summary((SELECT * FROM a))
-
-query III
-SELECT * FROM summary((SELECT * FROM a))
-----
-[1.000000, 10.000000] 1.000000 10.000000
-[42.000000, 420.000000] 42.000000 420.000000
-
-
-statement ok
-pragma threads=4
-
-statement ok
-pragma force_parallelism
-
-query III
-SELECT * FROM summary((SELECT * FROM a))
-----
-[1.000000, 10.000000] 1.000000 10.000000
-[42.000000, 420.000000] 42.000000 420.000000
\ No newline at end of file
diff --git a/modules/calcite/src/test/sql/function/interval/test_date_part.test b/modules/calcite/src/test/sql/function/interval/test_date_part.test
deleted file mode 100644
index 2b56862..0000000
--- a/modules/calcite/src/test/sql/function/interval/test_date_part.test
+++ /dev/null
@@ -1,75 +0,0 @@
-# name: test/sql/function/interval/test_date_part.test
-# description: Extract function
-# group: [interval]
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE intervals(i INTERVAL, s VARCHAR)
-
-statement ok
-INSERT INTO intervals VALUES ('2 years', 'year'), ('16 months', 'quarter'), ('42 days', 'day'), ('2066343400 microseconds', 'minute')
-
-# test date_part with different combinations of constant/non-constant columns
-query I
-SELECT date_part(NULL::VARCHAR, NULL::INTERVAL) FROM intervals;
-----
-NULL
-NULL
-NULL
-NULL
-
-query I
-SELECT date_part(s, NULL::INTERVAL) FROM intervals;
-----
-NULL
-NULL
-NULL
-NULL
-
-# intervals
-query I
-SELECT date_part(NULL, i) FROM intervals;
-----
-NULL
-NULL
-NULL
-NULL
-
-query I
-SELECT date_part(s, INTERVAL '4 years 5 months 18 days 128 seconds') FROM intervals;
-----
-4
-2
-18
-2
-
-query I
-SELECT date_part('seconds', i) FROM intervals;
-----
-0
-0
-0
-2066
-
-query I
-SELECT date_part(s, i) FROM intervals;
-----
-2
-2
-42
-34
-
-# interval gives errors for date-only parts
-statement error
-SELECT dayofweek(i) FROM times
-
-statement error
-SELECT isodow(i) FROM times
-
-statement error
-SELECT dayofyear(i) FROM times
-
-statement error
-SELECT week(i) FROM times
diff --git a/modules/calcite/src/test/sql/function/interval/test_extract.test b/modules/calcite/src/test/sql/function/interval/test_extract.test_ignore
similarity index 97%
rename from modules/calcite/src/test/sql/function/interval/test_extract.test
rename to modules/calcite/src/test/sql/function/interval/test_extract.test_ignore
index f8beaa7..ebcfae1 100644
--- a/modules/calcite/src/test/sql/function/interval/test_extract.test
+++ b/modules/calcite/src/test/sql/function/interval/test_extract.test_ignore
@@ -1,9 +1,7 @@
# name: test/sql/function/interval/test_extract.test
# description: Extract function
# group: [interval]
-
-statement ok
-PRAGMA enable_verification
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14975
statement ok
CREATE TABLE intervals(i INTERVAL)
diff --git a/modules/calcite/src/test/sql/function/nested/test_lambda.test b/modules/calcite/src/test/sql/function/nested/test_lambda.test
deleted file mode 100644
index 9c5f880..0000000
--- a/modules/calcite/src/test/sql/function/nested/test_lambda.test
+++ /dev/null
@@ -1,44 +0,0 @@
-# name: test/sql/function/nested/test_lambda.test
-# description: Test basic lambda expressions
-# group: [nested]
-
-statement ok
-PRAGMA enable_verification
-
-# FIXME: just so we don't get "function not found" errors
-statement ok
-create function map(x, y) as x + y;
-
-# FIXME: this should work
-statement error
-select map(i, (x, y) -> x + y) from (values (list_value(1, 2, 3))) tbl(i);
-
-# test basic lambda binding
-# FIXME: this should work
-statement error
-select map(i, x -> x + 1) from (values (list_value(1, 2, 3))) tbl(i);
-
-# test lambda function precedence
-statement error
-select map(i, x -> x * 3 + 2 / zz) from (values (list_value(1, 2, 3))) tbl(i);
-
-statement error
-select map(i, x -> (1 + 2 * 27) / x) from (values (list_value(1, 2, 3))) tbl(i);
-
-statement error
-select map(i, x -> x + 1 AND y + 1) from (values (list_value(1, 2, 3))) tbl(i);
-
-# nested lambda functions
-statement error
-select map(i, x -> (map(x, y -> y + 1))) from (values (list_value(1, 2, 3))) tbl(i);
-
-# lambda cannot be the root of a plan
-statement error
-select x -> x + 1 from (values (list_value(1, 2, 3))) tbl(i);
-
-# lhs of lambda must be an unqualified column name
-statement error
-select map(i, y + 1 -> x + 1) from (values (list_value(1, 2, 3))) tbl(i);
-
-statement error
-select map(i, a.x -> x + 1) from (values (list_value(1, 2, 3))) tbl(i);
diff --git a/modules/calcite/src/test/sql/function/numeric/test_bit_count.test b/modules/calcite/src/test/sql/function/numeric/test_bit_count.test
deleted file mode 100644
index c5df3f9..0000000
--- a/modules/calcite/src/test/sql/function/numeric/test_bit_count.test
+++ /dev/null
@@ -1,20 +0,0 @@
-# name: test/sql/function/numeric/test_bit_count.test
-# description: BIT_COUNT test
-# group: [numeric]
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE bits(t tinyint, s smallint, i integer, b bigint)
-
-statement ok
-INSERT INTO bits VALUES (NULL, NULL, NULL, NULL), (31, 1023, 11834119, 50827156903621017), (-59, -517, -575693, -9876543210)
-
-query IIII
-select bit_count(t), bit_count(s),bit_count(i), bit_count(b) from bits
-----
-NULL NULL NULL NULL
-5 10 11 27
-4 14 24 49
-
diff --git a/modules/calcite/src/test/sql/function/numeric/test_factorial.test b/modules/calcite/src/test/sql/function/numeric/test_factorial.test
deleted file mode 100644
index 388c866..0000000
--- a/modules/calcite/src/test/sql/function/numeric/test_factorial.test
+++ /dev/null
@@ -1,52 +0,0 @@
-# name: test/sql/function/numeric/test_factorial.test
-# description: Mod test
-# group: [numeric]
-
-
-statement ok
-PRAGMA enable_verification
-
-query I
-SELECT factorial(0);
-----
-1
-
-query I
-SELECT factorial(-1);
-----
-1
-
-query I
-SELECT factorial(NULL);
-----
-NULL
-
-query I
-SELECT factorial(2);
-----
-2
-
-query I
-SELECT factorial(10);
-----
-3628800
-
-# postfix op
-query I
-SELECT 10!;
-----
-3628800
-
-query I
-SELECT factorial(20);
-----
-2432902008176640000
-
-query I
-SELECT factorial(30);
-----
-265252859812191058636308480000000
-
-# overflow
-statement error
-SELECT factorial(40);
diff --git a/modules/calcite/src/test/sql/function/numeric/test_floor_ceil.test b/modules/calcite/src/test/sql/function/numeric/test_floor_ceil.test
index 0036524..da338a7 100644
--- a/modules/calcite/src/test/sql/function/numeric/test_floor_ceil.test
+++ b/modules/calcite/src/test/sql/function/numeric/test_floor_ceil.test
@@ -3,9 +3,6 @@
# group: [numeric]
statement ok
-PRAGMA enable_verification
-
-statement ok
CREATE TABLE numbers(n DOUBLE)
statement ok
@@ -14,130 +11,130 @@ INSERT INTO numbers VALUES (NULL),(-42.8),(-42.2),(0), (42.2), (42.8)
query I
SELECT cast(CEIL(n::tinyint) as bigint) FROM numbers ORDER BY n
----
-NULL
-42
-42
0
42
42
+NULL
query I
SELECT cast(CEIL(n::smallint) as bigint) FROM numbers ORDER BY n
----
-NULL
-42
-42
0
42
42
+NULL
query I
SELECT cast(CEIL(n::integer) as bigint) FROM numbers ORDER BY n
----
-NULL
-42
-42
0
42
42
+NULL
query I
SELECT cast(CEIL(n::bigint) as bigint) FROM numbers ORDER BY n
----
-NULL
-42
-42
0
42
42
+NULL
query I
SELECT cast(CEIL(n::float) as bigint) FROM numbers ORDER BY n
----
-NULL
-42
-42
0
43
43
+NULL
query I
SELECT cast(CEIL(n::double) as bigint) FROM numbers ORDER BY n
----
-NULL
-42
-42
0
43
43
+NULL
query I
SELECT cast(CEILING(n::double) as bigint) FROM numbers ORDER BY n
----
-NULL
-42
-42
0
43
43
+NULL
query I
SELECT cast(FLOOR(n::tinyint) as bigint) FROM numbers ORDER BY n
----
-NULL
-42
-42
0
42
42
+NULL
query I
SELECT cast(FLOOR(n::smallint) as bigint) FROM numbers ORDER BY n
----
-NULL
-42
-42
0
42
42
+NULL
query I
SELECT cast(FLOOR(n::integer) as bigint) FROM numbers ORDER BY n
----
-NULL
-42
-42
0
42
42
+NULL
query I
SELECT cast(FLOOR(n::bigint) as bigint) FROM numbers ORDER BY n
----
-NULL
-42
-42
0
42
42
+NULL
query I
SELECT cast(FLOOR(n::float) as bigint) FROM numbers ORDER BY n
----
-NULL
-43
-43
0
42
42
+NULL
query I
SELECT cast(FLOOR(n::double) as bigint) FROM numbers ORDER BY n
----
-NULL
-43
-43
0
42
42
+NULL
diff --git a/modules/calcite/src/test/sql/function/numeric/test_gamma.test b/modules/calcite/src/test/sql/function/numeric/test_gamma.test
deleted file mode 100644
index 291ef67..0000000
--- a/modules/calcite/src/test/sql/function/numeric/test_gamma.test
+++ /dev/null
@@ -1,109 +0,0 @@
-# name: test/sql/function/numeric/test_gamma.test
-# description: Test gamma function
-# group: [numeric]
-
-statement ok
-PRAGMA enable_verification
-
-query I
-SELECT gamma(NULL)
-----
-NULL
-
-query I
-SELECT gamma(0)
-----
-NULL
-
-query I
-SELECT gamma(-1)
-----
-NULL
-
-query I
-SELECT gamma(1)
-----
-1
-
-query I
-SELECT gamma(-0.1)
-----
--10.686287021193193
-
-
-query I
-SELECT gamma(2)
-----
-1
-
-query I
-SELECT gamma(10)
-----
-362880.0
-
-query I
-SELECT gamma(2::tinyint)
-----
-1
-
-query I
-SELECT gamma(2::hugeint)
-----
-1
-
-statement error
-SELECT gamma('asdf')
-
-query I
-SELECT lgamma(NULL)
-----
-NULL
-
-query I
-SELECT lgamma(0)
-----
-NULL
-
-query I
-SELECT lgamma(-1)
-----
-NULL
-
-query I
-SELECT lgamma(-100)
-----
-NULL
-
-query I
-SELECT lgamma(1)
-----
-0
-
-query I
-SELECT lgamma(2)
-----
-0
-
-query I
-SELECT lgamma(3)
-----
-0.693147180559945
-
-query I
-SELECT lgamma(10)
-----
-12.801827480081467
-
-
-query I
-SELECT lgamma(2::tinyint)
-----
-0
-
-query I
-SELECT lgamma(2::hugeint)
-----
-0
-
-statement error
-SELECT lgamma('asdf')
\ No newline at end of file
diff --git a/modules/calcite/src/test/sql/function/numeric/test_invalid_math.test b/modules/calcite/src/test/sql/function/numeric/test_invalid_math.test
index eb7ec6b..dc60e5c 100644
--- a/modules/calcite/src/test/sql/function/numeric/test_invalid_math.test
+++ b/modules/calcite/src/test/sql/function/numeric/test_invalid_math.test
@@ -2,31 +2,36 @@
# description: Test invalid input for math functions
# group: [numeric]
-statement ok
-PRAGMA enable_verification
-
query RR
SELECT SQRT(-1), SQRT(0)
----
-NULL 0.000000
+NaN 0.000000
-query RRRRRR
-SELECT LN(-1), LN(0), LOG10(-1), LOG10(0), LOG2(-1), LOG2(0)
+query RRRR
+SELECT LN(-1), LN(0), LOG10(-1), LOG10(0)
----
-NULL NULL NULL NULL NULL NULL
+NaN
+-Infinity
+NaN
+-Infinity
query RRR
-SELECT POW(1e300,100), POW(-1e300,100), POW(-1.0, 0.5)
+SELECT POWER(1e300,100), POWER(-1e300,100), POWER(-1.0, 0.5)
----
-NULL NULL NULL
+Infinity
+Infinity
+NaN
query RR
SELECT EXP(1e300), EXP(1e100)
----
-NULL NULL
+Infinity
+Infinity
query RRR
SELECT ACOS(3), ACOS(100), DEGREES(1e308)
----
-NULL NULL NULL
+NaN
+NaN
+Infinity
diff --git a/modules/calcite/src/test/sql/function/numeric/test_mod.test b/modules/calcite/src/test/sql/function/numeric/test_mod.test
index d7f48b0..3ba98dc 100644
--- a/modules/calcite/src/test/sql/function/numeric/test_mod.test
+++ b/modules/calcite/src/test/sql/function/numeric/test_mod.test
@@ -3,32 +3,23 @@
# group: [numeric]
statement ok
-PRAGMA enable_verification
-
-statement ok
CREATE TABLE modme(a DOUBLE, b INTEGER)
statement ok
INSERT INTO modme VALUES (42.123456, 3)
query R
-select mod(a, 40) from modme
+select CAST(mod(a, 40) AS FLOAT) from modme
----
2.123456
-# mod 0 results in NULL: SQLite behavior
-query I
-select mod(42, 0)
-----
-NULL
-
query R
-select mod(a, 2) from modme
+select CAST(mod(a, 2) AS FLOAT) from modme
----
0.123456
query R
-select mod(b, 2.1) from modme
+select CAST(mod(b, 2.1) AS FLOAT) from modme
----
0.900000
diff --git a/modules/calcite/src/test/sql/function/numeric/test_pg_math.test b/modules/calcite/src/test/sql/function/numeric/test_pg_math.test
index dcaf6e7..997b31f 100644
--- a/modules/calcite/src/test/sql/function/numeric/test_pg_math.test
+++ b/modules/calcite/src/test/sql/function/numeric/test_pg_math.test
@@ -4,9 +4,6 @@
# see https://www.postgresql.org/docs/10/functions-math.html
-statement ok
-PRAGMA enable_verification
-
query R
select abs(-17.4)
----
@@ -28,9 +25,9 @@ select ceiling(-95.3)
-95.000000
query R
-select exp(1.0)
+select CAST(exp(1.0) AS FLOAT)
----
-2.718282
+2.7182817
query R
select floor(-42.8)
@@ -38,14 +35,9 @@ select floor(-42.8)
-43.000000
query R
-select ln(2.0)
-----
-0.693147
-
-query R
-select log(100.0)
+select CAST(ln(2.0) AS FLOAT)
----
-2.000000
+0.6931472
query R
select log10(100.0)
@@ -53,27 +45,17 @@ select log10(100.0)
2.000000
query R
-select log2(4.0)
-----
-2.000000
-
-query R
-select pi()
-----
-3.141593
-
-query R
-select sqrt(2.0)
+select CAST(sqrt(2.0) AS FLOAT)
----
-1.414214
+1.4142135
query R
-select radians(45.0)
+select CAST(radians(45.0) AS FLOAT)
----
-0.785398
+0.7853982
query R
-select degrees(0.5)
+select CAST(degrees(0.5) AS FLOAT)
----
28.647890
diff --git a/modules/calcite/src/test/sql/function/numeric/test_pow.test b/modules/calcite/src/test/sql/function/numeric/test_pow.test
index f89b3ec..5b62b04 100644
--- a/modules/calcite/src/test/sql/function/numeric/test_pow.test
+++ b/modules/calcite/src/test/sql/function/numeric/test_pow.test
@@ -3,36 +3,33 @@
# group: [numeric]
statement ok
-PRAGMA enable_verification
-
-statement ok
CREATE TABLE powerme(a DOUBLE, b INTEGER)
statement ok
INSERT INTO powerme VALUES (2.1, 3)
query R
-select pow(a, 0) from powerme
+select power(a, 0) from powerme
----
1.000000
query R
-select pow(b, -2) from powerme
+select power(b, -2)::REAL from powerme
----
-0.111111
+0.11111111
query R
-select pow(a, b) from powerme
+select power(a, b)::REAL from powerme
----
9.261000
query R
-select pow(b, a) from powerme
+select power(b, a)::REAL from powerme
----
10.045109
query R
-select power(b, a) from powerme
+select power(b, a)::REAL from powerme
----
10.045109
diff --git a/modules/calcite/src/test/sql/function/numeric/test_random.test b/modules/calcite/src/test/sql/function/numeric/test_random.test
index 1531751..b5ad82e 100644
--- a/modules/calcite/src/test/sql/function/numeric/test_random.test
+++ b/modules/calcite/src/test/sql/function/numeric/test_random.test
@@ -2,97 +2,26 @@
# description: Test random & setseed functions
# group: [numeric]
-# we need to be a bit more clever here for testing random
-# it's very unlikely that three random variables are exactly equivalent
-
-statement ok
-BEGIN TRANSACTION
-
statement ok
-CREATE TEMPORARY TABLE t1 AS SELECT RANDOM() a;
+CREATE TABLE t1 (a DOUBLE);
statement ok
-CREATE TEMPORARY TABLE t2 AS SELECT RANDOM() b;
-
-statement ok
-CREATE TEMPORARY TABLE t3 AS SELECT RANDOM() c;
-
-# empty result on this join
-query I
-SELECT COUNT(*) FROM (SELECT a FROM t1 JOIN t2 ON (a=b) JOIN t3 ON (b=c)) s1
-----
-0
+CREATE TABLE t2 (b DOUBLE);
statement ok
-ROLLBACK
+CREATE TABLE t3 (c DOUBLE);
-# with setseed we can enforce the equivalence of these values
statement ok
-select setseed(0.1)
+INSERT INTO t1 SELECT RAND();
statement ok
-CREATE TEMPORARY TABLE t1 AS SELECT RANDOM() a;
+INSERT INTO t2 SELECT RAND();
statement ok
-select setseed(0.1)
+INSERT INTO t3 SELECT RAND();
-statement ok
-CREATE TEMPORARY TABLE t2 AS SELECT RANDOM() b;
-
-statement ok
-select setseed(0.1)
-
-statement ok
-CREATE TEMPORARY TABLE t3 AS SELECT RANDOM() c;
-
-# now the join has one value
+# empty result on this join
query I
SELECT COUNT(*) FROM (SELECT a FROM t1 JOIN t2 ON (a=b) JOIN t3 ON (b=c)) s1
----
-1
-
-# incorrect usage of setseed
-statement error
-select setseed(1.1)
-
-statement error
-select setseed(-1.1)
-
-# we can use setseed with a table as well
-statement ok
-CREATE TABLE seeds(a DOUBLE)
-
-statement ok
-INSERT INTO seeds VALUES (-0.1), (0.0), (0.1)
-
-query IR
-select setseed(a), a from seeds;
-----
-NULL -0.100000
-NULL 0.000000
-NULL 0.100000
-
-# use random in some complicated expressions
-statement ok
-CREATE TABLE numbers(a INTEGER)
-
-statement ok
-INSERT INTO numbers VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
-
-query I
-select case when min(random()) >= 0 then 1 else 0 end from numbers;
-----
-1
-
-query I
-select case when max(random()) < 1 then 1 else 0 end from numbers;
-----
-1
-
-# we can order by random
-statement ok
-select * from numbers order by random()
-
-# we can select random
-statement ok
-select random() from numbers
+0
diff --git a/modules/calcite/src/test/sql/function/numeric/test_round.test b/modules/calcite/src/test/sql/function/numeric/test_round.test
index 0756fa6..119b138 100644
--- a/modules/calcite/src/test/sql/function/numeric/test_round.test
+++ b/modules/calcite/src/test/sql/function/numeric/test_round.test
@@ -3,9 +3,6 @@
# group: [numeric]
statement ok
-PRAGMA enable_verification
-
-statement ok
CREATE TABLE roundme(a DOUBLE, b INTEGER)
statement ok
@@ -27,9 +24,9 @@ select round(42.12345::DOUBLE, 2)
42.120000
query RR
-select round(42.12345::DOUBLE, 4), round(42.1235::DOUBLE, 1000)
+select round(42.12345::DOUBLE, 4), round(42.12345::DOUBLE, 1000)
----
-42.123400 42.12345
+42.1235 42.12345
query I
select round(42::DOUBLE, 0)
diff --git a/modules/calcite/src/test/sql/function/numeric/test_trigo.test b/modules/calcite/src/test/sql/function/numeric/test_trigo.test
index a9b4c3d..1bb03ac 100644
--- a/modules/calcite/src/test/sql/function/numeric/test_trigo.test
+++ b/modules/calcite/src/test/sql/function/numeric/test_trigo.test
@@ -3,9 +3,6 @@
# group: [numeric]
statement ok
-PRAGMA enable_verification
-
-statement ok
CREATE TABLE numbers(n DOUBLE)
statement ok
@@ -14,242 +11,242 @@ INSERT INTO numbers VALUES (-42),(-1),(0), (1), (42), (NULL)
query I
SELECT cast(SIN(n::tinyint)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
916
-841
0
841
-916
+NULL
query I
SELECT cast(SIN(n::smallint)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
916
-841
0
841
-916
+NULL
query I
SELECT cast(SIN(n::integer)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
916
-841
0
841
-916
+NULL
query I
SELECT cast(SIN(n::bigint)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
916
-841
0
841
-916
+NULL
query I
SELECT cast(SIN(n::float)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
916
-841
0
841
-916
+NULL
query I
SELECT cast(SIN(n::double)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
916
-841
0
841
-916
+NULL
query I
SELECT cast(COS(n::tinyint)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-399
540
1000
540
-399
+NULL
query I
SELECT cast(COS(n::smallint)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-399
540
1000
540
-399
+NULL
query I
SELECT cast(COS(n::integer)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-399
540
1000
540
-399
+NULL
query I
SELECT cast(COS(n::bigint)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-399
540
1000
540
-399
+NULL
query I
SELECT cast(COS(n::float)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-399
540
1000
540
-399
+NULL
query I
SELECT cast(COS(n::double)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-399
540
1000
540
-399
+NULL
query I
SELECT cast(TAN(n::tinyint)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-2291
-1557
0
1557
2291
+NULL
query I
SELECT cast(TAN(n::smallint)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-2291
-1557
0
1557
2291
+NULL
query I
SELECT cast(TAN(n::integer)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-2291
-1557
0
1557
2291
+NULL
query I
SELECT cast(TAN(n::bigint)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-2291
-1557
0
1557
2291
+NULL
query I
SELECT cast(TAN(n::float)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-2291
-1557
0
1557
2291
+NULL
query I
SELECT cast(TAN(n::double)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-2291
-1557
0
1557
2291
+NULL
query I
SELECT cast(ATAN(n::tinyint)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-1546
-785
0
785
1546
+NULL
query I
SELECT cast(ATAN(n::smallint)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-1546
-785
0
785
1546
+NULL
query I
SELECT cast(ATAN(n::integer)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-1546
-785
0
785
1546
+NULL
query I
SELECT cast(ATAN(n::bigint)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-1546
-785
0
785
1546
+NULL
query I
SELECT cast(ATAN(n::float)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-1546
-785
0
785
1546
+NULL
query I
SELECT cast(ATAN(n::double)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-1546
-785
0
785
1546
+NULL
query I
SELECT cast(ASIN(n::tinyint)*1000 as bigint) FROM numbers WHERE n between -1 and 1 ORDER BY n
@@ -389,60 +386,60 @@ SELECT cast(COT(n::double)*1000 as bigint) FROM numbers WHERE n > 0.1 OR N < -0
query I
SELECT cast(ATAN2(n::tinyint, 42)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-785
-23
0
23
785
+NULL
query I
SELECT cast(ATAN2(n::smallint, 42)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-785
-23
0
23
785
+NULL
query I
SELECT cast(ATAN2(n::integer, 42)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-785
-23
0
23
785
+NULL
query I
SELECT cast(ATAN2(n::bigint, 42)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-785
-23
0
23
785
+NULL
query I
SELECT cast(ATAN2(n::float, 42)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-785
-23
0
23
785
+NULL
query I
SELECT cast(ATAN2(n::double, 42)*1000 as bigint) FROM numbers ORDER BY n
----
-NULL
-785
-23
0
23
785
+NULL
diff --git a/modules/calcite/src/test/sql/function/numeric/test_type_resolution.test b/modules/calcite/src/test/sql/function/numeric/test_type_resolution.test
index e1efa69..937d0a5 100644
--- a/modules/calcite/src/test/sql/function/numeric/test_type_resolution.test
+++ b/modules/calcite/src/test/sql/function/numeric/test_type_resolution.test
@@ -1,9 +1,6 @@
# name: test/sql/function/numeric/test_type_resolution.test
# group: [numeric]
-statement ok
-PRAGMA enable_verification
-
# test type resolution of various additions
query I
SELECT 1::TINYINT + 1::TINYINT
diff --git a/modules/calcite/src/test/sql/function/numeric/test_unary.test b/modules/calcite/src/test/sql/function/numeric/test_unary.test
index 23d1a97..3b869a6 100644
--- a/modules/calcite/src/test/sql/function/numeric/test_unary.test
+++ b/modules/calcite/src/test/sql/function/numeric/test_unary.test
@@ -3,9 +3,6 @@
# group: [numeric]
statement ok
-PRAGMA enable_verification
-
-statement ok
CREATE TABLE test(i INTEGER)
statement ok
diff --git a/modules/calcite/src/test/sql/function/operator/test_arithmetic.test b/modules/calcite/src/test/sql/function/operator/test_arithmetic.test
index 47d4a24a..837d75f 100644
--- a/modules/calcite/src/test/sql/function/operator/test_arithmetic.test
+++ b/modules/calcite/src/test/sql/function/operator/test_arithmetic.test
@@ -3,9 +3,6 @@
# group: [operator]
statement ok
-PRAGMA enable_verification
-
-statement ok
CREATE TABLE integers(i INTEGER);
statement ok
@@ -19,128 +16,128 @@ INSERT INTO integers VALUES (1), (2), (3), (NULL)
query TT
SELECT i+2=5, 5=i+2 FROM integers ORDER BY i
----
+false false
+false false
+true true
NULL NULL
-0 0
-0 0
-1 1
# 2+i=5 => i=3
query TT
SELECT 2+i=5, 5=2+i FROM integers ORDER BY i
----
+false false
+false false
+true true
NULL NULL
-0 0
-0 0
-1 1
# multiplication is unordered
# i*2=6 => i=3
query TT
SELECT i*2=6, 6=i*2 FROM integers ORDER BY i
----
+false false
+false false
+true true
NULL NULL
-0 0
-0 0
-1 1
# 2*i=6 => i=3
query TT
SELECT 2*i=6, 6=2*i FROM integers ORDER BY i
----
+false false
+false false
+true true
NULL NULL
-0 0
-0 0
-1 1
# i*2=5 (this comparison is always FALSE, except if i is NULL in which case it is NULL)
query T
SELECT i*2=5 FROM integers ORDER BY i
----
+false
+false
+false
NULL
-0
-0
-0
# i*0=5
query T
SELECT i*0=5 FROM integers ORDER BY i
----
+false
+false
+false
NULL
-0
-0
-0
# -i>-2 => i<2
query T
SELECT -i>-2 FROM integers ORDER BY i
----
+true
+false
+false
NULL
-1
-0
-0
# subtraction is ordered
# i-2=1 => i=3
query TT
SELECT i-2=1, 1=i-2 FROM integers ORDER BY i
----
+false false
+false false
+true true
NULL NULL
-0 0
-0 0
-1 1
# 3-i=1 => i=2
query TT
SELECT 3-i=1, 1=3-i FROM integers ORDER BY i
----
+false false
+true true
+false false
NULL NULL
-0 0
-1 1
-0 0
# non-equality comparisons should also be flipped in this case
# 3-i<2 => i>2
query TT
SELECT 3-i<2, 2>3-i FROM integers ORDER BY i
----
+false false
+true true
+true true
NULL NULL
-0 0
-1 1
-1 1
query TT
SELECT 3-i<=1, 1>=3-i FROM integers ORDER BY i
----
+false false
+true true
+true true
NULL NULL
-0 0
-1 1
-1 1
# division is ordered
# i/2=1 => i>=2 or i<=3
query TT
SELECT i/2=1, 1=i/2 FROM integers ORDER BY i
----
+false false
+true true
+true true
NULL NULL
-0 0
-1 1
-1 1
# 2/i=1 => i=2
query TT
SELECT 2/i=1, 1=2/i FROM integers ORDER BY i
----
+false false
+true true
+false false
NULL NULL
-0 0
-1 1
-0 0
# 3/i=2 => i=2
query TT
SELECT 2/i=1, 1=2/i FROM integers ORDER BY i
----
+false false
+true true
+false false
NULL NULL
-0 0
-1 1
-0 0
diff --git a/modules/calcite/src/test/sql/function/operator/test_arithmetic_sqllogic.test b/modules/calcite/src/test/sql/function/operator/test_arithmetic_sqllogic.test
index 5a36114..978bda5 100644
--- a/modules/calcite/src/test/sql/function/operator/test_arithmetic_sqllogic.test
+++ b/modules/calcite/src/test/sql/function/operator/test_arithmetic_sqllogic.test
@@ -3,9 +3,6 @@
# group: [operator]
statement ok
-PRAGMA enable_verification
-
-statement ok
CREATE TABLE tab0(col0 INTEGER, col1 INTEGER, col2 INTEGER);
statement ok
diff --git a/modules/calcite/src/test/sql/function/operator/test_bitwise_ops.test b/modules/calcite/src/test/sql/function/operator/test_bitwise_ops.test
deleted file mode 100644
index 13e4fe6..0000000
--- a/modules/calcite/src/test/sql/function/operator/test_bitwise_ops.test
+++ /dev/null
@@ -1,68 +0,0 @@
-# name: test/sql/function/operator/test_bitwise_ops.test
-# description: Test scalar bitwise ops
-# group: [operator]
-
-statement ok
-PRAGMA enable_verification
-
-# left shift
-query III
-SELECT 1 << 2, NULL << 2, 2 << NULL
-----
-4 NULL NULL
-
-# right shift
-query IIII
-SELECT 16 >> 2, 1 >> 2, NULL >> 2, 2 >> NULL
-----
-4 0 NULL NULL
-
-# bitwise and
-query IIIII
-SELECT 1 & 1, 1 & 0, 0 & 0, NULL & 1, 1 & NULL
-----
-1 0 0 NULL NULL
-
-# bitwise or
-query IIIII
-SELECT 1 | 1, 1 | 0, 0 | 0, NULL | 1, 1 | NULL
-----
-1 1 0 NULL NULL
-
-# bitwise xor
-query IIIII
-SELECT xor(1, 1), xor(1, 0), xor(0, 0), xor(NULL, 1), xor(1, NULL)
-----
-0 1 0 NULL NULL
-
-# out of range shifts return 0
-query IIII
-SELECT 1::TINYINT << -1::TINYINT, 1::TINYINT >> -1::TINYINT, 1::TINYINT << 12::TINYINT, 1::TINYINT >> 12::TINYINT
-----
-0 0 0 0
-
-query IIII
-SELECT 1::SMALLINT << -1::SMALLINT, 1::SMALLINT >> -1::SMALLINT, 1::SMALLINT << 20::SMALLINT, 1::SMALLINT >> 20::SMALLINT
-----
-0 0 0 0
-
-query IIII
-SELECT 1::INT << -1::INT, 1::INT >> -1::INT, 1::INT << 40::INT, 1::INT >> 40::INT
-----
-0 0 0 0
-
-query IIII
-SELECT 1::BIGINT << -1::BIGINT, 1::BIGINT >> -1::BIGINT, 1::BIGINT << 1000::BIGINT, 1::BIGINT >> 1000::BIGINT
-----
-0 0 0 0
-
-# test invalid bitwise ops
-statement error
-SELECT 'hello' << 3
-
-statement error
-SELECT 3 << 'hello'
-
-statement error
-SELECT 2.0 << 1
-
diff --git a/modules/calcite/src/test/sql/function/operator/test_bitwise_ops_types.test b/modules/calcite/src/test/sql/function/operator/test_bitwise_ops_types.test
deleted file mode 100644
index 4908a77..0000000
--- a/modules/calcite/src/test/sql/function/operator/test_bitwise_ops_types.test
+++ /dev/null
@@ -1,103 +0,0 @@
-# name: test/sql/function/operator/test_bitwise_ops_types.test
-# description: Test bitwise ops with tables and different types
-# group: [operator]
-
-statement ok
-PRAGMA enable_verification
-
-# type TINYINT
-statement ok
-BEGIN TRANSACTION
-
-statement ok
-CREATE TABLE bitwise_test(i TINYINT, j TINYINT)
-
-statement ok
-INSERT INTO bitwise_test VALUES (1, 1), (1, 0), (0, 1), (0, 0), (1, NULL), (NULL, 1), (NULL, NULL)
-
-query IIIII
-SELECT i << j, i >> j, i & j, i | j, xor(i, j) FROM bitwise_test
-----
-2 0 1 1 0
-1 1 0 1 1
-0 0 0 1 1
-0 0 0 0 0
-NULL NULL NULL NULL NULL
-NULL NULL NULL NULL NULL
-NULL NULL NULL NULL NULL
-
-statement ok
-ROLLBACK
-
-# type SMALLINT
-statement ok
-BEGIN TRANSACTION
-
-statement ok
-CREATE TABLE bitwise_test(i SMALLINT, j SMALLINT)
-
-statement ok
-INSERT INTO bitwise_test VALUES (1, 1), (1, 0), (0, 1), (0, 0), (1, NULL), (NULL, 1), (NULL, NULL)
-
-query IIIII
-SELECT i << j, i >> j, i & j, i | j, xor(i, j) FROM bitwise_test
-----
-2 0 1 1 0
-1 1 0 1 1
-0 0 0 1 1
-0 0 0 0 0
-NULL NULL NULL NULL NULL
-NULL NULL NULL NULL NULL
-NULL NULL NULL NULL NULL
-
-statement ok
-ROLLBACK
-
-# type INTEGER
-statement ok
-BEGIN TRANSACTION
-
-statement ok
-CREATE TABLE bitwise_test(i INTEGER, j INTEGER)
-
-statement ok
-INSERT INTO bitwise_test VALUES (1, 1), (1, 0), (0, 1), (0, 0), (1, NULL), (NULL, 1), (NULL, NULL)
-
-query IIIII
-SELECT i << j, i >> j, i & j, i | j, xor(i, j) FROM bitwise_test
-----
-2 0 1 1 0
-1 1 0 1 1
-0 0 0 1 1
-0 0 0 0 0
-NULL NULL NULL NULL NULL
-NULL NULL NULL NULL NULL
-NULL NULL NULL NULL NULL
-
-statement ok
-ROLLBACK
-
-# type BIGINT
-statement ok
-BEGIN TRANSACTION
-
-statement ok
-CREATE TABLE bitwise_test(i BIGINT, j BIGINT)
-
-statement ok
-INSERT INTO bitwise_test VALUES (1, 1), (1, 0), (0, 1), (0, 0), (1, NULL), (NULL, 1), (NULL, NULL)
-
-query IIIII
-SELECT i << j, i >> j, i & j, i | j, xor(i, j) FROM bitwise_test
-----
-2 0 1 1 0
-1 1 0 1 1
-0 0 0 1 1
-0 0 0 0 0
-NULL NULL NULL NULL NULL
-NULL NULL NULL NULL NULL
-NULL NULL NULL NULL NULL
-
-statement ok
-ROLLBACK
-
diff --git a/modules/calcite/src/test/sql/function/operator/test_comparison.test b/modules/calcite/src/test/sql/function/operator/test_comparison.test
index f6da08d..cf9dba4 100644
--- a/modules/calcite/src/test/sql/function/operator/test_comparison.test
+++ b/modules/calcite/src/test/sql/function/operator/test_comparison.test
@@ -2,20 +2,15 @@
# description: Test basic comparison statements
# group: [operator]
-statement ok
-PRAGMA enable_verification
-
-# = and == are the same
-query TTTTT
-SELECT 1 == 1, 1 = 1, 1 == 0, 1 = 0, 1 == NULL
+query TTT
+SELECT 1 = 1, 1 = 0, 1 = NULL
----
-1 1 0 0 NULL
+true false NULL
-# != and <> are the same
-query TTTTT
-SELECT 1 <> 1, 1 != 1, 1 <> 0, 1 != 0, 1 <> NULL
+query TTT
+SELECT 1 <> 1, 1 <> 0, 1 <> NULL
----
-0 0 1 1 NULL
+false true NULL
# Test auto casting of comparison statements
# string <> number comparisons should result in the string being cast to a number
@@ -32,16 +27,16 @@ select 20.0 = 'abc'
query T
select '1000' > 20
----
-1
+true
# ... but '1000' < '20'!
query T
select '1000' > '20'
----
-0
+false
query T
select ('abc' between '20' and 'true');
----
-1
+true
diff --git a/modules/calcite/src/test/sql/function/operator/test_conjunction.test b/modules/calcite/src/test/sql/function/operator/test_conjunction.test
index 363ec1a..3e286a8 100644
--- a/modules/calcite/src/test/sql/function/operator/test_conjunction.test
+++ b/modules/calcite/src/test/sql/function/operator/test_conjunction.test
@@ -2,9 +2,6 @@
# description: Test conjunction statements
# group: [operator]
-statement ok
-PRAGMA enable_verification
-
# create table
statement ok
CREATE TABLE a (i integer, j integer);
@@ -21,32 +18,32 @@ SELECT * FROM a WHERE (i > 3 AND j < 5) OR (i > 3 AND j > 5)
query T
SELECT true AND true
----
-1
+true
query T
SELECT true AND false
----
-0
+false
query T
SELECT false AND true
----
-0
+false
query T
SELECT false AND false
----
-0
+false
query T
SELECT false AND NULL
----
-0
+false
query T
SELECT NULL AND false
----
-0
+false
query T
SELECT NULL AND true
@@ -61,17 +58,17 @@ NULL
query T
SELECT true OR true
----
-1
+true
query T
SELECT true OR NULL
----
-1
+true
query T
SELECT NULL OR true
----
-1
+true
query T
SELECT false OR NULL
@@ -86,107 +83,107 @@ NULL
query T
SELECT true OR false
----
-1
+true
query T
SELECT false OR true
----
-1
+true
query T
SELECT false OR false
----
-0
+false
# test single constant in conjunctions
query T
SELECT true AND i>3 FROM a ORDER BY i
----
-0
-1
-1
+false
+true
+true
query T
SELECT i>3 AND true FROM a ORDER BY i
----
-0
-1
-1
+false
+true
+true
query T
SELECT 2>3 AND i>3 FROM a ORDER BY i
----
-0
-0
-0
+false
+false
+false
query T
SELECT false AND i>3 FROM a ORDER BY i
----
-0
-0
-0
+false
+false
+false
query T
SELECT i>3 AND false FROM a ORDER BY i
----
-0
-0
-0
+false
+false
+false
query T
SELECT false OR i>3 FROM a ORDER BY i
----
-0
-1
-1
+false
+true
+true
query T
SELECT i>3 OR false FROM a ORDER BY i
----
-0
-1
-1
+false
+true
+true
query T
SELECT true OR i>3 FROM a ORDER BY i
----
-1
-1
-1
+true
+true
+true
query T
SELECT i>3 OR true FROM a ORDER BY i
----
-1
-1
-1
+true
+true
+true
query T
SELECT NULL OR i>3 FROM a ORDER BY i
----
NULL
-1
-1
+true
+true
query T
SELECT i>3 OR NULL FROM a ORDER BY i
----
NULL
-1
-1
+true
+true
query T
SELECT NULL AND i>3 FROM a ORDER BY i
----
-0
+false
NULL
NULL
query T
SELECT i>3 AND NULL FROM a ORDER BY i
----
-0
+false
NULL
NULL
diff --git a/modules/calcite/src/test/sql/function/string/md5.test b/modules/calcite/src/test/sql/function/string/md5.test
index e2adc1f..9153675 100644
--- a/modules/calcite/src/test/sql/function/string/md5.test
+++ b/modules/calcite/src/test/sql/function/string/md5.test
@@ -2,9 +2,6 @@
# description: Test the md5 function
# group: [string]
-statement ok
-PRAGMA enable_verification
-
query II
select md5('hello'), md5(NULL)
----
@@ -12,7 +9,10 @@ select md5('hello'), md5(NULL)
NULL
statement ok
-CREATE TABLE strings AS SELECT s::VARCHAR s FROM generate_series(0,10,1) t(s)
+CREATE TABLE strings (s VARCHAR);
+
+statement ok
+INSERT INTO strings SELECT x::VARCHAR s FROM table(system_range(0, 10))
query II
select md5(s), md5('1') from strings ORDER BY s
diff --git a/modules/calcite/src/test/sql/function/string/regex_filter_pushdown.test b/modules/calcite/src/test/sql/function/string/regex_filter_pushdown.test_ignore
similarity index 85%
rename from modules/calcite/src/test/sql/function/string/regex_filter_pushdown.test
rename to modules/calcite/src/test/sql/function/string/regex_filter_pushdown.test_ignore
index 8798eec..7806268 100644
--- a/modules/calcite/src/test/sql/function/string/regex_filter_pushdown.test
+++ b/modules/calcite/src/test/sql/function/string/regex_filter_pushdown.test_ignore
@@ -1,12 +1,10 @@
# name: test/sql/function/string/regex_filter_pushdown.test
# description: regex filter push test
# group: [string]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14991
statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE regex(s STRING)
+CREATE TABLE regex(s VARCHAR)
statement ok
INSERT INTO regex VALUES ('asdf'), ('xxxx'), ('aaaa')
diff --git a/modules/calcite/src/test/sql/function/string/regex_replace.test b/modules/calcite/src/test/sql/function/string/regex_replace.test_ignore
similarity index 95%
rename from modules/calcite/src/test/sql/function/string/regex_replace.test
rename to modules/calcite/src/test/sql/function/string/regex_replace.test_ignore
index c9c9a9c..befd414 100644
--- a/modules/calcite/src/test/sql/function/string/regex_replace.test
+++ b/modules/calcite/src/test/sql/function/string/regex_replace.test_ignore
@@ -1,9 +1,7 @@
# name: test/sql/function/string/regex_replace.test
# description: regex replace test
# group: [string]
-
-statement ok
-PRAGMA enable_verification
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-15001
# standard replace
query T
diff --git a/modules/calcite/src/test/sql/function/string/regex_search.test b/modules/calcite/src/test/sql/function/string/regex_search.test_ignore
similarity index 97%
rename from modules/calcite/src/test/sql/function/string/regex_search.test
rename to modules/calcite/src/test/sql/function/string/regex_search.test_ignore
index 88ad903..04823ff 100644
--- a/modules/calcite/src/test/sql/function/string/regex_search.test
+++ b/modules/calcite/src/test/sql/function/string/regex_search.test_ignore
@@ -1,9 +1,7 @@
# name: test/sql/function/string/regex_search.test
# description: regex search test
# group: [string]
-
-statement ok
-PRAGMA enable_verification
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14991
# constant strings
query T
diff --git a/modules/calcite/src/test/sql/function/string/strip_accents.test b/modules/calcite/src/test/sql/function/string/strip_accents.test
deleted file mode 100644
index 2442af0..0000000
--- a/modules/calcite/src/test/sql/function/string/strip_accents.test
+++ /dev/null
@@ -1,37 +0,0 @@
-# name: test/sql/function/string/strip_accents.test
-# description: Test strip accents function
-# group: [string]
-
-query TT
-SELECT strip_accents('hello'), strip_accents('héllo')
-----
-hello hello
-
-query TT
-SELECT strip_accents('mühleisen'), strip_accents('hannes mühleisen')
-----
-muhleisen hannes muhleisen
-
-statement ok
-CREATE TABLE collate_test(s VARCHAR, str VARCHAR)
-
-statement ok
-INSERT INTO collate_test VALUES ('äää', 'aaa')
-
-statement ok
-INSERT INTO collate_test VALUES ('hännës mühlëïsën', 'hannes muhleisen')
-
-statement ok
-INSERT INTO collate_test VALUES ('olá', 'ola')
-
-statement ok
-INSERT INTO collate_test VALUES ('ôâêóáëòõç', 'oaeoaeooc')
-
-query T
-SELECT strip_accents(s)=strip_accents(str) FROM collate_test
-----
-1
-1
-1
-1
-
diff --git a/modules/calcite/src/test/sql/function/string/test_ascii.test b/modules/calcite/src/test/sql/function/string/test_ascii.test
index c08a02f..04f915e 100644
--- a/modules/calcite/src/test/sql/function/string/test_ascii.test
+++ b/modules/calcite/src/test/sql/function/string/test_ascii.test
@@ -2,9 +2,6 @@
# description: test ascii() and chr() functions
# group: [string]
-#statement ok
-#PRAGMA enable_verification
-
# Some ascii checks
query I
SELECT ascii('x')
diff --git a/modules/calcite/src/test/sql/function/string/test_bit_length.test b/modules/calcite/src/test/sql/function/string/test_bit_length.test
deleted file mode 100644
index c76dfcb2..0000000
--- a/modules/calcite/src/test/sql/function/string/test_bit_length.test
+++ /dev/null
@@ -1,52 +0,0 @@
-# name: test/sql/function/string/test_bit_length.test
-# description: BIT_LENGTH test
-# group: [string]
-
-statement ok
-PRAGMA enable_verification
-
-# test on scalars
-query IIIIII
-select BIT_LENGTH(NULL), BIT_LENGTH(''), BIT_LENGTH('$'), BIT_LENGTH('¢'), BIT_LENGTH('€'), BIT_LENGTH('𐍈')
-----
-NULL 0 8 16 24 32
-
-# test on tables
-statement ok
-CREATE TABLE strings(a STRING, b STRING)
-
-statement ok
-INSERT INTO strings VALUES ('', 'Zero'), ('$', NULL), ('¢','Two'), ('€', NULL), ('𐍈','Four')
-
-query I
-select BIT_LENGTH(a) FROM strings
-----
-0
-8
-16
-24
-32
-
-query I
-select BIT_LENGTH(b) FROM strings
-----
-32
-NULL
-24
-NULL
-32
-
-query I
-select BIT_LENGTH(a) FROM strings WHERE b IS NOT NULL
-----
-0
-16
-32
-
-# test incorrect usage
-statement error
-select BIT_LENGTH()
-
-statement error
-select BIT_LENGTH(1, 2)
-
diff --git a/modules/calcite/src/test/sql/function/string/test_caseconvert.test b/modules/calcite/src/test/sql/function/string/test_caseconvert.test
index d0452fd..bad26aa 100644
--- a/modules/calcite/src/test/sql/function/string/test_caseconvert.test
+++ b/modules/calcite/src/test/sql/function/string/test_caseconvert.test
@@ -2,9 +2,6 @@
# description: UPPER/LOWER test
# group: [string]
-statement ok
-PRAGMA enable_verification
-
# unicode
query TTTT
select UPPER('áaaá'), UPPER('ö'), LOWER('S̈'), UPPER('ω')
@@ -18,59 +15,48 @@ SELECT UPPER('Αα Ββ Γγ Δδ Εε Ζζ Ηη Θθ Ιι Κκ Λλ Μμ Νν
ΑΑ ΒΒ ΓΓ ΔΔ ΕΕ ΖΖ ΗΗ ΘΘ ΙΙ ΚΚ ΛΛ ΜΜ ΝΝ ΞΞ ΟΟ ΠΠ ΡΡ ΣΣΣ ΤΤ ΥΥ ΦΦ ΧΧ ΨΨ ΩΩ αα ββ γγ δδ εε ζζ ηη θθ ιι κκ λλ μμ νν ξξ οο ππ ρρ σσς ττ υυ φφ χχ ψψ ωω
# test upper/lower on scalar values
-query TTTT
-select UPPER(''), UPPER('hello'), UPPER('MotörHead'), UPPER(NULL)
-----
-(empty) HELLO MOTÖRHEAD NULL
-
-query TTTT
-select LOWER(''), LOWER('hello'), LOWER('MotörHead'), LOWER(NULL)
+query TTT
+select UPPER(''), UPPER('hello'), UPPER('MotörHead')
----
-(empty) hello motörhead NULL
+(empty) HELLO MOTÖRHEAD
-# test ucase/lcase on scalar values
-query TTTT
-select UCASE(''), UCASE('hello'), UCASE('MotörHead'), UCASE(NULL)
+query TTT
+select LOWER(''), LOWER('hello'), LOWER('MotörHead')
----
-(empty) HELLO MOTÖRHEAD NULL
-
-query TTTT
-select LCASE(''), LCASE('hello'), LCASE('MotörHead'), LCASE(NULL)
-----
-(empty) hello motörhead NULL
+(empty) hello motörhead
# test on entire tables
statement ok
-CREATE TABLE strings(a STRING, b STRING)
+CREATE TABLE strings(a VARCHAR, b VARCHAR)
statement ok
INSERT INTO strings VALUES ('Hello', 'World'), ('HuLlD', NULL), ('MotörHead','RÄcks')
-query TT
-select UPPER(a), UCASE(a) FROM strings
+query T
+select UPPER(a) FROM strings ORDER BY a
----
-HELLO HELLO
-HULLD HULLD
-MOTÖRHEAD MOTÖRHEAD
+HELLO
+HULLD
+MOTÖRHEAD
-query TT
-select LOWER(a), LCASE(a) FROM strings
+query T
+select LOWER(a) FROM strings ORDER BY a
----
-hello hello
-hulld hulld
-motörhead motörhead
+hello
+hulld
+motörhead
-query TT
-select LOWER(b), LCASE(b) FROM strings
+query T
+select LOWER(b) FROM strings ORDER BY b
----
-world world
-NULL NULL
-räcks räcks
+räcks
+world
+NULL
# test with selection vector
-query TTTT
-select UPPER(a), LOWER(a), UCASE(a), LCASE(a) FROM strings WHERE b IS NOT NULL
+query TT
+select UPPER(a), LOWER(a) FROM strings WHERE b IS NOT NULL ORDER BY a
----
-HELLO hello HELLO hello
-MOTÖRHEAD motörhead MOTÖRHEAD motörhead
+HELLO hello
+MOTÖRHEAD motörhead
diff --git a/modules/calcite/src/test/sql/function/string/test_caseconvert.test b/modules/calcite/src/test/sql/function/string/test_caseconvert.test_ignore
similarity index 51%
copy from modules/calcite/src/test/sql/function/string/test_caseconvert.test
copy to modules/calcite/src/test/sql/function/string/test_caseconvert.test_ignore
index d0452fd..2a7f012 100644
--- a/modules/calcite/src/test/sql/function/string/test_caseconvert.test
+++ b/modules/calcite/src/test/sql/function/string/test_caseconvert.test_ignore
@@ -1,9 +1,14 @@
# name: test/sql/function/string/test_caseconvert.test
# description: UPPER/LOWER test
# group: [string]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-15002
-statement ok
-PRAGMA enable_verification
+# test NULL constant
+query TT
+select UPPER(NULL), LOWER(NULL)
+----
+NULL
+NULL
# unicode
query TTTT
@@ -18,59 +23,48 @@ SELECT UPPER('Αα Ββ Γγ Δδ Εε Ζζ Ηη Θθ Ιι Κκ Λλ Μμ Νν
ΑΑ ΒΒ ΓΓ ΔΔ ΕΕ ΖΖ ΗΗ ΘΘ ΙΙ ΚΚ ΛΛ ΜΜ ΝΝ ΞΞ ΟΟ ΠΠ ΡΡ ΣΣΣ ΤΤ ΥΥ ΦΦ ΧΧ ΨΨ ΩΩ αα ββ γγ δδ εε ζζ ηη θθ ιι κκ λλ μμ νν ξξ οο ππ ρρ σσς ττ υυ φφ χχ ψψ ωω
# test upper/lower on scalar values
-query TTTT
-select UPPER(''), UPPER('hello'), UPPER('MotörHead'), UPPER(NULL)
-----
-(empty) HELLO MOTÖRHEAD NULL
-
-query TTTT
-select LOWER(''), LOWER('hello'), LOWER('MotörHead'), LOWER(NULL)
-----
-(empty) hello motörhead NULL
-
-# test ucase/lcase on scalar values
-query TTTT
-select UCASE(''), UCASE('hello'), UCASE('MotörHead'), UCASE(NULL)
+query TTT
+select UPPER(''), UPPER('hello'), UPPER('MotörHead')
----
-(empty) HELLO MOTÖRHEAD NULL
+(empty) HELLO MOTÖRHEAD
-query TTTT
-select LCASE(''), LCASE('hello'), LCASE('MotörHead'), LCASE(NULL)
+query TTT
+select LOWER(''), LOWER('hello'), LOWER('MotörHead')
----
-(empty) hello motörhead NULL
+(empty) hello motörhead
# test on entire tables
statement ok
-CREATE TABLE strings(a STRING, b STRING)
+CREATE TABLE strings(a VARCHAR, b VARCHAR)
statement ok
INSERT INTO strings VALUES ('Hello', 'World'), ('HuLlD', NULL), ('MotörHead','RÄcks')
-query TT
-select UPPER(a), UCASE(a) FROM strings
+query T
+select UPPER(a) FROM strings ORDER BY a
----
-HELLO HELLO
-HULLD HULLD
-MOTÖRHEAD MOTÖRHEAD
+HELLO
+HULLD
+MOTÖRHEAD
-query TT
-select LOWER(a), LCASE(a) FROM strings
+query T
+select LOWER(a) FROM strings ORDER BY a
----
-hello hello
-hulld hulld
-motörhead motörhead
+hello
+hulld
+motörhead
-query TT
-select LOWER(b), LCASE(b) FROM strings
+query T
+select LOWER(b) FROM strings ORDER BY b
----
-world world
-NULL NULL
-räcks räcks
+räcks
+world
+NULL
# test with selection vector
-query TTTT
-select UPPER(a), LOWER(a), UCASE(a), LCASE(a) FROM strings WHERE b IS NOT NULL
+query TT
+select UPPER(a), LOWER(a) FROM strings WHERE b IS NOT NULL ORDER BY a
----
-HELLO hello HELLO hello
-MOTÖRHEAD motörhead MOTÖRHEAD motörhead
+HELLO hello
+MOTÖRHEAD motörhead
diff --git a/modules/calcite/src/test/sql/function/string/test_complex_unicode.test b/modules/calcite/src/test/sql/function/string/test_complex_unicode.test_ignore
similarity index 95%
rename from modules/calcite/src/test/sql/function/string/test_complex_unicode.test
rename to modules/calcite/src/test/sql/function/string/test_complex_unicode.test_ignore
index fdec2c1..b2fe293 100644
--- a/modules/calcite/src/test/sql/function/string/test_complex_unicode.test
+++ b/modules/calcite/src/test/sql/function/string/test_complex_unicode.test_ignore
@@ -1,9 +1,7 @@
# name: test/sql/function/string/test_complex_unicode.test
# description: Test correct behavior of various string functions under complex unicode characters
# group: [string]
-
-statement ok
-PRAGMA enable_verification
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14677
# length with grapheme clusters
query I
diff --git a/modules/calcite/src/test/sql/function/string/test_concat.test b/modules/calcite/src/test/sql/function/string/test_concat.test
index a6c47bf..9755f46 100644
--- a/modules/calcite/src/test/sql/function/string/test_concat.test
+++ b/modules/calcite/src/test/sql/function/string/test_concat.test
@@ -10,9 +10,6 @@
statement ok
-PRAGMA enable_verification
-
-statement ok
CREATE TABLE strings(s VARCHAR)
statement ok
@@ -22,24 +19,24 @@ INSERT INTO strings VALUES ('hello'), ('world'), (NULL)
query T
SELECT s || ' ' || s FROM strings ORDER BY s
----
-NULL
hello hello
world world
+NULL
# unicode concat
query T
SELECT s || ' ' || '🦆' FROM strings ORDER BY s
----
-NULL
hello 🦆
world 🦆
+NULL
query T
SELECT s || ' ' || '🦆' FROM strings ORDER BY s
----
-NULL
hello 🦆
world 🦆
+NULL
# concat with constant NULL
query T
@@ -49,15 +46,13 @@ NULL
NULL
NULL
-# concat requires at least one argument
+# concat requires at least two argument
statement error
SELECT CONCAT()
# concat with one argument works
-query T
+statement error
SELECT CONCAT('hello')
-----
-hello
# varargs concat
query T
@@ -65,17 +60,16 @@ SELECT CONCAT('hello', 33, 22)
----
hello3322
-# CONCAT ignores null values
+# CONCAT with null
query T
SELECT CONCAT('hello', 33, NULL, 22, NULL)
----
-hello3322
+NULL
# this also applies to non-constant null values
query T
SELECT CONCAT('hello', ' ', s) FROM strings ORDER BY s
----
-hello
hello hello
hello world
-
+NULL
diff --git a/modules/calcite/src/test/sql/function/string/test_concat_function.test b/modules/calcite/src/test/sql/function/string/test_concat_function.test
index 59df589..3098209 100644
--- a/modules/calcite/src/test/sql/function/string/test_concat_function.test
+++ b/modules/calcite/src/test/sql/function/string/test_concat_function.test
@@ -3,47 +3,45 @@
# group: [string]
statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE strings(a STRING, b STRING)
+CREATE TABLE strings(a VARCHAR, b VARCHAR)
statement ok
INSERT INTO strings VALUES ('Hello', 'World'), ('HuLlD', NULL), ('MotörHead','RÄcks')
query T
-select CONCAT(a, 'SUFFIX') FROM strings
+select CONCAT(a, 'SUFFIX') FROM strings ORDER BY a
----
HelloSUFFIX
HuLlDSUFFIX
MotörHeadSUFFIX
query T
-select CONCAT('PREFIX', b) FROM strings
+select CONCAT('PREFIX', b) FROM strings ORDER BY a
----
PREFIXWorld
-PREFIX
+NULL
PREFIXRÄcks
query T
-select CONCAT(a, b) FROM strings
+select CONCAT(a, b) FROM strings ORDER BY a
----
HelloWorld
-HuLlD
+NULL
MotörHeadRÄcks
+
query T
-select CONCAT(a, b, 'SUFFIX') FROM strings
+select CONCAT(a, b, 'SUFFIX') FROM strings ORDER BY a
----
HelloWorldSUFFIX
-HuLlDSUFFIX
+NULL
MotörHeadRÄcksSUFFIX
query T
-select CONCAT(a, b, a) FROM strings
+select CONCAT(a, b, a) FROM strings ORDER BY a
----
HelloWorldHello
-HuLlDHuLlD
+NULL
MotörHeadRÄcksMotörHead
query T
@@ -60,5 +58,5 @@ select '1234567890' || '1234567890', '1234567890' || NULL
query TT
select CONCAT('1234567890', '1234567890'), CONCAT('1234567890', NULL)
----
-12345678901234567890 1234567890
+12345678901234567890 NULL
diff --git a/modules/calcite/src/test/sql/function/string/test_concat_ws.test b/modules/calcite/src/test/sql/function/string/test_concat_ws.test
deleted file mode 100644
index 60c5270..0000000
--- a/modules/calcite/src/test/sql/function/string/test_concat_ws.test
+++ /dev/null
@@ -1,158 +0,0 @@
-# name: test/sql/function/string/test_concat_ws.test
-# description: CONCAT_WS test
-# group: [string]
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE strings(a STRING, b STRING)
-
-statement ok
-INSERT INTO strings VALUES ('Hello', 'World'), ('HuLlD', NULL), ('MotörHead','RÄcks')
-
-query T
-select CONCAT_WS(',',a, 'SUFFIX') FROM strings
-----
-Hello,SUFFIX
-HuLlD,SUFFIX
-MotörHead,SUFFIX
-
-query T
-select CONCAT_WS('@','PREFIX', b) FROM strings
-----
-PREFIX@World
-PREFIX
-PREFIX@RÄcks
-
-query T
-select CONCAT_WS('$',a, b) FROM strings
-----
-Hello$World
-HuLlD
-MotörHead$RÄcks
-
-query T
-select CONCAT_WS(a, b, 'SUFFIX') FROM strings
-----
-WorldHelloSUFFIX
-SUFFIX
-RÄcksMotörHeadSUFFIX
-
-query T
-select CONCAT_WS(a, b, b) FROM strings
-----
-WorldHelloWorld
-(empty)
-RÄcksMotörHeadRÄcks
-
-query T
-select CONCAT_WS('@','1', '2', '3', '4', '5', '6', '7', '8', '9')
-----
-1@2@3@4@5@6@7@8@9
-
-query T
-select CONCAT_WS(b, '[', ']') FROM strings ORDER BY a
-----
-[World]
-NULL
-[RÄcks]
-
-# filters
-query T
-select CONCAT_WS(',', a, 'SUFFIX') FROM strings WHERE a != 'Hello'
-----
-HuLlD,SUFFIX
-MotörHead,SUFFIX
-
-# concat WS needs at least two parameters
-statement error
-select CONCAT_WS()
-
-statement error
-select CONCAT_WS(',')
-
-# one entry: just returns the entry
-query T
-select CONCAT_WS(',', 'hello')
-----
-hello
-
-# NULL in separator results in null
-query T
-select CONCAT_WS(NULL, 'hello')
-----
-NULL
-
-# NULL in data results in empty string
-query T
-select CONCAT_WS(',', NULL)
-----
-(empty)
-
-# NULL separator returns in entire column being NULL
-query T
-select CONCAT_WS(NULL, b, 'SUFFIX') FROM strings
-----
-NULL
-NULL
-NULL
-
-# NULL in separator is just ignored
-query T
-select CONCAT_WS(',', NULL, 'SUFFIX') FROM strings
-----
-SUFFIX
-SUFFIX
-SUFFIX
-
-# empty strings still get split up by the separator
-query T
-select CONCAT_WS(',', '', '')
-----
-,
-
-query T
-select CONCAT_WS(',', '', '', '')
-----
-,,
-
-# but NULLs do not
-query T
-select CONCAT_WS(',', NULL, NULL)
-----
-(empty)
-
-query T
-select CONCAT_WS(',', NULL, NULL, NULL)
-----
-(empty)
-
-query T
-select CONCAT_WS(',', NULL, NULL, 'hello')
-----
-hello
-
-# now test for non-constant separators
-query T
-select CONCAT_WS(a, '', NULL, '') FROM strings ORDER BY a
-----
-Hello
-HuLlD
-MotörHead
-
-query T
-select CONCAT_WS(a, NULL, '', '') FROM strings ORDER BY a;
-----
-Hello
-HuLlD
-MotörHead
-
-# now non-constant separator with a mix of constant and non-constant strings to concatenate
-query T
-select CONCAT_WS(a, NULL, b, '') FROM strings ORDER BY a
-----
-WorldHello
-(empty)
-RÄcksMotörHead
-
diff --git a/modules/calcite/src/test/sql/function/string/test_contains.test b/modules/calcite/src/test/sql/function/string/test_contains.test
deleted file mode 100644
index e7daf87..0000000
--- a/modules/calcite/src/test/sql/function/string/test_contains.test
+++ /dev/null
@@ -1,154 +0,0 @@
-# name: test/sql/function/string/test_contains.test
-# description: Contains test
-# group: [string]
-
-statement ok
-PRAGMA enable_verification
-
-# contains of various lengths
-query IIIIIIIIII
-SELECT CONTAINS('hello world', 'h'),
- CONTAINS('hello world', 'he'),
- CONTAINS('hello world', 'hel'),
- CONTAINS('hello world', 'hell'),
- CONTAINS('hello world', 'hello'),
- CONTAINS('hello world', 'hello '),
- CONTAINS('hello world', 'hello w'),
- CONTAINS('hello world', 'hello wo'),
- CONTAINS('hello world', 'hello wor'),
- CONTAINS('hello world', 'hello worl')
-----
-1 1 1 1 1 1 1 1 1 1
-
-query IIIIIIIIII
-SELECT CONTAINS('hello world', 'a'),
- CONTAINS('hello world', 'ha'),
- CONTAINS('hello world', 'hea'),
- CONTAINS('hello world', 'hela'),
- CONTAINS('hello world', 'hella'),
- CONTAINS('hello world', 'helloa'),
- CONTAINS('hello world', 'hello a'),
- CONTAINS('hello world', 'hello wa'),
- CONTAINS('hello world', 'hello woa'),
- CONTAINS('hello world', 'hello wora')
-----
-0 0 0 0 0 0 0 0 0 0
-
-# empty contains
-query III
-select contains('hello', ''), contains('', ''), contains(NULL, '')
-----
-1 1 NULL
-
-statement ok
-CREATE TABLE strings(s VARCHAR, off INTEGER, length INTEGER);
-
-statement ok
-INSERT INTO strings VALUES ('hello', 1, 2), ('world', 2, 3), ('b', 1, 1), (NULL, 2, 2)
-
-# Test first letter
-query T
-SELECT contains(s,'h') FROM strings
-----
-1
-0
-0
-NULL
-
-# Test second letter
-query T
-SELECT contains(s,'e') FROM strings
-----
-1
-0
-0
-NULL
-
-# Test last letter
-query T
-SELECT contains(s,'d') FROM strings
-----
-0
-1
-0
-NULL
-
-# Test multiple letters
-query T
-SELECT contains(s,'he') FROM strings
-----
-1
-0
-0
-NULL
-
-# Test multiple letters in the middle
-query T
-SELECT contains(s,'ello') FROM strings
-----
-1
-0
-0
-NULL
-
-# Test multiple letters at the end
-query T
-SELECT contains(s,'lo') FROM strings
-----
-1
-0
-0
-NULL
-
-# Test no match
-query T
-SELECT contains(s,'he-man') FROM strings
-----
-0
-0
-0
-NULL
-
-# Test matching needle in multiple rows
-query T
-SELECT contains(s,'o') FROM strings
-----
-1
-1
-0
-NULL
-
-# Test NULL constant in different places
-query T
-SELECT contains(NULL,'o') FROM strings
-----
-NULL
-NULL
-NULL
-NULL
-
-query T
-SELECT contains(s,NULL) FROM strings
-----
-NULL
-NULL
-NULL
-NULL
-
-query T
-SELECT contains(NULL,NULL) FROM strings
-----
-NULL
-NULL
-NULL
-NULL
-
-# Test empty pattern
-query T
-SELECT contains(s,'') FROM strings
-----
-1
-1
-1
-NULL
-
diff --git a/modules/calcite/src/test/sql/function/string/test_contains_utf8.test b/modules/calcite/src/test/sql/function/string/test_contains_utf8.test
deleted file mode 100644
index 7eabe91..0000000
--- a/modules/calcite/src/test/sql/function/string/test_contains_utf8.test
+++ /dev/null
@@ -1,85 +0,0 @@
-# name: test/sql/function/string/test_contains_utf8.test
-# description: Contains test with UTF8
-# group: [string]
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE strings(s VARCHAR);
-
-statement ok
-INSERT INTO strings VALUES ('átomo')
-
-statement ok
-INSERT INTO strings VALUES ('olá mundo')
-
-statement ok
-INSERT INTO strings VALUES ('你好世界')
-
-statement ok
-INSERT INTO strings VALUES ('two ñ three ₡ four 🦆 end')
-
-# Test one matching UTF8 letter
-query T
-SELECT contains(s,'á') FROM strings
-----
-1
-1
-0
-0
-
-# Test a sentence with an UTF-8
-query T
-SELECT contains(s,'olá mundo') FROM strings
-----
-0
-1
-0
-0
-
-# Test an entire UTF-8 word
-query T
-SELECT contains(s,'你好世界') FROM strings
-----
-0
-0
-1
-0
-
-# Test a substring of the haystack from the beginning
-query T
-SELECT contains(s,'two ñ thr') FROM strings
-----
-0
-0
-0
-1
-
-# Test a single UTF8 substring of the haystack in the middle
-query T
-SELECT contains(s,'ñ') FROM strings
-----
-0
-0
-0
-1
-
-# Test a multiple UTF8 substring of the haystack in the middle
-query T
-SELECT contains(s,'₡ four 🦆 e') FROM strings
-----
-0
-0
-0
-1
-
-# Test a substring of the haystack from the middle to the end
-query T
-SELECT contains(s,'🦆 end') FROM strings
-----
-0
-0
-0
-1
-
diff --git a/modules/calcite/src/test/sql/function/string/test_format.test b/modules/calcite/src/test/sql/function/string/test_format.test
deleted file mode 100644
index 0364d6c..0000000
--- a/modules/calcite/src/test/sql/function/string/test_format.test
+++ /dev/null
@@ -1,95 +0,0 @@
-# name: test/sql/function/string/test_format.test
-# description: Test format
-# group: [string]
-
-statement ok
-PRAGMA enable_verification
-
-# format without format specifiers
-query TT
-SELECT format('hello'), format(NULL)
-----
-hello NULL
-
-# format strings
-query TT
-SELECT format('{}', 'hello'), format('{}: {}', 'hello', 'world')
-----
-hello hello: world
-
-# format strings with NULL values
-query TT
-SELECT format('{}', NULL), format(NULL, 'hello', 'world')
-----
-NULL NULL
-
-# booleans
-query T
-SELECT format('{} {}', TRUE, FALSE)
-----
-true false
-
-# integers
-query TT
-SELECT format('{}', 33), format('{} + {} = {}', 3, 5, 3 + 5)
-----
-33 3 + 5 = 8
-
-# exotic types
-# dates, times and timestamps are strings
-query T
-SELECT format('{} {} = {}', DATE '1992-01-01', TIME '12:01:00', TIMESTAMP '1992-01-01 12:01:00')
-----
-1992-01-01 12:01:00 = 1992-01-01 12:01:00
-
-# blob
-query T
-SELECT format('{}', BLOB '\x00hello')
-----
-\x00hello
-
-# hugeint
-query T
-SELECT format('{}', 120381902481294715712::HUGEINT)
-----
-120381902481294715712
-
-# decimal
-query T
-SELECT format('{:.3f}', '1.234'::DECIMAL)
-----
-1.234
-
-# integers with special formatting specifiers
-query TTT
-SELECT format('{:04d}', 33), format('{} {:02d}:{:02d}:{:02d} {}', 'time', 12, 3, 16, 'AM'), format('{:10d}', 1992)
-----
-0033 time 12:03:16 AM 1992
-
-# numeric input of arguments
-query T
-SELECT format('{1} {1} {0} {0}', 1, 2)
-----
-2 2 1 1
-
-# incorrect number of parameters
-# too few parameters
-statement error
-SELECT format('{}')
-
-statement error
-SELECT format('{} {}', 'hello')
-
-# excess parameters are ignored
-query T
-SELECT format('{}', 'hello', 'world')
-----
-hello
-
-# incorrect types
-statement error
-SELECT format('{:s}', 42)
-
-statement error
-SELECT format('{:d}', 'hello')
-
diff --git a/modules/calcite/src/test/sql/function/string/test_glob.test b/modules/calcite/src/test/sql/function/string/test_glob.test
deleted file mode 100644
index 8a4353b..0000000
--- a/modules/calcite/src/test/sql/function/string/test_glob.test
+++ /dev/null
@@ -1,245 +0,0 @@
-# name: test/sql/function/string/test_glob.test
-# description: Test GLOB statement
-# group: [string]
-
-statement ok
-PRAGMA enable_verification
-
-# scalar glob
-query T
-SELECT 'aaa' GLOB 'bbb'
-----
-0
-
-query T
-SELECT 'aaa' GLOB 'aaa'
-----
-1
-
-query T
-SELECT 'aaa' GLOB '*'
-----
-1
-
-query T
-SELECT 'aaa' GLOB '*a'
-----
-1
-
-query T
-SELECT 'aaa' GLOB '*b'
-----
-0
-
-query T
-SELECT 'aaa' GLOB 'a*'
-----
-1
-
-query T
-SELECT 'aaa' GLOB 'b*'
-----
-0
-
-query T
-SELECT 'aaa' GLOB 'a?a'
-----
-1
-
-query T
-SELECT 'aaa' GLOB 'a?'
-----
-0
-
-query T
-SELECT 'aaa' GLOB '??*'
-----
-1
-
-query T
-SELECT 'aaa' GLOB '????*'
-----
-0
-
-query T
-SELECT 'ababac' GLOB '*abac'
-----
-1
-
-# bracket matching
-query T
-SELECT '3' GLOB '[0-9]'
-----
-1
-
-query T
-SELECT 'a' GLOB '[0-9]'
-----
-0
-
-# multiple brackets in a row
-query T
-SELECT '012' GLOB '[0-9][0-9][0-9]'
-----
-1
-
-# trailing in pattern after brackets
-query T
-SELECT '012' GLOB '[0-9][0-9][0-9]a'
-----
-0
-
-# trailing in string after brackets
-query T
-SELECT '012a' GLOB '[0-9][0-9][0-9]'
-----
-0
-
-# more complicated brackets
-query T
-SELECT 'b3' GLOB '[abc0-9][abc0-9]'
-----
-1
-
-query T
-SELECT 'd3' GLOB '[abc0-9][abc0-9]'
-----
-0
-
-# inverse brackets
-query T
-SELECT 'a' GLOB '[!0-9]'
-----
-1
-
-query T
-SELECT '1' GLOB '[!0-9]'
-----
-0
-
-# escapes
-query T
-SELECT '*' GLOB '\*'
-----
-1
-
-query T
-SELECT 'a' GLOB '\*'
-----
-0
-
-# escaped escape
-query T
-SELECT '\' GLOB '\\'
-----
-1
-
-# '
-query T
-SELECT 'a' GLOB '\\'
-----
-0
-
-# bracket in a bracket
-# this is valid as long as the closing bracket is the first non-exclamation mark character
-query T
-SELECT '3]' GLOB '[]3][]]'
-----
-1
-
-query T
-SELECT '3]' GLOB '[]3][]]'
-----
-1
-
-# bracket is not properly closed
-query T
-SELECT '3' GLOB '[3'
-----
-0
-
-# trailing range
-query T
-SELECT '3' GLOB '[3-'
-----
-0
-
-# trailing escape
-query T
-SELECT '3' GLOB '\\'
-----
-0
-
-# a bunch of asterisks
-query T
-SELECT '3' GLOB '3***'
-----
-1
-
-query T
-SELECT '1245' GLOB '**1***2*******4*5***'
-----
-1
-
-query T
-SELECT 'aaaaaaaaaaaaaaaaaaaaaaa' GLOB '*a'
-----
-1
-
-# special characters in brackets
-query T
-SELECT '?' GLOB '[?]'
-----
-1
-
-query T
-SELECT '3' GLOB '[?]'
-----
-0
-
-query T
-SELECT '*' GLOB '[*]'
-----
-1
-
-query T
-SELECT '3' GLOB '[*]'
-----
-0
-
-# multiple ranges in a bracket
-query T
-SELECT '6' GLOB '[1-35-7]'
-----
-1
-
-query T
-SELECT '4' GLOB '[1-35-7]'
-----
-0
-
-# like with table
-statement ok
-CREATE TABLE strings(s STRING, pat STRING);
-
-statement ok
-INSERT INTO strings VALUES ('abab', 'ab*'), ('aaa', 'a?a'), ('aaa', '*b*')
-
-query T
-SELECT s FROM strings WHERE s GLOB 'ab*'
-----
-abab
-
-query T
-SELECT s FROM strings WHERE 'aba' GLOB pat
-----
-abab
-aaa
-aaa
-
-query T
-SELECT s FROM strings WHERE s GLOB pat
-----
-abab
-aaa
-
diff --git a/modules/calcite/src/test/sql/function/string/test_ilike.test b/modules/calcite/src/test/sql/function/string/test_ilike.test
deleted file mode 100644
index 3a1a0f8..0000000
--- a/modules/calcite/src/test/sql/function/string/test_ilike.test
+++ /dev/null
@@ -1,136 +0,0 @@
-# name: test/sql/function/string/test_ilike.test
-# description: Test ILIKE statement
-# group: [string]
-
-statement ok
-PRAGMA enable_verification
-
-# scalar like
-query T
-SELECT 'aaa' ILIKE 'bbb'
-----
-0
-
-query T
-SELECT 'aaa' ILIKE 'aAa'
-----
-1
-
-query T
-SELECT 'aaa' ILIKE '%'
-----
-1
-
-query T
-SELECT 'aaa' ILIKE '%A'
-----
-1
-
-query T
-SELECT 'aaa' ILIKE '%b'
-----
-0
-
-query T
-SELECT 'aaa' ILIKE 'A%'
-----
-1
-
-query T
-SELECT 'aaa' ILIKE 'b%'
-----
-0
-
-query T
-SELECT 'aaa' ILIKE 'A_a'
-----
-1
-
-query T
-SELECT 'aaa' ILIKE 'a_'
-----
-0
-
-query T
-SELECT 'aaa' ILIKE '__%'
-----
-1
-
-query T
-SELECT 'aaa' ILIKE '____%'
-----
-0
-
-query T
-SELECT 'ababac' ILIKE '%abac'
-----
-1
-
-query T
-SELECT 'ababac' ILIKE '%%%aBac'
-----
-1
-
-query T
-SELECT 'ababac' ILIKE 'abab%%%%%'
-----
-1
-
-query T
-SELECT 'ababac' ILIKE '%%%a%%%b%%a%b%%%%%a%c%%'
-----
-1
-
-query T
-SELECT 'ababac' ILIKE '%%%a%%%b%%a%b%%%%%a%d%%'
-----
-0
-
-query T
-SELECT 'ababac' NOT ILIKE '%Abac'
-----
-0
-
-# like with table
-statement ok
-CREATE TABLE strings(s STRING, pat STRING);
-
-statement ok
-INSERT INTO strings VALUES ('abab', 'Ab%'), ('aaa', 'A_a'), ('aaa', '%b%')
-
-query T
-SELECT s FROM strings WHERE s LIKE 'ab%'
-----
-abab
-
-query T
-SELECT s FROM strings WHERE 'aba' ILIKE pat
-----
-abab
-aaa
-aaa
-
-query T
-SELECT s FROM strings WHERE s ILIKE pat
-----
-abab
-aaa
-
-# unicode
-query T
-SELECT 'MÜHLEISEN' ILIKE 'mühleisen'
-----
-1
-
-
-statement ok
-CREATE TABLE unicode_strings(s STRING, pat STRING);
-
-statement ok
-INSERT INTO unicode_strings VALUES ('öäb', 'Ö%B'), ('aaÄ', 'A_ä'), ('aaa', '%b%')
-
-query T
-SELECT s FROM unicode_strings WHERE s ILIKE pat
-----
-öäb
-aaÄ
diff --git a/modules/calcite/src/test/sql/function/string/test_instr.test b/modules/calcite/src/test/sql/function/string/test_instr.test
deleted file mode 100644
index 582166e..0000000
--- a/modules/calcite/src/test/sql/function/string/test_instr.test
+++ /dev/null
@@ -1,110 +0,0 @@
-# name: test/sql/function/string/test_instr.test
-# description: Instr test
-# group: [string]
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE strings(s VARCHAR, off INTEGER, length INTEGER);
-
-statement ok
-INSERT INTO strings VALUES ('hello', 1, 2), ('world', 2, 3), ('b', 1, 1), (NULL, 2, 2)
-
-# Test first letter
-query I
-SELECT instr(s,'h') FROM strings
-----
-1
-0
-0
-NULL
-
-# Test second letter
-query I
-SELECT instr(s,'e') FROM strings
-----
-2
-0
-0
-NULL
-
-# Test last letter
-query I
-SELECT instr(s,'d') FROM strings
-----
-0
-5
-0
-NULL
-
-# Test multiple letters
-query I
-SELECT instr(s,'he') FROM strings
-----
-1
-0
-0
-NULL
-
-# Test multiple letters in the middle
-query I
-SELECT instr(s,'ello') FROM strings
-----
-2
-0
-0
-NULL
-
-# Test multiple letters at the end
-query I
-SELECT instr(s,'lo') FROM strings
-----
-4
-0
-0
-NULL
-
-# Test no match
-query I
-SELECT instr(s,'he-man') FROM strings
-----
-0
-0
-0
-NULL
-
-# Test matching needle in multiple rows
-query IT
-SELECT instr(s,'o'),s FROM strings
-----
-5 hello
-2 world
-0 b
-NULL NULL
-
-# Test NULL constant in different places
-query I
-SELECT instr(NULL,'o') FROM strings
-----
-NULL
-NULL
-NULL
-NULL
-
-query I
-SELECT instr(s,NULL) FROM strings
-----
-NULL
-NULL
-NULL
-NULL
-
-query I
-SELECT instr(NULL,NULL) FROM strings
-----
-NULL
-NULL
-NULL
-NULL
-
diff --git a/modules/calcite/src/test/sql/function/string/test_instr_utf8.test b/modules/calcite/src/test/sql/function/string/test_instr_utf8.test
deleted file mode 100644
index a2ae5fb..0000000
--- a/modules/calcite/src/test/sql/function/string/test_instr_utf8.test
+++ /dev/null
@@ -1,85 +0,0 @@
-# name: test/sql/function/string/test_instr_utf8.test
-# description: Instr test with UTF8
-# group: [string]
-
-statement ok
-PRAGMA enable_verification
-
-statement ok
-CREATE TABLE strings(s VARCHAR);
-
-statement ok
-INSERT INTO strings VALUES ('átomo')
-
-statement ok
-INSERT INTO strings VALUES ('olá mundo')
-
-statement ok
-INSERT INTO strings VALUES ('你好世界')
-
-statement ok
-INSERT INTO strings VALUES ('two ñ three ₡ four 🦆 end')
-
-# Test one matching UTF8 letter
-query I
-SELECT INSTR(s,'á') FROM strings
-----
-1
-3
-0
-0
-
-# Test a sentence with UTF-8
-query I
-SELECT INSTR(s,'olá mundo') FROM strings
-----
-0
-1
-0
-0
-
-# Test an entire UTF-8 word
-query I
-SELECT INSTR(s,'你好世界') FROM strings
-----
-0
-0
-1
-0
-
-# Test a substring of the haystack from the beginning
-query I
-SELECT instr(s,'two ñ thr') FROM strings
-----
-0
-0
-0
-1
-
-# Test a single UTF8 substring of the haystack in the middle
-query I
-SELECT instr(s,'ñ') FROM strings
-----
-0
-0
-0
-5
-
-# Test a multiple UTF8 substring of the haystack in the middle
-query I
-SELECT instr(s,'₡ four 🦆 e') FROM strings
-----
-0
-0
-0
-13
-
-# Test a substring of the haystack from the middle to the end
-query I
-SELECT instr(s,'🦆 end') FROM strings
-----
-0
-0
-0
-20
-
diff --git a/modules/calcite/src/test/sql/function/string/test_jaccard.test b/modules/calcite/src/test/sql/function/string/test_jaccard.test
deleted file mode 100644
index bf7c367..0000000
--- a/modules/calcite/src/test/sql/function/string/test_jaccard.test
+++ /dev/null
@@ -1,144 +0,0 @@
-# name: test/sql/function/string/test_jaccard.test
-# description: Test jaccard function
-# group: [string]
-
-statement ok
-PRAGMA enable_verification
-
-# jaccard
-query I
-SELECT jaccard('hello', 'hello')
-----
-1
-
-query I
-SELECT jaccard('hello', NULL)
-----
-NULL
-
-query I
-SELECT jaccard(NULL, 'hello')
-----
-NULL
-
-query I
-SELECT jaccard(NULL, NULL)
-----
-NULL
-
-query I
-SELECT jaccard('ab', 'aabb')
-----
-1.0
-
-query I
-SELECT jaccard('aabb', 'ab')
-----
-1.0
-
-query I
-SELECT jaccard('ab', 'cd')
-----
-0.0
-
-query I
-SELECT jaccard('cd', 'ab')
-----
-0.0
-
-query I
-SELECT round(jaccard('ab', 'aabbcc'), 3)
-----
-0.667
-
-query I
-SELECT round(jaccard('aabbcc', 'ab'), 3)
-----
-0.667
-
-query I
-SELECT round(jaccard('aabbccddeeff', 'ab'), 3)
-----
-0.333
-
-query I
-SELECT round(jaccard('ab', 'aabbccddeeff'), 3)
-----
-0.333
-
-query I
-SELECT round(jaccard('aabbccddeeffg', 'ab'), 3)
-----
-0.286
-
-query I
-SELECT round(jaccard('ab', 'aaaabbbccddeeffgabcccc'), 3)
-----
-0.286
-
-query I
-SELECT round(jaccard('ababababababba', 'aaaabbbccddeeffgabcccc'), 3)
-----
-0.286
-
-statement error
-SELECT jaccard('hello', '')
-
-statement error
-SELECT jaccard('', 'hello')
-
-statement error
-SELECT jaccard('', '')
-
-
-statement ok
-CREATE TABLE strings(s VARCHAR, t VARCHAR)
-
-statement ok
-INSERT INTO strings VALUES ('hello', 'hallo'), ('aloha', 'fello'), ('fellow', 'ducks'), (NULL, NULL)
-
-query I
-select round(jaccard(s, t), 1) from strings
-----
-0.6
-0.3
-0.0
-NULL
-
-query I
-select round(jaccard(s, 'hallo'), 1) from strings
-----
-0.6
-1.0
-0.3
-NULL
-
-query I
-select round(jaccard('hallo', t), 1) from strings
-----
-1.0
-0.3
-0.0
-NULL
-
-query I
-select round(jaccard(NULL, t), 1) from strings
-----
-NULL
-NULL
-NULL
-NULL
-
-query I
-select round(jaccard(s, NULL), 1) from strings
-----
-NULL
-NULL
-NULL
-NULL
-
-statement error
-select round(jaccard('', t), 1) from strings
-
-statement error
-select round(jaccard(s, ''), 1) from strings
diff --git a/modules/calcite/src/test/sql/function/string/test_left.test b/modules/calcite/src/test/sql/function/string/test_left.test
index d5a3c49..bcf3c0d 100644
--- a/modules/calcite/src/test/sql/function/string/test_left.test
+++ b/modules/calcite/src/test/sql/function/string/test_left.test
@@ -2,64 +2,30 @@
# description: LEFT test
# group: [string]
-statement ok
-PRAGMA enable_verification
-
# test LEFT on positive positions
query TTTTT
SELECT LEFT('abcd', 0), LEFT('abc', 1), LEFT('abc', 2), LEFT('abc', 3), LEFT('abc', 4)
----
(empty) a ab abc abc
-query TTTTT
-SELECT LEFT('🦆ab', 0), LEFT('🦆ab', 1), LEFT('🦆ab', 2), LEFT('🦆ab', 3), LEFT('🦆ab', 4)
-----
-(empty) 🦆 🦆a 🦆ab 🦆ab
-
-query TTTT
-SELECT LEFT('🦆🤦S̈', 0), LEFT('🦆🤦S̈', 1), LEFT('🦆🤦S̈', 2), LEFT('🦆🤦S̈', 3)
-----
-(empty) 🦆 🦆🤦 🦆🤦S̈
-
# test LEFT on negative positions
query TTTTT
SELECT LEFT('abcd', 0), LEFT('abc', -1), LEFT('abc', -2), LEFT('abc', -3), LEFT('abc', -4)
----
-(empty) ab a (empty) (empty)
-
-query TTTTT
-SELECT LEFT('🦆ab', 0), LEFT('🦆ab', -1), LEFT('🦆ab', -2), LEFT('🦆ab', -3), LEFT('🦆ab', -4)
-----
-(empty) 🦆a 🦆 (empty) (empty)
-
-query TTTT
-SELECT LEFT('🦆🤦S̈', 0), LEFT('🦆🤦S̈', -1), LEFT('🦆🤦S̈', -2), LEFT('🦆🤦S̈', -3)
-----
-(empty) 🦆🤦 🦆 (empty)
-
-# test LEFT on NULL values
-query TTT
-SELECT LEFT(NULL, 0), LEFT('abc', NULL), LEFT(NULL, NULL)
-----
-NULL NULL NULL
-
-query TTT
-SELECT LEFT(NULL, 0), LEFT('🦆ab', NULL), LEFT(NULL, NULL)
-----
-NULL NULL NULL
+(empty) (empty) (empty) (empty) (empty)
# test on tables
statement ok
DROP TABLE IF EXISTS strings
statement ok
-CREATE TABLE strings(a STRING, b BIGINT)
+CREATE TABLE strings(a VARCHAR, b INTEGER)
statement ok
INSERT INTO STRINGS VALUES ('abcd', 0), ('abc', 1), ('abc', 2), ('abc', 3), ('abc', 4)
query T
-SELECT LEFT(a, b) FROM strings
+SELECT LEFT(a, b) FROM strings ORDER BY b
----
(empty)
a
@@ -71,7 +37,7 @@ statement ok
DROP TABLE IF EXISTS strings
statement ok
-CREATE TABLE strings(a STRING, b BIGINT)
+CREATE TABLE strings(a VARCHAR, b INTEGER)
statement ok
INSERT INTO STRINGS VALUES ('abcd', 0), ('abc', -1), ('abc', -2), ('abc', -3), ('abc', -4)
@@ -80,8 +46,8 @@ query T
SELECT LEFT(a, b) FROM strings
----
(empty)
-ab
-a
+(empty)
+(empty)
(empty)
(empty)
@@ -89,7 +55,7 @@ statement ok
DROP TABLE IF EXISTS strings
statement ok
-CREATE TABLE strings(a STRING, b BIGINT)
+CREATE TABLE strings(a VARCHAR, b INTEGER)
statement ok
INSERT INTO STRINGS VALUES (NULL, 0), ('abc', NULL), (NULL, NULL)
diff --git a/modules/calcite/src/test/sql/function/string/test_left.test b/modules/calcite/src/test/sql/function/string/test_left.test_ignore
similarity index 81%
copy from modules/calcite/src/test/sql/function/string/test_left.test
copy to modules/calcite/src/test/sql/function/string/test_left.test_ignore
index d5a3c49..1095c82 100644
--- a/modules/calcite/src/test/sql/function/string/test_left.test
+++ b/modules/calcite/src/test/sql/function/string/test_left.test_ignore
@@ -1,9 +1,9 @@
# name: test/sql/function/string/test_left.test
# description: LEFT test
# group: [string]
-
-statement ok
-PRAGMA enable_verification
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14677
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-15002
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-15003
# test LEFT on positive positions
query TTTTT
@@ -25,7 +25,7 @@ SELECT LEFT('🦆🤦S̈', 0), LEFT('🦆🤦S̈', 1), LEFT('🦆🤦S̈', 2), L
query TTTTT
SELECT LEFT('abcd', 0), LEFT('abc', -1), LEFT('abc', -2), LEFT('abc', -3), LEFT('abc', -4)
----
-(empty) ab a (empty) (empty)
+(empty) (empty) (empty) (empty) (empty)
query TTTTT
SELECT LEFT('🦆ab', 0), LEFT('🦆ab', -1), LEFT('🦆ab', -2), LEFT('🦆ab', -3), LEFT('🦆ab', -4)
@@ -53,13 +53,13 @@ statement ok
DROP TABLE IF EXISTS strings
statement ok
-CREATE TABLE strings(a STRING, b BIGINT)
+CREATE TABLE strings(a VARCHAR, b BIGINT)
statement ok
INSERT INTO STRINGS VALUES ('abcd', 0), ('abc', 1), ('abc', 2), ('abc', 3), ('abc', 4)
query T
-SELECT LEFT(a, b) FROM strings
+SELECT LEFT(a, b) FROM strings ORDER BY b
----
(empty)
a
@@ -71,7 +71,7 @@ statement ok
DROP TABLE IF EXISTS strings
statement ok
-CREATE TABLE strings(a STRING, b BIGINT)
+CREATE TABLE strings(a VARCHAR, b BIGINT)
statement ok
INSERT INTO STRINGS VALUES ('abcd', 0), ('abc', -1), ('abc', -2), ('abc', -3), ('abc', -4)
@@ -80,8 +80,8 @@ query T
SELECT LEFT(a, b) FROM strings
----
(empty)
-ab
-a
+(empty)
+(empty)
(empty)
(empty)
@@ -89,7 +89,7 @@ statement ok
DROP TABLE IF EXISTS strings
statement ok
-CREATE TABLE strings(a STRING, b BIGINT)
+CREATE TABLE strings(a VARCHAR, b BIGINT)
statement ok
INSERT INTO STRINGS VALUES (NULL, 0), ('abc', NULL), (NULL, NULL)
diff --git a/modules/calcite/src/test/sql/function/string/test_length.test b/modules/calcite/src/test/sql/function/string/test_length.test
index e66a422..e04f34a 100644
--- a/modules/calcite/src/test/sql/function/string/test_length.test
+++ b/modules/calcite/src/test/sql/function/string/test_length.test
@@ -3,9 +3,6 @@
# group: [string]
statement ok
-PRAGMA enable_verification
-
-statement ok
CREATE TABLE strings(s VARCHAR)
statement ok
@@ -15,15 +12,15 @@ INSERT INTO strings VALUES ('hello'), ('world'), (NULL)
query I
SELECT length(s) FROM strings ORDER BY s
----
-NULL
5
5
+NULL
# length after concat
query I
-SELECT length(s || ' ' || '🦆') FROM strings ORDER BY s
+SELECT length(s || ' ' || 'a') FROM strings ORDER BY s
----
-NULL
7
7
+NULL
diff --git a/modules/calcite/src/test/sql/function/string/test_length.test b/modules/calcite/src/test/sql/function/string/test_length.test_ignore
similarity index 86%
copy from modules/calcite/src/test/sql/function/string/test_length.test
copy to modules/calcite/src/test/sql/function/string/test_length.test_ignore
index e66a422..436980f 100644
--- a/modules/calcite/src/test/sql/function/string/test_length.test
+++ b/modules/calcite/src/test/sql/function/string/test_length.test_ignore
@@ -1,9 +1,7 @@
# name: test/sql/function/string/test_length.test
# description: Test length function
# group: [string]
-
-statement ok
-PRAGMA enable_verification
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14677
statement ok
CREATE TABLE strings(s VARCHAR)
@@ -15,15 +13,15 @@ INSERT INTO strings VALUES ('hello'), ('world'), (NULL)
query I
SELECT length(s) FROM strings ORDER BY s
----
-NULL
5
5
+NULL
# length after concat
query I
SELECT length(s || ' ' || '🦆') FROM strings ORDER BY s
----
-NULL
7
7
+NULL
diff --git a/modules/calcite/src/test/sql/function/string/test_levenshtein.test b/modules/calcite/src/test/sql/function/string/test_levenshtein.test
deleted file mode 100644
index a7446e6..0000000
--- a/modules/calcite/src/test/sql/function/string/test_levenshtein.test
+++ /dev/null
@@ -1,273 +0,0 @@
-# name: test/sql/function/string/test_levenshtein.test
-# description: Test levenshtein function
-# group: [string]
-
-statement ok
-PRAGMA enable_verification
-
-
-# normal queries
-query I
-SELECT levenshtein('hallo', 'hallo')
-----
-0
-
-query I
-SELECT levenshtein('hallo', 'hello')
-----
-1
-
-query I
-SELECT levenshtein('hello', 'hallo')
-----
-1
-
-query I
-SELECT levenshtein('lawn', 'flaw')
-----
-2
-
-query I
-SELECT levenshtein('flaw', 'lawn')
-----
-2
-
-query I
-SELECT levenshtein('kitten', 'sitting')
-----
-3
-
-query I
-SELECT levenshtein('sitting', 'kitten')
-----
-3
-
-query I
-SELECT levenshtein('hallo', 'hoi')
-----
-4
-
-query I
-SELECT levenshtein('hoi', 'hallo')
-----
-4
-
-query I
-SELECT levenshtein(NULL, 'hi')
-----
-NULL
-
-query I
-SELECT levenshtein('hi', NULL)
-----
-NULL
-
-query I
-SELECT levenshtein(NULL, NULL)
-----
-NULL
-
-statement ok
-SELECT mismatches('', NULL)
-
-statement ok
-SELECT mismatches(NULL, '')
-
-
-# incorrect usage
-statement error
-SELECT mismatches('', 'hi')
-
-statement error
-SELECT mismatches('hi', '')
-
-statement error
-SELECT mismatches('', '')
-
-
-
-statement ok
-CREATE TABLE strings(s VARCHAR)
-
-statement ok
-INSERT INTO strings VALUES ('hello'), ('hallo'), ('aloha'), ('fello'), ('fellow'), ('ducks')
-
-query I
-SELECT levenshtein(s, 'hallo') FROM strings ORDER BY s
-----
-4
-5
-2
-3
-0
-1
-
-query I
-SELECT levenshtein('hallo', s) FROM strings ORDER BY s
-----
-4
-5
-2
-3
-0
-1
-
-query I
-SELECT levenshtein(NULL, s) FROM strings
-----
-NULL
-NULL
-NULL
-NULL
-NULL
-NULL
-
-query I
-SELECT levenshtein(NULL, s) FROM strings
-----
-NULL
-NULL
-NULL
-NULL
-NULL
-NULL
-
-statement error
-SELECT mismatches('', s) FROM strings
-
-statement error
-SELECT mismatches(s, '') FROM strings
-
-
-statement ok
-DROP TABLE strings
-
-statement ok
-CREATE TABLE strings(s VARCHAR)
-
-statement ok
-INSERT INTO strings VALUES (NULL)
-
-query I
-SELECT levenshtein(s, NULL) from strings
-----
-NULL
-
-query I
-SELECT levenshtein(NULL, s) from strings
-----
-NULL
-
-query I
-SELECT levenshtein('hi', s) from strings
-----
-NULL
-
-query I
-SELECT levenshtein(s, 'hi') from strings
-----
-NULL
-
-query I
-SELECT mismatches('', s) FROM strings
-----
-NULL
-
-query I
-SELECT mismatches(s, '') FROM strings
-----
-NULL
-
-
-statement ok
-DROP TABLE strings
-
-statement ok
-CREATE TABLE strings(s VARCHAR)
-
-statement ok
-INSERT INTO strings VALUES ('')
-
-query I
-SELECT mismatches(NULL, s) FROM strings
-----
-NULL
-
-query I
-SELECT mismatches(s, NULL) FROM strings
-----
-NULL
-
-statement error
-SELECT mismatches(s, '') FROM strings
-
-statement error
-SELECT mismatches('', s) FROM strings
-
-statement error
-SELECT mismatches(s, 'hi') FROM strings
-
-statement error
-SELECT mismatches('hi', s) FROM strings
-
-
-
-# editdist3
-query I
-SELECT editdist3('hallo', 'hello')
-----
-1
-
-statement error
-SELECT editdist3(s, 'hello') FROM strings
-
-
-# Comparing fields from two columns row wise
-
-statement ok
-DROP TABLE strings
-
-statement ok
-CREATE TABLE strings(s VARCHAR, t VARCHAR)
-
-statement ok
-INSERT INTO strings VALUES ('hello', 'hello'), ('hello', 'hallo'), ('flaw', 'lawn'),
- ('sitting', 'kitten'), ('hallo', 'aloha'), ('hello', 'aloha'),
- (NULL, NULL), ('', ''),
- (NULL, 'bora'), ('bora', NULL),
- ('hi', ''), ('', 'hi'),
- (NULL, ''), ('', NULL)
-
-
-query I
-SELECT levenshtein(s, t) ld FROM strings WHERE length(s) > 1 AND length(t) > 0 AND s is not NULL and t is not NULL
-----
-0
-1
-2
-3
-4
-5
-
-# NULL inserts result in empty string
-
-statement error
-SELECT levenshtein(s, t) FROM strings
-
-statement error
-SELECT s, t, levenshtein(s, t) ld FROM strings WHERE length(s) > 1 AND s is not NULL and t is not NULL
-
-statement error
-SELECT levenshtein(s, t) FROM strings WHERE length(s) < 1
-
-statement error
-SELECT levenshtein(t, s) FROM strings WHERE length(s) < 1
-
-statement error
-SELECT levenshtein(s, t) FROM strings WHERE length(t) < 1
-
-statement error
-SELECT levenshtein(t, s) FROM strings WHERE length(t) < 1
-
-statement error
-SELECT levenshtein(s, t) FROM strings WHERE rowid > 5
diff --git a/modules/calcite/src/test/sql/function/string/test_like.test b/modules/calcite/src/test/sql/function/string/test_like.test
index 1493385..a50b176 100644
--- a/modules/calcite/src/test/sql/function/string/test_like.test
+++ b/modules/calcite/src/test/sql/function/string/test_like.test
@@ -2,268 +2,265 @@
# description: Test LIKE statement
# group: [string]
-statement ok
-PRAGMA enable_verification
-
# scalar like
query T
SELECT 'aaa' LIKE 'bbb'
----
-0
+false
query T
SELECT 'aaa' LIKE 'abab'
----
-0
+false
query T
SELECT 'aaa' LIKE 'aaa'
----
-1
+true
query T
SELECT 'aaa' LIKE '%'
----
-1
+true
query T
SELECT 'aaa' LIKE '%a'
----
-1
+true
query T
SELECT 'aaa' LIKE '%b'
----
-0
+false
query T
SELECT 'aaa' LIKE 'a%'
----
-1
+true
query T
SELECT 'aaa' LIKE 'b%'
----
-0
+false
query T
SELECT 'aaa' LIKE 'a_a'
----
-1
+true
query T
SELECT 'aaa' LIKE 'a_'
----
-0
+false
query T
SELECT 'aaa' LIKE '__%'
----
-1
+true
query T
SELECT 'aaa' LIKE '____%'
----
-0
+false
query T
SELECT 'ababac' LIKE '%abac'
----
-1
+true
query T
SELECT 'ababac' LIKE '%%%abac'
----
-1
+true
query T
SELECT 'ababac' LIKE 'abab%%%%%'
----
-1
+true
query T
SELECT 'ababac' LIKE '%%%a%%%b%%a%b%%%%%a%c%%'
----
-1
+true
query T
SELECT 'ababac' LIKE '%%%a%%%b%%a%b%%%%%a%d%%'
----
-0
+false
query T
SELECT 'ababac' NOT LIKE '%abac'
----
-0
+false
query T
SELECT 'aabbccc' LIKE '%aa%bb%cc'
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' LIKE ''
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' LIKE '%'
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' LIKE 'zebra'
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' LIKE 'zebra elephant tiger horse'
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' LIKE 'zebra elephant tiger horse%'
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' LIKE '%zebra elephant tiger horse%'
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' LIKE '%zebra elephant tiger horse blabla'
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' LIKE 'zebra elephant tiger horse blabla%'
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' LIKE 'zebra%'
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' LIKE '%horse'
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' LIKE 'zebra%elephant%horse'
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' LIKE 'zebra%elephant%tiger%horse'
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' LIKE '%zebra%elephant%tiger%horse'
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' LIKE 'zebra%elephant%tiger%horse%'
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' LIKE '%zebra%elephant%tiger%horse%'
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' LIKE '%%zebra %%%ele%phan%t t%ig%er% horse%'
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' LIKE 'zebra%tiger%horse'
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' LIKE 'zebra%tiger%elephant%horse'
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' NOT LIKE ''
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' NOT LIKE '%'
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' NOT LIKE 'zebra'
----
-1
+true
query T
SELECT 'zebra elephant tiger horse' NOT LIKE 'zebra elephant tiger horse'
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' NOT LIKE 'zebra%'
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' NOT LIKE '%horse'
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' NOT LIKE 'zebra%elephant%horse'
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' NOT LIKE 'zebra%elephant%tiger%horse'
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' NOT LIKE '%zebra%elephant%tiger%horse'
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' NOT LIKE 'zebra%elephant%tiger%horse%'
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' NOT LIKE '%zebra%elephant%tiger%horse%'
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' NOT LIKE '%%zebra %%%ele%phan%t t%ig%er% horse%'
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' NOT LIKE 'zebra%tiger%horse'
----
-0
+false
query T
SELECT 'zebra elephant tiger horse' NOT LIKE 'zebra%tiger%elephant%horse'
----
-1
+true
# like with table
statement ok
-CREATE TABLE strings(s STRING, pat STRING);
+CREATE TABLE strings(s VARCHAR, pat VARCHAR);
statement ok
INSERT INTO strings VALUES ('abab', 'ab%'), ('aaa', 'a_a'), ('aaa', '%b%')
@@ -274,15 +271,15 @@ SELECT s FROM strings WHERE s LIKE 'ab%'
abab
query T
-SELECT s FROM strings WHERE 'aba' LIKE pat
+SELECT s FROM strings WHERE 'aba' LIKE pat ORDER BY s
----
-abab
aaa
aaa
+abab
query T
-SELECT s FROM strings WHERE s LIKE pat
+SELECT s FROM strings WHERE s LIKE pat ORDER BY s
----
-abab
aaa
+abab
diff --git a/modules/calcite/src/test/sql/function/string/test_like_escape.test b/modules/calcite/src/test/sql/function/string/test_like_escape.test
index ebfd042..18e9250 100644
--- a/modules/calcite/src/test/sql/function/string/test_like_escape.test
+++ b/modules/calcite/src/test/sql/function/string/test_like_escape.test
@@ -6,13 +6,13 @@
query T
SELECT '%++' LIKE '*%++' ESCAPE '*';
----
-1
+true
# Not Like
query T
SELECT '%++' NOT LIKE '*%++' ESCAPE '*';
----
-0
+false
# unterminated escapes
statement error
@@ -25,37 +25,32 @@ SELECT '%' LIKE '*' ESCAPE '*';
query T
SELECT '\' LIKE '\\' ESCAPE '\';
----
-1
+true
query T
SELECT '\\' LIKE '\\' ESCAPE '\';
----
-0
+false
query T
SELECT '%' LIKE '*%' ESCAPE '*';
----
-1
+true
query T
SELECT '_ ' LIKE '*_ ' ESCAPE '*';
----
-1
+true
query T
SELECT ' a ' LIKE '*_ ' ESCAPE '*';
----
-0
-
-query T
-SELECT '%_' LIKE '%_' ESCAPE '';
-----
-1
+false
query T
SELECT '*%' NOT LIKE '*%' ESCAPE '*';
----
-1
+true
# It should fail when more than one escape character is specified
statement error
@@ -66,26 +61,26 @@ SELECT '%_' LIKE '%_' ESCAPE '**';
# Test LIKE statement with ESCAPE in the middle of the pattern
statement ok
-CREATE TABLE strings(s STRING, pat STRING);
+CREATE TABLE strings(s VARCHAR, pat VARCHAR);
statement ok
INSERT INTO strings VALUES ('abab', 'ab%'), ('aaa', 'a*_a'), ('aaa', '*%b'), ('bbb', 'a%');
query T
-SELECT s FROM strings;
+SELECT s FROM strings ORDER BY s;
----
-abab
aaa
aaa
+abab
bbb
query T
-SELECT pat FROM strings;
+SELECT pat FROM strings ORDER BY pat;
----
-ab%
-a*_a
*%b
a%
+a*_a
+ab%
query T
SELECT s FROM strings WHERE pat LIKE 'a*%' ESCAPE '*';
@@ -93,7 +88,7 @@ SELECT s FROM strings WHERE pat LIKE 'a*%' ESCAPE '*';
bbb
query T
-SELECT s FROM strings WHERE 'aba' LIKE pat ESCAPE '*';
+SELECT s FROM strings WHERE 'aba' LIKE pat ESCAPE '*' ORDER BY s;
----
abab
bbb
diff --git a/modules/calcite/src/test/sql/function/string/test_like_escape.test b/modules/calcite/src/test/sql/function/string/test_like_escape.test_ignore
similarity index 81%
copy from modules/calcite/src/test/sql/function/string/test_like_escape.test
copy to modules/calcite/src/test/sql/function/string/test_like_escape.test_ignore
index ebfd042..5eab08c 100644
--- a/modules/calcite/src/test/sql/function/string/test_like_escape.test
+++ b/modules/calcite/src/test/sql/function/string/test_like_escape.test_ignore
@@ -1,18 +1,19 @@
# name: test/sql/function/string/test_like_escape.test
# description: Test LIKE statement with custom ESCAPE
# group: [string]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-15004
# scalar like with escape
query T
SELECT '%++' LIKE '*%++' ESCAPE '*';
----
-1
+true
# Not Like
query T
SELECT '%++' NOT LIKE '*%++' ESCAPE '*';
----
-0
+false
# unterminated escapes
statement error
@@ -25,37 +26,37 @@ SELECT '%' LIKE '*' ESCAPE '*';
query T
SELECT '\' LIKE '\\' ESCAPE '\';
----
-1
+true
query T
SELECT '\\' LIKE '\\' ESCAPE '\';
----
-0
+false
query T
SELECT '%' LIKE '*%' ESCAPE '*';
----
-1
+true
query T
SELECT '_ ' LIKE '*_ ' ESCAPE '*';
----
-1
+true
query T
SELECT ' a ' LIKE '*_ ' ESCAPE '*';
----
-0
+false
query T
SELECT '%_' LIKE '%_' ESCAPE '';
----
-1
+true
query T
SELECT '*%' NOT LIKE '*%' ESCAPE '*';
----
-1
+true
# It should fail when more than one escape character is specified
statement error
@@ -66,26 +67,26 @@ SELECT '%_' LIKE '%_' ESCAPE '**';
# Test LIKE statement with ESCAPE in the middle of the pattern
statement ok
-CREATE TABLE strings(s STRING, pat STRING);
+CREATE TABLE strings(s VARCHAR, pat VARCHAR);
statement ok
INSERT INTO strings VALUES ('abab', 'ab%'), ('aaa', 'a*_a'), ('aaa', '*%b'), ('bbb', 'a%');
query T
-SELECT s FROM strings;
+SELECT s FROM strings ORDER BY s;
----
-abab
aaa
aaa
+abab
bbb
query T
-SELECT pat FROM strings;
+SELECT pat FROM strings ORDER BY pat;
----
-ab%
-a*_a
*%b
a%
+a*_a
+ab%
query T
SELECT s FROM strings WHERE pat LIKE 'a*%' ESCAPE '*';
@@ -93,7 +94,7 @@ SELECT s FROM strings WHERE pat LIKE 'a*%' ESCAPE '*';
bbb
query T
-SELECT s FROM strings WHERE 'aba' LIKE pat ESCAPE '*';
+SELECT s FROM strings WHERE 'aba' LIKE pat ESCAPE '*' ORDER BY s;
----
abab
bbb
diff --git a/modules/calcite/src/test/sql/function/string/test_mismatches.test b/modules/calcite/src/test/sql/function/string/test_mismatches.test
deleted file mode 100644
index 7f2ddc7..0000000
--- a/modules/calcite/src/test/sql/function/string/test_mismatches.test
+++ /dev/null
@@ -1,178 +0,0 @@
-# name: test/sql/function/string/test_mismatches.test
-# description: Test mismatches function
-# group: [string]
-
-statement ok
-PRAGMA enable_verification
-
-query I
-SELECT mismatches('hallo', 'hallo')
-----
-0
-
-query I
-SELECT mismatches('hello', 'hallo')
-----
-1
-
-query I
-SELECT mismatches('hallo', 'hello')
-----
-1
-
-query I
-SELECT mismatches('aloha', 'hallo')
-----
-5
-
-query I
-SELECT mismatches('hallo', 'aloha')
-----
-5
-
-query I
-SELECT mismatches(NULL, 'hallo')
-----
-NULL
-
-query I
-SELECT mismatches('hello', NULL)
-----
-NULL
-
-query I
-SELECT mismatches(NULL, NULL)
-----
-NULL
-
-
-
-statement ok
-CREATE TABLE strings(s VARCHAR)
-
-statement ok
-INSERT INTO strings VALUES ('hello'), ('hallo'), ('aloha'), ('world'), (NULL)
-
-# normal counts
-query I
-SELECT mismatches(s, 'hallo') FROM strings ORDER BY s
-----
-NULL
-5
-0
-1
-4
-
-query I
-SELECT mismatches('hallo', s) FROM strings ORDER BY s
-----
-NULL
-5
-0
-1
-4
-
-
-
-
-# special cases
-
-statement error
-SELECT mismatches('', '')
-
-query I
-SELECT mismatches(NULL, s) FROM strings ORDER BY s
-----
-NULL
-NULL
-NULL
-NULL
-NULL
-
-query I
-SELECT mismatches(s, NULL) FROM strings ORDER BY s
-----
-NULL
-NULL
-NULL
-NULL
-NULL
-
-
-
-
-
-# incorrect usages
-statement error
-SELECT mismatches('hoi', 'hallo')
-
-statement error
-SELECT mismatches('hallo', 'hoi')
-
-statement error
-SELECT mismatches('', 'hallo')
-
-statement error
-SELECT mismatches('hi', '')
-
-statement error
-SELECT mismatches('', s) FROM strings ORDER BY s
-
-statement error
-SELECT mismatches(s, '') FROM strings ORDER BY s
-
-
-statement ok
-DROP TABLE strings
-
-statement ok
-CREATE TABLE strings(s VARCHAR)
-
-statement ok
-INSERT INTO strings VALUES ('hello'), ('halo'), (NULL)
-
-
-# incorrect usage
-
-statement error
-SELECT mismatches(s, 'hallo') FROM strings
-
-statement error
-SELECT mismatches('hallo', s) FROM strings
-
-
-
-
-query I
-SELECT hamming('hallo', 'hallo')
-----
-0
-
-query I
-SELECT hamming('hello', 'hallo')
-----
-1
-
-query I
-SELECT hamming(s, 'hallo') FROM strings WHERE s = 'hello'
-----
-1
-
-
-
-# Comparing fields from two columns row wise
-
-statement ok
-DROP TABLE strings;
-
-statement ok
-CREATE TABLE strings(s VARCHAR, t VARCHAR)
-
-statement ok
-INSERT INTO strings VALUES ('hello', 'world'), ('hallo', 'ola'), ('hello', ''), (NULL, NULL), ('', ''), ('bora', 'bora')
-
-statement error
-SELECT s, t, hamming(s, t) hd FROM strings WHERE length(s) = length(t)
-
-statement error
-SELECT hamming(s, t) FROM strings
diff --git a/modules/calcite/src/test/sql/function/string/test_pad.test b/modules/calcite/src/test/sql/function/string/test_pad.test
deleted file mode 100644
index 6228272..0000000
--- a/modules/calcite/src/test/sql/function/string/test_pad.test
+++ /dev/null
@@ -1,102 +0,0 @@
-# name: test/sql/function/string/test_pad.test
-# description: LPAD/RPAD test
-# group: [string]
-
-statement ok
-PRAGMA enable_verification
-
-# test lpad on NULLs
-query TTTTTTT
-select LPAD(NULL, 7, '-'), LPAD('Base', NULL, '-'), LPAD('Base', 7, NULL), LPAD(NULL, NULL, '-'), LPAD(NULL, 7, NULL), LPAD('Base', NULL, NULL), LPAD(NULL, NULL, NULL)
-----
-NULL NULL NULL NULL NULL NULL NULL
-
-# test rpad on NULLs
-query TTTTTTT
-select RPAD(NULL, 7, '-'), RPAD('Base', NULL, '-'), RPAD('Base', 7, NULL), RPAD(NULL, NULL, '-'), RPAD(NULL, 7, NULL), RPAD('Base', NULL, NULL), RPAD(NULL, NULL, NULL)
-----
-NULL NULL NULL NULL NULL NULL NULL
-
-# test lpad/rpad on scalar values
-query TTTT
-select LPAD('Base', 7, '-'), LPAD('Base', 4, '-'), LPAD('Base', 2, ''), LPAD('Base', -1, '-')
-----
----Base Base Ba (empty)
-
-query TTTT
-select RPAD('Base', 7, '-'), RPAD('Base', 4, '-'), RPAD('Base', 2, ''), RPAD('Base', -1, '-')
-----
-Base--- Base Ba (empty)
-
-query TTTT
-select LPAD('Base', 7, '-|'), LPAD('Base', 6, '-|'), LPAD('Base', 5, '-|'), LPAD('Base', 4, '-|')
-----
--|-Base -|Base -Base Base
-
-query TTTT
-select RPAD('Base', 7, '-|'), RPAD('Base', 6, '-|'), RPAD('Base', 5, '-|'), RPAD('Base', 4, '-|')
-----
-Base-|- Base-| Base- Base
-
-query TTT
-select LPAD('MotörHead', 16, 'RÄcks'), LPAD('MotörHead', 12, 'RÄcks'), LPAD('MotörHead', 10, 'RÄcks')
-----
-RÄcksRÄMotörHead RÄcMotörHead RMotörHead
-
-query TTT
-select RPAD('MotörHead', 16, 'RÄcks'), RPAD('MotörHead', 12, 'RÄcks'), RPAD('MotörHead', 10, 'RÄcks')
-----
-MotörHeadRÄcksRÄ MotörHeadRÄc MotörHeadR
-
-# test on entire tables
-statement ok
-CREATE TABLE strings(a STRING, b STRING)
-
-statement ok
-INSERT INTO strings VALUES ('Hello', 'World'), ('HuLlD', NULL), ('MotörHead','RÄcks')
-
-query TT
-select LPAD(a, 16, b), RPAD(a, 16, b) FROM strings
-----
-WorldWorldWHello HelloWorldWorldW
-NULL NULL
-RÄcksRÄMotörHead MotörHeadRÄcksRÄ
-
-# test with selection vector
-query TTTT
-select LPAD(a, 12, b), RPAD(a, 12, b), UCASE(a), LCASE(a) FROM strings WHERE b IS NOT NULL
-----
-WorldWoHello HelloWorldWo HELLO hello
-RÄcMotörHead MotörHeadRÄc MOTÖRHEAD motörhead
-
-# test incorrect usage
-statement error
-select LPAD()
-
-statement error
-select LPAD(1)
-
-statement error
-select LPAD(1, 2)
-
-statement error
-select LPAD('Hello', 10, '')
-
-statement error
-select LPAD('a', 100000000000000000, 0)
-
-statement error
-select RPAD()
-
-statement error
-select RPAD(1)
-
-statement error
-select RPAD(1, 2)
-
-statement error
-select RPAD('Hello', 10, '')
-
-statement error
-select RPAD('a', 100000000000000000, 0)
-
diff --git a/modules/calcite/src/test/sql/function/string/test_prefix.test b/modules/calcite/src/test/sql/function/string/test_prefix.test
deleted file mode 100644
index 716d0c5..0000000
--- a/modules/calcite/src/test/sql/function/string/test_prefix.test
+++ /dev/null
@@ -1,241 +0,0 @@
-# name: test/sql/function/string/test_prefix.test
-# description: Prefix test
-# group: [string]
-
-statement ok
-PRAGMA enable_verification
-
-# "Early out prefix"
-query T
-SELECT prefix('abcd', 'a')
-----
-1
-
-query T
-SELECT prefix('abcd', 'ab')
-----
-1
-
-query T
-SELECT prefix('abcd', 'abc')
-----
-1
-
-query T
-SELECT prefix('abcd', 'abcd')
-----
-1
-
-query T
-SELECT prefix('abcd', 'b')
-----
-0
-
-# "Inlined string"
-query T
-SELECT prefix('abcdefgh', 'a')
-----
-1
-
-query T
-SELECT prefix('abcdefgh', 'ab')
-----
-1
-
-query T
-SELECT prefix('abcdefgh', 'abc')
-----
-1
-
-query T
-SELECT prefix('abcdefgh', 'abcd')
-----
-1
-
-query T
-SELECT prefix('abcdefgh', 'abcde')
-----
-1
-
-query T
-SELECT prefix('abcdefgh', 'b')
-----
-0
-
-# Longer (non-inlined) strings
-query T
-SELECT prefix('abcdefghijklmnopqrstuvwxyz', 'a')
-----
-1
-
-query T
-SELECT prefix('abcdefghijklmnopqrstuvwxyz', 'ab')
-----
-1
-
-query T
-SELECT prefix('abcdefghijklmnopqrstuvwxyz', 'abc')
-----
-1
-
-query T
-SELECT prefix('abcdefghijklmnopqrstuvwxyz', 'abcd')
-----
-1
-
-query T
-SELECT prefix('abcdefghijklmnopqrstuvwxyz', 'abcde')
-----
-1
-
-query T
-SELECT prefix('abcdefghijklmnopqrstuvwxyz', 'b')
-----
-0
-
-query T
-SELECT prefix('abcdefghijklmnopqrstuvwxyz', 'abcdefghijklmnopqrstuvwx')
-----
-1
-
-# "Empty string and prefix"
-query T
-SELECT prefix('', 'aaa')
-----
-0
-
-query T
-SELECT prefix('aaa', '')
-----
-1
-
-# "Issue #572 alloc exception on empty table"
-statement ok
-CREATE TABLE t0(c0 VARCHAR)
-
-query T
-SELECT * FROM t0 WHERE PREFIX(t0.c0, '')
-----
-
-# "Prefix test with UTF8"
-query T
-SELECT prefix('átomo', 'á')
-----
-1
-
-query T
-SELECT prefix('átomo', 'á')
-----
-1
-
-query T
-SELECT prefix('átomo', 'a')
-----
-0
-
-query T
-SELECT prefix('olá mundo', 'olá')
-----
-1
-
-query T
-SELECT prefix('olá mundo', 'olá')
-----
-1
-
-query T
-SELECT prefix('olá mundo', 'ola')
-----
-0
-
-query T
-SELECT prefix('ñeft', 'ñ')
-----
-1
-
-query T
-SELECT prefix('ñeft', 'ñ')
-----
-1
-
-query T
-SELECT prefix('ñeft', 'ñeft')
-----
-1
-
-query T
-SELECT prefix('ñeft', 'neft')
-----
-0
-
-query T
-SELECT prefix('two ñ three ₡ four 🦆 end', 'two ñ')
-----
-1
-
-query T
-SELECT prefix('two ñ three ₡ four 🦆 end', 'two ñ')
-----
-1
-
-query T
-SELECT prefix('two ñ three ₡ four 🦆 end', 'two n')
-----
-0
-
-query T
-SELECT prefix('two ñ three ₡ four 🦆 end', 'two ñ three')
-----
-1
-
-query T
-SELECT prefix('two ñ three ₡ four 🦆 end', 'two ñ three ₡')
-----
-1
-
-query T
-SELECT prefix('two ñ three ₡ four 🦆 end', 'two ñ three ₡ four 🦆')
-----
-1
-
-query T
-SELECT prefix('two ñ three ₡ four 🦆 end', 'two ñ three ₡ four 🦆 end')
-----
-1
-
-query T
-SELECT prefix('two ñ three ₡ four 🦆 end', 'two ñ three ₡ four 🦆 end')
-----
-1
-
-# test empty prefix removal
-statement ok
-INSERT INTO t0 VALUES ('a'), ('b'), ('d')
-
-query I
-SELECT COUNT(*) FROM t0 WHERE prefix(t0.c0, '');
-----
-3
-
-query II
-SELECT c0, prefix(t0.c0, '') FROM t0 WHERE c0 <> 'b' ORDER BY t0.c0;
-----
-a 1
-d 1
-
-
-# now with a NULL value
-statement ok
-INSERT INTO t0 VALUES (NULL)
-
-query I
-SELECT COUNT(*) FROM t0 WHERE prefix(t0.c0, '');
-----
-3
-
-query II
-SELECT c0, prefix(t0.c0, '') FROM t0 WHERE c0 IS NULL OR c0 <> 'b' ORDER BY t0.c0;
-----
-NULL NULL
-a 1
-d 1
diff --git a/modules/calcite/src/test/sql/function/string/test_printf.test b/modules/calcite/src/test/sql/function/string/test_printf.test
deleted file mode 100644
index 488a1c2..0000000
--- a/modules/calcite/src/test/sql/function/string/test_printf.test
+++ /dev/null
@@ -1,185 +0,0 @@
-# name: test/sql/function/string/test_printf.test
-# description: Test printf
-# group: [string]
-
-statement ok
-PRAGMA enable_verification
-
-# printf without format specifiers
-query TT
-SELECT printf('hello'), printf(NULL)
-----
-hello NULL
-
-# format strings
-query TT
-SELECT printf('%s', 'hello'), printf('%s: %s', 'hello', 'world')
-----
-hello hello: world
-
-# format strings with NULL values
-query TT
-SELECT printf('%s', NULL), printf(NULL, 'hello', 'world')
-----
-NULL NULL
-
-# booleans
-query T
-SELECT printf('%d', TRUE)
-----
-1
-
-# integers
-query TT
-SELECT printf('%d', 33), printf('%d + %d = %d', 3, 5, 3 + 5)
-----
-33 3 + 5 = 8
-
-# integers with special formatting specifiers
-query TTT
-SELECT printf('%04d', 33), printf('%s %02d:%02d:%02d %s', 'time', 12, 3, 16, 'AM'), printf('%10d', 1992)
-----
-0033 time 12:03:16 AM 1992
-
-# different integer types
-query T
-SELECT printf('%hhd %hd %d %lld', 33::TINYINT, 12::SMALLINT, 40::INTEGER, 80::BIGINT)
-----
-33 12 40 80
-
-# ...but really any of these can be used
-query T
-SELECT printf('%d %lld %hhd %hd', 33::TINYINT, 12::SMALLINT, 40::INTEGER, 80::BIGINT)
-----
-33 12 40 80
-
-# exotic types
-# dates, times and timestamps are strings
-query T
-SELECT printf('%s %s = %s', DATE '1992-01-01', TIME '12:01:00', TIMESTAMP '1992-01-01 12:01:00')
-----
-1992-01-01 12:01:00 = 1992-01-01 12:01:00
-
-# blob
-query T
-SELECT printf('%s', BLOB '\x01\xa0')
-----
-\x01\xA0
-
-# hugeint
-query T
-SELECT printf('%s', 120381902481294715712::HUGEINT)
-----
-120381902481294715712
-
-# decimal
-query T
-SELECT printf('%.3f', '1.234'::DECIMAL)
-----
-1.234
-
-# octal hex etc
-query T
-SELECT printf('%d %x %o %#x %#o', 100, 100, 100, 100, 100)
-----
-100 64 144 0x64 0144
-
-# ascii characters
-query T
-SELECT printf('%c', 65)
-----
-A
-
-# width trick
-query T
-SELECT printf('%*d', 5, 10)
-----
- 10
-
-# floating point numbers
-query TT
-SELECT printf('%.2f', 10.0::FLOAT), printf('%.4f', 0.5)
-----
-10.00 0.5000
-
-# weird float stuff
-query T
-SELECT printf('floats: %4.2f %+.0e %E', 3.1416, 3.1416, 3.1416)
-----
-floats: 3.14 +3e+00 3.141600E+00
-
-# incorrect number of parameters
-# too few parameters
-statement error
-SELECT printf('%s')
-
-statement error
-SELECT printf('%s %s', 'hello')
-
-# excess parameters are ignored
-query T
-SELECT printf('%s', 'hello', 'world')
-----
-hello
-
-# incorrect types
-statement error
-SELECT printf('%s', 42)
-
-statement error
-SELECT printf('%d', 'hello')
-
-# Test printf with vectors
-statement ok
-CREATE TABLE strings(idx INTEGER, fmt STRING, pint INTEGER, pstring STRING)
-
-statement ok
-INSERT INTO strings VALUES (1, '%d: %s', 10, 'hello')
-
-statement ok
-INSERT INTO strings VALUES (2, 'blabla %d blabla %s', 20, 'blabla')
-
-statement ok
-INSERT INTO strings VALUES (3, NULL, 30, 'abcde')
-
-# printf without format specifiers: too few parameters
-statement error
-SELECT printf(fmt) FROM strings ORDER BY idx
-
-query T
-SELECT printf(CASE WHEN pint < 15 THEN NULL ELSE pint END) FROM strings ORDER BY idx
-----
-NULL
-20
-30
-
-# standard vectorized printf
-query T
-SELECT printf(fmt, pint, pstring) FROM strings ORDER BY idx
-----
-10: hello
-blabla 20 blabla blabla
-NULL
-
-# printf with constants in format arguments
-query T
-SELECT printf(fmt, 10, pstring) FROM strings ORDER BY idx
-----
-10: hello
-blabla 10 blabla blabla
-NULL
-
-# printf with constant format string
-query T
-SELECT printf('%s: %s', pstring, pstring) FROM strings ORDER BY idx
-----
-hello: hello
-blabla: blabla
-abcde: abcde
-
-# printf with selection vector
-query T
-SELECT printf('%s: %s', pstring, pstring) FROM strings WHERE idx <> 2 ORDER BY idx
-----
-hello: hello
-abcde: abcde
diff --git a/modules/calcite/src/test/sql/function/string/test_repeat.test b/modules/calcite/src/test/sql/function/string/test_repeat.test
index 163504f..80e2479 100644
--- a/modules/calcite/src/test/sql/function/string/test_repeat.test
+++ b/modules/calcite/src/test/sql/function/string/test_repeat.test
@@ -2,14 +2,11 @@
# description: REPEAT test
# group: [string]
-statement ok
-PRAGMA enable_verification
-
# test repeat on NULLs
-query TTT
-select REPEAT(NULL, NULL), REPEAT(NULL, 3), REPEAT('MySQL', NULL)
+query T
+select REPEAT('MySQL', NULL)
----
-NULL NULL NULL
+NULL
# test repeat on scalars
query TTTT
@@ -19,13 +16,13 @@ select REPEAT('', 3), REPEAT('MySQL', 3), REPEAT('MotörHead', 2), REPEAT('Hello
# test repeat on tables
statement ok
-CREATE TABLE strings(a STRING, b STRING)
+CREATE TABLE strings(id INTEGER, a VARCHAR, b VARCHAR)
statement ok
-INSERT INTO strings VALUES ('Hello', 'World'), ('HuLlD', NULL), ('MotörHead','RÄcks'), ('', NULL)
+INSERT INTO strings VALUES (0, 'Hello', 'World'), (1, 'HuLlD', NULL), (2, 'MotörHead','RÄcks'), (3, '', NULL)
query T
-select REPEAT(a, 3) FROM strings
+select REPEAT(a, 3) FROM strings ORDER BY id
----
HelloHelloHello
HuLlDHuLlDHuLlD
@@ -33,7 +30,7 @@ MotörHeadMotörHeadMotörHead
(empty)
query T
-select REPEAT(b, 2) FROM strings
+select REPEAT(b, 2) FROM strings ORDER BY id
----
WorldWorld
NULL
@@ -41,7 +38,7 @@ RÄcksRÄcks
NULL
query T
-select REPEAT(a, 4) FROM strings WHERE b IS NOT NULL
+select REPEAT(a, 4) FROM strings WHERE b IS NOT NULL ORDER BY id
----
HelloHelloHelloHello
MotörHeadMotörHeadMotörHeadMotörHead
diff --git a/modules/calcite/src/test/sql/function/string/test_repeat.test b/modules/calcite/src/test/sql/function/string/test_repeat.test_ignore
similarity index 69%
copy from modules/calcite/src/test/sql/function/string/test_repeat.test
copy to modules/calcite/src/test/sql/function/string/test_repeat.test_ignore
index 163504f..7a01404 100644
--- a/modules/calcite/src/test/sql/function/string/test_repeat.test
+++ b/modules/calcite/src/test/sql/function/string/test_repeat.test_ignore
@@ -1,9 +1,7 @@
# name: test/sql/function/string/test_repeat.test
# description: REPEAT test
# group: [string]
-
-statement ok
-PRAGMA enable_verification
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-15002
# test repeat on NULLs
query TTT
@@ -19,13 +17,13 @@ select REPEAT('', 3), REPEAT('MySQL', 3), REPEAT('MotörHead', 2), REPEAT('Hello
# test repeat on tables
statement ok
-CREATE TABLE strings(a STRING, b STRING)
+CREATE TABLE strings(id INTEGER, a VARCHAR, b VARCHAR)
statement ok
-INSERT INTO strings VALUES ('Hello', 'World'), ('HuLlD', NULL), ('MotörHead','RÄcks'), ('', NULL)
+INSERT INTO strings VALUES (0, 'Hello', 'World'), (1, 'HuLlD', NULL), (2, 'MotörHead','RÄcks'), (3, '', NULL)
query T
-select REPEAT(a, 3) FROM strings
+select REPEAT(a, 3) FROM strings ORDER BY id
----
HelloHelloHello
HuLlDHuLlDHuLlD
@@ -33,7 +31,7 @@ MotörHeadMotörHeadMotörHead
(empty)
query T
-select REPEAT(b, 2) FROM strings
+select REPEAT(b, 2) FROM strings ORDER BY id
----
WorldWorld
NULL
@@ -41,7 +39,7 @@ RÄcksRÄcks
NULL
query T
-select REPEAT(a, 4) FROM strings WHERE b IS NOT NULL
+select REPEAT(a, 4) FROM strings WHERE b IS NOT NULL ORDER BY id
----
HelloHelloHelloHello
MotörHeadMotörHeadMotörHeadMotörHead
diff --git a/modules/calcite/src/test/sql/function/string/test_replace.test b/modules/calcite/src/test/sql/function/string/test_replace.test_ignore
similarity index 92%
rename from modules/calcite/src/test/sql/function/string/test_replace.test
rename to modules/calcite/src/test/sql/function/string/test_replace.test_ignore
index 1c1f76a..26cf02d 100644
--- a/modules/calcite/src/test/sql/function/string/test_replace.test
+++ b/modules/calcite/src/test/sql/function/string/test_replace.test_ignore
@@ -1,9 +1,7 @@
# name: test/sql/function/string/test_replace.test
# description: REPLACE test
# group: [string]
-
-statement ok
-PRAGMA enable_verification
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-15031
# test replace on NULLs
query T
@@ -39,7 +37,7 @@ select REPLACE('aaaaaaa', 'a', '0123456789')
# test replace on tables
statement ok
-CREATE TABLE strings(a STRING, b STRING)
+CREATE TABLE strings(a VARCHAR, b VARCHAR)
statement ok
INSERT INTO strings VALUES ('Hello', 'World'), ('HuLlD', NULL), ('MotörHead','RÄcks'), ('', NULL)
diff --git a/modules/calcite/src/test/sql/function/string/test_reverse.test b/modules/calcite/src/test/sql/function/string/test_reverse.test
index da7f308..1c9426a 100644
--- a/modules/calcite/src/test/sql/function/string/test_reverse.test
+++ b/modules/calcite/src/test/sql/function/string/test_reverse.test
@@ -2,24 +2,21 @@
# description: REVERSE test
# group: [string]
-statement ok
-PRAGMA enable_verification
-
# test reverse on scalars
-query TTTT
-select REVERSE(''), REVERSE('Hello'), REVERSE('MotörHead'), REVERSE(NULL)
+query TTT
+select REVERSE(''), REVERSE('Hello'), REVERSE('MotörHead')
----
-(empty) olleH daeHrötoM NULL
+(empty) olleH daeHrötoM
# test reverse on tables
statement ok
-CREATE TABLE strings(a STRING, b STRING)
+CREATE TABLE strings(id INTEGER, a VARCHAR, b VARCHAR)
statement ok
-INSERT INTO strings VALUES ('Hello', 'World'), ('HuLlD', NULL), ('MotörHead','RÄcks'), ('', NULL)
+INSERT INTO strings VALUES (0, 'Hello', 'World'), (1, 'HuLlD', NULL), (2, 'MotörHead','RÄcks'), (3, '', NULL)
query T
-select REVERSE(a) FROM strings
+select REVERSE(a) FROM strings ORDER BY id
----
olleH
DlLuH
@@ -27,7 +24,7 @@ daeHrötoM
(empty)
query T
-select REVERSE(b) FROM strings
+select REVERSE(b) FROM strings ORDER BY id
----
dlroW
NULL
@@ -35,7 +32,7 @@ skcÄR
NULL
query T
-select REVERSE(a) FROM strings WHERE b IS NOT NULL
+select REVERSE(a) FROM strings WHERE b IS NOT NULL ORDER BY id
----
olleH
daeHrötoM
diff --git a/modules/calcite/src/test/sql/function/string/test_reverse.test b/modules/calcite/src/test/sql/function/string/test_reverse.test_ignore
similarity index 60%
copy from modules/calcite/src/test/sql/function/string/test_reverse.test
copy to modules/calcite/src/test/sql/function/string/test_reverse.test_ignore
index da7f308..ae7c636 100644
--- a/modules/calcite/src/test/sql/function/string/test_reverse.test
+++ b/modules/calcite/src/test/sql/function/string/test_reverse.test_ignore
@@ -1,9 +1,7 @@
# name: test/sql/function/string/test_reverse.test
# description: REVERSE test
# group: [string]
-
-statement ok
-PRAGMA enable_verification
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-15002
# test reverse on scalars
query TTTT
@@ -13,13 +11,13 @@ select REVERSE(''), REVERSE('Hello'), REVERSE('MotörHead'), REVERSE(NULL)
# test reverse on tables
statement ok
-CREATE TABLE strings(a STRING, b STRING)
+CREATE TABLE strings(id INTEGER, a VARCHAR, b VARCHAR)
statement ok
-INSERT INTO strings VALUES ('Hello', 'World'), ('HuLlD', NULL), ('MotörHead','RÄcks'), ('', NULL)
+INSERT INTO strings VALUES (0, 'Hello', 'World'), (1, 'HuLlD', NULL), (2, 'MotörHead','RÄcks'), (3, '', NULL)
query T
-select REVERSE(a) FROM strings
+select REVERSE(a) FROM strings ORDER BY id
----
olleH
DlLuH
@@ -27,7 +25,7 @@ daeHrötoM
(empty)
query T
-select REVERSE(b) FROM strings
+select REVERSE(b) FROM strings ORDER BY id
----
dlroW
NULL
@@ -35,7 +33,7 @@ skcÄR
NULL
query T
-select REVERSE(a) FROM strings WHERE b IS NOT NULL
+select REVERSE(a) FROM strings WHERE b IS NOT NULL ORDER BY id
----
olleH
daeHrötoM
diff --git a/modules/calcite/src/test/sql/function/string/test_right.test b/modules/calcite/src/test/sql/function/string/test_right.test
index fcf5e3b..eb283cd 100644
--- a/modules/calcite/src/test/sql/function/string/test_right.test
+++ b/modules/calcite/src/test/sql/function/string/test_right.test
@@ -2,64 +2,30 @@
# description: RIGHT test
# group: [string]
-statement ok
-PRAGMA enable_verification
-
# test RIGHT on positive positions
query TTTTT
SELECT RIGHT('abcd', 0), RIGHT('abc', 1), RIGHT('abc', 2), RIGHT('abc', 3), RIGHT('abc', 4)
----
(empty) c bc abc abc
-query TTTTT
-SELECT RIGHT('🦆ab', 0), RIGHT('🦆ab', 1), RIGHT('🦆ab', 2), RIGHT('🦆ab', 3), RIGHT('🦆ab', 4)
-----
-(empty) b ab 🦆ab 🦆ab
-
-query TTTT
... 3098 lines suppressed ...