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/04/16 16:32:16 UTC
[ignite] branch sql-calcite updated: IGNITE-14508 Calcite
integration: introduce SQL script test runner
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 bdfa843 IGNITE-14508 Calcite integration: introduce SQL script test runner
bdfa843 is described below
commit bdfa843ff3840c2db2d55c6f9be8aabab20bc9fa
Author: tledkov <tl...@gridgain.com>
AuthorDate: Fri Apr 16 19:31:58 2021 +0300
IGNITE-14508 Calcite integration: introduce SQL script test runner
---
.../query/calcite/type/IgniteTypeSystem.java | 10 +
modules/calcite/src/test/config/log4j-test.xml | 158 +++++
.../logical/ScriptRunnerTestsEnvironment.java | 58 ++
.../query/calcite/logical/ScriptTestRunner.java | 262 ++++++++
.../query/calcite/logical/SqlScriptRunner.java | 596 ++++++++++++++++
.../apache/ignite/testsuites/ScriptTestSuite.java | 39 ++
.../sql/aggregate/aggregates/test_aggr_string.test | 35 +
.../aggregates/test_aggr_string.test_ignore | 73 ++
.../aggregate/aggregates/test_aggregate_types.test | 190 ++++++
.../aggregates/test_aggregate_types_scalar.test | 108 +++
.../aggregate/aggregates/test_approx_quantile.test | 172 +++++
.../test_approximate_distinct_count.test | 91 +++
.../sql/aggregate/aggregates/test_arg_min_max.test | 130 ++++
.../test/sql/aggregate/aggregates/test_avg.test | 54 ++
.../sql/aggregate/aggregates/test_bit_and.test | 56 ++
.../test/sql/aggregate/aggregates/test_bit_or.test | 56 ++
.../sql/aggregate/aggregates/test_bit_xor.test | 56 ++
.../test/sql/aggregate/aggregates/test_bool.test | 95 +++
.../test/sql/aggregate/aggregates/test_corr.test | 51 ++
.../test/sql/aggregate/aggregates/test_count.test | 35 +
.../aggregate/aggregates/test_count.test_ignored | 41 ++
.../sql/aggregate/aggregates/test_count_star.test | 15 +
.../test/sql/aggregate/aggregates/test_covar.test | 98 +++
.../aggregate/aggregates/test_distinct_aggr.test | 21 +
.../aggregates/test_distinct_string_agg.test | 16 +
.../aggregate/aggregates/test_empty_aggregate.test | 31 +
.../sql/aggregate/aggregates/test_entropy.test | 94 +++
.../aggregates/test_first_noninlined.test | 38 ++
.../aggregates/test_group_by_many_groups.test_slow | 14 +
.../aggregates/test_group_on_expression.test | 75 +++
.../sql/aggregate/aggregates/test_histogram.test | 93 +++
.../aggregates/test_incorrect_aggregate.test | 55 ++
.../sql/aggregate/aggregates/test_kurtosis.test | 82 +++
.../test/sql/aggregate/aggregates/test_mode.test | 96 +++
.../aggregate/aggregates/test_null_aggregates.test | 406 +++++++++++
.../sql/aggregate/aggregates/test_perfect_ht.test | 24 +
.../aggregates/test_perfect_ht.test_ignored | 175 +++++
.../sql/aggregate/aggregates/test_product.test | 68 ++
.../sql/aggregate/aggregates/test_quantile.test | 222 ++++++
.../aggregate/aggregates/test_quantile_list.test | 114 ++++
.../sql/aggregate/aggregates/test_regression.test | 346 ++++++++++
.../sql/aggregate/aggregates/test_scalar_aggr.test | 65 ++
.../sql/aggregate/aggregates/test_skewness.test | 82 +++
.../test/sql/aggregate/aggregates/test_stddev.test | 122 ++++
.../sql/aggregate/aggregates/test_string_agg.test | 90 +++
.../aggregate/aggregates/test_string_agg_big.test | 16 +
.../test_string_agg_many_groups.test_slow | 22 +
.../test/sql/aggregate/aggregates/test_sum.test | 75 +++
.../test/sql/aggregate/distinct/test_distinct.test | 55 ++
.../distinct/test_distinct_on.test_ignored | 133 ++++
.../aggregate/distinct/test_distinct_order_by.test | 41 ++
.../test/sql/aggregate/group/test_group_by.test | 181 +++++
.../sql/aggregate/group/test_group_by_alias.test | 96 +++
.../group/test_group_by_large_string.test | 16 +
.../group/test_group_by_multi_column.test | 17 +
.../test/sql/aggregate/group/test_group_null.test | 17 +
.../test_corel_subquery_in_having.test_ignored | 38 ++
.../src/test/sql/aggregate/having/test_having.test | 45 ++
.../having/test_scalar_having.test_ignored | 81 +++
.../src/test/sql/cast/test_boolean_cast.test | 170 +++++
.../src/test/sql/cast/test_exponent_in_cast.test | 44 ++
.../src/test/sql/cast/test_string_cast.test | 55 ++
.../calcite/src/test/sql/cast/test_try_cast.test | 22 +
.../calcite/src/test/sql/delete/test_delete.test | 52 ++
.../src/test/sql/delete/test_large_delete.test | 15 +
.../src/test/sql/delete/test_segment_deletes.test | 179 +++++
.../src/test/sql/filter/test_alias_filter.test | 20 +
.../test/sql/filter/test_constant_comparisons.test | 103 +++
.../src/test/sql/filter/test_filter_clause.test | 542 +++++++++++++++
.../src/test/sql/filter/test_illegal_filters.test | 17 +
.../src/test/sql/filter/test_obsolete_filters.test | 280 ++++++++
.../test/sql/filter/test_transitive_filters.test | 295 ++++++++
.../src/test/sql/filter/test_zonemap.test_slow | 76 +++
.../calcite/src/test/sql/function/blob/base64.test | 92 +++
.../calcite/src/test/sql/function/blob/encode.test | 37 +
.../test/sql/function/date/date_part_stats.test | 201 ++++++
.../src/test/sql/function/date/test_date_part.test | 168 +++++
.../test/sql/function/date/test_date_trunc.test | 143 ++++
.../src/test/sql/function/date/test_extract.test | 114 ++++
.../sql/function/date/test_extract_edge_cases.test | 341 ++++++++++
.../test/sql/function/date/test_extract_month.test | 746 +++++++++++++++++++++
.../test/sql/function/date/test_extract_year.test | 650 ++++++++++++++++++
.../src/test/sql/function/date/test_strftime.test | 101 +++
.../function/date/test_strftime_exhaustive.test | 354 ++++++++++
.../test/sql/function/generic/test_between.test | 222 ++++++
.../src/test/sql/function/generic/test_case.test | 96 +++
.../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 | 138 ++++
.../sql/function/generic/test_large_in.test_slow | 51 ++
.../sql/function/generic/test_least_greatest.test | 134 ++++
.../test/sql/function/generic/test_null_if.test | 57 ++
.../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/sql/function/interval/test_extract.test | 189 ++++++
.../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 | 143 ++++
.../src/test/sql/function/numeric/test_gamma.test | 109 +++
.../sql/function/numeric/test_invalid_math.test | 32 +
.../src/test/sql/function/numeric/test_mod.test | 34 +
.../test/sql/function/numeric/test_pg_math.test | 99 +++
.../src/test/sql/function/numeric/test_pow.test | 38 ++
.../src/test/sql/function/numeric/test_random.test | 98 +++
.../src/test/sql/function/numeric/test_round.test | 58 ++
.../src/test/sql/function/numeric/test_trigo.test | 448 +++++++++++++
.../sql/function/numeric/test_type_resolution.test | 205 ++++++
.../src/test/sql/function/numeric/test_unary.test | 64 ++
.../sql/function/operator/test_arithmetic.test | 146 ++++
.../operator/test_arithmetic_sqllogic.test | 54 ++
.../sql/function/operator/test_bitwise_ops.test | 68 ++
.../function/operator/test_bitwise_ops_types.test | 103 +++
.../sql/function/operator/test_comparison.test | 47 ++
.../sql/function/operator/test_conjunction.test | 192 ++++++
.../calcite/src/test/sql/function/string/md5.test | 37 +
.../sql/function/string/regex_filter_pushdown.test | 34 +
.../test/sql/function/string/regex_replace.test | 79 +++
.../src/test/sql/function/string/regex_search.test | 169 +++++
.../test/sql/function/string/strip_accents.test | 37 +
.../src/test/sql/function/string/test_ascii.test | 77 +++
.../test/sql/function/string/test_bit_length.test | 52 ++
.../test/sql/function/string/test_caseconvert.test | 76 +++
.../sql/function/string/test_complex_unicode.test | 85 +++
.../src/test/sql/function/string/test_concat.test | 81 +++
.../sql/function/string/test_concat_function.test | 64 ++
.../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 | 103 +++
.../src/test/sql/function/string/test_length.test | 29 +
.../test/sql/function/string/test_levenshtein.test | 273 ++++++++
.../src/test/sql/function/string/test_like.test | 288 ++++++++
.../test/sql/function/string/test_like_escape.test | 105 +++
.../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 | 61 ++
.../src/test/sql/function/string/test_replace.test | 78 +++
.../src/test/sql/function/string/test_reverse.test | 52 ++
.../src/test/sql/function/string/test_right.test | 103 +++
.../test/sql/function/string/test_similar_to.test | 139 ++++
.../function/string/test_string_split.test_slow | 360 ++++++++++
.../test/sql/function/string/test_substring.test | 214 ++++++
.../sql/function/string/test_substring_utf8.test | 59 ++
.../src/test/sql/function/string/test_suffix.test | 232 +++++++
.../src/test/sql/function/string/test_trim.test | 130 ++++
.../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 | 104 +++
.../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 +
.../src/test/sql/function/timestamp/epoch.test | 14 +
.../test/sql/function/timestamp/test_extract.test | 102 +++
.../sql/function/timestamp/test_extract_ms.test | 37 +
.../src/test/sql/function/timestamp/test_now.test | 36 +
.../timestamp/test_strftime_timestamp.test | 336 ++++++++++
.../test/sql/function/timestamp/test_strptime.test | 199 ++++++
.../test/sql/insert/big_insert_rollback.test_slow | 25 +
.../src/test/sql/insert/insert_rollback.test | 25 +
.../insert/interleaved_insert_rollback.test_slow | 49 ++
.../calcite/src/test/sql/insert/null_values.test | 40 ++
.../src/test/sql/insert/test_big_insert.test | 56 ++
.../calcite/src/test/sql/insert/test_insert.test | 40 ++
.../src/test/sql/insert/test_insert_invalid.test | 37 +
.../src/test/sql/insert/test_insert_query.test | 19 +
.../src/test/sql/insert/test_insert_type.test | 33 +
.../sql/insert/unaligned_interleaved_appends.test | 81 +++
.../sql/join/full_outer/test_full_outer_join.test | 51 ++
.../full_outer/test_full_outer_join_complex.test | 31 +
.../test_full_outer_join_inequality.test | 31 +
.../test_full_outer_join_many_matches.test | 24 +
.../full_outer/test_full_outer_join_range.test | 32 +
.../test/sql/join/inner/join_cross_product.test | 39 ++
.../src/test/sql/join/inner/test_eq_ineq_join.test | 131 ++++
.../calcite/src/test/sql/join/inner/test_join.test | 100 +++
.../test/sql/join/inner/test_join_duplicates.test | 26 +
.../src/test/sql/join/inner/test_join_types.test | 406 +++++++++++
.../src/test/sql/join/inner/test_lt_join.test | 30 +
.../src/test/sql/join/inner/test_range_join.test | 59 ++
.../src/test/sql/join/inner/test_unequal_join.test | 83 +++
.../join/inner/test_unequal_join_duplicates.test | 26 +
.../src/test/sql/join/inner/test_using_chain.test | 73 ++
.../src/test/sql/join/inner/test_using_join.test | 85 +++
.../src/test/sql/join/inner/test_varchar_join.test | 27 +
.../sql/join/left_outer/left_join_issue_1172.test | 133 ++++
.../test/sql/join/left_outer/test_left_outer.test | 180 +++++
.../test/sql/join/mark/test_mark_join_types.test | 682 +++++++++++++++++++
.../src/test/sql/join/natural/natural_join.test | 114 ++++
.../sql/join/right_outer/test_right_outer.test | 153 +++++
.../src/test/sql/join/test_complex_join_expr.test | 37 +
.../join/test_cross_product_parallelism.test_slow | 31 +
.../src/test/sql/join/test_join_on_aggregates.test | 32 +
.../src/test/sql/join/test_not_distinct_from.test | 277 ++++++++
modules/calcite/src/test/sql/order/test_limit.test | 184 +++++
.../src/test/sql/order/test_nulls_first.test | 124 ++++
.../calcite/src/test/sql/order/test_order_by.test | 173 +++++
.../test/sql/order/test_order_by_exceptions.test | 60 ++
.../src/test/sql/order/test_order_large.test | 12 +
.../src/test/sql/order/test_order_pragma.test | 44 ++
.../test/sql/order/test_order_same_value.test_slow | 79 +++
.../order/test_order_variable_size_payload.test | 409 +++++++++++
modules/calcite/src/test/sql/order/test_top_n.test | 43 ++
.../test/sql/subquery/any_all/test_any_all.test | 82 +++
.../subquery/any_all/test_correlated_any_all.test | 181 +++++
.../sql/subquery/any_all/test_scalar_any_all.test | 49 ++
.../test/sql/subquery/any_all/test_scalar_in.test | 76 +++
.../sql/subquery/any_all/test_simple_not_in.test | 36 +
.../any_all/test_uncorrelated_all_subquery.test | 216 ++++++
.../any_all/test_uncorrelated_any_subquery.test | 131 ++++
.../subquery/exists/test_correlated_exists.test | 126 ++++
.../sql/subquery/exists/test_scalar_exists.test | 36 +
.../exists/test_uncorrelated_exists_subquery.test | 115 ++++
.../sql/subquery/lateral/test_lateral_join.test | 26 +
.../scalar/test_complex_correlated_subquery.test | 157 +++++
.../test_complex_nested_correlated_subquery.test | 48 ++
.../scalar/test_correlated_aggregate_subquery.test | 373 +++++++++++
.../subquery/scalar/test_correlated_subquery.test | 147 ++++
.../scalar/test_correlated_subquery_cte.test | 147 ++++
.../scalar/test_correlated_subquery_where.test | 33 +
.../subquery/scalar/test_count_star_subquery.test | 100 +++
.../sql/subquery/scalar/test_delete_subquery.test | 34 +
.../scalar/test_grouped_correlated_subquery.test | 135 ++++
.../sql/subquery/scalar/test_join_in_subquery.test | 34 +
.../scalar/test_many_correlated_columns.test | 65 ++
.../scalar/test_nested_correlated_subquery.test | 329 +++++++++
.../sql/subquery/scalar/test_scalar_subquery.test | 153 +++++
.../subquery/scalar/test_scalar_subquery_cte.test | 153 +++++
.../scalar/test_tpcds_correlated_subquery.test | 18 +
.../scalar/test_uncorrelated_scalar_subquery.test | 127 ++++
.../scalar/test_uncorrelated_varchar_subquery.test | 68 ++
.../sql/subquery/scalar/test_update_subquery.test | 73 ++
.../scalar/test_varchar_correlated_subquery.test | 93 +++
.../scalar/test_window_function_subquery.test | 80 +++
.../src/test/sql/subquery/table/test_aliasing.test | 23 +
.../subquery/table/test_nested_table_subquery.test | 27 +
.../sql/subquery/table/test_subquery_union.test | 13 +
.../sql/subquery/table/test_table_subquery.test | 54 ++
.../src/test/sql/subquery/test_neumann.test_ignore | 60 ++
.../calcite/src/test/sql/types/blob/test_blob.test | 95 +++
.../src/test/sql/types/blob/test_blob_cast.test | 71 ++
.../test/sql/types/blob/test_blob_function.test | 84 +++
.../test/sql/types/blob/test_blob_operator.test | 64 ++
.../src/test/sql/types/blob/test_blob_string.test | 35 +
.../src/test/sql/types/date/date_parsing.test | 343 ++++++++++
.../src/test/sql/types/date/test_bc_dates.test | 110 +++
.../calcite/src/test/sql/types/date/test_date.test | 70 ++
.../test/sql/types/date/test_incorrect_dates.test | 54 ++
.../test/sql/types/decimal/cast_from_decimal.test | 78 +++
.../test/sql/types/decimal/cast_to_decimal.test | 286 ++++++++
.../test/sql/types/decimal/decimal_aggregates.test | 90 +++
.../test/sql/types/decimal/decimal_arithmetic.test | 203 ++++++
.../decimal/decimal_decimal_overflow_cast.test | 94 +++
.../test/sql/types/decimal/decimal_overflow.test | 51 ++
.../sql/types/decimal/decimal_overflow_table.test | 39 ++
.../sql/types/decimal/large_decimal_constants.test | 43 ++
.../src/test/sql/types/decimal/test_decimal.test | 135 ++++
.../sql/types/decimal/test_decimal_cast.test_slow | 87 +++
.../test/sql/types/decimal/test_decimal_ops.test | 253 +++++++
.../test/sql/types/hugeint/hugeint_multiply.test | 144 ++++
.../sql/types/hugeint/test_hugeint_aggregates.test | 26 +
.../sql/types/hugeint/test_hugeint_arithmetic.test | 193 ++++++
.../sql/types/hugeint/test_hugeint_auto_cast.test | 32 +
.../sql/types/hugeint/test_hugeint_bitwise.test | 137 ++++
.../sql/types/hugeint/test_hugeint_conversion.test | 167 +++++
.../sql/types/hugeint/test_hugeint_functions.test | 45 ++
.../sql/types/hugeint/test_hugeint_null_value.test | 48 ++
.../test/sql/types/hugeint/test_hugeint_ops.test | 75 +++
.../sql/types/interval/interval_constants.test | 145 ++++
.../src/test/sql/types/interval/test_interval.test | 239 +++++++
.../sql/types/interval/test_interval_addition.test | 255 +++++++
.../types/interval/test_interval_comparison.test | 48 ++
.../test/sql/types/interval/test_interval_ops.test | 51 ++
.../calcite/src/test/sql/types/list/array_agg.test | 59 ++
.../test/sql/types/list/lineitem_list.test_slow | 120 ++++
.../calcite/src/test/sql/types/list/list.test_slow | 14 +
.../src/test/sql/types/list/list_aggregates.test | 67 ++
.../src/test/sql/types/list/test_list_extract.test | 182 +++++
.../src/test/sql/types/list/test_list_index.test | 62 ++
.../src/test/sql/types/list/test_nested_list.test | 265 ++++++++
.../src/test/sql/types/list/test_scalar_list.test | 140 ++++
.../calcite/src/test/sql/types/map/test_map.test | 35 +
.../src/test/sql/types/null/test_boolean_null.test | 50 ++
.../src/test/sql/types/null/test_is_null.test | 24 +
.../calcite/src/test/sql/types/null/test_null.test | 88 +++
.../src/test/sql/types/null/test_null_aggr.test | 53 ++
.../test/sql/types/string/test_big_strings.test | 29 +
.../types/string/test_scan_big_varchar.test_slow | 152 +++++
.../src/test/sql/types/string/test_unicode.test | 32 +
.../sql/types/struct/lineitem_struct.test_slow | 38 ++
.../test/sql/types/struct/struct_aggregates.test | 16 +
.../src/test/sql/types/struct/test_struct.test | 177 +++++
.../calcite/src/test/sql/types/time/test_time.test | 31 +
.../src/test/sql/types/time/time_parsing.test | 60 ++
.../src/test/sql/types/timestamp/bc_timestamp.test | 18 +
.../types/timestamp/test_incorrect_timestamp.test | 52 ++
.../test/sql/types/timestamp/test_timestamp.test | 143 ++++
.../sql/types/timestamp/test_timestamp_ms.test | 14 +
.../types/unsigned/test_unsigned_arithmetic.test | 98 +++
.../types/unsigned/test_unsigned_auto_cast.test | 196 ++++++
.../types/unsigned/test_unsigned_conversion.test | 506 ++++++++++++++
.../src/test/sql/update/null_update_merge.test | 128 ++++
.../sql/update/null_update_merge_transaction.test | 120 ++++
.../test/sql/update/test_big_string_update.test | 96 +++
.../sql/update/test_big_table_update.test_slow | 111 +++
.../src/test/sql/update/test_null_update.test | 193 ++++++
.../sql/update/test_repeated_string_update.test | 61 ++
.../src/test/sql/update/test_string_update.test | 65 ++
.../update/test_string_update_many_strings.test | 127 ++++
.../test/sql/update/test_string_update_null.test | 39 ++
.../sql/update/test_string_update_rollback.test | 117 ++++
.../update/test_string_update_rollback_null.test | 88 +++
.../calcite/src/test/sql/update/test_update.test | 79 +++
.../sql/update/test_update_delete_same_tuple.test | 43 ++
.../src/test/sql/update/test_update_from.test | 159 +++++
.../test/sql/update/test_update_many_updaters.test | 315 +++++++++
.../update/test_update_many_updaters_nulls.test | 118 ++++
.../src/test/sql/update/test_update_mix.test | 74 ++
.../sql/update/test_update_same_string_value.test | 41 ++
.../test/sql/update/test_update_same_value.test | 179 +++++
330 files changed, 36584 insertions(+)
diff --git a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/type/IgniteTypeSystem.java b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/type/IgniteTypeSystem.java
index d4785f6..92d2299 100644
--- a/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/type/IgniteTypeSystem.java
+++ b/modules/calcite/src/main/java/org/apache/ignite/internal/processors/query/calcite/type/IgniteTypeSystem.java
@@ -27,4 +27,14 @@ import org.apache.calcite.rel.type.RelDataTypeSystemImpl;
*/
public class IgniteTypeSystem extends RelDataTypeSystemImpl implements Serializable {
public static final RelDataTypeSystem INSTANCE = new IgniteTypeSystem();
+
+ /** {@inheritDoc} */
+ @Override public int getMaxNumericScale() {
+ return Short.MAX_VALUE;
+ }
+
+ /** {@inheritDoc} */
+ @Override public int getMaxNumericPrecision() {
+ return Short.MAX_VALUE;
+ }
}
diff --git a/modules/calcite/src/test/config/log4j-test.xml b/modules/calcite/src/test/config/log4j-test.xml
new file mode 100755
index 0000000..c0e49b6
--- /dev/null
+++ b/modules/calcite/src/test/config/log4j-test.xml
@@ -0,0 +1,158 @@
+<?xml version="1.0" encoding="UTF-8"?>
+
+<!--
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+-->
+
+<!DOCTYPE log4j:configuration PUBLIC "-//APACHE//DTD LOG4J 1.2//EN"
+ "http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/xml/doc-files/log4j.dtd">
+<!--
+ Log4j configuration.
+-->
+<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/" debug="false">
+ <!--
+ Logs System.out messages to console.
+ -->
+ <appender name="CONSOLE" class="org.apache.log4j.ConsoleAppender">
+ <!-- Log to STDOUT. -->
+ <param name="Target" value="System.out"/>
+
+ <!-- Log from DEBUG and higher. -->
+ <param name="Threshold" value="DEBUG"/>
+
+ <!-- The default pattern: Date Priority [Category] Message\n -->
+ <layout class="org.apache.log4j.PatternLayout">
+ <param name="ConversionPattern" value="[%d{ISO8601}][%-5p][%t][%cc{1}] %m%n"/>
+ </layout>
+
+ <!-- Do not log beyond INFO level. -->
+ <filter class="org.apache.log4j.varia.LevelRangeFilter">
+ <param name="levelMin" value="DEBUG"/>
+ <param name="levelMax" value="INFO"/>
+ </filter>
+ </appender>
+
+ <!--
+ Logs all System.err messages to console.
+ -->
+ <appender name="CONSOLE_ERR" class="org.apache.log4j.ConsoleAppender">
+ <!-- Log to STDERR. -->
+ <param name="Target" value="System.err"/>
+
+ <!-- Log from WARN and higher. -->
+ <param name="Threshold" value="WARN"/>
+
+ <!-- The default pattern: Date Priority [Category] Message\n -->
+ <layout class="org.apache.log4j.PatternLayout">
+ <param name="ConversionPattern" value="[%d{ISO8601}][%-5p][%t][%cc{1}] %m%n"/>
+ </layout>
+ </appender>
+
+ <!--
+ Logs all output to specified file.
+ -->
+ <appender name="FILE" class="org.apache.log4j.RollingFileAppender">
+ <param name="Threshold" value="DEBUG"/>
+ <param name="File" value="${IGNITE_HOME}/work/log/ignite.log"/>
+ <param name="Append" value="true"/>
+ <param name="MaxFileSize" value="10MB"/>
+ <param name="MaxBackupIndex" value="10"/>
+ <layout class="org.apache.log4j.PatternLayout">
+ <param name="ConversionPattern" value="[%d{ISO8601}][%-5p][%t][%cc{1}] %m%n"/>
+ </layout>
+ </appender>
+
+ <!--
+ Uncomment to enable Ignite query execution debugging.
+ -->
+ <!--
+ <category name="org.apache.ignite.internal.processors.query">
+ <level value="DEBUG"/>
+ </category>
+ -->
+
+ <!--
+ Uncomment to enable Exchange, Rebalance and Partitions workflow debugging.
+ -->
+ <!--
+ <category name="org.apache.ignite.internal.processors.cache.distributed.dht.preloader">
+ <level value="DEBUG"/>
+ </category>
+
+ <category name="org.apache.ignite.internal.processors.cache.distributed.dht.topology">
+ <level value="DEBUG"/>
+ </category>
+ -->
+
+ <!--
+ Uncomment to enable debugging of partition counters.
+ -->
+ <!--
+ <category name="org.apache.ignite.internal.processors.cache.PartitionUpdateCounterDebugWrapper">
+ <level value="DEBUG"/>
+ </category>
+ -->
+
+ <!--
+ Uncomment to enable transactions debugging.
+ -->
+ <!--
+ <category name="org.apache.ignite.cache.msg.tx.prepare">
+ <level value="DEBUG"/>
+ </category>
+
+ <category name="org.apache.ignite.cache.msg.tx.finish">
+ <level value="DEBUG"/>
+ </category>
+
+ <category name="org.apache.ignite.cache.msg.tx.recovery">
+ <level value="DEBUG"/>
+ </category>
+ -->
+
+ <!--
+ Uncomment to enable debugging of partition eviction.
+ -->
+ <!--
+ <category name="org.apache.ignite.internal.processors.cache.distributed.dht.topology.PartitionsEvictManager">
+ <level value="DEBUG"/>
+ </category>
+ -->
+
+ <!-- Disable all open source debugging. -->
+<!-- <category name="org.apache.calcite">-->
+<!-- <level value="DEBUG"/>-->
+<!-- </category>-->
+
+ <category name="org">
+ <level value="INFO"/>
+ </category>
+
+ <category name="org.eclipse.jetty">
+ <level value="INFO"/>
+ </category>
+
+ <!-- Default settings. -->
+ <root>
+ <!-- Print at info by default. -->
+ <level value="INFO"/>
+
+ <!-- Append to file and console. -->
+ <appender-ref ref="FILE"/>
+ <appender-ref ref="CONSOLE"/>
+ <appender-ref ref="CONSOLE_ERR"/>
+ </root>
+</log4j:configuration>
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/ScriptRunnerTestsEnvironment.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/ScriptRunnerTestsEnvironment.java
new file mode 100644
index 0000000..fab315e
--- /dev/null
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/ScriptRunnerTestsEnvironment.java
@@ -0,0 +1,58 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.ignite.internal.processors.query.calcite.logical;
+
+import java.lang.annotation.ElementType;
+import java.lang.annotation.Retention;
+import java.lang.annotation.RetentionPolicy;
+import java.lang.annotation.Target;
+
+/**
+ *
+ */
+@Retention(RetentionPolicy.RUNTIME)
+@Target(ElementType.TYPE)
+public @interface ScriptRunnerTestsEnvironment {
+ /**
+ * @return Test scripts root directory.
+ */
+ String scriptsRoot();
+
+ /**
+ * The Regular expression may be used by debug / development runs of the test scripts
+ * to specify only necessary tests to run.
+ *
+ * @return Regular expression to filter test path to execute.
+ */
+ String regex() default "";
+
+ /**
+ * @return Ignite nodes count.
+ */
+ int nodes() default 2;
+
+ /**
+ * @return {@code true} if the cluster must be restarted for each test group (directory).
+ */
+ boolean restart() default false;
+
+ /**
+ * @return default timeout for a test script.
+ */
+ long timeout() default 30_000;
+}
diff --git a/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/ScriptTestRunner.java b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/ScriptTestRunner.java
new file mode 100644
index 0000000..3cf36da
--- /dev/null
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/ScriptTestRunner.java
@@ -0,0 +1,262 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.ignite.internal.processors.query.calcite.logical;
+
+import java.nio.file.FileSystem;
+import java.nio.file.FileSystems;
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.util.List;
+import java.util.concurrent.TimeoutException;
+import java.util.concurrent.atomic.AtomicReference;
+import java.util.regex.Pattern;
+
+import org.apache.ignite.Ignite;
+import org.apache.ignite.IgniteLogger;
+import org.apache.ignite.Ignition;
+import org.apache.ignite.configuration.IgniteConfiguration;
+import org.apache.ignite.internal.IgniteEx;
+import org.apache.ignite.internal.IgniteKernal;
+import org.apache.ignite.internal.IgnitionEx;
+import org.apache.ignite.internal.processors.query.QueryEngine;
+import org.apache.ignite.internal.processors.query.calcite.util.Commons;
+import org.apache.ignite.internal.util.typedef.F;
+import org.apache.ignite.internal.util.typedef.internal.U;
+import org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi;
+import org.apache.ignite.spi.discovery.tcp.ipfinder.vm.TcpDiscoveryVmIpFinder;
+import org.apache.ignite.testframework.junits.logger.GridTestLog4jLogger;
+import org.apache.ignite.thread.IgniteThread;
+import org.junit.runner.Description;
+import org.junit.runner.Runner;
+import org.junit.runner.notification.Failure;
+import org.junit.runner.notification.RunNotifier;
+
+/**
+ *
+ */
+public class ScriptTestRunner extends Runner {
+ /** Filesystem. */
+ private static final FileSystem FS = FileSystems.getDefault();
+
+ /** Shared finder. */
+ private static final TcpDiscoveryVmIpFinder sharedFinder = new TcpDiscoveryVmIpFinder().setShared(true);
+
+ /** */
+ private static IgniteLogger log;
+
+ static {
+ try {
+ log = new GridTestLog4jLogger("src/test/config/log4j-test.xml");
+ }
+ catch (Exception e) {
+ e.printStackTrace();
+
+ log = null;
+
+ assert false : "Cannot init logger";
+ }
+ }
+
+ /** */
+ private final Class<?> testCls;
+
+ /** Scripts root directory. */
+ private final Path scriptsRoot;
+
+ /** Regex to filter test path to run only specified tests. */
+ private final Pattern testRegex;
+
+ /** Nodes count. */
+ private final int nodes;
+
+ /** Restart cluster for each test group. */
+ private final boolean restartCluster;
+
+ /** Test script timeout. */
+ private final long timeout;
+
+ /** */
+ public ScriptTestRunner(Class<?> testCls) {
+ this.testCls = testCls;
+ ScriptRunnerTestsEnvironment env = testCls.getAnnotation(ScriptRunnerTestsEnvironment.class);
+
+ assert !F.isEmpty(env.scriptsRoot());
+
+ nodes = env.nodes();
+ scriptsRoot = FS.getPath(env.scriptsRoot());
+ testRegex = F.isEmpty(env.regex()) ? null : Pattern.compile(env.regex());
+ restartCluster = env.restart();
+ timeout = env.timeout();
+ }
+
+ /** {@inheritDoc} */
+ @Override public Description getDescription() {
+ return Description.createSuiteDescription(testCls.getName(), "scripts");
+ }
+
+ /** {@inheritDoc} */
+ @Override public void run(final RunNotifier notifier) {
+ try {
+ Files.walk(scriptsRoot).sorted().forEach((p) -> {
+ if (p.equals(scriptsRoot))
+ return;
+
+ if (Files.isDirectory(p)) {
+ if (!F.isEmpty(Ignition.allGrids()) && restartCluster) {
+ log.info(">>> Restart cluster");
+
+ Ignition.stopAll(false);
+ }
+
+ return;
+ }
+
+ runTest(p, notifier);
+ });
+ }
+ catch (Exception e) {
+ throw new RuntimeException(e);
+ }
+ finally {
+ Ignition.stopAll(false);
+ }
+ }
+
+ /** */
+ private void runTest(Path test, RunNotifier notifier) {
+ String dirName = test.subpath(scriptsRoot.getNameCount(), test.getNameCount() - 1).toString();
+ String fileName = test.getFileName().toString();
+
+ if (
+ (!fileName.endsWith(".test") && !fileName.endsWith(".test_slow") && testRegex == null)
+ || (testRegex != null && !testRegex.matcher(test.toString()).find())
+ )
+ return;
+
+ beforeTest();
+
+ Description desc = Description.createTestDescription(dirName, fileName);
+
+ notifier.fireTestStarted(desc);
+
+ try {
+ Ignite ign = F.first(Ignition.allGrids());
+
+ QueryEngine engine = Commons.lookupComponent(
+ ((IgniteEx)ign).context(),
+ QueryEngine.class
+ );
+
+ SqlScriptRunner scriptTestRunner = new SqlScriptRunner(test, engine, log);
+
+ log.info(">>> Start: " + dirName + "/" + fileName);
+
+ runScript(scriptTestRunner);
+ }
+ catch (Throwable e) {
+ notifier.fireTestFailure(new Failure(desc, e));
+ }
+ finally {
+ log.info(">>> Finish: " + dirName + "/" + fileName);
+ notifier.fireTestFinished(desc);
+ }
+ }
+
+ /** */
+ private void beforeTest() {
+ if (F.isEmpty(Ignition.allGrids()))
+ startCluster();
+ else {
+ Ignite ign = F.first(Ignition.allGrids());
+
+ for (String cacheName : ign.cacheNames())
+ ign.destroyCache(cacheName);
+ }
+ }
+
+ /** */
+ private void startCluster() {
+ for (int i = 0; i < nodes; ++i) {
+ Ignition.start(
+ new IgniteConfiguration()
+ .setIgniteInstanceName("srv" + i)
+ .setDiscoverySpi(
+ new TcpDiscoverySpi()
+ .setIpFinder(sharedFinder)
+ )
+ .setGridLogger(log)
+ );
+ }
+ }
+
+ /** */
+ private void runScript(SqlScriptRunner scriptRunner) throws Throwable {
+ final AtomicReference<Throwable> ex = new AtomicReference<>();
+
+ Thread runner = new IgniteThread("srv0", "test-runner", new Runnable() {
+ @Override public void run() {
+ try {
+ scriptRunner.run();
+ }
+ catch (Throwable e) {
+ ex.set(e);
+ }
+ }
+ });
+
+ runner.start();
+
+ runner.join(timeout);
+
+ if (runner.isAlive()) {
+ U.error(log,
+ "Test has been timed out and will be interrupted");
+
+ List<Ignite> nodes = IgnitionEx.allGridsx();
+
+ for (Ignite node : nodes)
+ ((IgniteKernal)node).dumpDebugInfo();
+
+ // We dump threads to stdout, because we can loose logs in case
+ // the build is cancelled on TeamCity.
+ U.dumpThreads(null);
+
+ U.dumpThreads(log);
+
+ // Try to interrupt runner several times for case when InterruptedException is handled invalid.
+ for (int i = 0; i < 100 && runner.isAlive(); ++i) {
+ U.interrupt(runner);
+
+ U.sleep(10);
+ }
+
+ U.join(runner, log);
+
+ // Restart cluster
+ Ignition.stopAll(true);
+ startCluster();
+
+ throw new TimeoutException("Test has been timed out");
+ }
+
+ Throwable t = ex.get();
+
+ if (t != null)
+ throw t;
+ }
+}
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
new file mode 100644
index 0000000..12515cb
--- /dev/null
+++ b/modules/calcite/src/test/java/org/apache/ignite/internal/processors/query/calcite/logical/SqlScriptRunner.java
@@ -0,0 +1,596 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.ignite.internal.processors.query.calcite.logical;
+
+import java.io.BufferedReader;
+import java.io.IOException;
+import java.math.BigDecimal;
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.HashMap;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+import java.util.NoSuchElementException;
+import java.util.Set;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+
+import com.google.common.collect.ImmutableSet;
+import org.apache.ignite.IgniteException;
+import org.apache.ignite.IgniteLogger;
+import org.apache.ignite.cache.query.FieldsQueryCursor;
+import org.apache.ignite.cache.query.QueryCursor;
+import org.apache.ignite.internal.processors.query.IgniteSQLException;
+import org.apache.ignite.internal.processors.query.QueryEngine;
+import org.apache.ignite.internal.util.tostring.GridToStringInclude;
+import org.apache.ignite.internal.util.typedef.F;
+import org.apache.ignite.internal.util.typedef.internal.S;
+import org.jetbrains.annotations.NotNull;
+
+/**
+ *
+ */
+public class SqlScriptRunner {
+ /** Hashing label pattern. */
+ private static final Pattern HASHING_PTRN = Pattern.compile("([0-9]+) values hashing to ([0-9a-fA-F]+)");
+
+ /** Ignored statements. */
+ private static final Set<String> ignoredStmts = ImmutableSet.of("PRAGMA");
+
+ /** NULL label. */
+ private static final String NULL = "NULL";
+
+ /** Default schema. */
+ private static final String schemaPublic = "PUBLIC";
+
+ /** Test script path. */
+ private final Path test;
+
+ /** Query engine. */
+ private final QueryEngine engine;
+
+ /** Loop variables. */
+ private final Map<String, Integer> loopVars = new HashMap<>();
+
+ /** Logger. */
+ private final IgniteLogger log;
+
+ /** Script. */
+ private Script script;
+
+ /** */
+ public SqlScriptRunner(Path test, QueryEngine engine, IgniteLogger log) {
+ this.test = test;
+ this.engine = engine;
+ this.log = log;
+ }
+
+ /** */
+ public void run() throws Exception {
+ try (Script s = new Script(test)) {
+ script = s;
+
+ for (Command cmd : script) {
+ try {
+ cmd.execute();
+ }
+ finally {
+ loopVars.clear();
+ }
+ }
+ }
+ }
+
+ /** */
+ private List<List<?>> sql(String sql) {
+ if (!loopVars.isEmpty()) {
+ for (Map.Entry<String, Integer> loopVar : loopVars.entrySet())
+ sql = sql.replaceAll("\\$\\{" + loopVar.getKey() + "\\}", loopVar.getValue().toString());
+ }
+
+ log.info("Execute: " + sql);
+
+ List<FieldsQueryCursor<List<?>>> curs = engine.query(null, schemaPublic, sql);
+
+ assert curs.size() == 1 : "Unexpected results [cursorsCount=" + curs.size() + ']';
+
+ try (QueryCursor<List<?>> cur = curs.get(0)) {
+ return cur.getAll();
+ }
+ }
+
+ /** */
+ private class Script implements Iterable<Command>, AutoCloseable {
+ /** Reader. */
+ private final String fileName;
+
+ /** Reader. */
+ private final BufferedReader r;
+
+ /** Line number. */
+ private int lineNum;
+
+ /** */
+ Script(Path test) throws IOException {
+ fileName = test.getFileName().toString();
+
+ r = Files.newBufferedReader(test);
+ }
+
+ /** */
+ String nextLine() throws IOException {
+ if (r.ready()) {
+ String s = r.readLine();
+
+ lineNum++;
+
+ return s.trim();
+ }
+
+ return null;
+ }
+
+ /** */
+ boolean ready() throws IOException {
+ return r.ready();
+ }
+
+ /** */
+ String positionDescription() {
+ return fileName + ':' + lineNum;
+ }
+
+ /** {@inheritDoc} */
+ @Override public void close() throws Exception {
+ r.close();
+ }
+
+ /** */
+ private Command nextCommand() {
+ try {
+ while (script.ready()) {
+ String s = script.nextLine();
+
+ if (F.isEmpty(s) || s.startsWith("#"))
+ continue;
+
+ String[] tokens = s.split("\\s+");
+
+ assert !F.isEmpty(tokens) : "Invalid command line. "
+ + script.positionDescription() + ". [cmd=" + s + ']';
+
+ Command cmd = null;
+
+ switch (tokens[0]) {
+ case "statement":
+ cmd = new Statement(tokens);
+
+ break;
+
+ case "query":
+ cmd = new Query(tokens);
+
+ break;
+
+ case "loop":
+ cmd = new Loop(tokens);
+
+ break;
+
+ case "endloop":
+ cmd = new EndLoop();
+
+ break;
+
+ case "mode":
+ // TODO: output_hash. output_result, debug, skip, unskip
+
+ break;
+
+ default:
+ throw new IgniteException("Unexpected command. "
+ + script.positionDescription() + ". [cmd=" + s + ']');
+ }
+
+ if (cmd != null)
+ return cmd;
+ }
+
+ return null;
+ }
+ catch (IOException e) {
+ throw new RuntimeException("Cannot read next command", e);
+ }
+ }
+
+ /** */
+ @NotNull @Override public Iterator<Command> iterator() {
+ final Command cmd0 = nextCommand();
+ return new Iterator<Command>() {
+ /** */
+ private Command cmd = cmd0;
+
+ @Override public boolean hasNext() {
+ return cmd != null;
+ }
+
+ @Override public Command next() {
+ if (cmd == null)
+ throw new NoSuchElementException();
+
+ Command ret = cmd;
+
+ cmd = nextCommand();
+
+ return ret;
+ }
+ };
+ }
+ }
+
+ /** */
+ private abstract class Command {
+ /** */
+ protected final String posDesc;
+
+ /** */
+ Command() {
+ posDesc = script.positionDescription();
+ }
+
+ /** */
+ abstract void execute();
+ }
+
+ /** */
+ private class Loop extends Command {
+ /** */
+ List<Command> cmds = new ArrayList<>();
+
+ /** */
+ int begin;
+
+ /** */
+ int end;
+
+ /** */
+ String var;
+
+ /** */
+ Loop(String[] cmdTokens) throws IOException {
+ try {
+ var = cmdTokens[1];
+ begin = Integer.parseInt(cmdTokens[2]);
+ end = Integer.parseInt(cmdTokens[3]);
+ }
+ catch (Exception e) {
+ throw new IgniteException("Unexpected loop syntax. "
+ + script.positionDescription() + ". [cmd=" + cmdTokens + ']');
+ }
+
+ while (script.ready()) {
+ Command cmd = script.nextCommand();
+
+ if (cmd instanceof EndLoop)
+ break;
+
+ cmds.add(cmd);
+ }
+ }
+
+ /** */
+ @Override void execute() {
+ for (int i = begin; i < end; ++i) {
+ loopVars.put(var, i);
+
+ for (Command c : cmds)
+ c.execute();
+ }
+ }
+ }
+
+ /** */
+ private class EndLoop extends Command {
+ /** {@inheritDoc} */
+ @Override void execute() {
+ // No-op.
+ }
+ }
+
+
+ /** */
+ private class Statement extends Command {
+ /** */
+ @GridToStringInclude
+ List<String> queries;
+
+ /** */
+ @GridToStringInclude
+ ExpectedStatementStatus expected;
+
+ /** */
+ Statement(String[] cmd) throws IOException {
+ switch (cmd[1]) {
+ case "ok":
+ expected = ExpectedStatementStatus.OK;
+
+ break;
+
+ case "error":
+ expected = ExpectedStatementStatus.ERROR;
+
+ break;
+
+ default:
+ throw new IgniteException("Statement argument should be 'ok' or 'error'. "
+ + script.positionDescription() + "[cmd=" + Arrays.toString(cmd) + ']');
+ }
+
+ queries = new ArrayList<>();
+
+ while (script.ready()) {
+ String s = script.nextLine();
+
+ if (F.isEmpty(s))
+ break;
+
+ queries.add(s);
+ }
+ }
+
+ /** {@inheritDoc} */
+ @Override void execute() {
+ for (String qry : queries) {
+
+ String[] toks = qry.split("\\s+");
+
+ if (ignoredStmts.contains(toks[0])) {
+ log.info("Ignore: " + toString());
+
+ continue;
+ }
+
+ try {
+ sql(qry);
+
+ if (expected != ExpectedStatementStatus.OK)
+ throw new IgniteException("Error expected at: " + posDesc + ". Statement: " + this);
+ }
+ catch (Throwable e) {
+ if (expected != ExpectedStatementStatus.ERROR)
+ throw new IgniteException("Error at: " + posDesc + ". Statement: " + this, e);
+ }
+ }
+ }
+
+ /** {@inheritDoc} */
+ @Override public String toString() {
+ return S.toString(Statement.class, this);
+ }
+ }
+
+ /** */
+ private class Query extends Command {
+ @GridToStringInclude
+ List<ColumnType> resTypes = new ArrayList<>();
+
+ /** */
+ @GridToStringInclude
+ StringBuilder sql = new StringBuilder();
+
+ /** */
+ @GridToStringInclude
+ List<List<String>> expectedRes;
+
+ /** */
+ String expectedHash;
+
+ /** */
+ int expectedRows;
+
+ /** */
+ Query(String[] cmd) throws IOException {
+ String resTypesChars = cmd[1];
+
+ for (int i = 0; i < resTypesChars.length(); i++) {
+ switch (resTypesChars.charAt(i)) {
+ case 'I':
+ resTypes.add(ColumnType.I);
+
+ break;
+
+ case 'R':
+ resTypes.add(ColumnType.R);
+
+ break;
+
+ case 'T':
+ resTypes.add(ColumnType.T);
+
+ break;
+
+ default:
+ throw new IgniteException("Unknown type character '" + resTypesChars.charAt(i) + "'. "
+ + script.positionDescription() + "[cmd=" + Arrays.toString(cmd) + ']');
+ }
+ }
+
+ if (F.isEmpty(resTypes)) {
+ throw new IgniteException("Missing type string. "
+ + script.positionDescription() + "[cmd=" + Arrays.toString(cmd) + ']');
+ }
+
+ // Read SQL query
+ while (script.ready()) {
+ String s = script.nextLine();
+
+ if (s.equals("----"))
+ break;
+
+ if (sql.length() > 0)
+ sql.append(" ");
+
+ sql.append(s);
+ }
+
+ // Read expected results
+ String s = script.nextLine();
+ Matcher m = HASHING_PTRN.matcher(s);
+
+ if (m.matches()) {
+ // Expected results are hashing
+ expectedRows = Integer.parseInt(m.group(1));
+ expectedHash = m.group(2);
+ }
+ else {
+ // Read expected results tuples.
+ expectedRes = new ArrayList<>();
+
+ boolean singleValOnLine = false;
+
+ List<String> row = new ArrayList<>();
+
+ while (!F.isEmpty(s)) {
+ String[] vals = s.split("\\t");
+
+ if (!singleValOnLine && vals.length == 1 && vals.length != resTypes.size())
+ singleValOnLine = true;
+
+ if (vals.length != resTypes.size() && !singleValOnLine) {
+ throw new IgniteException("Invalid columns count at the result. "
+ + script.positionDescription() + " [row=\"" + s + "\", types=" + resTypes + ']');
+ }
+
+ try {
+ if (singleValOnLine) {
+ row.add(NULL.equals(vals[0]) ? null : vals[0]);
+
+ if (row.size() == resTypes.size()) {
+ expectedRes.add(row);
+
+ row = new ArrayList<>();
+ }
+ }
+ else {
+ for (String val : vals)
+ row.add(NULL.equals(val) ? null : val);
+
+ expectedRes.add(row);
+ row = new ArrayList<>();
+ }
+ }
+ catch (Exception e) {
+ throw new IgniteException("Cannot parse expected results. "
+ + script.positionDescription() + "[row=\"" + s + "\", types=" + resTypes + ']', e);
+ }
+
+ s = script.nextLine();
+ }
+ }
+ }
+
+ /** {@inheritDoc} */
+ @Override void execute() {
+ try {
+ List<List<?>> res = sql(sql.toString());
+
+ checkResult(res);
+ }
+ catch (IgniteSQLException e) {
+ throw new IgniteException("Error at: " + posDesc + ". sql: " + sql, e);
+ }
+ }
+
+ /** */
+ void checkResult(List<List<?>> res) {
+ if (expectedHash != null)
+ checkResultsHashed(res);
+ else
+ checkResultTuples(res);
+ }
+
+ /** */
+ private void checkResultTuples(List<List<?>> res) {
+ if (expectedRes.size() != res.size()) {
+ throw new AssertionError("Invalid results rows count at " + posDesc +
+ ". [expected=" + expectedRes + ", actual=" + res + ']');
+ }
+
+ for (int i = 0; i < expectedRes.size(); ++i) {
+ List<String> expectedRow = expectedRes.get(i);
+ List<?> row = res.get(i);
+
+ if (row.size() != expectedRow.size()) {
+ throw new AssertionError("Invalid columns count at " + posDesc +
+ ". [expected=" + expectedRes + ", actual=" + res+ ']');
+ }
+
+ for (int j = 0; j < expectedRow.size(); ++j) {
+ checkEquals("Not expected result at " + posDesc +
+ ". [row=" + i + ", col=" + j +
+ ", expected=" + expectedRow.get(j) + ", actual=" + row.get(j) + ']', expectedRow.get(j), row.get(j));
+ }
+ }
+ }
+
+ /** */
+ private void checkEquals(String msg, String expectedStr, Object actual) {
+ if (actual != null ^ expectedStr != null)
+ throw new AssertionError(msg);
+
+ if (actual instanceof Number) {
+ BigDecimal actDec = new BigDecimal(String.valueOf(actual));
+ BigDecimal expDec = new BigDecimal(expectedStr);
+
+ if (actDec.compareTo(expDec) != 0)
+ throw new AssertionError(msg);
+ }
+ else {
+ if (!String.valueOf(expectedStr).equals(String.valueOf(actual)))
+ throw new AssertionError(msg);
+ }
+ }
+
+ /** */
+ private void checkResultsHashed(List<List<?>> res) {
+ // TODO:
+ throw new UnsupportedOperationException("Hashed results compare not supported");
+ }
+
+ /** {@inheritDoc} */
+ @Override public String toString() {
+ return S.toString(Query.class, this);
+ }
+ }
+
+ /** */
+ private enum ExpectedStatementStatus {
+ OK,
+ ERROR
+ }
+
+ /** */
+ private enum ColumnType {
+ I,
+ T,
+ R
+ }
+}
diff --git a/modules/calcite/src/test/java/org/apache/ignite/testsuites/ScriptTestSuite.java b/modules/calcite/src/test/java/org/apache/ignite/testsuites/ScriptTestSuite.java
new file mode 100644
index 0000000..a5085f4
--- /dev/null
+++ b/modules/calcite/src/test/java/org/apache/ignite/testsuites/ScriptTestSuite.java
@@ -0,0 +1,39 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.ignite.testsuites;
+
+import org.apache.ignite.internal.processors.query.calcite.logical.ScriptRunnerTestsEnvironment;
+import org.apache.ignite.internal.processors.query.calcite.logical.ScriptTestRunner;
+import org.junit.runner.RunWith;
+
+/**
+ * Test suite to run SQL test scripts.
+ *
+ * By default only "*.test" and "*.test_slow" scripts are run.
+ * Other files are ignored.
+ *
+ * Use {@link ScriptRunnerTestsEnvironment#regex()} property to specify regular expression for filter
+ * script path to debug run. In this case the file suffix will be ignored.
+ * e.g. regex = "test_aggr_string.test"
+ *
+ * Use other properties of the {@link ScriptRunnerTestsEnvironment} to setup cluster and test environment.
+ */
+@RunWith(ScriptTestRunner.class)
+@ScriptRunnerTestsEnvironment(scriptsRoot = "src/test/sql")
+public class ScriptTestSuite {
+}
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_aggr_string.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_aggr_string.test
new file mode 100644
index 0000000..5c2e5d8
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_aggr_string.test
@@ -0,0 +1,35 @@
+# name: test/sql/aggregate/aggregates/test_aggr_string.test
+# description: Test aggregations on strings
+# group: [aggregates]
+
+statement ok
+CREATE TABLE test (a INTEGER, s VARCHAR);
+
+statement ok
+INSERT INTO test VALUES (11, 'hello'), (12, 'world'), (11, NULL)
+
+# scalar aggregation on string
+query II
+SELECT COUNT(*), COUNT(s) FROM test;
+----
+3 2
+
+# grouped aggregation on string
+query III
+SELECT a, COUNT(*), COUNT(s) FROM test GROUP BY a ORDER BY a;
+----
+11 2 1
+12 1 1
+
+# group by the strings
+query TR
+SELECT s, SUM(a) FROM test GROUP BY s ORDER BY s;
+----
+hello 11.000000
+world 12.000000
+NULL 11.000000
+
+# distinct aggregations ons tring
+statement ok
+INSERT INTO test VALUES (11, 'hello'), (12, 'world')
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_aggr_string.test_ignore b/modules/calcite/src/test/sql/aggregate/aggregates/test_aggr_string.test_ignore
new file mode 100644
index 0000000..3f7ef2b
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_aggr_string.test_ignore
@@ -0,0 +1,73 @@
+# name: test/sql/aggregate/aggregates/test_aggr_string.test
+# description: Test aggregations on strings
+# group: [aggregates]
+# Ignore: https://issues.apache.org/jira/browse/IGNITE-14544
+# Ignore: https://issues.apache.org/jira/browse/IGNITE-14545
+# Ignore: https://issues.apache.org/jira/browse/IGNITE-14546
+
+query TTTTI
+SELECT NULL as a, NULL as b, NULL as c, NULL as d, 1 as id UNION SELECT 'Кирилл' as a, 'Müller' as b, '我是谁' as c, 'ASCII' as d, 2 as id ORDER BY 1
+----
+NULL NULL NULL NULL 1
+Кирилл Müller 我是谁 ASCII 2
+
+statement ok
+CREATE TABLE test (a INTEGER, s VARCHAR);
+
+statement ok
+INSERT INTO test VALUES (11, 'hello'), (12, 'world'), (11, NULL)
+
+# scalar aggregation on string
+query II
+SELECT COUNT(*), COUNT(s) FROM test;
+----
+3 2
+
+# grouped aggregation on string
+query III
+SELECT a, COUNT(*), COUNT(s) FROM test GROUP BY a ORDER BY a;
+----
+11 2 1
+12 1 1
+
+# group by the strings
+query TR
+SELECT s, SUM(a) FROM test GROUP BY s ORDER BY s;
+----
+hello 11.000000
+world 12.000000
+NULL 11.000000
+
+# distinct aggregations ons tring
+statement ok
+INSERT INTO test VALUES (11, 'hello'), (12, 'world')
+
+# scalar distinct
+query III
+SELECT COUNT(*), COUNT(s), COUNT(DISTINCT s) FROM test;
+----
+5 4 2
+
+# grouped distinct
+query IIII
+SELECT a, COUNT(*), COUNT(s), COUNT(DISTINCT s) FROM test GROUP BY a ORDER BY a;
+----
+11 3 2 1
+12 2 2 1
+
+# now with WHERE clause
+query IIII
+SELECT a, COUNT(*), COUNT(s), COUNT(DISTINCT s) FROM test WHERE s IS NOT NULL GROUP BY a ORDER BY a;
+----
+11 2 2 1
+12 2 2 1
+
+# string min/max with long strings
+statement ok
+CREATE TABLE test_strings(s VARCHAR);
+INSERT INTO test_strings VALUES ('aaaaaaaahello'), ('bbbbbbbbbbbbbbbbbbbbhello'), ('ccccccccccccccchello'), ('aaaaaaaaaaaaaaaaaaaaaaaahello');;
+
+query II
+SELECT MIN(s), MAX(s) FROM test_strings;
+----
+aaaaaaaaaaaaaaaaaaaaaaaahello ccccccccccccccchello
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_aggregate_types.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_aggregate_types.test
new file mode 100644
index 0000000..3dfe6d6
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_aggregate_types.test
@@ -0,0 +1,190 @@
+# name: test/sql/aggregate/aggregates/test_aggregate_types.test
+# description: Test aggregates with many different types
+# group: [aggregates]
+
+statement ok
+CREATE TABLE strings(s STRING, g INTEGER)
+
+statement ok
+INSERT INTO strings VALUES ('hello', 0), ('world', 1), (NULL, 0), ('r', 1)
+
+# simple aggregates only
+query IITT
+SELECT COUNT(*), COUNT(s), MIN(s), MAX(s) FROM strings
+----
+4
+3
+hello
+world
+
+query IITT
+SELECT COUNT(*), COUNT(s), MIN(s), MAX(s) FROM strings WHERE s IS NULL
+----
+1
+0
+NULL
+NULL
+
+# add string agg
+query TTTT
+SELECT STRING_AGG(s, ' '), STRING_AGG(s, ''), STRING_AGG('', ''), STRING_AGG('hello', ' ') FROM strings
+----
+hello world r
+helloworldr
+(empty)
+hello hello hello hello
+
+# more complex agg (groups)
+query IIITTT
+SELECT g, COUNT(*), COUNT(s), MIN(s), MAX(s), STRING_AGG(s, ' ') FROM strings GROUP BY g ORDER BY g
+----
+0
+2
+1
+hello
+hello
+hello
+1
+2
+2
+r
+world
+world r
+
+# empty group
+query IIITTT
+SELECT g, COUNT(*), COUNT(s), MIN(s), MAX(s), STRING_AGG(s, ' ') FROM strings WHERE s IS NULL OR s <> 'hello' GROUP BY g ORDER BY g
+----
+0
+1
+0
+NULL
+NULL
+NULL
+1
+2
+2
+r
+world
+world r
+
+# unsupported aggregates
+statement error
+SELECT SUM(s) FROM strings GROUP BY g ORDER BY g
+
+statement error
+SELECT AVG(s) FROM strings GROUP BY g ORDER BY g
+
+# booleans
+statement ok
+CREATE TABLE booleans(b BOOLEAN, g INTEGER)
+
+statement ok
+INSERT INTO booleans VALUES (false, 0), (true, 1), (NULL, 0), (false, 1)
+
+query IITT
+SELECT COUNT(*), COUNT(b), MIN(b), MAX(b) FROM booleans
+----
+4
+3
+0
+1
+
+query IITT
+SELECT COUNT(*), COUNT(b), MIN(b), MAX(b) FROM booleans WHERE b IS NULL
+----
+1
+0
+NULL
+NULL
+
+query IIITT
+SELECT g, COUNT(*), COUNT(b), MIN(b), MAX(b) FROM booleans GROUP BY g ORDER BY g
+----
+0
+2
+1
+0
+0
+1
+2
+2
+0
+1
+
+query IIITT
+SELECT g, COUNT(*), COUNT(b), MIN(b), MAX(b) FROM booleans WHERE b IS NULL OR b=true GROUP BY g ORDER BY g
+----
+0
+1
+0
+NULL
+NULL
+1
+1
+1
+1
+1
+
+statement error
+SELECT SUM(b) FROM booleans GROUP BY g ORDER BY g
+
+statement error
+SELECT AVG(b) FROM booleans GROUP BY g ORDER BY g
+
+statement ok
+CREATE TABLE integers(i INTEGER, g INTEGER)
+
+statement ok
+INSERT INTO integers VALUES (12, 0), (22, 1), (NULL, 0), (14, 1)
+
+query IIIIR
+SELECT COUNT(*), COUNT(i), MIN(i), MAX(i), SUM(i) FROM integers
+----
+4
+3
+12
+22
+48.000000
+
+query IIIIR
+SELECT COUNT(*), COUNT(i), MIN(i), MAX(i), SUM(i) FROM INTEGERS WHERE i IS NULL
+----
+1
+0
+NULL
+NULL
+NULL
+
+query IIIIIR
+SELECT g, COUNT(*), COUNT(i), MIN(i), MAX(i), SUM(i) FROM integers GROUP BY g ORDER BY g
+----
+0
+2
+1
+12
+12
+12.000000
+1
+2
+2
+14
+22
+36.000000
+
+query IIIIIR
+SELECT g, COUNT(*), COUNT(i), MIN(i), MAX(i), SUM(i) FROM integers WHERE i IS NULL OR i > 15 GROUP BY g ORDER BY g
+----
+0
+1
+0
+NULL
+NULL
+NULL
+1
+1
+1
+22
+22
+22.000000
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_aggregate_types_scalar.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_aggregate_types_scalar.test
new file mode 100644
index 0000000..205b848
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_aggregate_types_scalar.test
@@ -0,0 +1,108 @@
+# name: test/sql/aggregate/aggregates/test_aggregate_types_scalar.test
+# description: Test scalar aggregates with many different types
+# group: [aggregates]
+
+query IIIIII
+SELECT COUNT(), COUNT(1), COUNT(*), COUNT(NULL), COUNT('hello'), COUNT(DATE '1992-02-02')
+----
+1
+1
+1
+0
+1
+1
+
+statement error
+SELECT COUNT(1, 2)
+
+query RRR
+SELECT SUM(1), SUM(NULL), SUM(33.3)
+----
+1.000000
+NULL
+33.300000
+
+statement error
+SELECT SUM(True)
+
+statement error
+SELECT SUM('hello')
+
+statement error
+SELECT SUM(DATE '1992-02-02')
+
+statement error
+SELECT SUM()
+
+statement error
+SELECT SUM(1, 2)
+
+query IIRTTTT
+SELECT MIN(1), MIN(NULL), MIN(33.3), MIN('hello'), MIN(True), MIN(DATE '1992-02-02'), MIN(TIMESTAMP '2008-01-01 00:00:01')
+----
+1 NULL 33.300000 hello 1 1992-02-02 2008-01-01 00:00:01
+
+statement error
+SELECT MIN()
+
+statement error
+SELECT MIN(1, 2)
+
+query IIRTTTT
+SELECT MAX(1), MAX(NULL), MAX(33.3), MAX('hello'), MAX(True), MAX(DATE '1992-02-02'), MAX(TIMESTAMP '2008-01-01 00:00:01')
+----
+1 NULL 33.300000 hello 1 1992-02-02 2008-01-01 00:00:01
+
+statement error
+SELECT MAX()
+
+statement error
+SELECT MAX(1, 2)
+
+query IIRTTTT
+SELECT FIRST(1), FIRST(NULL), FIRST(33.3), FIRST('hello'), FIRST(True), FIRST(DATE '1992-02-02'), FIRST(TIMESTAMP '2008-01-01 00:00:01')
+----
+1 NULL 33.300000 hello 1 1992-02-02 2008-01-01 00:00:01
+
+statement error
+SELECT FIRST()
+
+statement error
+SELECT FIRST(1, 2)
+
+query RRR
+SELECT AVG(1), AVG(NULL), AVG(33.3)
+----
+1.000000 NULL 33.300000
+
+statement error
+SELECT AVG(True)
+
+statement error
+SELECT AVG('hello')
+
+statement error
+SELECT AVG(DATE '1992-02-02')
+
+statement error
+SELECT AVG()
+
+statement error
+SELECT AVG(1, 2)
+
+query T
+SELECT STRING_AGG('hello')
+----
+hello
+
+query TTTTT
+SELECT STRING_AGG('hello', ' '), STRING_AGG('hello', NULL), STRING_AGG(NULL, ' '), STRING_AGG(NULL, NULL), STRING_AGG('', '')
+----
+hello NULL NULL NULL (empty)
+
+statement error
+SELECT STRING_AGG()
+
+statement error
+SELECT STRING_AGG(1, 2, 3)
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_approx_quantile.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_approx_quantile.test
new file mode 100644
index 0000000..b4030f7
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_approx_quantile.test
@@ -0,0 +1,172 @@
+# name: test/sql/aggregate/aggregates/test_approx_quantile.test
+# description: Test approx quantile operator
+# group: [aggregates]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+create table quantile as select range r, random() from range(10000) union all values (NULL, 0.1), (NULL, 0.5), (NULL, 0.9) order by 2;
+
+query I
+SELECT CASE
+ WHEN ( approx_quantile between (true_quantile - 100) and (true_quantile + 100) )
+ THEN TRUE
+ ELSE FALSE
+ END
+ FROM (SELECT approx_quantile(r, 0.5) as approx_quantile ,quantile(r,0.5) as true_quantile FROM quantile) AS T
+----
+1
+
+
+query I
+SELECT CASE
+ WHEN ( approx_quantile between (true_quantile - 100) and (true_quantile + 100) )
+ THEN TRUE
+ ELSE FALSE
+ END
+ FROM (SELECT approx_quantile(r, 1.0) as approx_quantile ,quantile(r, 1.0) as true_quantile FROM quantile) AS T
+----
+1
+
+query I
+SELECT CASE
+ WHEN ( approx_quantile between (true_quantile - 100) and (true_quantile + 100) )
+ THEN TRUE
+ ELSE FALSE
+ END
+ FROM (SELECT approx_quantile(r, 0.0) as approx_quantile ,quantile(r, 0.0) as true_quantile from quantile) AS T
+----
+1
+
+query II
+SELECT approx_quantile(NULL, 0.5) as approx_quantile ,quantile(NULL, 0.5) as true_quantile
+----
+NULL NULL
+
+query I
+SELECT CASE
+ WHEN ( approx_quantile between (true_quantile - 100) and (true_quantile + 100) )
+ THEN TRUE
+ ELSE FALSE
+ END
+ FROM (SELECT approx_quantile(42, 0.5) as approx_quantile ,quantile(42, 0.5) as true_quantile) AS T
+----
+1
+
+query II
+SELECT approx_quantile(NULL, 0.5) as approx_quantile ,quantile(NULL, 0.5) as true_quantile
+----
+NULL NULL
+
+query I
+SELECT CASE
+ WHEN ( approx_quantile between (true_quantile - 100) and (true_quantile + 100) )
+ THEN TRUE
+ ELSE FALSE
+ END
+ FROM (SELECT approx_quantile(42, 0.5) as approx_quantile ,quantile(42, 0.5) as true_quantile) AS T
+----
+1
+
+query I
+SELECT CASE
+ WHEN ( approx_quantile between (true_quantile - 100) and (true_quantile + 100) )
+ THEN TRUE
+ ELSE FALSE
+ END
+ FROM (SELECT approx_quantile(r, 0.1) as approx_quantile ,quantile(r, 0.1) as true_quantile from quantile) AS T
+----
+1
+
+query I
+SELECT CASE
+ WHEN ( approx_quantile between (true_quantile - 100) and (true_quantile + 100) )
+ THEN TRUE
+ ELSE FALSE
+ END
+ FROM (SELECT approx_quantile(r, 0.9) as approx_quantile ,quantile(r, 0.9) as true_quantile from quantile) AS T
+----
+1
+
+
+
+statement error
+SELECT approx_quantile(r, -0.1) FROM quantile
+
+statement error
+SELECT approx_quantile(r, 1.1) FROM quantile
+
+statement error
+SELECT approx_quantile(r, NULL) FROM quantile
+
+statement error
+SELECT approx_quantile(r::string, 0.5) FROM quantile
+
+statement error
+SELECT approx_quantile(r) FROM quantile
+
+statement error
+SELECT approx_quantile(r, 0.1, 0.2) FROM quantile
+
+
+statement ok
+pragma threads=4
+
+statement ok
+PRAGMA force_parallelism
+
+query I
+SELECT CASE
+ WHEN (approx_quantile between (true_quantile - (sumr * 0.01)) and (true_quantile + (sumr * 0.01)))
+ THEN TRUE
+ ELSE FALSE
+ END
+ FROM (SELECT approx_quantile(r, 0.1) as approx_quantile, quantile(r, 0.1) as true_quantile, SUM(r) as sumr from quantile) AS T
+----
+1
+
+query I
+SELECT CASE
+ WHEN (approx_quantile between (true_quantile - (sumr * 0.01)) and (true_quantile + (sumr * 0.01)))
+ THEN TRUE
+ ELSE FALSE
+ END
+ FROM (SELECT approx_quantile(r, 0.9) as approx_quantile, quantile(r, 0.9) as true_quantile, SUM(r) as sumr from quantile) AS T
+----
+1
+
+query I
+SELECT CASE
+ WHEN (approx_quantile between (true_quantile - (sumr * 0.01)) and (true_quantile + (sumr * 0.01)))
+ THEN TRUE
+ ELSE FALSE
+ END
+ FROM (SELECT approx_quantile(r, 0.5) as approx_quantile, quantile(r, 0.5) as true_quantile, SUM(r) as sumr from quantile) AS T
+----
+1
+
+
+# Test reservoir quantile
+statement ok
+PRAGMA disable_verification;
+
+statement ok
+SELECT reservoir_quantile(r, 0.9) from quantile
+
+statement ok
+SELECT reservoir_quantile(r, 0.9,1000) from quantile
+
+statement ok
+SELECT RESERVOIR_QUANTILE(b, 0.5)
+FROM (SELECT 'a' AS a, 1.0 AS b) y
+GROUP BY a
+
+statement ok
+SELECT APPROX_QUANTILE(b, 0.5)
+FROM (
+ SELECT 'a' AS a, 1.0 AS b
+ UNION ALL SELECT 'a' AS a, 1.0 AS b
+ UNION ALL SELECT 'b' AS a, 1.0 AS b
+ ) y
+GROUP BY a
\ No newline at end of file
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_approximate_distinct_count.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_approximate_distinct_count.test
new file mode 100644
index 0000000..b91d77c
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_approximate_distinct_count.test
@@ -0,0 +1,91 @@
+# name: test/sql/aggregate/aggregates/test_approximate_distinct_count.test
+# description: Test approx_count_distinct operator
+# group: [aggregates]
+
+statement error
+select approx_count_distinct(*)
+
+query I
+select approx_count_distinct(1)
+----
+1
+
+query I
+select approx_count_distinct(NULL)
+----
+0
+
+query I
+select approx_count_distinct(10) from range(100);
+----
+1
+
+query I
+select approx_count_distinct (i) from range (100) tbl(i) WHERE 1 == 0;
+----
+0
+
+statement ok
+CREATE TABLE IF NOT EXISTS dates (t date);
+
+statement ok
+INSERT INTO dates VALUES ('2008-01-01'), (NULL), ('2007-01-01'), ('2008-02-01'), ('2008-01-02'), ('2008-01-01'), ('2008-01-01'), ('2008-01-01')
+
+statement ok
+CREATE TABLE IF NOT EXISTS timestamp (t TIMESTAMP);
+
+statement ok
+INSERT INTO timestamp VALUES ('2008-01-01 00:00:01'), (NULL), ('2007-01-01 00:00:01'), ('2008-02-01 00:00:01'), ('2008-01-02 00:00:01'), ('2008-01-01 10:00:00'), ('2008-01-01 00:10:00'), ('2008-01-01 00:00:10')
+
+statement ok
+CREATE TABLE IF NOT EXISTS names (t string);
+
+statement ok
+INSERT INTO names VALUES ('Pedro'), (NULL), ('Pedro'), ('Pedro'), ('Mark'), ('Mark'),('Mark'),('Hannes-Muehleisen'),('Hannes-Muehleisen')
+
+# test counts on a set of values
+statement ok
+create table t as select range a, mod(range,10) b from range(2000);
+
+query III
+SELECT COUNT( a),approx_count_distinct(a),approx_count_distinct(b) from t
+----
+2000 1990 10
+
+query I
+SELECT approx_count_distinct(a) from t group by a %2;
+----
+1007
+1003
+
+require vector_size 512
+
+query I
+SELECT approx_count_distinct(a) over (partition by a%2) from t where a < 10;
+----
+5
+5
+5
+5
+5
+5
+5
+5
+5
+5
+
+query II
+SELECT COUNT( t),approx_count_distinct(t) from timestamp
+----
+7 7
+
+query II
+SELECT COUNT( t),approx_count_distinct(t) from dates
+----
+7 4
+
+query II
+SELECT COUNT(t),approx_count_distinct(t) from names
+----
+8 3
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_arg_min_max.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_arg_min_max.test
new file mode 100644
index 0000000..2def236
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_arg_min_max.test
@@ -0,0 +1,130 @@
+# name: test/sql/aggregate/aggregates/test_arg_min_max.test
+# description: Test arg_min and arg_max operator
+# group: [aggregates]
+
+#Corner cases
+statement error
+select arg_min()
+
+query I
+select arg_min(NULL,NULL)
+----
+NULL
+
+query I
+select arg_min(1,1)
+----
+1
+
+statement error
+select arg_min(*)
+
+query I
+select arg_min(i,i) from range (100) tbl(i);
+----
+0
+
+query I
+select arg_min(i,i) from range (100) tbl(i) where 1 = 0;
+----
+NULL
+
+statement error
+select arg_max()
+
+query I
+select arg_max(NULL,NULL)
+----
+NULL
+
+query I
+select arg_max(1,1)
+----
+1
+
+statement error
+select arg_max(*)
+
+query I
+select arg_max(i,i) from range (100) tbl(i);
+----
+99
+
+query I
+select arg_max(i,i) from range (100) tbl(i) where 1 = 0;
+----
+NULL
+
+statement ok
+create table args (a integer, b integer)
+
+statement ok
+insert into args values (1,1), (2,2), (8,8), (10,10)
+
+query II
+select arg_min(a,b), arg_max(a,b) from args;
+----
+1.000000 10.000000
+
+query II
+select arg_min(a,b), arg_max(a,b) from args group by a%2;
+----
+1 1
+2 10
+
+statement ok
+CREATE TABLE blobs (b BYTEA, a BIGINT);
+
+statement ok
+INSERT INTO blobs VALUES('\xaa\xff\xaa',5), ('\xAA\xFF\xAA\xAA\xFF\xAA',30), ('\xAA\xFF\xAA\xAA\xFF\xAA\xAA\xFF\xAA',20)
+
+query II
+select arg_min(b,a), arg_max(b,a) from blobs ;
+----
+\xAA\xFF\xAA \xAA\xFF\xAA\xAA\xFF\xAA
+
+query II
+select arg_min(a,b), arg_max(a,b) from blobs ;
+----
+5 20
+
+# Window Function
+require vector_size 512
+
+query I rowsort
+select arg_min(a,b) over ( partition by a%2) from args;
+----
+1
+2
+2
+2
+
+query I rowsort
+select arg_max(a,b) over ( partition by a%2) from args;
+----
+1
+10
+10
+10
+
+statement ok
+create table names (name string, salary integer)
+
+statement ok
+insert into names values ('Pedro',10), ('Hannes',20), ('Mark',15), ('Hubert-Blaine-Wolfeschlegelsteinhausenbergerdorff',30)
+
+query II
+select arg_min(name,salary),arg_max(name,salary) from names;
+----
+Pedro Hubert-Blaine-Wolfeschlegelsteinhausenbergerdorff
+
+query II
+select arg_min(salary,name),arg_max(salary,name) from names;
+----
+20 10
+
+# test min_by max_by alias
+query II
+select min_by(name,salary),max_by(name,salary) from names;
+----
+Pedro Hubert-Blaine-Wolfeschlegelsteinhausenbergerdorff
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_avg.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_avg.test
new file mode 100644
index 0000000..bf32a26
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_avg.test
@@ -0,0 +1,54 @@
+# name: test/sql/aggregate/aggregates/test_avg.test
+# description: Test AVG operator
+# group: [aggregates]
+
+# scalar average
+query RR
+SELECT AVG(3), AVG(NULL)
+----
+3
+NULL
+
+# test average on sequence
+statement ok
+CREATE SEQUENCE seq;
+
+query R
+SELECT AVG(nextval('seq'))
+----
+1
+
+query R
+SELECT AVG(nextval('seq'))
+----
+2
+
+statement ok
+CREATE TABLE integers(i INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (1), (2), (3)
+
+query RRRR
+SELECT AVG(i), AVG(1), AVG(DISTINCT i), AVG(NULL) FROM integers
+----
+2
+1
+2
+NULL
+
+query R
+SELECT AVG(i) FROM integers WHERE i > 100
+----
+NULL
+
+# invalid use of average
+statement error
+SELECT AVG()
+
+statement error
+SELECT AVG(1, 2, 3)
+
+statement error
+SELECT AVG(AVG(1))
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_bit_and.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_bit_and.test
new file mode 100644
index 0000000..dc0808e
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_bit_and.test
@@ -0,0 +1,56 @@
+# name: test/sql/aggregate/aggregates/test_bit_and.test
+# description: Test BIT_AND operator
+# group: [aggregates]
+
+# test on scalar values
+query II
+SELECT BIT_AND(3), BIT_AND(NULL)
+----
+3
+NULL
+
+# test on a sequence
+statement ok
+CREATE SEQUENCE seq;
+
+query I
+SELECT BIT_AND(nextval('seq'))
+----
+1
+
+query I
+SELECT BIT_AND(nextval('seq'))
+----
+2
+
+# test on a set of integers
+statement ok
+CREATE TABLE integers(i INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (3), (7), (15), (31), (3), (15)
+
+query IIII
+SELECT BIT_AND(i), BIT_AND(1), BIT_AND(DISTINCT i), BIT_AND(NULL) FROM integers
+----
+3
+1
+3
+NULL
+
+# test on an empty set
+query I
+SELECT BIT_AND(i) FROM integers WHERE i > 100
+----
+NULL
+
+# test incorrect usage
+statement error
+SELECT BIT_AND()
+
+statement error
+SELECT BIT_AND(1, 2, 3)
+
+statement error
+SELECT BIT_AND(BIT_AND(1))
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_bit_or.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_bit_or.test
new file mode 100644
index 0000000..29910d4
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_bit_or.test
@@ -0,0 +1,56 @@
+# name: test/sql/aggregate/aggregates/test_bit_or.test
+# description: Test BIT_OR operator
+# group: [aggregates]
+
+# test on scalar values
+query II
+SELECT BIT_OR(3), BIT_OR(NULL)
+----
+3
+NULL
+
+# test on a sequence
+statement ok
+CREATE SEQUENCE seq;
+
+query I
+SELECT BIT_OR(nextval('seq'))
+----
+1
+
+query I
+SELECT BIT_OR(nextval('seq'))
+----
+2
+
+# test on a set of integers
+statement ok
+CREATE TABLE integers(i INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (3), (7), (15), (31), (3), (15)
+
+query IIII
+SELECT BIT_OR(i), BIT_OR(1), BIT_OR(DISTINCT i), BIT_OR(NULL) FROM integers
+----
+31
+1
+31
+NULL
+
+# test on an empty set
+query I
+SELECT BIT_OR(i) FROM integers WHERE i > 100
+----
+NULL
+
+# test incorrect usage
+statement error
+SELECT BIT_OR()
+
+statement error
+SELECT BIT_OR(1, 2, 3)
+
+statement error
+SELECT BIT_OR(BIT_AND(1))
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_bit_xor.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_bit_xor.test
new file mode 100644
index 0000000..ad4fe1b
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_bit_xor.test
@@ -0,0 +1,56 @@
+# name: test/sql/aggregate/aggregates/test_bit_xor.test
+# description: Test BIT_XOR operator
+# group: [aggregates]
+
+# test on scalar values
+query II
+SELECT BIT_XOR(3), BIT_XOR(NULL)
+----
+3
+NULL
+
+# test on a sequence
+statement ok
+CREATE SEQUENCE seq;
+
+query I
+SELECT BIT_XOR(nextval('seq'))
+----
+1
+
+query I
+SELECT BIT_XOR(nextval('seq'))
+----
+2
+
+# test on a set of integers
+statement ok
+CREATE TABLE integers(i INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (3), (7), (15), (31), (3), (15)
+
+query IIII
+SELECT BIT_XOR(i), BIT_XOR(1), BIT_XOR(DISTINCT i), BIT_XOR(NULL) FROM integers
+----
+24
+0
+20
+NULL
+
+# test on an empty set
+query I
+SELECT BIT_XOR(i) FROM integers WHERE i > 100
+----
+NULL
+
+# test incorrect usage
+statement error
+SELECT BIT_XOR()
+
+statement error
+SELECT BIT_XOR(1, 2, 3)
+
+statement error
+SELECT BIT_XOR(BIT_XOR(1))
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_bool.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_bool.test
new file mode 100644
index 0000000..6f50fd1
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_bool.test
@@ -0,0 +1,95 @@
+# name: test/sql/aggregate/aggregates/test_bool.test
+# description: Test Bool operator
+# group: [aggregates]
+
+# Corner Cases
+statement error
+select bool_or(0)
+
+statement error
+select bool_and(0)
+
+query I
+select bool_or(NULL)
+----
+NULL
+
+query I
+select bool_and(NULL)
+----
+NULL
+
+statement error
+select bool_or()
+
+statement error
+select bool_and()
+
+statement error
+select bool_or(*)
+
+statement error
+select bool_and(*)
+
+query I
+SELECT bool_or(True) FROM range(100);
+----
+1
+
+query I
+SELECT bool_and(True) FROM range(100);
+----
+1
+
+query I
+SELECT bool_or(True) FROM range(100) tbl(i) WHERE 1=0;
+----
+NULL
+
+query I
+SELECT bool_and(True) FROM range(100) tbl(i) WHERE 1=0;
+----
+NULL
+
+statement ok
+create table t (d date)
+
+statement ok
+insert into t values (DATE'2021-02-09'-1),(DATE'2021-02-09'+1),(NULL)
+
+query II
+select bool_or(d > '2021-02-09') AS or_result,
+ bool_and(d > '2021-02-09') AS and_result
+from t;
+----
+1 0
+
+query III
+select d,bool_or(d > '2021-02-09') AS or_result,
+ bool_and(d > '2021-02-09') AS and_result
+from t
+group by d;
+----
+NULL NULL NULL
+2021-02-08 0 0
+2021-02-10 1 1
+
+# Window Function
+require vector_size 512
+
+query I
+select bool_or(d > '2021-02-09') over (partition by d)
+ from t order by d;
+----
+NULL
+0
+1
+
+query I
+select bool_and(d > '2021-02-09') over (partition by d)
+ from t order by d;
+----
+NULL
+0
+1
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_corr.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_corr.test
new file mode 100644
index 0000000..57355d2
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_corr.test
@@ -0,0 +1,51 @@
+# name: test/sql/aggregate/aggregates/test_corr.test
+# description: Test CORR operator
+# group: [aggregates]
+
+#Corner cases
+statement error
+select corr()
+
+query I
+select corr(NULL,NULL)
+----
+NULL
+
+query I
+select corr(1,1)
+----
+NULL
+
+statement error
+select corr(*)
+
+statement ok
+create table aggr(k int, v decimal(10,2), v2 decimal(10, 2));
+
+statement ok
+insert into aggr values(1, 10, null),(2, 10, 11), (2, 20, 22), (2, 25, null), (2, 30, 35);
+
+query II
+select k, corr(v, v2) from aggr group by k;
+----
+1 NULL
+2 0.9988445981
+
+query I
+select corr(v, v2) from aggr
+----
+0.9988445981
+
+# Window Function
+require vector_size 512
+
+query I rowsort
+select corr(v, v2) over (partition by k)
+ from aggr;
+----
+0.998845
+0.998845
+0.998845
+0.998845
+NULL
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_count.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_count.test
new file mode 100644
index 0000000..aa4bd5c
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_count.test
@@ -0,0 +1,35 @@
+# name: test/sql/aggregate/aggregates/test_count.test
+# description: Test COUNT operator
+# group: [aggregates]
+
+# test counts on scalar values
+query III
+SELECT COUNT(1), COUNT(100), COUNT(DISTINCT 1)
+----
+1
+1
+1
+
+# test counts on a set of values
+statement ok
+CREATE TABLE integers(i INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (1), (2), (NULL)
+
+query IIII
+SELECT COUNT(*), COUNT(1), COUNT(i), COUNT(COALESCE(i, 1)) FROM integers
+----
+3
+3
+2
+3
+
+# ordered aggregates are not supported
+statement error
+SELECT COUNT(1 ORDER BY 1)
+
+# cannot do DISTINCT *
+statement error
+SELECT COUNT(DISTINCT *) FROM integers
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_count.test_ignored b/modules/calcite/src/test/sql/aggregate/aggregates/test_count.test_ignored
new file mode 100644
index 0000000..1d63f01
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_count.test_ignored
@@ -0,0 +1,41 @@
+# name: test/sql/aggregate/aggregates/test_count.test
+# description: Test COUNT operator
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14543
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14544
+
+# test counts on scalar values
+query IIIII
+SELECT COUNT(*), COUNT(1), COUNT(100), COUNT(NULL), COUNT(DISTINCT 1)
+----
+1
+1
+1
+0
+1
+
+# test counts on a set of values
+statement ok
+CREATE TABLE integers(i INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (1), (2), (NULL)
+
+query IIIIII
+SELECT COUNT(*), COUNT(1), COUNT(i), COUNT(COALESCE(i, 1)), COUNT(DISTINCT i), COUNT(DISTINCT 1) FROM integers
+----
+3
+3
+2
+3
+2
+1
+
+# ordered aggregates are not supported
+statement error
+SELECT COUNT(1 ORDER BY 1)
+
+# cannot do DISTINCT *
+statement error
+SELECT COUNT(DISTINCT *) FROM integers
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_count_star.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_count_star.test
new file mode 100644
index 0000000..ad530e8
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_count_star.test
@@ -0,0 +1,15 @@
+# name: test/sql/aggregate/aggregates/test_count_star.test
+# description: Aggregate only COUNT STAR
+# group: [aggregates]
+
+statement ok
+CREATE TABLE integers(i INTEGER, j INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (3, 4), (3, 4), (2, 4);
+
+query II
+SELECT i, COUNT(*) FROM integers GROUP BY i ORDER BY i
+----
+2 1
+3 2
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_covar.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_covar.test
new file mode 100644
index 0000000..f20100f
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_covar.test
@@ -0,0 +1,98 @@
+# name: test/sql/aggregate/aggregates/test_covar.test
+# description: Test COVAR operators
+# group: [aggregates]
+
+# test incorrect usage of COVAR_POP function
+statement error
+SELECT COVAR_POP()
+
+statement error
+SELECT COVAR_POP(1, 2, 3)
+
+statement error
+SELECT COVAR_POP(COVAR_POP(1))
+
+# test incorrect usage of COVAR_SAMP function
+statement error
+SELECT COVAR_SAMP()
+
+statement error
+SELECT COVAR_SAMP(1, 2, 3)
+
+statement error
+SELECT COVAR_SAMP(COVAR_SAMP(1))
+
+# test population covariance on scalar values
+query RRRR
+SELECT COVAR_POP(3,3), COVAR_POP(NULL,3), COVAR_POP(3,NULL), COVAR_POP(NULL,NULL)
+----
+0.000000
+NULL
+NULL
+NULL
+
+# test sample covariance on scalar values
+query RRRR
+SELECT COVAR_SAMP(3,3), COVAR_SAMP(NULL,3), COVAR_SAMP(3,NULL), COVAR_SAMP(NULL,NULL)
+----
+NULL
+NULL
+NULL
+NULL
+
+# test population covariance on a sequence
+statement ok
+CREATE SEQUENCE seqx;
+
+statement ok
+CREATE SEQUENCE seqy;
+
+query R
+SELECT COVAR_POP(nextval('seqx'),nextval('seqy'))
+----
+0.000000
+
+query R
+SELECT COVAR_POP(nextval('seqx'),nextval('seqy'))
+----
+0.000000
+
+# test population covariance on a set of values
+statement ok
+CREATE TABLE integers(x INTEGER, y INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (10,NULL), (10,11), (20,22), (25,NULL), (30,35)
+
+query RRRRR
+SELECT COVAR_POP(x,y), COVAR_POP(x,1), COVAR_POP(1,y), COVAR_POP(x,NULL), COVAR_POP(NULL,y) FROM integers
+----
+80.000000
+0.000000
+0.000000
+NULL
+NULL
+
+query RRRRR
+SELECT COVAR_SAMP(x,y), COVAR_SAMP(x,1), COVAR_SAMP(1,y), COVAR_SAMP(x,NULL), COVAR_SAMP(NULL,y) FROM integers
+----
+120.000000
+0.000000
+0.000000
+NULL
+NULL
+
+# test covar on empty set
+query RR
+SELECT COVAR_POP(x,y), COVAR_SAMP(x,y) FROM integers WHERE x > 100
+----
+NULL
+NULL
+
+# test covar with only null inputs
+query RR
+SELECT COVAR_POP(NULL, NULL), COVAR_SAMP(NULL, NULL) FROM integers
+----
+NULL
+NULL
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_distinct_aggr.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_distinct_aggr.test
new file mode 100644
index 0000000..6cc863f
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_distinct_aggr.test
@@ -0,0 +1,21 @@
+# name: test/sql/aggregate/aggregates/test_distinct_aggr.test
+# description: DISTINCT aggregations
+# group: [aggregates]
+
+statement ok
+CREATE TABLE distinctagg(i INTEGER, j INTEGER);
+
+statement ok
+INSERT INTO distinctagg VALUES (1,1),(1,1),(2,2), (1,2)
+
+query IIRR
+SELECT COUNT(i), COUNT(DISTINCT i), SUM(i), SUM(DISTINCT i) FROM distinctagg
+----
+4 2 5.000000 3.000000
+
+query IIRR
+SELECT COUNT(i), COUNT(DISTINCT i), SUM(i), SUM(DISTINCT i) FROM distinctagg GROUP BY j ORDER BY j
+----
+2 1 2.000000 1.000000
+2 2 3.000000 3.000000
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_distinct_string_agg.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_distinct_string_agg.test
new file mode 100644
index 0000000..8b206fd
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_distinct_string_agg.test
@@ -0,0 +1,16 @@
+# name: test/sql/aggregate/aggregates/test_distinct_string_agg.test
+# description: Test distinct STRING_AGG operator
+# group: [aggregates]
+
+statement ok
+CREATE TABLE strings(s VARCHAR);
+
+statement ok
+INSERT INTO strings VALUES ('a'), ('b'), ('a');
+
+query TT
+SELECT STRING_AGG(s,','), STRING_AGG(DISTINCT s, ',') FROM strings
+----
+a,b,a
+a,b
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_empty_aggregate.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_empty_aggregate.test
new file mode 100644
index 0000000..6ee4b96
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_empty_aggregate.test
@@ -0,0 +1,31 @@
+# name: test/sql/aggregate/aggregates/test_empty_aggregate.test
+# description: Test aggregate operators on empty set
+# group: [aggregates]
+
+statement ok
+CREATE TABLE integers(i INTEGER);
+
+query IIRRRIII
+SELECT COUNT(*), COUNT(i), STDDEV_SAMP(i), SUM(i), SUM(DISTINCT i), FIRST(i), MAX(i), MIN(i) FROM integers WHERE i > 100
+----
+0
+0
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+
+statement ok
+CREATE TABLE emptyaggr(i INTEGER);
+
+query I
+SELECT COUNT(*) FROM emptyaggr
+----
+0
+
+query RIIIRIIIII
+SELECT SUM(i), COUNT(i), COUNT(DISTINCT i), COUNT(*), AVG(i), COUNT(*)+1, COUNT(i)+1, MIN(i), MIN(i+1), MIN(i)+1 FROM emptyaggr
+----
+NULL 0 0 0 NULL 1 1 NULL NULL NULL
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_entropy.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_entropy.test
new file mode 100644
index 0000000..d58eddd
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_entropy.test
@@ -0,0 +1,94 @@
+# name: test/sql/aggregate/aggregates/test_entropy.test
+# description: Test entropy function
+# group: [aggregates]
+
+#Corner cases
+statement error
+select entropy()
+
+query I
+select entropy(NULL)
+----
+0
+
+query I
+select entropy(1)
+----
+0
+
+statement error
+select entropy(*)
+
+statement ok
+create table aggr(k int);
+
+statement ok
+insert into aggr values (0),(1),(1),(1),(4),(0),(3),(3),(2),(2),(4),(4),(2),(4),(0),(0),(0),(1),(2),(3),(4),(2),(3),(3),(1);
+
+query I
+select entropy(k) from aggr ;
+----
+2.321928
+
+query I
+SELECT entropy(2) FROM range(100);
+----
+0
+
+
+
+query I
+select entropy(k) from aggr group by k%2
+----
+1.584963
+1.000000
+
+statement ok
+create table names (name string)
+
+statement ok
+insert into names values ('pedro'), ('pedro'), ('pedro'),('hannes'),('hannes'),('mark'),(null);
+
+query I
+select entropy(name) from names;
+----
+1.459148
+
+require vector_size 512
+
+query I rowsort
+select entropy(k) over (partition by k%2)
+ from aggr;
+----
+1.000000
+1.000000
+1.000000
+1.000000
+1.000000
+1.000000
+1.000000
+1.000000
+1.000000
+1.000000
+1.584963
+1.584963
+1.584963
+1.584963
+1.584963
+1.584963
+1.584963
+1.584963
+1.584963
+1.584963
+1.584963
+1.584963
+1.584963
+1.584963
+1.584963
+
+# Empty Table
+query I
+SELECT entropy(i) FROM range(100) tbl(i) WHERE 1=0;
+----
+0
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_first_noninlined.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_first_noninlined.test
new file mode 100644
index 0000000..fb9a310
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_first_noninlined.test
@@ -0,0 +1,38 @@
+# name: test/sql/aggregate/aggregates/test_first_noninlined.test
+# description: Test FIRST with non-inlined strings
+# group: [aggregates]
+
+statement ok
+CREATE TABLE tbl(a INTEGER, b VARCHAR)
+
+statement ok
+INSERT INTO tbl VALUES (1, NULL), (2, 'thisisalongstring'), (3, 'thisisalsoalongstring')
+
+# non-grouped aggregate
+query T
+SELECT FIRST(b) FROM tbl WHERE a=2
+----
+thisisalongstring
+
+# Check if arbitraty alias works like FIRST
+query T
+SELECT ARBITRARY(b) FROM tbl WHERE a=2
+----
+thisisalongstring
+
+query T
+SELECT FIRST(b) FROM tbl WHERE a=1
+----
+NULL
+
+# grouped aggregate
+query IT
+SELECT a, FIRST(b) FROM tbl GROUP BY a ORDER BY a
+----
+1
+NULL
+2
+thisisalongstring
+3
+thisisalsoalongstring
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_group_by_many_groups.test_slow b/modules/calcite/src/test/sql/aggregate/aggregates/test_group_by_many_groups.test_slow
new file mode 100644
index 0000000..6988ab9
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_group_by_many_groups.test_slow
@@ -0,0 +1,14 @@
+# name: test/sql/aggregate/aggregates/test_group_by_many_groups.test_slow
+# description: Test GROUP BY with many groups
+# group: [aggregates]
+
+statement ok
+CREATE TABLE integers AS SELECT i, 1 AS j FROM range(0, 10000, 1) t1(i) UNION ALL SELECT i, 2 j FROM range(0, 10000, 1) t1(i);
+
+
+query RR
+SELECT SUM(i), SUM(sums) FROM (SELECT i, SUM(j) AS sums FROM integers GROUP BY i) tbl1
+----
+49995000
+30000
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_group_on_expression.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_group_on_expression.test
new file mode 100644
index 0000000..d90f78a
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_group_on_expression.test
@@ -0,0 +1,75 @@
+# name: test/sql/aggregate/aggregates/test_group_on_expression.test
+# description: Test GROUP BY on expression
+# group: [aggregates]
+
+statement ok
+CREATE TABLE integer(i INTEGER, j INTEGER);
+
+statement ok
+INSERT INTO integer VALUES (3, 4), (3, 5), (3, 7);
+
+# group by on expression
+query I
+SELECT j * 2 FROM integer GROUP BY j * 2 ORDER BY j * 2;
+----
+8
+10
+14
+
+# verify that adding or removing the table name does not impact the validity of the query
+query I
+SELECT integer.j * 2 FROM integer GROUP BY j * 2 ORDER BY j * 2;
+----
+8
+10
+14
+
+query I
+SELECT j * 2 FROM integer GROUP BY integer.j * 2 ORDER BY j * 2;
+----
+8
+10
+14
+
+query I
+SELECT j * 2 FROM integer GROUP BY j * 2 ORDER BY integer.j * 2;
+----
+8
+10
+14
+
+query I
+SELECT integer.j * 2 FROM integer GROUP BY j * 2 ORDER BY integer.j * 2;
+----
+8
+10
+14
+
+query I
+SELECT j * 2 FROM integer GROUP BY integer.j * 2 ORDER BY integer.j * 2;
+----
+8
+10
+14
+
+query I
+SELECT integer.j * 2 FROM integer GROUP BY integer.j * 2 ORDER BY j * 2;
+----
+8
+10
+14
+
+query I
+SELECT integer.j * 2 FROM integer GROUP BY integer.j * 2 ORDER BY integer.j * 2;
+----
+8
+10
+14
+
+query I
+SELECT j * 2 AS i FROM integer GROUP BY j * 2 ORDER BY i;
+----
+8
+10
+14
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_histogram.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_histogram.test
new file mode 100644
index 0000000..e3bfbed
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_histogram.test
@@ -0,0 +1,93 @@
+# name: test/sql/aggregate/aggregates/test_histogram.test
+# description: Test histogram aggregation
+# group: [aggregates]
+
+statement ok
+PRAGMA enable_verification
+
+# Empty Table
+query I
+SELECT histogram(i) FROM range(100) tbl(i) WHERE 1=0;
+----
+NULL
+
+#Corner cases
+statement error
+select histogram()
+
+query I
+select histogram(NULL)
+----
+NULL
+
+query I
+select histogram(1)
+----
+[<bucket: 1, count: 1>]
+
+statement error
+select histogram(*)
+
+query I
+SELECT histogram(2) FROM range(100);
+----
+[<bucket: 2, count: 100>]
+
+statement ok
+CREATE TABLE hist_data (g INTEGER, e INTEGER)
+
+statement ok
+INSERT INTO hist_data VALUES (1, 1), (1, 2), (2, 3), (2, 4), (2, 5), (3, 6), (5, NULL)
+
+
+query T
+SELECT histogram(g) from hist_data
+----
+[<bucket: 1, count: 2>, <bucket: 2, count: 3>, <bucket: 3, count: 1>, <bucket: 5, count: 1>]
+
+query T
+SELECT histogram(e) from hist_data
+----
+[<bucket: 1, count: 1>, <bucket: 2, count: 1>, <bucket: 3, count: 1>, <bucket: 4, count: 1>, <bucket: 5, count: 1>, <bucket: 6, count: 1>]
+
+query I
+select histogram(g)
+ from hist_data
+ group by g%2==0
+----
+[<bucket: 1, count: 2>, <bucket: 3, count: 1>, <bucket: 5, count: 1>]
+[<bucket: 2, count: 3>]
+
+query I
+select histogram(g)
+ from hist_data
+ where g < 3
+----
+[<bucket: 1, count: 2>, <bucket: 2, count: 3>]
+
+statement ok
+create table names (name string)
+
+statement ok
+insert into names values ('pedro'), ('pedro'), ('pedro'),('hannes'),('hannes'),('mark'),(null),('Hubert Blaine Wolfeschlegelsteinhausenbergerdorff Sr.');
+
+query I
+select histogram(name) from names;
+----
+[<bucket: Hubert Blaine Wolfeschlegelsteinhausenbergerdorff Sr., count: 1>, <bucket: hannes, count: 2>, <bucket: mark, count: 1>, <bucket: pedro, count: 3>]
+
+require vector_size 512
+
+query II rowsort
+select g,histogram(g) over (partition by g%2)
+ from hist_data;
+----
+1 [<bucket: 1, count: 2>, <bucket: 3, count: 1>, <bucket: 5, count: 1>]
+1 [<bucket: 1, count: 2>, <bucket: 3, count: 1>, <bucket: 5, count: 1>]
+2 [<bucket: 2, count: 3>]
+2 [<bucket: 2, count: 3>]
+2 [<bucket: 2, count: 3>]
+3 [<bucket: 1, count: 2>, <bucket: 3, count: 1>, <bucket: 5, count: 1>]
+5 [<bucket: 1, count: 2>, <bucket: 3, count: 1>, <bucket: 5, count: 1>]
+
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_incorrect_aggregate.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_incorrect_aggregate.test
new file mode 100644
index 0000000..026da2c
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_incorrect_aggregate.test
@@ -0,0 +1,55 @@
+# name: test/sql/aggregate/aggregates/test_incorrect_aggregate.test
+# description: Test incorrect usages of aggregates
+# group: [aggregates]
+
+statement error
+SELECT COUNT(1, 2, 3)
+
+statement error
+SELECT COUNT(COUNT(1))
+
+statement error
+SELECT STDDEV_SAMP()
+
+statement error
+SELECT STDDEV_SAMP(1, 2, 3)
+
+statement error
+SELECT STDDEV_SAMP(STDDEV_SAMP(1))
+
+statement error
+SELECT SUM()
+
+statement error
+SELECT SUM(1, 2, 3)
+
+statement error
+SELECT SUM(SUM(1))
+
+statement error
+SELECT FIRST()
+
+statement error
+SELECT FIRST(1, 2, 3)
+
+statement error
+SELECT FIRST(FIRST(1))
+
+statement error
+SELECT MAX()
+
+statement error
+SELECT MAX(1, 2, 3)
+
+statement error
+SELECT MAX(MAX(1))
+
+statement error
+SELECT MIN()
+
+statement error
+SELECT MIN(1, 2, 3)
+
+statement error
+SELECT MIN(MIN(1))
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_kurtosis.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_kurtosis.test
new file mode 100644
index 0000000..4680809
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_kurtosis.test
@@ -0,0 +1,82 @@
+# name: test/sql/aggregate/aggregates/test_kurtosis.test
+# description: Test kurtosis aggregate
+# group: [aggregates]
+
+#Corner cases
+statement error
+select kurtosis()
+
+query I
+select kurtosis(NULL)
+----
+NULL
+
+query I
+select kurtosis(1)
+----
+NULL
+
+statement error
+select kurtosis(*)
+
+
+# Constant Value (This shoud be an error)
+query I
+select kurtosis (10) from range (5)
+----
+NULL
+
+#Empty Table
+query I
+select kurtosis (10) from range (5) where 1 == 0
+----
+NULL
+
+statement ok
+create table aggr(k int, v int, v2 int);
+
+statement ok
+insert into aggr values
+ (1, 10, null),
+ (2, 10, 11),
+ (2, 10, 15),
+ (2, 10, 18),
+ (2, 20, 22),
+ (2, 20, 25),
+ (2, 25, null),
+ (2, 30, 35),
+ (2, 30, 40),
+ (2, 30, 50),
+ (2, 30, 51);
+
+query III
+select kurtosis(k), kurtosis(v), kurtosis(v2) from aggr;
+----
+11.000000 -1.961428 -1.445120
+
+query I
+select kurtosis(v2) from aggr group by v;
+----
+NULL
+NULL
+NULL
+-3.977599
+
+# Window Function
+require vector_size 512
+
+query I rowsort
+select kurtosis(v2) over (partition by v)
+ from aggr;
+----
+-3.977599
+-3.977599
+-3.977599
+-3.977599
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_mode.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_mode.test
new file mode 100644
index 0000000..aad6973
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_mode.test
@@ -0,0 +1,96 @@
+# name: test/sql/aggregate/aggregates/test_mode.test
+# description: Test mode function
+# group: [aggregates]
+
+#Corner cases
+statement error
+select mode()
+
+query I
+select mode(NULL)
+----
+NULL
+
+query I
+select mode(1)
+----
+1
+
+statement error
+select mode(*)
+
+statement ok
+create table aggr(k int, v decimal(10,2));
+
+statement ok
+insert into aggr (k, v) values (1, 10), (1, 10), (1, 20), (1, 21);
+
+query I
+select mode(v) from aggr;
+----
+10
+
+query I
+SELECT mode(2) FROM range(100);
+----
+2
+
+statement ok
+insert into aggr (k, v) values (2, 20),(2, 20), (2, 25), (2, 30);
+
+query I
+SELECT CASE
+ WHEN ( value = 10 or value = 20)
+ THEN TRUE
+ ELSE FALSE
+ END
+ FROM (select mode(v) as value from aggr) AS T
+----
+1
+
+statement ok
+insert into aggr (k, v) values (3, null);
+
+query II
+select k, mode(v)
+ from aggr
+ group by k
+ order by k;
+----
+1 10.000000
+2 20.000000
+3 NULL
+
+statement ok
+create table names (name string)
+
+statement ok
+insert into names values ('pedro'), ('pedro'), ('pedro'),('hannes'),('hannes'),('mark'),(null);
+
+query I
+select mode(name) from names;
+----
+pedro
+
+require vector_size 512
+query III
+select k, v, mode(v) over (partition by k)
+ from aggr
+ order by k, v;
+----
+1 10.00 10.000000
+1 10.00 10.000000
+1 20.00 10.000000
+1 21.00 10.000000
+2 20.00 20.000000
+2 20.00 20.000000
+2 25.00 20.000000
+2 30.00 20.000000
+3 NULL NULL
+
+# Empty Table
+query I
+SELECT mode(i) FROM range(100) tbl(i) WHERE 1=0;
+----
+NULL
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_null_aggregates.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_null_aggregates.test
new file mode 100644
index 0000000..faa0fb8
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_null_aggregates.test
@@ -0,0 +1,406 @@
+# name: test/sql/aggregate/aggregates/test_null_aggregates.test
+# description: Test aggregates over many nulls on smallint column
+# group: [aggregates]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE t1(c0 BIGINT, c1 SMALLINT);
+
+statement ok
+INSERT INTO t1 VALUES(NULL,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(NULL,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(NULL,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(NULL,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(NULL,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(NULL,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(NULL,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(NULL,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-9121942514766415310,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-9113483941634330359,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-8718457747090493475,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-7650527153348320600,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-7511073704802549520,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-7342137292157212364,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-7003121677824953185,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-6971852266038069200,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-6873545755554765972,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-6355311124878824053,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-6350463272352412486,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-5908442705000090253,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-5897662788702027960,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-5877879044803815845,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-5732980609151508408,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-5361272612100082873,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-5336571579832669145,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-4928993529687100359,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-4468905900574568755,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-4170492860397664351,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-3684174996218175685,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-3550425917959859111,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-3538537641982313134,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-3509778083052175642,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-3297429447844697659,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-3285304895013369375,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-2783073089603195828,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-2422155131602272083,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-2411133157184452856,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-2353272908390735004,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-2242558770815087701,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-1554405226393925625,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-1337520990873830579,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-1217288122333132479,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-829779308050048379,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-783860634233596188,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-750940733896551510,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-595923232719547231,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-542467477806120649,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-424237581585430344,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-214362279664766533,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(-71301914094672848,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(85486376371946746,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(88239714065746993,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(587212336705139504,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(672222439154311688,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(831201880315087268,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(995204053540447006,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(1246914698489704287,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(1546231510864932275,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(1791765016181687769,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(1799302827895858725,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(2026591599286391832,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(2195119737828970803,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(2342493223442167775,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(2453343748991321803,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(2499109626526694126,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(2753988324592681474,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(2810878285747130284,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(2848885963459816804,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(2915647809434477614,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(3475034101394730335,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(3626542162137919338,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(3877673001272535186,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(4007330825134180665,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(4077358421272316858,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(4690678276679226532,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(4866304904348119643,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(5214401850561094529,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(5272799208960207736,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(5530918740051863299,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(5569314186296520615,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(5740904173463435848,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(5849452934504718062,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(6218815181136940951,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(6275945720557189700,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(6279008355318181000,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(7017987158241964732,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(7237035290160030660,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(7374688146326987272,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(7612353589185494102,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(7958180433948844465,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(8093404925372580611,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(8165972772169640480,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(8531143325322891078,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(8658728983219000078,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(8730638167239698291,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(8757751876611013998,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(8994059213096666367,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(9034558451786630908,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(9049770455330813268,NULL);
+
+statement ok
+INSERT INTO t1 VALUES(9196517019233481682,NULL);
+
+query IIII
+SELECT c0, sum(c1), min(c1), max(c1) FROM t1 GROUP BY c0 ORDER BY 1, 2, 3, 4
+----
+NULL NULL NULL NULL
+-9121942514766415310 NULL NULL NULL
+-9113483941634330359 NULL NULL NULL
+-8718457747090493475 NULL NULL NULL
+-7650527153348320600 NULL NULL NULL
+-7511073704802549520 NULL NULL NULL
+-7342137292157212364 NULL NULL NULL
+-7003121677824953185 NULL NULL NULL
+-6971852266038069200 NULL NULL NULL
+-6873545755554765972 NULL NULL NULL
+-6355311124878824053 NULL NULL NULL
+-6350463272352412486 NULL NULL NULL
+-5908442705000090253 NULL NULL NULL
+-5897662788702027960 NULL NULL NULL
+-5877879044803815845 NULL NULL NULL
+-5732980609151508408 NULL NULL NULL
+-5361272612100082873 NULL NULL NULL
+-5336571579832669145 NULL NULL NULL
+-4928993529687100359 NULL NULL NULL
+-4468905900574568755 NULL NULL NULL
+-4170492860397664351 NULL NULL NULL
+-3684174996218175685 NULL NULL NULL
+-3550425917959859111 NULL NULL NULL
+-3538537641982313134 NULL NULL NULL
+-3509778083052175642 NULL NULL NULL
+-3297429447844697659 NULL NULL NULL
+-3285304895013369375 NULL NULL NULL
+-2783073089603195828 NULL NULL NULL
+-2422155131602272083 NULL NULL NULL
+-2411133157184452856 NULL NULL NULL
+-2353272908390735004 NULL NULL NULL
+-2242558770815087701 NULL NULL NULL
+-1554405226393925625 NULL NULL NULL
+-1337520990873830579 NULL NULL NULL
+-1217288122333132479 NULL NULL NULL
+-829779308050048379 NULL NULL NULL
+-783860634233596188 NULL NULL NULL
+-750940733896551510 NULL NULL NULL
+-595923232719547231 NULL NULL NULL
+-542467477806120649 NULL NULL NULL
+-424237581585430344 NULL NULL NULL
+-214362279664766533 NULL NULL NULL
+-71301914094672848 NULL NULL NULL
+85486376371946746 NULL NULL NULL
+88239714065746993 NULL NULL NULL
+587212336705139504 NULL NULL NULL
+672222439154311688 NULL NULL NULL
+831201880315087268 NULL NULL NULL
+995204053540447006 NULL NULL NULL
+1246914698489704287 NULL NULL NULL
+1546231510864932275 NULL NULL NULL
+1791765016181687769 NULL NULL NULL
+1799302827895858725 NULL NULL NULL
+2026591599286391832 NULL NULL NULL
+2195119737828970803 NULL NULL NULL
+2342493223442167775 NULL NULL NULL
+2453343748991321803 NULL NULL NULL
+2499109626526694126 NULL NULL NULL
+2753988324592681474 NULL NULL NULL
+2810878285747130284 NULL NULL NULL
+2848885963459816804 NULL NULL NULL
+2915647809434477614 NULL NULL NULL
+3475034101394730335 NULL NULL NULL
+3626542162137919338 NULL NULL NULL
+3877673001272535186 NULL NULL NULL
+4007330825134180665 NULL NULL NULL
+4077358421272316858 NULL NULL NULL
+4690678276679226532 NULL NULL NULL
+4866304904348119643 NULL NULL NULL
+5214401850561094529 NULL NULL NULL
+5272799208960207736 NULL NULL NULL
+5530918740051863299 NULL NULL NULL
+5569314186296520615 NULL NULL NULL
+5740904173463435848 NULL NULL NULL
+5849452934504718062 NULL NULL NULL
+6218815181136940951 NULL NULL NULL
+6275945720557189700 NULL NULL NULL
+6279008355318181000 NULL NULL NULL
+7017987158241964732 NULL NULL NULL
+7237035290160030660 NULL NULL NULL
+7374688146326987272 NULL NULL NULL
+7612353589185494102 NULL NULL NULL
+7958180433948844465 NULL NULL NULL
+8093404925372580611 NULL NULL NULL
+8165972772169640480 NULL NULL NULL
+8531143325322891078 NULL NULL NULL
+8658728983219000078 NULL NULL NULL
+8730638167239698291 NULL NULL NULL
+8757751876611013998 NULL NULL NULL
+8994059213096666367 NULL NULL NULL
+9034558451786630908 NULL NULL NULL
+9049770455330813268 NULL NULL NULL
+9196517019233481682 NULL NULL NULL
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_perfect_ht.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_perfect_ht.test
new file mode 100644
index 0000000..fd8721b
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_perfect_ht.test
@@ -0,0 +1,24 @@
+# name: test/sql/aggregate/aggregates/test_perfect_ht.test
+# description: Test aggregates that can trigger a perfect HT
+# group: [aggregates]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+PRAGMA perfect_ht_threshold=20;
+
+statement ok
+CREATE TABLE timeseries(year INTEGER, val INTEGER);
+
+statement ok
+INSERT INTO timeseries VALUES (1996, 10), (1997, 12), (1996, 20), (2001, 30), (NULL, 1), (1996, NULL);
+
+query IIII
+SELECT year, SUM(val), COUNT(val), COUNT(*) FROM timeseries GROUP BY year ORDER BY year;
+----
+1996 30 2 3
+1997 12 1 1
+2001 30 1 1
+NULL 1 1 1
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_perfect_ht.test_ignored b/modules/calcite/src/test/sql/aggregate/aggregates/test_perfect_ht.test_ignored
new file mode 100644
index 0000000..a4b4a65
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_perfect_ht.test_ignored
@@ -0,0 +1,175 @@
+# name: test/sql/aggregate/aggregates/test_perfect_ht.test
+# description: Test aggregates that can trigger a perfect HT
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14542
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+PRAGMA perfect_ht_threshold=20;
+
+statement ok
+CREATE TABLE timeseries(year INTEGER, val INTEGER);
+
+statement ok
+INSERT INTO timeseries VALUES (1996, 10), (1997, 12), (1996, 20), (2001, 30), (NULL, 1), (1996, NULL);
+
+query IIII
+SELECT year, SUM(val), COUNT(val), COUNT(*) FROM timeseries GROUP BY year ORDER BY year;
+----
+1996 30 2 3
+1997 12 1 1
+2001 30 1 1
+NULL 1 1 1
+
+# use aggregates with destructors
+query III
+SELECT year, LIST(val), STRING_AGG(val, ',') FROM timeseries GROUP BY year ORDER BY year;
+----
+NULL [1] 1
+1996 [10, 20, NULL] 10,20
+1997 [12] 12
+2001 [30] 30
+
+# many small columns each having only the values 0 and 1
+# total possible combinations is 2^10, but there are only 2 groups
+statement ok
+create table manycolumns as select i a, i b, i c, i d, i e from range(0,2) tbl(i);
+
+query IIIII
+select a, b, c, d, e FROM manycolumns GROUP BY 1, 2, 3, 4, 5
+----
+0 0 0 0 0
+1 1 1 1 1
+
+# test edge cases: multiple tinyints without statistics
+# create a table of tinyints [-127, 128] stored as varchar
+# by forcing a varchar to tinyint cast we lose statistics
+statement ok
+CREATE TABLE tinyints AS SELECT i::TINYINT::VARCHAR AS t FROM range(-127, 128) tbl(i);
+
+query IIII
+SELECT COUNT(DISTINCT i), MIN(i), MAX(i), SUM(i) / COUNT(i) FROM (SELECT t::TINYINT t1 FROM tinyints GROUP BY t1) tbl(i)
+----
+255 -127 127 0
+
+# now do the same with a single smallint column
+statement ok
+CREATE TABLE smallints AS SELECT i::SMALLINT::VARCHAR AS t FROM range(-32767, 32768) tbl(i);
+
+query IIII
+SELECT COUNT(DISTINCT i), MIN(i), MAX(i), SUM(i) / COUNT(i) FROM (SELECT t::SMALLINT t1 FROM smallints GROUP BY t1) tbl(i)
+----
+65535 -32767 32767 0
+
+# test result ordering of perfect HT
+statement ok
+PRAGMA disable_verification
+
+statement ok
+create table dates as select date '1992-01-01' + concat(i, ' months')::interval as d from range(100) tbl(i);
+
+query II
+select extract(year from d), extract(month from d) from dates group by 1, 2;
+----
+1992 1
+1992 2
+1992 3
+1992 4
+1992 5
+1992 6
+1992 7
+1992 8
+1992 9
+1992 10
+1992 11
+1992 12
+1993 1
+1993 2
+1993 3
+1993 4
+1993 5
+1993 6
+1993 7
+1993 8
+1993 9
+1993 10
+1993 11
+1993 12
+1994 1
+1994 2
+1994 3
+1994 4
+1994 5
+1994 6
+1994 7
+1994 8
+1994 9
+1994 10
+1994 11
+1994 12
+1995 1
+1995 2
+1995 3
+1995 4
+1995 5
+1995 6
+1995 7
+1995 8
+1995 9
+1995 10
+1995 11
+1995 12
+1996 1
+1996 2
+1996 3
+1996 4
+1996 5
+1996 6
+1996 7
+1996 8
+1996 9
+1996 10
+1996 11
+1996 12
+1997 1
+1997 2
+1997 3
+1997 4
+1997 5
+1997 6
+1997 7
+1997 8
+1997 9
+1997 10
+1997 11
+1997 12
+1998 1
+1998 2
+1998 3
+1998 4
+1998 5
+1998 6
+1998 7
+1998 8
+1998 9
+1998 10
+1998 11
+1998 12
+1999 1
+1999 2
+1999 3
+1999 4
+1999 5
+1999 6
+1999 7
+1999 8
+1999 9
+1999 10
+1999 11
+1999 12
+2000 1
+2000 2
+2000 3
+2000 4
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_product.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_product.test
new file mode 100644
index 0000000..55896a2
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_product.test
@@ -0,0 +1,68 @@
+# name: test/sql/aggregate/aggregates/test_product.test
+# description: Test Product operator
+# group: [aggregates]
+
+#Corner cases
+statement error
+select product()
+
+query I
+select product(NULL)
+----
+NULL
+
+query I
+select product(1)
+----
+1
+
+statement error
+select product(*)
+
+statement ok
+CREATE TABLE integers(i INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (1), (2),(4), (NULL)
+
+query I
+SELECT product(i) FROM integers
+----
+8
+
+query I
+SELECT PRODUCT(2) FROM range(100);
+----
+1267650600228229401496703205376
+
+query I
+SELECT PRODUCT(2) FROM range(100) tbl(i) WHERE i % 2 != 0;
+----
+1125899906842624
+
+# Grouped Aggregation
+query I
+select product(i) from integers group by i%2
+----
+1.000000
+8.000000
+NULL
+
+# Empty Table
+query I
+SELECT PRODUCT(i) FROM range(100) tbl(i) WHERE 1=0;
+----
+NULL
+
+# Window Function
+require vector_size 512
+
+query I rowsort
+select product(i) over (partition by i%2)
+ from integers;
+----
+1
+8
+8
+NULL
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_quantile.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_quantile.test
new file mode 100644
index 0000000..ff1de49
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_quantile.test
@@ -0,0 +1,222 @@
+# name: test/sql/aggregate/aggregates/test_quantile.test
+# description: Test MEDIAN operator
+# group: [aggregates]
+
+statement ok
+PRAGMA enable_verification
+
+# scalar quantiles
+query II
+SELECT median(NULL), median(1)
+----
+NULL 1
+
+query II
+SELECT median(NULL), median(1) FROM range(2000)
+----
+NULL 1
+
+statement ok
+create table quantile as select range r, random() from range(10000) union all values (NULL, 0.1), (NULL, 0.5), (NULL, 0.9) order by 2;
+
+query I
+SELECT median(r) FROM quantile
+----
+4999
+
+query I
+SELECT median(r) FROM quantile
+----
+4999
+
+query R
+SELECT median(r::float) FROM quantile
+----
+4999
+
+query R
+SELECT median(r::double) FROM quantile
+----
+4999
+
+query I
+SELECT median(r::tinyint) FROM quantile where r < 100
+----
+49
+
+query I
+SELECT median(r::smallint) FROM quantile
+----
+4999
+
+query I
+SELECT median(r::integer) FROM quantile
+----
+4999
+
+query I
+SELECT median(r::bigint) FROM quantile
+----
+4999
+
+query I
+SELECT median(r::hugeint) FROM quantile
+----
+4999
+
+query I
+SELECT median(r::decimal(10,2)) FROM quantile
+----
+4999
+
+query I
+SELECT median(NULL) FROM quantile
+----
+NULL
+
+query I
+SELECT median(42) FROM quantile
+----
+42
+
+
+query I
+SELECT quantile(r, 0.5) FROM quantile
+----
+4999
+
+query I
+SELECT quantile(r::decimal(10,2), 0.5) FROM quantile
+----
+4999
+
+query I
+SELECT quantile(r, 1.0) FROM quantile
+----
+9999
+
+query I
+SELECT quantile(r, 0.0) FROM quantile
+----
+0
+
+query I
+SELECT quantile(NULL, 0.5) FROM quantile
+----
+NULL
+
+query I
+SELECT quantile(42, 0.5) FROM quantile
+----
+42
+
+query I
+SELECT quantile(NULL, 0.5)
+----
+NULL
+
+query I
+SELECT quantile(42, 0.5)
+----
+42
+
+# single GROUP
+query III
+SELECT quantile(r, 0.1), quantile(r, 0.5), quantile(r, 0.9) from quantile
+----
+999
+4999
+8999
+
+# multiple groups
+query II
+SELECT mod(r,10) as g, quantile(r, 0.1) FROM quantile GROUP BY 1 ORDER BY 1
+----
+NULL NULL
+0 990
+1 991
+2 992
+3 993
+4 994
+5 995
+6 996
+7 997
+8 998
+9 999
+
+# constant input
+query I
+SELECT quantile(1, 0.1) FROM quantile
+----
+1
+
+# empty input
+query I
+SELECT quantile(r, 0.1) FROM quantile WHERE 1=0
+----
+NULL
+
+statement error
+SELECT quantile(r, -0.1) FROM quantile
+
+statement error
+SELECT quantile(r, 1.1) FROM quantile
+
+statement error
+SELECT quantile(r, "string") FROM quantile
+
+statement error
+SELECT quantile(r, NULL) FROM quantile
+
+statement error
+SELECT quantile(r::string, 0.5) FROM quantile
+
+statement error
+SELECT quantile(r) FROM quantile
+
+statement error
+SELECT quantile(r, 0.1, 50) FROM quantile
+
+
+statement ok
+pragma threads=4
+
+statement ok
+PRAGMA force_parallelism
+
+# single GROUP
+query III
+SELECT quantile(r, 0.1), quantile(r, 0.5), quantile(r, 0.9) from quantile
+----
+999
+4999
+8999
+
+# multiple groups
+query II
+SELECT mod(r,10) as g, quantile(r, 0.1) FROM quantile GROUP BY 1 ORDER BY 1
+----
+NULL NULL
+0 990
+1 991
+2 992
+3 993
+4 994
+5 995
+6 996
+7 997
+8 998
+9 999
+
+# constant input
+query I
+SELECT quantile(1, 0.1) FROM quantile
+----
+1
+
+# empty input
+query I
+SELECT quantile(r, 0.1) FROM quantile WHERE 1=0
+----
+NULL
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_quantile_list.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_quantile_list.test
new file mode 100644
index 0000000..6002daf
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_quantile_list.test
@@ -0,0 +1,114 @@
+# name: test/sql/aggregate/aggregates/test_quantile_list.test
+# description: Test QUANTILE operator with LIST quantiles
+# group: [aggregates]
+
+statement ok
+PRAGMA enable_verification
+
+# scalar quantiles
+statement ok
+create table quantiles as select range r, random() FROM range(10000) union all values (NULL, 0.1), (NULL, 0.5), (NULL, 0.9) order by 2;
+
+# single GROUP
+query I
+SELECT quantile(r, LIST_VALUE(0.1, 0.5, 0.9)) FROM quantiles
+----
+[999, 4999, 8999]
+
+# multiple groups
+query II
+SELECT mod(r,10) as g, quantile(r, LIST_VALUE(0.1, 0.5, 0.9)) FROM quantiles GROUP BY 1 ORDER BY 1
+----
+NULL NULL
+0 [990, 4990, 8990]
+1 [991, 4991, 8991]
+2 [992, 4992, 8992]
+3 [993, 4993, 8993]
+4 [994, 4994, 8994]
+5 [995, 4995, 8995]
+6 [996, 4996, 8996]
+7 [997, 4997, 8997]
+8 [998, 4998, 8998]
+9 [999, 4999, 8999]
+
+# constant input
+query I
+SELECT quantile(1, LIST_VALUE(0.1, 0.5, 0.9)) FROM quantiles
+----
+[1, 1, 1]
+
+# empty input
+query I
+SELECT quantile(r, LIST_VALUE(0.1, 0.5, 0.9)) FROM quantiles WHERE 1=0
+----
+NULL
+
+# empty list
+query I
+SELECT quantile(r, LIST_VALUE()) FROM quantiles
+----
+[]
+
+statement ok
+pragma threads=4
+
+statement ok
+PRAGMA force_parallelism
+
+# single GROUP
+query I
+SELECT quantile(r, LIST_VALUE(0.1, 0.5, 0.9)) FROM quantiles
+----
+[999, 4999, 8999]
+
+# multiple groups
+query II
+SELECT mod(r,10) as g, quantile(r, LIST_VALUE(0.1, 0.5, 0.9)) FROM quantiles GROUP BY 1 ORDER BY 1
+----
+NULL NULL
+0 [990, 4990, 8990]
+1 [991, 4991, 8991]
+2 [992, 4992, 8992]
+3 [993, 4993, 8993]
+4 [994, 4994, 8994]
+5 [995, 4995, 8995]
+6 [996, 4996, 8996]
+7 [997, 4997, 8997]
+8 [998, 4998, 8998]
+9 [999, 4999, 8999]
+
+# constant input
+query I
+SELECT quantile(1, LIST_VALUE(0.1, 0.5, 0.9)) FROM quantiles
+----
+[1, 1, 1]
+
+# empty input
+query I
+SELECT quantile(r, LIST_VALUE(0.1, 0.5, 0.9)) FROM quantiles WHERE 1=0
+----
+NULL
+
+# empty list
+query I
+SELECT quantile(r, LIST_VALUE()) FROM quantiles
+----
+[]
+
+statement error
+SELECT quantile(r, LIST_VALUE(-0.1, 0.5, 0.9)) FROM quantiles
+
+statement error
+SELECT quantile(r, (0.1, 0.5, 1.1)) FROM quantiles
+
+statement error
+SELECT quantile(r, LIST_VALUE(0.1, 0.5, NULL)) FROM quantiles
+
+statement error
+SELECT quantile(r, LIST_VALUE("0.1", "0.5", "0.9")) FROM quantiles
+
+statement error
+SELECT quantile(r::string, LIST_VALUE(0.1, 0.5, 0.9)) FROM quantiles
+
+statement error
+SELECT quantile(r, LIST_VALUE(0.1, 0.5, 0.9), 50) FROM quantiles
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_regression.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_regression.test
new file mode 100644
index 0000000..bd1006c
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_regression.test
@@ -0,0 +1,346 @@
+# name: test/sql/aggregate/aggregates/test_regression.test
+# description: Test Regression Functions
+# group: [aggregates]
+
+#Corner cases
+statement error
+select regr_avgx()
+
+query I
+select regr_avgx(NULL,NULL)
+----
+NULL
+
+query I
+select regr_avgx(1,1)
+----
+1
+
+statement error
+select regr_avgx(*)
+
+statement error
+select regr_avgy()
+
+query I
+select regr_avgy(NULL,NULL)
+----
+NULL
+
+query I
+select regr_avgy(1,1)
+----
+1
+
+statement error
+select regr_avgy(*)
+
+statement error
+select regr_count()
+
+query I
+select regr_count(NULL,NULL)
+----
+0
+
+query I
+select regr_count(1,1)
+----
+1
+
+statement error
+select regr_count(*)
+
+statement error
+select regr_slope()
+
+query I
+select regr_slope(NULL,NULL)
+----
+NULL
+
+query I
+select regr_slope(1,1)
+----
+NULL
+
+statement error
+select regr_slope(*)
+
+statement error
+select regr_r2()
+
+query I
+select regr_r2(NULL,NULL)
+----
+NULL
+
+query I
+select regr_r2(1,1)
+----
+NULL
+
+statement error
+select regr_r2(*)
+
+statement error
+select regr_sxx()
+
+query I
+select regr_sxx(NULL,NULL)
+----
+NULL
+
+query I
+select regr_sxx(1,1)
+----
+0
+
+statement error
+select regr_syy()
+
+query I
+select regr_syy(NULL,NULL)
+----
+NULL
+
+query I
+select regr_syy(1,1)
+----
+0
+
+statement error
+select regr_sxy(*)
+
+statement error
+select regr_sxy()
+
+query I
+select regr_sxy(NULL,NULL)
+----
+NULL
+
+query I
+select regr_sxy(1,1)
+----
+0
+
+statement error
+select regr_syy(*)
+
+statement error
+select regr_intercept()
+
+query I
+select regr_intercept(NULL,NULL)
+----
+NULL
+
+query I
+select regr_intercept(1,1)
+----
+NULL
+
+
+statement error
+select regr_intercept(*)
+
+statement ok
+create table aggr(k int, v decimal(10,2), v2 decimal(10, 2));
+
+statement ok
+insert into aggr values(1, 10, null), (2, 10, 11), (2, 20, 22), (2, 25, null), (2, 30, 35);
+
+
+query II
+select k, regr_avgx(v, v2) from aggr group by k;
+----
+1 NULL
+2 22.666667
+
+query II
+select k, regr_avgy(v, v2) from aggr group by k;
+----
+1 NULL
+2 20
+
+query III
+select k, count(*), regr_count(v, v2) from aggr group by k;
+----
+1 1 0
+2 4 3
+
+query II
+select k, regr_slope(v, v2) from aggr group by k;
+----
+1 NULL
+2 0.831409
+
+query II
+select k, regr_r2(v, v2) from aggr group by k;
+----
+1 NULL
+2 0.997691
+
+query II
+select k, regr_sxx(v, v2) from aggr group by k;
+----
+1 NULL
+2 288.666667
+
+query II
+select k, regr_syy(v, v2) from aggr group by k;
+----
+1 NULL
+2 200.000000
+
+query II
+select k, regr_sxy(v, v2) from aggr group by k;
+----
+1 NULL
+2 240.000000
+
+query II
+select k, regr_intercept(v, v2) from aggr group by k;
+----
+1 NULL
+2 1.154734
+
+query I
+select regr_avgx(v, v2) from aggr ;
+----
+22.666667
+
+query I
+select regr_avgy(v, v2) from aggr ;
+----
+20
+
+query I
+select regr_count(v, v2) from aggr ;
+----
+3
+
+query I
+select regr_slope(v, v2) from aggr ;
+----
+0.831409
+
+query I
+select regr_r2(v, v2) from aggr ;
+----
+0.997691
+
+query I
+select regr_sxx(v, v2) from aggr ;
+----
+288.666667
+
+query I
+select regr_syy(v, v2) from aggr ;
+----
+200.000000
+
+query I
+select regr_sxy(v, v2) from aggr;
+----
+240.000000
+
+query I
+select regr_intercept(v, v2) from aggr;
+----
+1.154734
+
+
+# Window Function
+require vector_size 512
+
+query I rowsort
+select regr_avgx(v, v2) over (partition by k)
+ from aggr;
+----
+22.666667
+22.666667
+22.666667
+22.666667
+NULL
+
+query I rowsort
+select regr_avgy(v, v2) over (partition by k)
+ from aggr;
+----
+20.000000
+20.000000
+20.000000
+20.000000
+NULL
+
+query I rowsort
+select regr_count(v, v2) over (partition by k)
+ from aggr;
+----
+0
+3
+3
+3
+3
+
+query I
+select regr_slope(v, v2) over (partition by k)
+ from aggr;
+----
+0.831409
+0.831409
+0.831409
+0.831409
+NULL
+
+query I rowsort
+select regr_r2(v, v2) over (partition by k)
+ from aggr;
+----
+0.997691
+0.997691
+0.997691
+0.997691
+NULL
+
+query I rowsort
+select regr_sxx(v, v2) over (partition by k)
+ from aggr;
+----
+288.666667
+288.666667
+288.666667
+288.666667
+NULL
+
+query I rowsort
+select regr_syy(v, v2) over (partition by k)
+ from aggr;
+----
+200.000000
+200.000000
+200.000000
+200.000000
+NULL
+
+query I rowsort
+select regr_sxy(v, v2) over (partition by k)
+ from aggr;
+----
+240.000000
+240.000000
+240.000000
+240.000000
+NULL
+
+query I rowsort
+select regr_intercept(v, v2) over (partition by k)
+ from aggr;
+----
+1.154734
+1.154734
+1.154734
+1.154734
+NULL
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_scalar_aggr.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_scalar_aggr.test
new file mode 100644
index 0000000..328a1fc
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_scalar_aggr.test
@@ -0,0 +1,65 @@
+# name: test/sql/aggregate/aggregates/test_scalar_aggr.test
+# description: Test aggregates with scalar inputs
+# group: [aggregates]
+
+# test aggregate on scalar values
+query IIIIIT
+SELECT COUNT(1), MIN(1), FIRST(1), MAX(1), SUM(1), STRING_AGG('hello', ',')
+----
+1
+1
+1
+1
+1
+hello
+
+# test aggregate on scalar NULLs
+query IIIIIT
+SELECT COUNT(NULL), MIN(NULL), FIRST(NULL), MAX(NULL), SUM(NULL), STRING_AGG(NULL, NULL)
+----
+0
+NULL
+NULL
+NULL
+NULL
+NULL
+
+query I
+SELECT FIRST(NULL)
+----
+NULL
+
+query III
+SELECT NULL as a, NULL as b, 1 as id UNION SELECT CAST('00:00:00' AS TIME) as a, CAST('12:34:56' AS TIME) as b, 2 as id ORDER BY 1
+----
+NULL NULL 1
+00:00:00 12:34:56 2
+
+# test aggregates on a set of values with scalar inputs
+statement ok
+CREATE TABLE integers(i INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (1), (2), (NULL)
+
+query IIIIIT
+SELECT COUNT(1), MIN(1), FIRST(1), MAX(1), SUM(1), STRING_AGG('hello', ',') FROM integers
+----
+3
+1
+1
+1
+3
+hello,hello,hello
+
+# test aggregates on a set of values with scalar NULL values as inputs
+query IIIIIT
+SELECT COUNT(NULL), MIN(NULL), FIRST(NULL), MAX(NULL), SUM(NULL), STRING_AGG(NULL, NULL) FROM integers
+----
+0
+NULL
+NULL
+NULL
+NULL
+NULL
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_skewness.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_skewness.test
new file mode 100644
index 0000000..1c9d1ff
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_skewness.test
@@ -0,0 +1,82 @@
+# name: test/sql/aggregate/aggregates/test_skewness.test
+# description: Test skewness aggregate
+# group: [aggregates]
+
+#Corner cases
+statement error
+select skewness()
+
+query I
+select skewness(NULL)
+----
+NULL
+
+query I
+select skewness(1)
+----
+NULL
+
+statement error
+select skewness(*)
+
+
+# Constant Value
+query I
+select skewness (10) from range (5)
+----
+NULL
+
+#Empty Table
+query I
+select skewness (10) from range (5) where 1 == 0
+----
+NULL
+
+statement ok
+create table aggr(k int, v decimal(10,2), v2 decimal(10, 2));
+
+statement ok
+insert into aggr values
+ (1, 10, null),
+ (2, 10, 11),
+ (2, 10, 15),
+ (2, 10, 18),
+ (2, 20, 22),
+ (2, 20, 25),
+ (2, 25, null),
+ (2, 30, 35),
+ (2, 30, 40),
+ (2, 30, 50),
+ (2, 30, 51);
+
+query III
+select skewness(k), skewness(v), skewness(v2) from aggr
+----
+-3.316625 -0.163444 0.365401
+
+query I
+select skewness(v2) from aggr group by v
+----
+-0.423273
+NULL
+NULL
+-0.330141
+
+# Window Function
+require vector_size 512
+
+query I
+select skewness(v2) over (partition by v)
+ from aggr order by v;
+----
+-0.423273
+-0.423273
+-0.423273
+-0.423273
+NULL
+NULL
+NULL
+-0.330141
+-0.330141
+-0.330141
+-0.330141
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_stddev.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_stddev.test
new file mode 100644
index 0000000..ffc955a
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_stddev.test
@@ -0,0 +1,122 @@
+# name: test/sql/aggregate/aggregates/test_stddev.test
+# description: STDDEV aggregations
+# group: [aggregates]
+
+statement ok
+create table stddev_test(val integer, grp integer)
+
+statement ok
+insert into stddev_test values (42, 1), (43, 1), (42, 2), (1000, 2), (NULL, 1), (NULL, 3)
+
+# stddev_samp
+query R
+select round(stddev_samp(val), 1) from stddev_test
+----
+478.800000
+
+query R
+select round(stddev_samp(val), 1) from stddev_test where val is not null
+----
+478.800000
+
+query IRRI
+select grp, sum(val), round(stddev_samp(val), 1), min(val) from stddev_test group by grp order by grp
+----
+1 85.000000 0.700000 42
+2 1042.000000 677.400000 42
+3 NULL NULL NULL
+
+query IRRI
+select grp, sum(val), round(stddev_samp(val), 1), min(val) from stddev_test where val is not null group by grp order by grp
+----
+1 85.000000 0.700000 42
+2 1042.000000 677.400000 42
+
+# stddev_pop
+query R
+select round(stddev_pop(val), 1) from stddev_test
+----
+414.700000
+
+query R
+select round(stddev_pop(val), 1) from stddev_test where val is not null
+----
+414.700000
+
+query IRRI
+select grp, sum(val), round(stddev_pop(val), 1), min(val) from stddev_test group by grp order by grp
+----
+1 85.000000 0.500000 42
+2 1042.000000 479.000000 42
+3 NULL NULL NULL
+
+query IRRI
+select grp, sum(val), round(stddev_pop(val), 1), min(val) from stddev_test where val is not null group by grp order by grp
+----
+1 85.000000 0.500000 42
+2 1042.000000 479.000000 42
+
+# var_samp
+query R
+select round(var_samp(val), 1) from stddev_test
+----
+229281.600000
+
+query R
+select round(variance(val), 1) from stddev_test
+----
+229281.600000
+
+query R
+select round(var_samp(val), 1) from stddev_test where val is not null
+----
+229281.600000
+
+query IRRI
+select grp, sum(val), round(var_samp(val), 1), min(val) from stddev_test group by grp order by grp
+----
+1 85.000000 0.500000 42
+2 1042.000000 458882.000000 42
+3 NULL NULL NULL
+
+query IRRI
+select grp, sum(val), round(var_samp(val), 1), min(val) from stddev_test where val is not null group by grp order by grp
+----
+1 85.000000 0.500000 42
+2 1042.000000 458882.000000 42
+
+# var_pop
+query R
+select round(var_pop(val), 1) from stddev_test
+----
+171961.200000
+
+query R
+select round(var_pop(val), 1) from stddev_test where val is not null
+----
+171961.200000
+
+query IRRI
+select grp, sum(val), round(var_pop(val), 2), min(val) from stddev_test group by grp order by grp
+----
+1 85.000000 0.250000 42
+2 1042.000000 229441.000000 42
+3 NULL NULL NULL
+
+query IRRI
+select grp, sum(val), round(var_pop(val), 2), min(val) from stddev_test where val is not null group by grp order by grp
+----
+1 85.000000 0.250000 42
+2 1042.000000 229441.000000 42
+
+statement ok
+create table stddev_test_alias(val integer, grp integer)
+
+statement ok
+insert into stddev_test_alias values (42, 1), (43, 1), (42, 2), (1000, 2), (NULL, 1), (NULL, 3)
+
+# stddev_samp
+query R
+select round(stddev(val), 1) from stddev_test_alias
+----
+478.800000
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_string_agg.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_string_agg.test
new file mode 100644
index 0000000..9a451c4
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_string_agg.test
@@ -0,0 +1,90 @@
+# name: test/sql/aggregate/aggregates/test_string_agg.test
+# description: Test STRING_AGG operator
+# group: [aggregates]
+
+# test incorrect usage of STRING_AGG function
+
+statement error
+SELECT STRING_AGG()
+
+statement error
+SELECT STRING_AGG(1, 2, 3)
+
+statement error
+SELECT STRING_AGG(STRING_AGG('a',','))
+
+# test string aggregation on scalar values
+query T
+SELECT STRING_AGG('a',',')
+----
+a
+
+# test string aggregation on scalar values
+query TTTT
+SELECT STRING_AGG('a',','), STRING_AGG(NULL,','), STRING_AGG('a',NULL), STRING_AGG(NULL,NULL)
+----
+a
+NULL
+NULL
+NULL
+
+# test string aggregation on a set of values
+statement ok
+CREATE TABLE strings(g INTEGER, x VARCHAR, y VARCHAR);
+
+statement ok
+INSERT INTO strings VALUES (1,'a','/'), (1,'b','-'), (2,'i','/'), (2,NULL,'-'), (2,'j','+'), (3,'p','/'), (4,'x','/'), (4,'y','-'), (4,'z','+')
+
+query TT
+SELECT STRING_AGG(x,','), STRING_AGG(x,y) FROM strings
+----
+a,b,i,j,p,x,y,z
+a-b/i+j/p/x-y+z
+
+query ITT
+SELECT g, STRING_AGG(x,','), STRING_AGG(x,y) FROM strings GROUP BY g ORDER BY g
+----
+1
+a,b
+a-b
+2
+i,j
+i+j
+3
+p
+p
+4
+x,y,z
+x-y+z
+
+# test agg on empty set
+query TT
+SELECT STRING_AGG(x,','), STRING_AGG(x,y) FROM strings WHERE g > 100
+----
+NULL
+NULL
+
+# numerics are auto cast to strings
+query T
+SELECT STRING_AGG(1, 2)
+----
+1
+
+# group concat is an alias for string_agg
+query T
+SELECT GROUP_CONCAT('a', ',')
+----
+a
+
+query T
+SELECT GROUP_CONCAT('a')
+----
+a
+
+query TT
+SELECT g, GROUP_CONCAT(x) FROM strings GROUP BY g ORDER BY g
+----
+1 a,b
+2 i,j
+3 p
+4 x,y,z
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_string_agg_big.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_string_agg_big.test
new file mode 100644
index 0000000..68f2735
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_string_agg_big.test
@@ -0,0 +1,16 @@
+# name: test/sql/aggregate/aggregates/test_string_agg_big.test
+# description: STRING_AGG big
+# group: [aggregates]
+
+statement ok
+CREATE TABLE strings AS SELECT c::VARCHAR g, (c*10+e)::VARCHAR x FROM range(0, 100, 1) t1(c), range(0, 100, 1) t2(e);
+
+query I
+SELECT COUNT(*) FROM (SELECT g, STRING_AGG(x,',') FROM strings GROUP BY g) t1
+----
+100
+
+query I
+SELECT g, STRING_AGG(x,',') FROM strings GROUP BY g ORDER BY 1, 2
+----
+200 values hashing to b8126ea73f21372cdb3f2dc483106a12
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_string_agg_many_groups.test_slow b/modules/calcite/src/test/sql/aggregate/aggregates/test_string_agg_many_groups.test_slow
new file mode 100644
index 0000000..38ac86f
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_string_agg_many_groups.test_slow
@@ -0,0 +1,22 @@
+# name: test/sql/aggregate/aggregates/test_string_agg_many_groups.test_slow
+# description: Test STRING_AGG operator with many groups
+# group: [aggregates]
+
+# generate a table
+
+statement ok
+CREATE TABLE strings AS SELECT g, 'hello' x FROM range(0, 10000, 1) t1(g);
+
+query IT
+SELECT g, STRING_AGG(x, ',') FROM strings GROUP BY g ORDER BY g
+----
+20000 values hashing to acd848208cc35c7324ece9fcdd507823
+
+query IT
+SELECT 1, STRING_AGG(x, ',') FROM strings GROUP BY 1 ORDER BY 1
+----
+2 values hashing to 3bd0d16b476d2ffe18a77ebe1098b89c
+
+statement error
+SELECT STRING_AGG(k, ','), SUM(CAST(k AS BIGINT)) FROM (SELECT CAST(g AS VARCHAR) FROM strings UNION ALL SELECT CAST(x AS VARCHAR) FROM strings) tbl1(k)
+
diff --git a/modules/calcite/src/test/sql/aggregate/aggregates/test_sum.test b/modules/calcite/src/test/sql/aggregate/aggregates/test_sum.test
new file mode 100644
index 0000000..80bf112
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/aggregates/test_sum.test
@@ -0,0 +1,75 @@
+# name: test/sql/aggregate/aggregates/test_sum.test
+# description: Test sum aggregate
+# group: [aggregates]
+
+statement ok
+CREATE TABLE integers(i INTEGER);
+
+statement ok
+INSERT INTO integers SELECT * FROM range(0, 1000, 1);
+
+# positive numbers
+query I
+SELECT SUM(i) FROM integers;
+----
+499500
+
+# negative numbers
+statement ok
+INSERT INTO integers SELECT * FROM range(0, -1000, -1);
+
+query I
+SELECT SUM(i) FROM integers;
+----
+0
+
+# more negative numbers
+statement ok
+INSERT INTO integers SELECT * FROM range(0, -1000, -1);
+
+query I
+SELECT SUM(i) FROM integers;
+----
+-499500
+
+# now perform sum of a constant
+query I
+SELECT SUM(1) FROM integers;
+----
+3000
+
+# negative constant
+query I
+SELECT SUM(-1) FROM integers;
+----
+-3000
+
+# negative constant with a low amount of values
+query I
+SELECT SUM(-1) FROM integers WHERE i=-1;
+----
+-2
+
+# no values
+query I
+SELECT SUM(-1) FROM integers WHERE i>10000;
+----
+NULL
+
+# bigint sum
+statement ok
+CREATE TABLE bigints(b BIGINT);
+
+# a bunch of huge values
+statement ok
+INSERT INTO bigints SELECT * FROM range(4611686018427387904, 4611686018427388904, 1);
+
+# sum them up
+query I
+SELECT SUM(b) FROM bigints
+----
+4611686018427388403500
+
+# this is too big for a bigint
+statement error
+SELECT SUM(b)::BIGINT FROM bigints
diff --git a/modules/calcite/src/test/sql/aggregate/distinct/test_distinct.test b/modules/calcite/src/test/sql/aggregate/distinct/test_distinct.test
new file mode 100644
index 0000000..2fec755
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/distinct/test_distinct.test
@@ -0,0 +1,55 @@
+# name: test/sql/aggregate/distinct/test_distinct.test
+# description: Test DISTINCT keyword
+# group: [distinct]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE test (a INTEGER, b INTEGER);
+
+statement ok
+INSERT INTO test VALUES (11, 22), (13, 22), (11, 21), (11, 22)
+
+query II
+SELECT DISTINCT a, b FROM test ORDER BY a, b
+----
+11 21
+11 22
+13 22
+
+query II
+SELECT DISTINCT test.a, b FROM test ORDER BY a, b
+----
+11 21
+11 22
+13 22
+
+query I
+SELECT DISTINCT a FROM test ORDER BY a
+----
+11
+13
+
+query I
+SELECT DISTINCT b FROM test ORDER BY b
+----
+21
+22
+
+query IR
+SELECT DISTINCT a, SUM(B) FROM test GROUP BY a ORDER BY a
+----
+11 65.000000
+13 22.000000
+
+query I
+SELECT DISTINCT MAX(b) FROM test GROUP BY a
+----
+22
+
+query I
+SELECT DISTINCT CASE WHEN a > 11 THEN 11 ELSE a END FROM test
+----
+11
+
diff --git a/modules/calcite/src/test/sql/aggregate/distinct/test_distinct_on.test_ignored b/modules/calcite/src/test/sql/aggregate/distinct/test_distinct_on.test_ignored
new file mode 100644
index 0000000..7c65fd5
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/distinct/test_distinct_on.test_ignored
@@ -0,0 +1,133 @@
+# name: test/sql/aggregate/distinct/test_distinct_on.test
+# description: Test DISTINCT ON
+# group: [distinct]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14539
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE integers(i INTEGER, j INTEGER, k INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (2, 3, 5), (4, 5, 6), (2, 7, 6)
+
+query II
+SELECT DISTINCT ON (i) i, j FROM integers WHERE i <> 2
+----
+4 5
+
+query II
+SELECT DISTINCT ON (j) i, j FROM integers WHERE i <> 2
+----
+4 5
+
+query II
+SELECT DISTINCT ON (j, i) i, j FROM integers WHERE i <> 2
+----
+4 5
+
+query II
+SELECT DISTINCT ON (j + 1, i * 3) i, j FROM integers WHERE i <> 2
+----
+4 5
+
+query II
+SELECT DISTINCT ON (1) i, j FROM integers ORDER BY i
+----
+2 3
+4 5
+
+query II
+SELECT DISTINCT ON (1) i, j FROM integers ORDER BY i LIMIT 1
+----
+2 3
+
+query II
+SELECT DISTINCT ON (1) i, j FROM integers ORDER BY i LIMIT 1 OFFSET 1
+----
+4 5
+
+query II
+SELECT DISTINCT ON (2) i, j FROM integers ORDER BY 2
+----
+2 3
+4 5
+2 7
+
+query II
+SELECT DISTINCT ON (2) j, k FROM integers ORDER BY 2
+----
+3 5
+5 6
+
+query III
+SELECT DISTINCT ON (3) i, j, k FROM integers ORDER BY 2
+----
+2 3 5
+4 5 6
+
+query III
+SELECT DISTINCT ON (3) i, j, k FROM integers ORDER BY 3
+----
+2 3 5
+4 5 6
+
+query II
+SELECT DISTINCT ON (2) j, (SELECT i FROM integers) FROM integers ORDER BY 2
+----
+3 2
+
+query II
+SELECT DISTINCT ON (2) j, (SELECT DISTINCT ON (i) i FROM integers ORDER BY 1) FROM integers ORDER BY 2
+----
+3 2
+
+query II
+SELECT DISTINCT ON (i) i, j FROM integers ORDER BY j
+----
+2 3
+4 5
+
+query II
+SELECT * FROM (SELECT DISTINCT ON (i) i, j FROM integers) tbl1 WHERE i <> 2
+----
+4 5
+
+# order by a column that does not exist in the SELECT clause
+query II
+SELECT DISTINCT ON (i) i, j FROM integers ORDER BY k
+----
+2 3
+4 5
+
+# equivalent to this, but without projecting the k
+query III
+SELECT DISTINCT ON (i) i, j, k FROM integers ORDER BY k
+----
+2 3 5
+4 5 6
+
+# binding of DISTINCT ON with different column names
+query II
+SELECT DISTINCT ON (integers.i) i, j FROM integers ORDER BY 1, 2
+----
+2 3
+4 5
+
+query II
+SELECT DISTINCT ON (i) integers.i, integers.j FROM integers ORDER BY 1, 2
+----
+2 3
+4 5
+
+query II
+SELECT DISTINCT ON (integers.i) integers.i, integers.j FROM integers ORDER BY i, j
+----
+2 3
+4 5
+
+# out of bounds
+statement error
+SELECT DISTINCT ON (2) i FROM integers
+
diff --git a/modules/calcite/src/test/sql/aggregate/distinct/test_distinct_order_by.test b/modules/calcite/src/test/sql/aggregate/distinct/test_distinct_order_by.test
new file mode 100644
index 0000000..e3b1ffa
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/distinct/test_distinct_order_by.test
@@ -0,0 +1,41 @@
+# name: test/sql/aggregate/distinct/test_distinct_order_by.test
+# description: Test DISTINCT and ORDER BY
+# group: [distinct]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE integers(i INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (1), (2), (3)
+
+query I
+SELECT DISTINCT mod(i, 2) FROM integers ORDER BY 1
+----
+0
+1
+
+# binding of DISTINCT with column names
+query I
+SELECT DISTINCT integers.i FROM integers ORDER BY i DESC
+----
+3
+2
+1
+
+query I
+SELECT DISTINCT i FROM integers ORDER BY integers.i DESC
+----
+3
+2
+1
+
+query I
+SELECT DISTINCT integers.i FROM integers ORDER BY integers.i DESC
+----
+3
+2
+1
+
diff --git a/modules/calcite/src/test/sql/aggregate/group/test_group_by.test b/modules/calcite/src/test/sql/aggregate/group/test_group_by.test
new file mode 100644
index 0000000..816402a
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/group/test_group_by.test
@@ -0,0 +1,181 @@
+# name: test/sql/aggregate/group/test_group_by.test
+# description: Test aggregation/group by statements
+# group: [group]
+
+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)
+
+# aggregates cannot be nested
+statement error
+SELECT SUM(SUM(41)), COUNT(*);
+
+# simple aggregates without group by
+query RIR
+SELECT SUM(a), COUNT(*), AVG(a) FROM test;
+----
+36.000000 3 12.000000
+
+query I
+SELECT COUNT(*) FROM test;
+----
+3
+
+query RI
+SELECT SUM(a), COUNT(*) FROM test WHERE a = 11;
+----
+11.000000 1
+
+query RRR
+SELECT SUM(a), SUM(b), SUM(a) + SUM (b) FROM test;
+----
+36.000000 65.000000 101.000000
+
+query RR
+SELECT SUM(a+2), SUM(a) + 2 * COUNT(*) FROM test;
+----
+42.000000 42.000000
+
+# aggregations with group by
+query IRRR
+SELECT b, SUM(a), SUM(a+2), AVG(a) FROM test GROUP BY b ORDER BY b;
+----
+21 12.000000 14.000000 12.000000
+22 24.000000 28.000000 12.000000
+
+# ORDER BY aggregation that does not occur in SELECT clause
+query IR
+SELECT b, SUM(a) FROM test GROUP BY b ORDER BY COUNT(a);
+----
+21 12.000000
+22 24.000000
+
+query IR
+SELECT b, SUM(a) FROM test GROUP BY b ORDER BY COUNT(a) DESC;
+----
+22 24.000000
+21 12.000000
+
+query IRIR
+SELECT b, SUM(a), COUNT(*), SUM(a+2) FROM test GROUP BY b ORDER BY b;
+----
+21 12.000000 1 14.000000
+22 24.000000 2 28.000000
+
+# group by alias
+query IR
+SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f;
+----
+0 24.000000
+1 12.000000
+
+# group by with filter
+query IRIR
+SELECT b, SUM(a), COUNT(*), SUM(a+2) FROM test WHERE a <= 12 GROUP BY b ORDER BY b;
+----
+21 12.000000 1 14.000000
+22 11.000000 1 13.000000
+
+# nested aggregate in group by
+statement error
+SELECT b % 2 AS f, COUNT(SUM(a)) FROM test GROUP BY f;
+
+statement ok
+INSERT INTO test VALUES (12, 21), (12, 21), (12, 21)
+
+# group by with filter and multiple values per groups
+query IRIR
+SELECT b, SUM(a), COUNT(*), SUM(a+2) FROM test WHERE a <= 12 GROUP BY b ORDER BY b;
+----
+21 48.000000 4 56.000000
+22 11.000000 1 13.000000
+
+# group by with filter and multiple values per groups
+statement ok
+CREATE TABLE integers(i INTEGER, j INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (3, 4), (3, 4), (2, 4);
+
+# use GROUP BY column in math operator
+query II
+SELECT i, i + 10 FROM integers GROUP BY i ORDER BY i
+----
+2 12
+3 13
+
+# using non-group column and non-aggregate should throw an error
+statement error
+SELECT i, SUM(j), j FROM integers GROUP BY i ORDER BY i
+
+# but it works if we wrap it in FIRST()
+query IRI
+SELECT i, SUM(j), FIRST(j) FROM integers GROUP BY i ORDER BY i
+----
+2 4.000000 4
+3 8.000000 4
+
+# group by constant alias
+query IR
+SELECT 1 AS k, SUM(i) FROM integers GROUP BY k ORDER BY 2;
+----
+1 8.000000
+
+# use an alias that is identical to a column name (should prioritize column name)
+query IR
+SELECT 1 AS i, SUM(i) FROM integers GROUP BY i ORDER BY 2;
+----
+1 2.000000
+1 6.000000
+
+# refer to the same alias twice
+query IR
+SELECT i % 2 AS k, SUM(i) FROM integers GROUP BY k, k ORDER BY 1;
+----
+0 2.000000
+1 6.000000
+
+statement ok
+DROP TABLE integers;
+
+statement ok
+CREATE TABLE integers(i INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (1), (2), (3), (NULL);
+
+# group by NULL
+query IR
+SELECT i, SUM(i) FROM integers GROUP BY i ORDER BY 1;
+----
+NULL NULL
+1 1.000000
+2 2.000000
+3 3.000000
+
+# column reference should have preference over alias reference in grouping
+query IIR
+SELECT i, i % 2 AS i, SUM(i) FROM integers GROUP BY i ORDER BY 1;
+----
+NULL NULL NULL
+1 1 1.000000
+2 0 2.000000
+3 1 3.000000
+
+# aliases can only be referenced in the GROUP BY as the root column: operations not allowed
+# CONTROVERSIAL: this query DOES work in SQLite
+statement error
+SELECT 1 AS k, SUM(i) FROM integers GROUP BY k+1 ORDER BY 2;
+
+# group by column refs should be recognized, even if one uses an explicit table specifier and the other does not
+query IR
+SELECT test.b, SUM(a) FROM test GROUP BY b ORDER BY COUNT(a) DESC;
+----
+21 48.000000
+22 24.000000
+
diff --git a/modules/calcite/src/test/sql/aggregate/group/test_group_by_alias.test b/modules/calcite/src/test/sql/aggregate/group/test_group_by_alias.test
new file mode 100644
index 0000000..72d7fe6
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/group/test_group_by_alias.test
@@ -0,0 +1,96 @@
+# name: test/sql/aggregate/group/test_group_by_alias.test
+# description: Test aliases in group by/aggregation
+# group: [group]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE integers(i INTEGER)
+
+statement ok
+INSERT INTO integers VALUES (1), (2), (3), (NULL)
+
+# use alias in HAVING clause
+# CONTROVERSIAL: this query DOES NOT work in PostgreSQL
+query IR
+SELECT i % 2 AS k, SUM(i) FROM integers WHERE i IS NOT NULL GROUP BY k HAVING k>0;
+----
+1 4.000000
+
+# this is identical to this query
+# CONTROVERSIAL: this query does not work in MonetDB
+query IR
+SELECT i % 2 AS k, SUM(i) FROM integers WHERE i IS NOT NULL GROUP BY k HAVING i%2>0;
+----
+1 4.000000
+
+# select groups by constant (similar to order by constant)
+query IR
+SELECT i % 2 AS k, SUM(i) FROM integers WHERE i IS NOT NULL GROUP BY 1 HAVING i%2>0;
+----
+1 4.000000
+
+# constant out of range
+statement error
+SELECT i % 2 AS k, SUM(i) FROM integers WHERE i IS NOT NULL GROUP BY 42 HAVING i%2>0;
+
+# entry in GROUP BY should refer to base column
+# ...BUT the alias in ORDER BY should refer to the alias from the select list
+# note that both Postgres and MonetDB reject this query because of ambiguity. SQLite accepts it though so we do
+# too.
+query IIR
+SELECT i, i % 2 AS i, SUM(i) FROM integers GROUP BY i ORDER BY i, 3;
+----
+NULL NULL NULL
+2 0 2.000000
+1 1 1.000000
+3 1 3.000000
+
+# changing the name of the alias makes it more explicit what should happen
+query IIR
+SELECT i, i % 2 AS k, SUM(i) FROM integers GROUP BY i ORDER BY k, 3;
+----
+NULL NULL NULL
+2 0 2.000000
+1 1 1.000000
+3 1 3.000000
+
+# this now orders by the actual grouping column
+query IIR
+SELECT i, i % 2 AS k, SUM(i) FROM integers GROUP BY i ORDER BY i;
+----
+NULL NULL NULL
+1 1 1.000000
+2 0 2.000000
+3 1 3.000000
+
+# cannot use GROUP BY column in an aggregation...
+statement error
+SELECT i % 2 AS k, SUM(k) FROM integers GROUP BY k
+
+# ...unless it is one of the base columns
+query IR
+SELECT i, SUM(i) FROM integers GROUP BY i ORDER BY i
+----
+NULL NULL
+1 1.000000
+2 2.000000
+3 3.000000
+
+# ORDER on a non-grouping column
+# this query is refused by Postgres and MonetDB
+# but SQLite resolves it by first pushing a "FIRST(i)" aggregate into the projection, and then ordering by that
+# aggregate
+statement error
+SELECT (10-i) AS k, SUM(i) FROM integers GROUP BY k ORDER BY i;
+
+# we can manually get this behavior by pushing FIRST
+query IR
+SELECT (10-i) AS k, SUM(i) FROM integers GROUP BY k ORDER BY FIRST(i);
+----
+NULL NULL
+9 1.000000
+8 2.000000
+7 3.000000
+
diff --git a/modules/calcite/src/test/sql/aggregate/group/test_group_by_large_string.test b/modules/calcite/src/test/sql/aggregate/group/test_group_by_large_string.test
new file mode 100644
index 0000000..f6b56e2
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/group/test_group_by_large_string.test
@@ -0,0 +1,16 @@
+# name: test/sql/aggregate/group/test_group_by_large_string.test
+# description: GROUP BY large strings
+# group: [group]
+
+statement ok
+CREATE TABLE test (a VARCHAR, b INTEGER);
+
+statement ok
+INSERT INTO test VALUES ('helloworld', 22), ('thisisalongstring', 22), ('helloworld', 21)
+
+query TR
+SELECT a, SUM(b) FROM test GROUP BY a ORDER BY a
+----
+helloworld 43.000000
+thisisalongstring 22.000000
+
diff --git a/modules/calcite/src/test/sql/aggregate/group/test_group_by_multi_column.test b/modules/calcite/src/test/sql/aggregate/group/test_group_by_multi_column.test
new file mode 100644
index 0000000..64b6efe
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/group/test_group_by_multi_column.test
@@ -0,0 +1,17 @@
+# name: test/sql/aggregate/group/test_group_by_multi_column.test
+# description: Group by multiple columns
+# group: [group]
+
+statement ok
+CREATE TABLE integers(i INTEGER, j INTEGER, k INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (1, 1, 2), (1, 2, 2), (1, 1, 2), (2, 1, 2), (1, 2, 4), (1, 2, NULL);
+
+query IIRII
+SELECT i, j, SUM(k), COUNT(*), COUNT(k) FROM integers GROUP BY i, j ORDER BY 1, 2
+----
+1 1 4.000000 2 2
+1 2 6.000000 3 2
+2 1 2.000000 1 1
+
diff --git a/modules/calcite/src/test/sql/aggregate/group/test_group_null.test b/modules/calcite/src/test/sql/aggregate/group/test_group_null.test
new file mode 100644
index 0000000..a3afef6
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/group/test_group_null.test
@@ -0,0 +1,17 @@
+# name: test/sql/aggregate/group/test_group_null.test
+# description: GROUP BY NULL value
+# group: [group]
+
+statement ok
+CREATE TABLE integers(i INTEGER, j INTEGER);
+
+statement ok
+INSERT INTO integers VALUES (3, 4), (NULL, 4), (2, 4);
+
+query IR
+SELECT i, SUM(j) FROM integers GROUP BY i ORDER BY i
+----
+NULL 4.000000
+2 4.000000
+3 4.000000
+
diff --git a/modules/calcite/src/test/sql/aggregate/having/test_corel_subquery_in_having.test_ignored b/modules/calcite/src/test/sql/aggregate/having/test_corel_subquery_in_having.test_ignored
new file mode 100644
index 0000000..24f16d8
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/having/test_corel_subquery_in_having.test_ignored
@@ -0,0 +1,38 @@
+# name: test/sql/aggregate/having/test_having.test
+# description: Test HAVING clause
+# group: [having]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14540
+
+statement ok
+CREATE TABLE test (a INTEGER, b INTEGER);
+
+statement ok
+INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)
+
+# correlated subquery in having
+query IR
+SELECT test.b, SUM(a) FROM test GROUP BY test.b HAVING SUM(a)=(SELECT SUM(a) FROM test t WHERE test.b=t.b) ORDER BY test.b;
+----
+21 12.000000
+22 24.000000
+
+# use outer aggregation in inner subquery
+query IR
+SELECT test.b, SUM(a) FROM test GROUP BY test.b HAVING SUM(a)*2=(SELECT SUM(a)+SUM(t.a) FROM test t WHERE test.b=t.b) ORDER BY test.b
+----
+21 12.000000
+22 24.000000
+
+# use outer aggregation that hasn't been used yet in subquery
+query IR
+SELECT test.b, SUM(a) FROM test GROUP BY test.b HAVING SUM(a)*2+2=(SELECT SUM(a)+SUM(t.a)+COUNT(t.a) FROM test t WHERE test.b=t.b) ORDER BY test.b
+----
+22 24.000000
+
+# ORDER BY subquery
+query IR
+SELECT test.b, SUM(a) FROM test GROUP BY test.b ORDER BY (SELECT SUM(a) FROM test t WHERE test.b=t.b) DESC;
+----
+22 24.000000
+21 12.000000
+
diff --git a/modules/calcite/src/test/sql/aggregate/having/test_having.test b/modules/calcite/src/test/sql/aggregate/having/test_having.test
new file mode 100644
index 0000000..31a23f0
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/having/test_having.test
@@ -0,0 +1,45 @@
+# name: test/sql/aggregate/having/test_having.test
+# description: Test HAVING clause
+# group: [having]
+
+statement ok
+CREATE TABLE test (a INTEGER, b INTEGER);
+
+statement ok
+INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)
+
+# HAVING with condition on group
+query IR
+SELECT b, SUM(a) AS sum FROM test GROUP BY b HAVING b=21 ORDER BY b;
+----
+21 12.000000
+
+# HAVING with condition on sum
+query IR
+SELECT b, SUM(a) FROM test GROUP BY b HAVING SUM(a) < 20 ORDER BY b;
+----
+21 12.000000
+
+# HAVING without alias
+query IR
+SELECT b, SUM(a) AS sum FROM test GROUP BY b HAVING SUM(a) < 20 ORDER BY b;
+----
+21 12.000000
+
+# HAVING on column not in aggregate
+query IR
+SELECT b, SUM(a) AS sum FROM test GROUP BY b HAVING COUNT(*) = 1 ORDER BY b;
+----
+21 12.000000
+
+# expression in having
+query IR
+SELECT b, SUM(a) FROM test GROUP BY b HAVING SUM(a)+10>28;
+----
+22 24.000000
+
+# uncorrelated subquery in having
+query IR
+SELECT b, SUM(a) FROM test GROUP BY b HAVING SUM(a)>(SELECT SUM(t.a)*0.5 FROM test t);
+----
+22 24.000000
diff --git a/modules/calcite/src/test/sql/aggregate/having/test_scalar_having.test_ignored b/modules/calcite/src/test/sql/aggregate/having/test_scalar_having.test_ignored
new file mode 100644
index 0000000..d3c4dd5
--- /dev/null
+++ b/modules/calcite/src/test/sql/aggregate/having/test_scalar_having.test_ignored
@@ -0,0 +1,81 @@
+# name: test/sql/aggregate/having/test_scalar_having.test
+# description: Test HAVING clause without GROUP BY
+# group: [having]
+
+# CONTROVERSIAL: HAVING without GROUP BY works in PostgreSQL, but not in SQLite
+# scalar HAVING queries
+# constants only
+query I
+SELECT 42 HAVING 42 > 20
+----
+42
+
+query I
+SELECT 42 HAVING 42 > 80
+----
+
+# aggregates
+query R
+SELECT SUM(42) HAVING AVG(42) > MIN(20)
+----
+42.000000
+
+query R
+SELECT SUM(42) HAVING SUM(42) > SUM(80)
+----
+
+query RI
+SELECT SUM(42)+COUNT(*)+COUNT(1), 3 HAVING SUM(42)+MAX(20)+AVG(30) > SUM(120)-MIN(100)
+----
+44.000000 3
+
+# subqueries
+query R
+SELECT SUM(42) HAVING (SELECT SUM(42)) > SUM(80)
+----
+
+statement ok
+CREATE TABLE test (a INTEGER, b INTEGER);
+
+statement ok
+INSERT INTO test VALUES (11, 22), (13, 22), (12, 21)
+
+# HAVING with column references does not work
+# HAVING clause can only contain aggregates
+statement error
+SELECT a FROM test WHERE a=13 HAVING a > 11
+
+# HAVING clause also turns the rest of the query into an aggregate
+# thus column references in SELECT clause also produce errors
+statement error
+SELECT a FROM test WHERE a=13 HAVING SUM(a) > 11
+
+# once we produce a sum this works though
+query R
+SELECT SUM(a) FROM test WHERE a=13 HAVING SUM(a) > 11
+----
+13.000000
+
+query R
+SELECT SUM(a) FROM test WHERE a=13 HAVING SUM(a) > 20
+----
+
+# HAVING with single-node aggregation does work, even without GROUP BY
+query R
+SELECT SUM(a) FROM test HAVING SUM(a)>10;
+----
+36.000000
+
+query R
+SELECT SUM(a) FROM test HAVING SUM(a)<10;
+----
+
+query R
+SELECT SUM(a) FROM test HAVING COUNT(*)>1;
+----
+36.000000
+
+query R
+SELECT SUM(a) FROM test HAVING COUNT(*)>10;
+----
+
diff --git a/modules/calcite/src/test/sql/cast/test_boolean_cast.test b/modules/calcite/src/test/sql/cast/test_boolean_cast.test
new file mode 100644
index 0000000..6fb092f
--- /dev/null
+++ b/modules/calcite/src/test/sql/cast/test_boolean_cast.test
@@ -0,0 +1,170 @@
+# name: test/sql/cast/test_boolean_cast.test
+# description: Test boolean casts
+# group: [cast]
+
+statement ok
+PRAGMA enable_verification
+
+query T
+SELECT CAST(1=1 AS VARCHAR)
+----
+true
+
+query T
+SELECT CAST(1=0 AS VARCHAR)
+----
+false
+
+query T
+SELECT CAST('true' AS BOOLEAN)
+----
+1
+
+query T
+SELECT CAST('t' AS BOOLEAN)
+----
+1
+
+query T
+SELECT CAST('TRUE' AS BOOLEAN)
+----
+1
+
+query T
+SELECT CAST('false' AS BOOLEAN)
+----
+0
+
+query T
+SELECT CAST('f' AS BOOLEAN)
+----
+0
+
+query T
+SELECT CAST('FALSE' AS BOOLEAN)
+----
+0
+
+statement error
+SELECT CAST('12345' AS BOOLEAN)
+
+query T
+SELECT CAST(CAST('12345' AS INTEGER) AS BOOLEAN)
+----
+1
+
+query T
+SELECT CAST(CAST('0' AS INTEGER) AS BOOLEAN)
+----
+0
+
+query T
+SELECT CAST(CAST('1' AS tinyint) AS BOOLEAN)
+----
+1
+
+query T
+SELECT CAST(CAST('0' AS tinyint) AS BOOLEAN)
+----
+0
+
+query T
+SELECT CAST(CAST('1' AS smallint) AS BOOLEAN)
+----
+1
+
+query T
+SELECT CAST(CAST('0' AS smallint) AS BOOLEAN)
+----
+0
+
+query T
+SELECT CAST(CAST('1' AS integer) AS BOOLEAN)
+----
+1
+
+query T
+SELECT CAST(CAST('0' AS integer) AS BOOLEAN)
+----
+0
+
+query T
+SELECT CAST(CAST('1' AS bigint) AS BOOLEAN)
+----
+1
+
+query T
+SELECT CAST(CAST('0' AS bigint) AS BOOLEAN)
+----
+0
+
+query T
+SELECT CAST(CAST('1' AS decimal) AS BOOLEAN)
+----
+1
+
+query T
+SELECT CAST(CAST('0' AS decimal) AS BOOLEAN)
+----
+0
+
+query T
+SELECT CAST(CAST('1' AS decimal(1,0)) AS BOOLEAN)
+----
+1
+
+query T
+SELECT CAST(CAST('0' AS decimal(1,0)) AS BOOLEAN)
+----
+0
+
+query T
+SELECT CAST(CAST('1' AS decimal(9,0)) AS BOOLEAN)
+----
+1
+
+query T
+SELECT CAST(CAST('0' AS decimal(9,0)) AS BOOLEAN)
+----
+0
+
+query T
+SELECT CAST(CAST('1' AS decimal(38,0)) AS BOOLEAN)
+----
+1
+
+query T
+SELECT CAST(CAST('0' AS decimal(38,0)) AS BOOLEAN)
+----
+0
+
+query T
+SELECT CAST(CAST('1' AS float) AS BOOLEAN)
+----
+1
+
+query T
+SELECT CAST(CAST('0' AS float) AS BOOLEAN)
+----
+0
+
+query T
+SELECT CAST(CAST('1' AS double) AS BOOLEAN)
+----
+1
+
+query T
+SELECT CAST(CAST('0' AS double) AS BOOLEAN)
+----
+0
+
+query T
+SELECT CAST(CAST('1' AS HUGEINT) AS BOOLEAN)
+----
+1
+
+query T
+SELECT CAST(CAST('0' AS HUGEINT) AS BOOLEAN)
+----
+0
+
diff --git a/modules/calcite/src/test/sql/cast/test_exponent_in_cast.test b/modules/calcite/src/test/sql/cast/test_exponent_in_cast.test
new file mode 100644
index 0000000..58e7a21
--- /dev/null
+++ b/modules/calcite/src/test/sql/cast/test_exponent_in_cast.test
@@ -0,0 +1,44 @@
+# name: test/sql/cast/test_exponent_in_cast.test
+# description: Test exponents in cast
+# group: [cast]
+
+statement ok
+PRAGMA enable_verification
+
+statement error
+SELECT CAST('e1' AS INTEGER);
+
+statement error
+SELECT CAST(' e1' AS INTEGER);
+
+statement error
+SELECT CAST(' E1' AS INTEGER);
+
+statement error
+SELECT CAST('e1' AS DOUBLE);
+
+statement error
+SELECT CAST(' e1' AS DOUBLE);
+
+statement error
+SELECT CAST(' E1' AS DOUBLE);
+
+query I
+SELECT CAST('1e1' AS INTEGER);
+----
+10
+
+query I
+SELECT CAST(' 1e1' AS INTEGER);
+----
+10
+
+query I
+SELECT CAST('1e1' AS DOUBLE);
+----
+10.0
+
+query I
+SELECT CAST(' 1e1' AS DOUBLE);
+----
+10.0
\ No newline at end of file
diff --git a/modules/calcite/src/test/sql/cast/test_string_cast.test b/modules/calcite/src/test/sql/cast/test_string_cast.test
new file mode 100644
index 0000000..b421001
--- /dev/null
+++ b/modules/calcite/src/test/sql/cast/test_string_cast.test
@@ -0,0 +1,55 @@
+# name: test/sql/cast/test_string_cast.test
+# description: Test string casts
+# group: [cast]
+
+statement ok
+PRAGMA enable_verification
+
+query TTT
+SELECT (1=1)::VARCHAR, (1=0)::VARCHAR, NULL::BOOLEAN::VARCHAR
+----
+true
+false
+NULL
+
+query TTT
+SELECT 1::TINYINT::VARCHAR, 12::TINYINT::VARCHAR, (-125)::TINYINT::VARCHAR
+----
+1
+12
+-125
+
+query TTT
+SELECT 1::SMALLINT::VARCHAR, 12442::SMALLINT::VARCHAR, (-32153)::SMALLINT::VARCHAR
+----
+1
+12442
+-32153
+
+query TTT
+SELECT 1::INTEGER::VARCHAR, 12442952::INTEGER::VARCHAR, (-2000000111)::INTEGER::VARCHAR
+----
+1
+12442952
+-2000000111
+
+query TTT
+SELECT 1::BIGINT::VARCHAR, 1244295295289253::BIGINT::VARCHAR, (-2000000111551166)::BIGINT::VARCHAR
+----
+1
+1244295295289253
+-2000000111551166
+
+query TTT
+SELECT 2::FLOAT::VARCHAR, 0.5::FLOAT::VARCHAR, (-128.5)::FLOAT::VARCHAR
+----
+2.0
+0.5
+-128.5
+
+query TTT
+SELECT 2::DOUBLE::VARCHAR, 0.5::DOUBLE::VARCHAR, (-128.5)::DOUBLE::VARCHAR
+----
+2.0
+0.5
+-128.5
diff --git a/modules/calcite/src/test/sql/cast/test_try_cast.test b/modules/calcite/src/test/sql/cast/test_try_cast.test
new file mode 100644
index 0000000..1205892
--- /dev/null
+++ b/modules/calcite/src/test/sql/cast/test_try_cast.test
@@ -0,0 +1,22 @@
+# name: test/sql/cast/test_try_cast.test
+# description: Test try cast
+# group: [cast]
+
+statement ok
+PRAGMA enable_verification
+
+# try cast is only implemented in the parser for now: we expect this to fail in the binding phase
+statement error
+SELECT TRY_CAST('hello' as INTEGER)
+
+# not a reserved keyword
+statement ok
+CREATE TABLE try_cast(try_cast INTEGER);
+
+statement ok
+INSERT INTO try_cast VALUES (3);
+
+query I
+SELECT try_cast FROM try_cast;
+----
+3
diff --git a/modules/calcite/src/test/sql/delete/test_delete.test b/modules/calcite/src/test/sql/delete/test_delete.test
new file mode 100644
index 0000000..268c532
--- /dev/null
+++ b/modules/calcite/src/test/sql/delete/test_delete.test
@@ -0,0 +1,52 @@
+# name: test/sql/delete/test_delete.test
+# description: Test deletions
+# group: [delete]
+
+statement ok
+CREATE TABLE a(i INTEGER);
+
+statement ok
+INSERT INTO a VALUES (42);
+
+query I
+SELECT COUNT(*) FROM a;
+----
+1
+
+# delete everything
+statement ok
+DELETE FROM a;
+
+query I
+SELECT COUNT(*) FROM a;
+----
+0
+
+statement ok
+INSERT INTO a VALUES (42);
+
+query I
+SELECT COUNT(*) FROM a;
+----
+1
+
+# now test rollback of deletion
+statement ok
+BEGIN TRANSACTION
+
+statement ok
+DELETE FROM a;
+
+query I
+SELECT COUNT(*) FROM a;
+----
+0
+
+statement ok
+ROLLBACK
+
+query I
+SELECT COUNT(*) FROM a;
+----
+1
+
diff --git a/modules/calcite/src/test/sql/delete/test_large_delete.test b/modules/calcite/src/test/sql/delete/test_large_delete.test
new file mode 100644
index 0000000..fb25565
--- /dev/null
+++ b/modules/calcite/src/test/sql/delete/test_large_delete.test
@@ -0,0 +1,15 @@
+# name: test/sql/delete/test_large_delete.test
+# description: Test scan with large deletions
+# group: [delete]
+
+statement ok
+CREATE TABLE a AS SELECT * FROM range(0, 10000, 1) t1(i);
+
+statement ok
+DELETE FROM a WHERE i >= 2000 AND i < 5000;
+
+query I
+SELECT COUNT(*) FROM a;
+----
+7000
+
diff --git a/modules/calcite/src/test/sql/delete/test_segment_deletes.test b/modules/calcite/src/test/sql/delete/test_segment_deletes.test
new file mode 100644
index 0000000..b8e18fe
--- /dev/null
+++ b/modules/calcite/src/test/sql/delete/test_segment_deletes.test
@@ -0,0 +1,179 @@
+# name: test/sql/delete/test_segment_deletes.test
+# description: Test deletions
+# group: [delete]
+
+statement ok con1
+CREATE TABLE a(i INTEGER);
+
+# insert the values [0, 1, 2, .. 1022, 1023] repeatedly
+# however, make sure the order that we insert them is
+loop i 0 20
+
+statement ok con1
+INSERT INTO a SELECT * FROM range(0, 1024, 1);
+
+endloop
+
+# verify the count
+
+query I con1
+SELECT COUNT(*) FROM a
+----
+20480
+
+# we test the values 0, 1, 1022 and 1023
+# for every tested value, delete it and then rollback
+
+# value = 0
+statement ok con1
+BEGIN TRANSACTION;
+DELETE FROM a WHERE i=0;
+
+# verify the deleted count
+query I con1
+SELECT COUNT(*) FROM a
+----
+20460
+
+statement ok con1
+ROLLBACK
+
+# verify the initial count
+query I con1
+SELECT COUNT(*) FROM a
+----
+20480
+
+# value = 1
+statement ok con1
+BEGIN TRANSACTION;
+DELETE FROM a WHERE i=1;
+
+# verify the deleted count
+query I con1
+SELECT COUNT(*) FROM a
+----
+20460
+
+statement ok con1
+ROLLBACK
+
+# verify the initial count
+query I con1
+SELECT COUNT(*) FROM a
+----
+20480
+
+# value = 1022
+statement ok con1
+BEGIN TRANSACTION;
+DELETE FROM a WHERE i=1022;
+
+# verify the deleted count
+query I con1
+SELECT COUNT(*) FROM a
+----
+20460
+
+statement ok con1
+ROLLBACK
+
+# verify the initial count
+query I con1
+SELECT COUNT(*) FROM a
+----
+20480
+
+# value = 1023
+statement ok con1
+BEGIN TRANSACTION;
+DELETE FROM a WHERE i=1023;
+
+# verify the deleted count
+query I con1
+SELECT COUNT(*) FROM a
+----
+20460
+
+statement ok con1
+ROLLBACK
+
+# verify the initial count
+query I con1
+SELECT COUNT(*) FROM a
+----
+20480
+
+# now, for every tested value, delete it in a separate connection and verify the count
+# con2 -> 0
+statement ok con2
+BEGIN TRANSACTION;
+
+statement ok con2
+DELETE FROM a WHERE i=0;
+
+query I con2
+SELECT COUNT(*) FROM a;
+----
+20460
+
+# con3 -> 1
+statement ok con3
+BEGIN TRANSACTION;
+
+statement ok con3
+DELETE FROM a WHERE i=1;
+
+query I con3
+SELECT COUNT(*) FROM a;
+----
+20460
+
+# con4 -> 1022
+statement ok con4
+BEGIN TRANSACTION;
+
+statement ok con4
+DELETE FROM a WHERE i=1022;
+
+query I con4
+SELECT COUNT(*) FROM a;
+----
+20460
+
+# con5 -> 1023
+statement ok con5
+BEGIN TRANSACTION;
+
+statement ok con5
+DELETE FROM a WHERE i=1023;
+
+query I con5
+SELECT COUNT(*) FROM a;
+----
+20460
+
+# con1 still has the original count
+query I con1
+SELECT COUNT(*) FROM a;
+----
+20480
+
+# until we update the other transactions
+statement ok con2
+COMMIT
+
+statement ok con3
+COMMIT
+
+statement ok con4
+COMMIT
+
+statement ok con5
+COMMIT
+
+# now the count is updated
+query I con1
+SELECT COUNT(*) FROM a;
+----
+20400
\ No newline at end of file
diff --git a/modules/calcite/src/test/sql/filter/test_alias_filter.test b/modules/calcite/src/test/sql/filter/test_alias_filter.test
new file mode 100644
index 0000000..0f0c7f49
--- /dev/null
+++ b/modules/calcite/src/test/sql/filter/test_alias_filter.test
@@ -0,0 +1,20 @@
+# name: test/sql/filter/test_alias_filter.test
+# description: Test filter on alias
+# group: [filter]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE integers(i INTEGER)
+
+statement ok
+INSERT INTO integers VALUES (1), (2), (3), (NULL)
+
+# this fails in postgres and monetdb, but succeeds in sqlite
+# for now, we have this fail as well because it simplifies our life
+# the filter occurs before the projection, hence "j" is not computed until AFTER the filter normally
+# we probably want to change this to succeed
+statement error
+SELECT i % 2 AS j FROM integers WHERE j<>0;
+
diff --git a/modules/calcite/src/test/sql/filter/test_constant_comparisons.test b/modules/calcite/src/test/sql/filter/test_constant_comparisons.test
new file mode 100644
index 0000000..e6c9ba2
--- /dev/null
+++ b/modules/calcite/src/test/sql/filter/test_constant_comparisons.test
@@ -0,0 +1,103 @@
+# name: test/sql/filter/test_constant_comparisons.test
+# description: Test expressions with constant comparisons
+# group: [filter]
+
+statement ok
+PRAGMA enable_verification
+
+# Test various simple constant comparisons
+# The main point here is to check that the optimizer handles all these cases correctly
+
+statement ok
+CREATE TABLE integers(a INTEGER, b INTEGER)
+
+statement ok
+INSERT INTO integers VALUES (2, 12)
+
+query II
+SELECT * FROM integers WHERE 2=2
+----
+2 12
+
+statement ok
+SELECT * FROM integers WHERE 2=3
+
+query II
+SELECT * FROM integers WHERE 2<>3
+----
+2 12
+
+statement ok
+SELECT * FROM integers WHERE 2<>2
+
+query II
+SELECT * FROM integers WHERE 2>1
+----
+2 12
+
+statement ok
+SELECT * FROM integers WHERE 2>2
+
+query II
+SELECT * FROM integers WHERE 2>=2
+----
+2 12
+
+statement ok
+SELECT * FROM integers WHERE 2>=3
+
+query II
+SELECT * FROM integers WHERE 2<3
+----
+2 12
+
+statement ok
+SELECT * FROM integers WHERE 2<2
+
+query II
+SELECT * FROM integers WHERE 2<=2
+----
+2 12
+
+statement ok
+SELECT * FROM integers WHERE 2<=1
+
+query T
+SELECT a=NULL FROM integers
+----
+NULL
+
+query T
+SELECT NULL=a FROM integers
+----
+NULL
+
+query II
+SELECT * FROM integers WHERE 2 IN (2, 3, 4, 5)
+----
+2 12
+
+statement ok
+SELECT * FROM integers WHERE 2 NOT IN (2, 3, 4, 5)
+
+query II
+SELECT * FROM integers WHERE 2 IN (((1*2)+(1*0))*1, 3, 4, 5)
+----
+2 12
+
+statement ok
+SELECT * FROM integers WHERE 2 IN ((1+1)*2, 3, 4, 5)
+
+query I
+SELECT CASE WHEN 1 THEN 13 ELSE 12 END;
+----
+13
+
+query II
+SELECT * FROM integers WHERE CASE WHEN 2=2 THEN true ELSE false END;
+----
+2 12
+
+statement ok
+SELECT * FROM integers WHERE CASE WHEN 2=3 THEN true ELSE false END;
+
diff --git a/modules/calcite/src/test/sql/filter/test_filter_clause.test b/modules/calcite/src/test/sql/filter/test_filter_clause.test
new file mode 100644
index 0000000..6b261c1
--- /dev/null
+++ b/modules/calcite/src/test/sql/filter/test_filter_clause.test
@@ -0,0 +1,542 @@
+# name: test/sql/filter/test_filter_clause.test
+# description: Test aggregation with filter clause
+# group: [filter]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+create temporary table t as select range i, mod(range,10) j,mod(range,10)*10000000 k from range(1000);
+
+
+query II
+SELECT
+ COUNT(*) AS unfiltered,
+ COUNT(*) FILTER (WHERE i < 5) AS filtered
+FROM t;
+----
+1000 5
+
+query II
+SELECT
+ COUNT(*) AS unfiltered,
+ COUNT(*) FILTER (WHERE i > 5 and i < 10) AS filtered
+FROM t;
+----
+1000 4
+
+query II
+SELECT
+ SUM(i) AS unfiltered,
+ SUM(i) FILTER (WHERE i < 5) AS filtered
+FROM t;
+----
+499500 10
+
+query II
+SELECT
+ SUM(i) AS unfiltered,
+ SUM(i) FILTER (WHERE i between 5 and 10) AS filtered
+FROM t;
+----
+499500 45
+
+query II
+SELECT
+ SUM(i) AS unfiltered,
+ SUM(j) FILTER (WHERE j < 2) AS filtered
+FROM t;
+----
+499500 100
+
+
+query I
+SELECT
+ SUM(j) FILTER (WHERE i < 10)
+FROM t;
+----
+45
+
+query I
+SELECT
+ SUM(j) FILTER (WHERE i < (select 10))
+FROM t;
+----
+45
+
+query I
+SELECT
+ SUM(i) FILTER (WHERE i < (select i from t as t2 where t.i = t2.i))
+FROM t;
+----
+NULL
+
+# use it inside subquery
+query I
+SELECT
+ (select sum(t2.i) FILTER (where t2.i < 10) from t as t2)
+FROM t
+limit 5;
+----
+45
+45
+45
+45
+45
+
+# multiple filters
+query II
+SELECT
+ SUM(j) FILTER (WHERE i < 10),
+ SUM(i) FILTER (WHERE i < 5)
+FROM t;
+----
+45 10
+
+query II
+ SELECT
+ sum(i) AS unfiltered,
+ sum(i) FILTER (WHERE i < 5) AS filtered
+FROM t
+group by j;
+----
+49500 0
+49600 1
+49700 2
+49800 3
+49900 4
+50000 NULL
+50100 NULL
+50200 NULL
+50300 NULL
+50400 NULL
+
+
+
+query III
+ SELECT
+ COUNT(*) AS unfiltered,
+ COUNT(*) FILTER (WHERE i > 5 and i < 10) AS filtered,
+ j
+FROM t
+group by j;
+----
+100 0 0
+100 0 1
+100 0 2
+100 0 3
+100 0 4
+100 0 5
+100 1 6
+100 1 7
+100 1 8
+100 1 9
+
+
+
+query II
+SELECT
+ SUM(i) AS unfiltered,
+ SUM(i) FILTER (WHERE i between 5 and 10) AS filtered
+FROM t
+group by j;
+----
+49500 10
+49600 NULL
+49700 NULL
+49800 NULL
+49900 NULL
+50000 5
+50100 6
+50200 7
+50300 8
+50400 9
+
+
+
+
+query III
+SELECT
+ SUM(i) AS unfiltered,
+ SUM(j) FILTER (WHERE j = 1) AS filtered,
+ j
+FROM t
+group by j;
+----
+49500 NULL 0
+49600 100 1
+49700 NULL 2
+49800 NULL 3
+49900 NULL 4
+50000 NULL 5
+50100 NULL 6
+50200 NULL 7
+50300 NULL 8
+50400 NULL 9
+
+
+query I
+SELECT
+ SUM(j) FILTER (WHERE i < 10)
+FROM t
+group by j;
+----
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+
+query I
+SELECT
+ SUM(j) FILTER (WHERE i < (select 10))
+FROM t
+group by j;
+----
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+
+query I
+SELECT
+ SUM(i) FILTER (WHERE i < (select i from t as t2 where t.i = t2.i))
+FROM t
+group by j;
+----
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+
+# use it inside subquery
+query I
+ SELECT
+ (select sum(t2.i) FILTER (where t2.i < 10) from t as t2)
+FROM t
+group by j
+limit 5;
+----
+45
+45
+45
+45
+45
+
+# multiple filters
+query II
+SELECT
+ SUM(j) FILTER (WHERE i < 10),
+ SUM(i) FILTER (WHERE i < 5)
+FROM t
+group by j;
+----
+0 0
+1 1
+2 2
+3 3
+4 4
+5 NULL
+6 NULL
+7 NULL
+8 NULL
+9 NULL
+
+
+query II
+ SELECT
+ sum(i) AS unfiltered,
+ sum(i) FILTER (WHERE i < 5) AS filtered
+FROM t
+group by k;
+----
+49500 0
+49600 1
+49700 2
+49800 3
+49900 4
+50000 NULL
+50100 NULL
+50200 NULL
+50300 NULL
+50400 NULL
+
+
+
+query II
+ SELECT
+ COUNT(*) AS unfiltered,
+ COUNT(*) FILTER (WHERE i > 5 and i < 10) AS filtered
+FROM t
+group by k;
+----
+100 0
+100 0
+100 0
+100 0
+100 0
+100 0
+100 1
+100 1
+100 1
+100 1
+
+
+
+query II
+SELECT
+ SUM(i) AS unfiltered,
+ SUM(i) FILTER (WHERE i between 5 and 10) AS filtered
+FROM t
+group by k;
+----
+49500 10
+49600 NULL
+49700 NULL
+49800 NULL
+49900 NULL
+50000 5
+50100 6
+50200 7
+50300 8
+50400 9
+
+
+
+
+query III
+SELECT
+ SUM(i) AS unfiltered,
+ SUM(j) FILTER (WHERE j = 1) AS filtered,
+ k
+FROM t
+group by k;
+----
+49500 NULL 0
+49600 100 10000000
+49700 NULL 20000000
+49800 NULL 30000000
+49900 NULL 40000000
+50000 NULL 50000000
+50100 NULL 60000000
+50200 NULL 70000000
+50300 NULL 80000000
+50400 NULL 90000000
+
+
+query I
+SELECT
+ SUM(j) FILTER (WHERE i < 10)
+FROM t
+group by k;
+----
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+
+query I
+SELECT
+ SUM(j) FILTER (WHERE i < (select 10))
+FROM t
+group by k;
+----
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+
+query I
+SELECT
+ SUM(i) FILTER (WHERE i < (select i from t as t2 where t.i = t2.i))
+FROM t
+group by k;
+----
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+NULL
+
+# use it inside subquery
+query I
+ SELECT
+ (select sum(t2.i) FILTER (where t2.i < 10) from t as t2)
+FROM t
+group by k
+limit 5;
+----
+45
+45
+45
+45
+45
+
+# multiple filters
+query II
+SELECT
+ SUM(j) FILTER (WHERE i < 10),
+ SUM(i) FILTER (WHERE i < 5)
+FROM t
+group by k;
+----
+0 0
+1 1
+2 2
+3 3
+4 4
+5 NULL
+6 NULL
+7 NULL
+8 NULL
+9 NULL
+
+
+# use correlated expression inside the filter itself
+query I
+ SELECT
+ (select sum(t2.i) FILTER (where t.i = t2.i) from t as t2)
+FROM t
+where i < 5
+----
+0
+1
+2
+3
+4
+
+#Query with many different filter clauses (e.g. 5 aggregates, 5 different filters)
+statement ok
+create temporary table t_2 as select range a, length(range) b, mod(range,100) c, 5 d, 10000 e from range(1000);
+
+query IIIII
+select count (a) filter (where a>10 and a < 15), count (b) filter (where b between 1 and 3),
+ count (c) filter ( where c < 10), count (d) filter (where d =5), count(e) filter (where e < 10)
+ from t_2;
+----
+4 1000 100 1000 0
+
+query IIIII
+select count (a) filter (where a>10 and a < 15), count (b) filter (where b between 1 and 3),
+ count (c) filter ( where c < 10), count (d) filter (where d =5), count(e) filter (where e < 10)
+ from t_2
+ group by b;
+----
+0 10 10 10 0
+4 90 0 90 0
+0 900 90 900 0
+
+
+#Filter with some more complex aggregates: COVAR_POP (multiple input columns), STRING_AGG (strings) and ARRAY_AGG (lists)
+query II
+select COVAR_POP(a,b) filter (where a < 100), COVAR_POP(a,b) filter (where b <5) from t_2;
+----
+4.5 49.95
+
+query II
+select COVAR_POP(a,c) filter (where a < 100), COVAR_POP(a,c) filter (where c <50)
+from t_2
+group by b;
+----
+8.250000 8.250000
+674.916667 133.250000
+NULL 208.250000
+
+statement ok
+CREATE TABLE films(film_id INTEGER, title VARCHAR)
+
+statement ok
+CREATE TABLE actors(actor_id INTEGER, first_name VARCHAR, last_name VARCHAR)
+
+statement ok
+CREATE TABLE film_actor(film_id INTEGER, actor_id INTEGER)
+
+statement ok
+INSERT INTO films VALUES (1, 'The Martian'), (2, 'Saving Private Ryan'), (3, 'Team America');
+
+statement ok
+INSERT INTO actors VALUES (1, 'Matt', 'Damon'), (2, 'Jessica', 'Chastain'), (3, 'Tom', 'Hanks'), (4, 'Edward', 'Burns'),
+ (5, 'Kim', 'Jong Un'), (6, 'Alec', 'Baldwin');
+
+statement ok
+INSERT INTO film_actor VALUES (1, 1), (2, 1), (3, 1), (1, 2), (2, 3), (2, 4), (3, 5), (3, 6);
+
+query II
+SELECT
+ title,
+ ARRAY_AGG (first_name || ' ' || last_name) filter (where first_name = 'Matt') actors
+FROM films
+JOIN film_actor USING (film_id)
+JOIN actors USING (actor_id)
+GROUP BY
+ title
+ORDER BY
+ title;
+----
+Saving Private Ryan [Matt Damon]
+Team America [Matt Damon]
+The Martian [Matt Damon]
+
+
+query II
+SELECT
+ title,
+ STRING_AGG (first_name || ' ' || last_name) filter (where first_name = 'Matt') actors
+FROM films
+JOIN film_actor USING (film_id)
+JOIN actors USING (actor_id)
+GROUP BY
+ title
+ORDER BY
+ title;
+----
+Saving Private Ryan Matt Damon
+Team America Matt Damon
+The Martian Matt Damon
+
+
+#DISTINCT aggregates
+statement ok
+CREATE TABLE integers(i INTEGER)
+
+statement ok
+insert into integers values (1),(1),(2),(2),(3),(4),(8);
+
+query I
+select sum(distinct i) filter (where i >1 and i < 5) from integers;
+----
+9
\ No newline at end of file
diff --git a/modules/calcite/src/test/sql/filter/test_illegal_filters.test b/modules/calcite/src/test/sql/filter/test_illegal_filters.test
new file mode 100644
index 0000000..406f115
--- /dev/null
+++ b/modules/calcite/src/test/sql/filter/test_illegal_filters.test
@@ -0,0 +1,17 @@
+# name: test/sql/filter/test_illegal_filters.test
+# description: Test aggregation in WHERE
+# group: [filter]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE integers(a INTEGER, b INTEGER)
+
+statement ok
+INSERT INTO integers VALUES (2, 12)
+
+# aggregates in WHERE are not allowed
+statement error
+SELECT * FROM integers WHERE SUM(a)>10
+
diff --git a/modules/calcite/src/test/sql/filter/test_obsolete_filters.test b/modules/calcite/src/test/sql/filter/test_obsolete_filters.test
new file mode 100644
index 0000000..45ed607
--- /dev/null
+++ b/modules/calcite/src/test/sql/filter/test_obsolete_filters.test
@@ -0,0 +1,280 @@
+# name: test/sql/filter/test_obsolete_filters.test
+# description: Test expressions with obsolete filters
+# group: [filter]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE integers(a INTEGER, b INTEGER)
+
+statement ok
+INSERT INTO integers VALUES (1, 10), (2, 12), (3, 14), (4, 16), (5, NULL), (NULL, NULL)
+
+query II
+SELECT * FROM integers WHERE TRUE ORDER BY 1
+----
+NULL NULL
+1 10
+2 12
+3 14
+4 16
+5 NULL
+
+# Obsolete filters that can be pruned
+query II
+SELECT * FROM integers WHERE FALSE ORDER BY 1
+----
+
+query II
+SELECT * FROM integers WHERE NULL ORDER BY 1
+----
+
+# involving equality
+query II
+SELECT * FROM integers WHERE a=2 AND a=2
+----
+2 12
+
+query II
+SELECT * FROM integers WHERE a=2 AND a>0
+----
+2 12
+
+query II
+SELECT * FROM integers WHERE a>0 AND a=2
+----
+2 12
+
+query II
+SELECT * FROM integers WHERE a=2 AND a<4
+----
+2 12
+
+query II
+SELECT * FROM integers WHERE a<4 AND a=2
+----
+2 12
+
+query II
+SELECT * FROM integers WHERE a=2 AND a<=2
+----
+2 12
+
+query II
+SELECT * FROM integers WHERE a=2 AND a>=2
+----
+2 12
+
+# involving multiple GREATER THAN expressions
+query II
+SELECT * FROM integers WHERE a>2 AND a>4
+----
+5 NULL
+
+query II
+SELECT * FROM integers WHERE a>4 AND a>2
+----
+5 NULL
+
+query II
+SELECT * FROM integers WHERE a>4 AND a>=4
+----
+5 NULL
+
+query II
+SELECT * FROM integers WHERE a>=4 AND a>4
+----
+5 NULL
+
+# involving multiple LESS THAN expressions
+query II
+SELECT * FROM integers WHERE a<2 AND a<4
+----
+1 10
+
+query II
+SELECT * FROM integers WHERE a<4 AND a<2
+----
+1 10
+
+query II
+SELECT * FROM integers WHERE a<2 AND a<=2
+----
+1 10
+
+query II
+SELECT * FROM integers WHERE a<=2 AND a<2
+----
+1 10
+
+# involving inequality expression
+query II
+SELECT * FROM integers WHERE a<2 AND a<>3
+----
+1 10
+
+query II
+SELECT * FROM integers WHERE a<=1 AND a<>3
+----
+1 10
+
+query II
+SELECT * FROM integers WHERE a>4 AND a<>2
+----
+5 NULL
+
+query II
+SELECT * FROM integers WHERE a>=5 AND a<>2
+----
+5 NULL
+
+query II
+SELECT * FROM integers WHERE a>=4 AND a<>4 AND a<>4
+----
+5 NULL
+
+# many conditions
+query II
+SELECT * FROM integers WHERE a<3 AND a<4 AND a<5 AND a<10 AND a<2 AND a<20
+----
+1 10
+
+# various obsolete filters that always result in zero results
+# (i.e. entire tree can be pruned)
+
+query II
+SELECT * FROM integers WHERE a=2 AND a=4
+----
+
+query II
+SELECT * FROM integers WHERE a=2 AND a>4
+----
+
+query II
+SELECT * FROM integers WHERE a>4 AND a=2
+----
+
+query II
+SELECT * FROM integers WHERE a=2 AND a>2
+----
+
+query II
+SELECT * FROM integers WHERE a>=4 AND a=2
+----
+
+query II
+SELECT * FROM integers WHERE a=4 AND a<2
+----
+
+query II
+SELECT * FROM integers WHERE a<2 AND a=4
+----
+
+query II
+SELECT * FROM integers WHERE a=2 AND a<2
+----
+
+query II
+SELECT * FROM integers WHERE a<=2 AND a=4
+----
+
+query II
+SELECT * FROM integers WHERE a<2 AND a>4
+----
+
+query II
+SELECT * FROM integers WHERE a=2 AND a<>2
+----
+
+query II
+SELECT * FROM integers WHERE a<>2 AND a=2
+----
+
+query II
+SELECT * FROM integers WHERE 0
+----
+
+query II
+SELECT * FROM integers WHERE a<2 AND 0
+----
+
+# Test string expressions with obsolete filters
+statement ok
+CREATE TABLE strings(s VARCHAR)
+
+statement ok
+INSERT INTO strings VALUES ('hello'), ('world'), (NULL)
+
+query T
+SELECT * FROM strings WHERE s='hello' AND s='hello'
+----
+hello
+
+query T
+SELECT * FROM strings WHERE s='hello' AND s='world'
+----
+
+query T
+SELECT * FROM strings WHERE s='hello' AND s<>'hello'
+----
+
+query T
+SELECT * FROM strings WHERE s='hello' AND s<>'world'
+----
+hello
+
+query T
+SELECT * FROM strings WHERE s='hello' AND s>'a'
+----
+hello
+
+query T
+SELECT * FROM strings WHERE s='hello' AND s>='hello'
+----
+hello
+
+query T
+SELECT * FROM strings WHERE s='hello' AND s<='hello'
+----
+hello
+
+query T
+SELECT * FROM strings WHERE s='hello' AND s<'z'
+----
+hello
+
+query T
+SELECT * FROM strings WHERE s='hello' AND s<='a'
+----
+
+query T
+SELECT * FROM strings WHERE s='hello' AND s<'hello'
+----
+
+query T
+SELECT * FROM strings WHERE s='hello' AND s>'hello'
+----
+
+query T
+SELECT * FROM strings WHERE s='hello' AND s>='z'
+----
+
+query T
+SELECT * FROM strings WHERE s<>'hello' AND s<='a'
+----
+
+query T
+SELECT * FROM strings WHERE s<>'hello' AND s<'hello'
+----
+
+query T
+SELECT * FROM strings WHERE s<>'hello' AND s>'hello'
+----
+world
+
+query T
+SELECT * FROM strings WHERE s<>'world' AND s>='hello'
+----
+hello
+
diff --git a/modules/calcite/src/test/sql/filter/test_transitive_filters.test b/modules/calcite/src/test/sql/filter/test_transitive_filters.test
new file mode 100644
index 0000000..0055dfd
--- /dev/null
+++ b/modules/calcite/src/test/sql/filter/test_transitive_filters.test
@@ -0,0 +1,295 @@
+# name: test/sql/filter/test_transitive_filters.test
+# description: Test expressions with transitive filters
+# group: [filter]
+
+statement ok
+PRAGMA enable_verification
+
+# Test various transitive filters with simple constant comparisons followed by non-scalar comparisons
+# The main point here is to check that the optimizer handles all these cases correctly
+
+# Inserting i, j = i
+statement ok
+CREATE TABLE vals1 AS SELECT i AS i, i AS j FROM range(0, 11, 1) t1(i)
+
+# Inserting i, j = i+1
+statement ok
+INSERT INTO vals1 SELECT i, i+1 FROM vals1
+
+# Inserting i, j = i-1
+statement ok
+INSERT INTO vals1 SELECT DISTINCT(i), i-1 FROM vals1 ORDER by i
+
+### constant comparison [=, >, >=, <, <=] followed by j >= i #########################
+
+query II
+SELECT * FROM vals1 WHERE i=5 AND j>=i
+----
+5 5
+5 6
+
+query II
+SELECT * FROM vals1 WHERE i>9 AND j>=i
+----
+10 10
+10 11
+
+query II
+SELECT * FROM vals1 WHERE i>=10 AND j>=i
+----
+10 10
+10 11
+
+query II
+SELECT * FROM vals1 WHERE i<1 AND j>=i
+----
+0 0
+0 1
+
+query II
+SELECT * FROM vals1 WHERE i<=0 AND j>=i
+----
+0 0
+0 1
+
+### constant comparison [=, >, >=, <, <=] followed by j <= i #########################
+
+query II
+SELECT * FROM vals1 WHERE i=5 AND j<=i
+----
+5 5
+5 4
+
+query II
+SELECT * FROM vals1 WHERE i>9 AND j<=i
+----
+10 10
+10 9
+
+query II
+SELECT * FROM vals1 WHERE i>=10 AND j<=i
+----
+10 10
+10 9
+
+query II
+SELECT * FROM vals1 WHERE i<1 AND j<=i
+----
+0 0
+0 -1
+
+query II
+SELECT * FROM vals1 WHERE i<=0 AND j<=i
+----
+0 0
+0 -1
+
+### constant comparison [=, >, >=, <, <=] followed by j > i #########################
+
+query II
+SELECT * FROM vals1 WHERE i=5 AND j>i
+----
+5 6
+
+query II
+SELECT * FROM vals1 WHERE i>9 AND j>i
+----
+10 11
+
+query II
+SELECT * FROM vals1 WHERE i>=10 AND j>i
+----
+10 11
+
+query II
+SELECT * FROM vals1 WHERE i<1 AND j>i
+----
+0 1
+
+query II
+SELECT * FROM vals1 WHERE i<=0 AND j>i
+----
+0 1
+
+### constant comparison [=, >, >=, <, <=] followed by j < i #########################
+
+query II
+SELECT * FROM vals1 WHERE i=5 AND j<i
+----
+5 4
+
+query II
+SELECT * FROM vals1 WHERE i>9 AND j<i
+----
+10 9
+
+query II
+SELECT * FROM vals1 WHERE i>=10 AND j<i
+----
+10 9
+
+query II
+SELECT * FROM vals1 WHERE i<1 AND j<i
+----
+0 -1
+
+query II
+SELECT * FROM vals1 WHERE i<=0 AND j<i
+----
+0 -1
+
+
+#################################################################################################
+# Non-scalar comparisons followed by simple constant comparisons
+# e.g., j >= i AND i>10
+
+### j >= i followed by constant comparison [=, >, >=, <, <=] #########################
+
+query II
+SELECT * FROM vals1 WHERE j>=i AND i=5
+----
+5 5
+5 6
+
+query II
+SELECT * FROM vals1 WHERE j>=i AND i>9
+----
+10 10
+10 11
+
+query II
+SELECT * FROM vals1 WHERE j>=i AND i>=10
+----
+10 10
+10 11
+
+query II
+SELECT * FROM vals1 WHERE j>=i AND i<1
+----
+0 0
+0 1
+
+query II
+SELECT * FROM vals1 WHERE j>=i AND i<=0
+----
+0 0
+0 1
+
+### j <= i followed by constant comparison [=, >, >=, <, <=] #########################
+
+query II
+SELECT * FROM vals1 WHERE j<=i AND i=5
+----
+5 5
+5 4
+
+query II
+SELECT * FROM vals1 WHERE j<=i AND i>9
+----
+10 10
+10 9
+
+query II
+SELECT * FROM vals1 WHERE j<=i AND i>=10
+----
+10 10
+10 9
+
+query II
+SELECT * FROM vals1 WHERE j<=i AND i<1
+----
+0 0
+0 -1
+
+query II
+SELECT * FROM vals1 WHERE j<=i AND i<=0
+----
+0 0
+0 -1
+
+### j > i followed by constant comparison [=, >, >=, <, <=] #########################
+
+query II
+SELECT * FROM vals1 WHERE j>i AND i=5
+----
+5 6
+
+query II
+SELECT * FROM vals1 WHERE j>i AND i>9
+----
+10 11
+
+query II
+SELECT * FROM vals1 WHERE j>i AND i>=10
+----
+10 11
+
+query II
+SELECT * FROM vals1 WHERE j>i AND i<1
+----
+0 1
+
+query II
+SELECT * FROM vals1 WHERE j>i AND i<=0
+----
+0 1
+
+### j < i followed by constant comparison [=, >, >=, <, <=] #########################
+
+query II
+SELECT * FROM vals1 WHERE j<i AND i=5
+----
+5 4
+
+query II
+SELECT * FROM vals1 WHERE j<i AND i>9
+----
+10 9
+
+query II
+SELECT * FROM vals1 WHERE j<i AND i>=10
+----
+10 9
+
+query II
+SELECT * FROM vals1 WHERE j<i AND i<1
+----
+0 -1
+
+query II
+SELECT * FROM vals1 WHERE j<i AND i<=0
+----
+0 -1
+
+### Complex transitive filters #################################################
+statement ok
+CREATE TABLE vals2(k BIGINT, l BIGINT)
+
+statement ok
+INSERT INTO vals2 SELECT * FROM vals1
+
+query IIII
+SELECT * FROM vals1, vals2 WHERE i>9 AND j<=l AND k>=i AND l<11
+----
+10 10 10 10
+10 9 10 10
+10 9 10 9
+
+query IIII
+SELECT * FROM vals1, vals2 WHERE i>9 AND j>=i AND k>=j ORDER by l
+----
+10 10 10 9
+10 10 10 10
+10 10 10 11
+
+query IIII
+SELECT * FROM vals1, vals2 WHERE i>9 AND k>=j AND j>=i AND l>=k
+----
+10 10 10 10
+10 10 10 11
+
+query IIII
+SELECT * FROM vals1, vals2 WHERE i<1 AND k<=j AND j<=i AND l<=k
+----
+0 0 0 0
+0 0 0 -1
diff --git a/modules/calcite/src/test/sql/filter/test_zonemap.test_slow b/modules/calcite/src/test/sql/filter/test_zonemap.test_slow
new file mode 100644
index 0000000..c1b5553
--- /dev/null
+++ b/modules/calcite/src/test/sql/filter/test_zonemap.test_slow
@@ -0,0 +1,76 @@
+# name: test/sql/filter/test_zonemap.test_slow
+# description: Test expressions with transitive filters
+# group: [filter]
+
+# FIXME: temporarily removed because of too much memory usage on 32-bit
+# should be re-enabled when validity segments are no longer limited to 80 vectors
+mode skip
+
+statement ok
+PRAGMA explain_output = PHYSICAL_ONLY;
+
+statement ok
+create temporary table t as select range a, length(range) b, mod(range,10000) c, 5 d, 10000 e from range(100000000);
+
+query I
+select count(*) from t where a > 500 or a <= 700
+----
+100000000
+
+query II
+explain select count(*) from t where (a > 500 and b = 3) or (a > 7000 and b = 2)
+----
+physical_plan <REGEX>:.* Filters: .*b>=2.*b<=3.*
+
+query I
+select count(*) from t where (a > 500 and b = 3) or (a > 7000 and b = 2)
+----
+499
+
+query II
+explain select count(*) from t where (a > 500 AND b = 3) OR (a > 400) OR (a > 300 AND b=4) OR (a > 600 AND a > 300)
+----
+physical_plan <REGEX>:.* Filters: .*a>=300.*
+
+
+query I
+select count(*) from t where (a > 500 AND b = 3) OR (a > 400) OR (a > 300 AND b=4) OR (a > 600 AND a > 300)
+----
+99999599
+
+query II
+explain select count(*) from t where (a > 500 AND b = 3) OR (a > 400) OR (a > 300 AND b=4) OR (a > 600 AND a > 300)
+----
+physical_plan <REGEX>:.* Filters: .*a>=300.*
+
+
+query I
+select count(*) from t where (a > 500 AND b = 3) OR (a > 400) OR (a > 300 AND b=4) OR (a > 600 AND a > 300)
+----
+99999599
+
+
+query II
+explain select count(*) from t where (a > 500 AND b = 3) OR (c = 400) OR (a > 300 AND b=4) OR (a > 600 AND a > 300) or (d < 10)
+----
+physical_plan <!REGEX>:.* Filters: .*a>=300.*
+
+
+query I
+select count(*) from t where (a > 500 AND b = 3) OR (a > 400) OR (a > 300 AND b=4) OR (a > 600 AND a > 300)
+----
+99999599
+
+
+query II
+explain select count(*) from t where (a > 500 AND b = 1) OR b < 2
+----
+physical_plan <REGEX>:.* Filters: .*b<=2.*
+
+
+query I
+select count(*) from t where (a > 500 AND b = 1) OR b < 2
+----
+10
+
+
diff --git a/modules/calcite/src/test/sql/function/blob/base64.test b/modules/calcite/src/test/sql/function/blob/base64.test
new file mode 100644
index 0000000..465573a
--- /dev/null
+++ b/modules/calcite/src/test/sql/function/blob/base64.test
@@ -0,0 +1,92 @@
+# name: test/sql/function/blob/base64.test
+# 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')))
+----
+abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890
+
+query I
+select from_base64('AAAA');
+----
+\x00\x00\x00
+
+# malformed base64
+# must be multiple of 4
+statement error
+SELECT from_base64('ab');
+
+# unknown bytes
+statement error
+SELECT from_base64('üab');
\ No newline at end of file
diff --git a/modules/calcite/src/test/sql/function/blob/encode.test b/modules/calcite/src/test/sql/function/blob/encode.test
new file mode 100644
index 0000000..4103b06
--- /dev/null
+++ b/modules/calcite/src/test/sql/function/blob/encode.test
@@ -0,0 +1,37 @@
+# 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_stats.test b/modules/calcite/src/test/sql/function/date/date_part_stats.test
new file mode 100644
index 0000000..d2daca2
--- /dev/null
+++ b/modules/calcite/src/test/sql/function/date/date_part_stats.test
@@ -0,0 +1,201 @@
+# name: test/sql/function/date/date_part_stats.test
+# description: Test date part stats on empty table
+# group: [date]
+
+statement ok
+PRAGMA enable_verification
+
+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(microseconds FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(milliseconds 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 YEARWEEK(d) FROM dates;
+----
+
+query I
+SELECT DAYOFMONTH(d) FROM dates;
+----
+
+query I
+SELECT WEEKDAY(d) FROM dates;
+----
+
+query I
+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;
+----
+<REGEX>:.*1992.*2000.*
+
+query I
+SELECT stats(EXTRACT(month FROM d)) FROM dates LIMIT 1;
+----
+<REGEX>:.*1.*12.*
+
+query I
+SELECT stats(EXTRACT(day FROM d)) FROM dates LIMIT 1;
+----
+<REGEX>:.*1.*31.*
+
+query I
+SELECT stats(EXTRACT(decade FROM d)) FROM dates LIMIT 1;
+----
+<REGEX>:.*199.*200.*
+
+query I
+SELECT stats(EXTRACT(century FROM d)) FROM dates LIMIT 1;
+----
+<REGEX>:.*20.*20.*
+
+query I
+SELECT stats(EXTRACT(millennium FROM d)) FROM dates LIMIT 1;
+----
+<REGEX>:.*2.*2.*
+
+query I
+SELECT stats(EXTRACT(microseconds FROM d)) FROM dates LIMIT 1;
+----
+<REGEX>:.*0.*60000000.*
+
+query I
+SELECT stats(EXTRACT(milliseconds FROM d)) FROM dates LIMIT 1;
+----
+<REGEX>:.*0.*60000.*
+
+query I
+SELECT stats(EXTRACT(second FROM d)) FROM dates LIMIT 1;
+----
+<REGEX>:.*0.*60.*
+
+query I
+SELECT stats(EXTRACT(minute FROM d)) FROM dates LIMIT 1;
+----
+<REGEX>:.*0.*60.*
+
+query I
+SELECT stats(EXTRACT(hour FROM d)) FROM dates LIMIT 1;
+----
+<REGEX>:.*0.*24.*
+
+query I
+SELECT stats(EXTRACT(dow FROM d)) FROM dates LIMIT 1;
+----
+<REGEX>:.*0.*6.*
+
+query I
+SELECT stats(EXTRACT(isodow FROM d)) FROM dates LIMIT 1;
+----
+<REGEX>:.*1.*7.*
+
+query I
+SELECT stats(EXTRACT(week FROM d)) FROM dates LIMIT 1;
+----
+<REGEX>:.*1.*54.*
+
+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;
+----
+<REGEX>:.*1.*4.*
+
+query I
+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.*
+
+query I
+SELECT stats(WEEKOFYEAR(d)) FROM dates LIMIT 1;
+----
+<REGEX>:.*1.*54.*
+
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
new file mode 100644
index 0000000..fe41122
--- /dev/null
+++ b/modules/calcite/src/test/sql/function/date/test_date_part.test
@@ -0,0 +1,168 @@
+# name: test/sql/function/date/test_date_part.test
+# description: DATE_PART test
+# 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
+SELECT LAST_DAY(DATE '1900-02-12'), LAST_DAY(DATE '1992-02-12'), LAST_DAY(DATE '2000-02-12');
+----
+1900-02-28 1992-02-29 2000-02-29
+
+query T
+SELECT LAST_DAY(d) FROM dates;
+----
+1992-01-31
+1992-03-31
+1992-05-31
+
+query T
+SELECT LAST_DAY(d::timestamp) FROM dates;
+----
+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;
+----
+1
+3
+5
+
+query I
+SELECT WEEKDAY(d) FROM dates;
+----
+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_trunc.test b/modules/calcite/src/test/sql/function/date/test_date_trunc.test
new file mode 100644
index 0000000..39cec97
--- /dev/null
+++ b/modules/calcite/src/test/sql/function/date/test_date_trunc.test
@@ -0,0 +1,143 @@
+# 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
new file mode 100644
index 0000000..bd814a4
--- /dev/null
+++ b/modules/calcite/src/test/sql/function/date/test_extract.test
@@ -0,0 +1,114 @@
+# name: test/sql/function/date/test_extract.test
+# description: Extract function
+# group: [date]
+
+statement ok
+PRAGMA enable_verification
+
+statement ok
+CREATE TABLE dates(i DATE)
+
+statement ok
+INSERT INTO dates VALUES ('1993-08-14'), (NULL)
+
+# extract various parts of the date
+# year
+query I
+SELECT EXTRACT(year FROM i) FROM dates
+----
+1993
+NULL
+
+# month
+query I
+SELECT EXTRACT(month FROM i) FROM dates
+----
+8
+NULL
+
+# day
+query I
+SELECT EXTRACT(day FROM i) FROM dates
+----
+14
+NULL
+
+# decade
+query I
+SELECT EXTRACT(decade FROM i) FROM dates
+----
+199
+NULL
+
+# century
+query I
+SELECT EXTRACT(century FROM i) FROM dates
+----
+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
+----
+226
+NULL
+
+# epoch
+query I
+SELECT EXTRACT(epoch FROM i) FROM dates
+----
+745286400
+NULL
+
+# isodow (Monday = 1, Sunday = 7)
+query I
+SELECT EXTRACT(ISODOW FROM i) FROM dates
+----
+6
+NULL
+
+# millenium (change of millenium is January 1, X001)
+query I
+SELECT EXTRACT(millennium FROM i) FROM dates
+----
+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
+----
+0
+NULL
+
+query I
+SELECT EXTRACT(hour FROM i) FROM dates
+----
+0
+NULL
+
+query I
+SELECT EXTRACT(milliseconds FROM i) FROM dates
+----
+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_edge_cases.test b/modules/calcite/src/test/sql/function/date/test_extract_edge_cases.test
new file mode 100644
index 0000000..1db3179
--- /dev/null
+++ b/modules/calcite/src/test/sql/function/date/test_extract_edge_cases.test
@@ -0,0 +1,341 @@
+# name: test/sql/function/date/test_extract_edge_cases.test
+# description: Extract function edge cases
+# group: [date]
+
+# century changes in the year 1
+query I
+SELECT EXTRACT(century FROM cast('2000-10-10' AS DATE));
+----
+20
+
+query I
+SELECT EXTRACT(century FROM cast('2001-10-10' AS DATE));
+----
+21
+
+# millennium changes in the year 1
+query I
+SELECT EXTRACT(millennium FROM cast('2000-10-10' AS DATE));
+----
+2
+
+query I
+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));
+----
+7
+
+# week numbers are weird
+query I
+SELECT EXTRACT(week FROM cast('2005-01-01' AS DATE));
+----
+53
+
+query I
+SELECT EXTRACT(week FROM cast('2006-01-01' AS DATE));
+----
+52
+
+query I
+SELECT EXTRACT(week FROM cast('2007-01-01' AS DATE));
+----
+1
+
+query I
+SELECT EXTRACT(week FROM cast('2008-01-01' AS DATE));
+----
+1
+
+query I
+SELECT EXTRACT(week FROM cast('2009-01-01' AS DATE));
+----
+1
+
+query I
+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_month.test b/modules/calcite/src/test/sql/function/date/test_extract_month.test
new file mode 100644
index 0000000..fe2ed87
--- /dev/null
+++ b/modules/calcite/src/test/sql/function/date/test_extract_month.test
@@ -0,0 +1,746 @@
+# name: test/sql/function/date/test_extract_month.test
+# 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);
+----
+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
... 28930 lines suppressed ...