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 2022/07/06 11:03:52 UTC
[ignite-3] branch main updated: IGNITE-17186 Port script runner and tests to Ignite 3 (#903)
This is an automated email from the ASF dual-hosted git repository.
tledkov pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/ignite-3.git
The following commit(s) were added to refs/heads/main by this push:
new 1a0a3a4a8 IGNITE-17186 Port script runner and tests to Ignite 3 (#903)
1a0a3a4a8 is described below
commit 1a0a3a4a840004e55bc0ba956d1fd6b5694ec080
Author: Taras Ledkov <tl...@gridgain.com>
AuthorDate: Wed Jul 6 14:03:47 2022 +0300
IGNITE-17186 Port script runner and tests to Ignite 3 (#903)
---
.../ignite/internal/sqllogic/ItSqlLogicTest.java | 318 +
.../internal/sqllogic/SqlLogicTestEnvironment.java | 80 +
.../ignite/internal/sqllogic/SqlScriptRunner.java | 761 +
.../sql/aggregate/aggregates/test_aggr_string.test | 82 +
.../aggregates/test_aggregate_types.test_ignore | 190 +
.../test_aggregate_types_scalar.test_ignored | 110 +
.../aggregates/test_approx_quantile.test_ignore | 170 +
.../test_approximate_distinct_count.test_ignore | 93 +
.../aggregates/test_arg_min_max.test_ignore | 131 +
.../sql/aggregate/aggregates/test_avg.test_ignore | 40 +
.../sql/aggregate/aggregates/test_avg.test_ignored | 54 +
.../aggregate/aggregates/test_bit_and.test_ignore | 57 +
.../aggregate/aggregates/test_bit_or.test_ignore | 57 +
.../aggregate/aggregates/test_bit_xor.test_ignore | 57 +
.../sql/aggregate/aggregates/test_count.test | 51 +
.../sql/aggregate/aggregates/test_count_star.test | 46 +
.../aggregate/aggregates/test_covar.test_ignore | 100 +
.../aggregate/aggregates/test_distinct_aggr.test | 21 +
.../test_distinct_string_agg.test_ignore | 22 +
.../aggregate/aggregates/test_empty_aggregate.test | 29 +
.../aggregates/test_empty_aggregate.test_ignore | 31 +
.../test_group_by_many_groups.test_slow_ignore | 15 +
.../aggregates/test_group_on_expression.test | 75 +
.../aggregates/test_histogram.test_ignore | 94 +
.../sql/aggregate/aggregates/test_mode.test_ignore | 97 +
.../aggregate/aggregates/test_null_aggregates.test | 406 +
.../aggregates/test_perfect_ht.test_ignore | 178 +
.../aggregates/test_perfect_ht.test_ignore_old | 176 +
.../aggregate/aggregates/test_quantile.test_ignore | 222 +
.../aggregates/test_quantile_list.test_ignore | 115 +
.../aggregates/test_regression.test_ignore | 347 +
.../aggregates/test_scalar_aggr.test_ignore | 103 +
.../aggregate/aggregates/test_stddev.test_ignore | 123 +
.../aggregates/test_string_agg.test_ignore | 154 +
.../aggregates/test_string_agg_big.test_ignore | 17 +
.../test_string_agg_many_groups.test_ignore | 22 +
.../sql/aggregate/aggregates/test_sum.test | 81 +
.../sql/aggregate/distinct/test_distinct.test | 55 +
.../distinct/test_distinct_on.test_ignore | 133 +
.../aggregate/distinct/test_distinct_order_by.test | 41 +
.../sql/aggregate/group/test_group_by.test | 160 +
.../sql/aggregate/group/test_group_by.test_ignore | 182 +
.../sql/aggregate/group/test_group_by_alias.test | 38 +
.../group/test_group_by_alias.test_ignore | 68 +
.../group/test_group_by_large_string.test | 16 +
.../group/test_group_by_multi_column.test | 17 +
.../sql/aggregate/group/test_group_null.test | 17 +
.../test_corel_subquery_in_having.test_ignore | 38 +
.../sql/aggregate/having/test_having.test | 45 +
.../having/test_scalar_having.test_ignore | 119 +
.../sql/cast/test_boolean_cast.test | 130 +
.../sql/cast/test_boolean_cast.test_ignore | 160 +
.../sql/cast/test_exponent_in_cast.test_ignore | 45 +
.../integrationTest/sql/cast/test_string_cast.test | 55 +
.../integrationTest/sql/cast/test_try_cast.test | 22 +
.../integrationTest/sql/delete/test_delete.test | 33 +
.../sql/delete/test_large_delete.test_ignore | 15 +
.../sql/filter/test_alias_filter.test | 20 +
.../sql/filter/test_constant_comparisons.test | 103 +
.../filter/test_constant_comparisons.test_ignore | 12 +
.../sql/filter/test_filter_clause.test_ignore | 460 +
.../sql/filter/test_filter_clause.test_ignore_old | 528 +
.../sql/filter/test_illegal_filters.test | 17 +
.../sql/filter/test_obsolete_filters.test | 271 +
.../sql/filter/test_obsolete_filters.test_ignore | 281 +
.../sql/filter/test_transitive_filters.test_ignore | 322 +
.../sql/filter/test_zonemap.test_ignore | 32 +
.../integrationTest/sql/function/blob/base64.test | 27 +
.../sql/function/date/date_part.test | 189 +
.../sql/function/date/date_part.test_ignore | 214 +
.../sql/function/date/test_date_part.test | 61 +
.../sql/function/date/test_date_part.test_ignore | 166 +
.../sql/function/date/test_extract.test | 111 +
.../sql/function/date/test_extract.test_ignore | 112 +
.../sql/function/date/test_extract_edge_cases.test | 67 +
.../date/test_extract_edge_cases.test_ignore | 342 +
.../sql/function/date/test_extract_month.test | 748 +
.../sql/function/date/test_extract_year.test | 653 +
.../sql/function/generic/test_between.test | 219 +
.../sql/function/generic/test_case.test | 93 +
.../sql/function/generic/test_coalesce.test | 52 +
.../sql/function/generic/test_coalesce.test_ignore | 15 +
.../sql/function/generic/test_decode.test | 24 +
.../sql/function/generic/test_decode.test_ignore | 20 +
.../sql/function/generic/test_in.test | 135 +
.../sql/function/generic/test_large_in.test_ignore | 49 +
.../sql/function/generic/test_least_greatest.test | 118 +
.../sql/function/generic/test_null_if.test | 54 +
.../sql/function/generic/test_nvl.test_ignore | 29 +
.../sql/function/interval/test_extract.test_ignore | 234 +
.../sql/function/json/test_json.test | 28 +
.../sql/function/numeric/test_floor_ceil.test | 140 +
.../sql/function/numeric/test_invalid_math.test | 37 +
.../sql/function/numeric/test_mod.test | 25 +
.../sql/function/numeric/test_oracle_math.test | 20 +
.../sql/function/numeric/test_pg_math.test | 86 +
.../sql/function/numeric/test_pow.test | 35 +
.../sql/function/numeric/test_random.test | 31 +
.../sql/function/numeric/test_round.test | 55 +
.../sql/function/numeric/test_trigo.test | 445 +
.../sql/function/numeric/test_truncate.test_ignore | 44 +
.../sql/function/numeric/test_type_resolution.test | 202 +
.../sql/function/numeric/test_unary.test | 61 +
.../sql/function/operator/test_arithmetic.test | 143 +
.../operator/test_arithmetic_sqllogic.test | 51 +
.../sql/function/operator/test_comparison.test | 42 +
.../sql/function/operator/test_conjunction.test | 189 +
.../integrationTest/sql/function/string/md5.test | 37 +
.../sql/function/string/regex_filter_pushdown.test | 31 +
.../sql/function/string/regex_replace.test_ignore | 77 +
.../sql/function/string/regex_search.test | 129 +
.../sql/function/string/regex_search.test_ignore | 143 +
.../integrationTest/sql/function/string/sha1.test | 9 +
.../sql/function/string/test_ascii.test | 78 +
.../function/string/test_caseconvert.test_ignore | 69 +
.../sql/function/string/test_char_length.test | 36 +
.../function/string/test_char_length.test_ignore | 9 +
.../string/test_complex_unicode.test_ignore | 83 +
.../sql/function/string/test_compress.test | 10 +
.../sql/function/string/test_concat.test | 75 +
.../sql/function/string/test_concat_function.test | 62 +
.../sql/function/string/test_difference.test | 40 +
.../sql/function/string/test_initcap.test_ignore | 29 +
.../sql/function/string/test_left.test_ignore | 80 +
.../sql/function/string/test_left.test_ignore_old | 102 +
.../sql/function/string/test_length.test | 26 +
.../sql/function/string/test_length.test_ignore | 27 +
.../sql/function/string/test_like.test | 285 +
.../sql/function/string/test_like_escape.test | 100 +
.../function/string/test_like_escape.test_ignore | 106 +
.../sql/function/string/test_overlay.test | 19 +
.../sql/function/string/test_position.test | 58 +
.../sql/function/string/test_repeat.test_ignore | 58 +
.../sql/function/string/test_replace.test_ignore | 76 +
.../sql/function/string/test_reverse.test_ignore | 49 +
.../sql/function/string/test_right.test_ignore | 64 +
.../sql/function/string/test_right.test_ignore_old | 84 +
.../sql/function/string/test_similar_to.test | 137 +
.../sql/function/string/test_soundex.test | 28 +
.../sql/function/string/test_space.test | 9 +
.../sql/function/string/test_strcmp.test | 18 +
.../sql/function/string/test_substring.test_ignore | 192 +
.../string/test_substring_utf8.test_ignore | 57 +
.../sql/function/string/test_translate.test | 19 +
.../sql/function/string/test_trim.test_ignore | 106 +
.../sql/function/time/test_extract.test | 74 +
.../sql/function/time/test_extract.test_ignore | 102 +
.../sql/function/timestamp/current_time.test | 6 +
.../function/timestamp/test_extract.test_ignore | 93 +
.../timestamp/test_extract.test_ignore_old | 100 +
.../function/timestamp/test_extract_ms.test_ignore | 23 +
.../timestamp/test_extract_ms.test_ignore_old | 35 +
.../timestamp/test_timestampadd.test_ignore | 90 +
.../sql/function/timestamp/test_timestampdiff.test | 138 +
.../timestamp/test_timestampdiff.test_ignore | 15 +
.../sql/insert/test_big_insert.test | 55 +
.../sql/insert/test_insert.test_ignore | 41 +
.../sql/insert/test_insert_invalid.test | 28 +
.../sql/insert/test_insert_query.test | 19 +
.../sql/insert/test_insert_type.test_ignore | 33 +
.../sql/join/full_outer/test_full_outer_join.test | 53 +
.../full_outer/test_full_outer_join_complex.test | 31 +
.../test_full_outer_join_inequality.test | 31 +
.../test_full_outer_join_many_matches.test_ignore | 22 +
.../full_outer/test_full_outer_join_range.test | 32 +
.../sql/join/inner/join_cross_product.test | 39 +
.../sql/join/inner/test_eq_ineq_join.test | 131 +
.../integrationTest/sql/join/inner/test_join.test | 100 +
.../join/inner/test_join_duplicates.test_ignore | 27 +
.../sql/join/inner/test_join_types.test_ignore | 402 +
.../sql/join/inner/test_lt_join.test_ignore | 31 +
.../sql/join/inner/test_range_join.test | 59 +
.../sql/join/inner/test_unequal_join.test | 83 +
.../inner/test_unequal_join_duplicates.test_ignore | 25 +
.../sql/join/inner/test_using_chain.test | 84 +
.../sql/join/inner/test_using_join.test | 69 +
.../sql/join/inner/test_using_join.test_ignore | 87 +
.../sql/join/inner/test_varchar_join.test | 27 +
.../sql/join/left_outer/left_join_issue_1172.test | 120 +
.../sql/join/left_outer/test_left_outer.test | 180 +
.../sql/join/mark/test_mark_join_types.test_ignore | 678 +
.../sql/join/natural/natural_join.test | 74 +
.../sql/join/natural/natural_join.test_ignore | 122 +
.../sql/join/right_outer/test_right_outer.test | 153 +
.../sql/join/test_complex_join_expr.test | 37 +
.../test_cross_product_parallelism.test_ignore | 22 +
.../sql/join/test_join_on_aggregates.test | 32 +
.../sql/join/test_not_distinct_from.test | 191 +
.../sql/join/test_not_distinct_from.test_ignore | 256 +
.../src/integrationTest/sql/order/test_limit.test | 45 +
.../sql/order/test_limit.test_ignore | 186 +
.../sql/order/test_nulls_first.ignore | 20 +
.../sql/order/test_nulls_first.test | 78 +
.../integrationTest/sql/order/test_order_by.test | 128 +
.../sql/order/test_order_by.test_ignore | 174 +
.../sql/order/test_order_by_exceptions.test | 60 +
.../sql/order/test_order_large.test_ignore | 13 +
.../sql/order/test_order_pragma.test | 28 +
.../order/test_order_same_value.test_slow_ignore | 79 +
.../test_order_variable_size_payload.test_ignore | 408 +
.../src/integrationTest/sql/order/test_top_n.test | 43 +
.../sql/sqlite/aggregates/agg1.test_ignored | 34492 ++++++++++++++++
.../sql/sqlite/aggregates/agg1.test_slow | 34403 ++++++++++++++++
.../sql/sqlite/aggregates/agg2.test_ignored | 32616 +++++++++++++++
.../sql/sqlite/aggregates/agg2.test_slow | 32481 +++++++++++++++
.../sql/sqlite/aggregates/agg3.test_ignored | 33723 +++++++++++++++
.../sql/sqlite/aggregates/agg3.test_slow | 33678 +++++++++++++++
.../sql/sqlite/aggregates/agg4.test_ignored | 34088 ++++++++++++++++
.../sql/sqlite/aggregates/agg4.test_slow | 34042 ++++++++++++++++
.../src/integrationTest/sql/sqlite/join/join1.test | 158 +
.../sql/sqlite/orderby/orderby1_10_1.test_slow | 5758 +++
.../sql/sqlite/orderby/orderby1_10_10.test_slow | 7805 ++++
.../sql/sqlite/orderby/orderby1_10_2.test_slow | 5082 +++
.../sqlite/orderby/orderby1_10_2_long1.test_slow | 123 +
.../sqlite/orderby/orderby1_10_2_long2.test_slow | 133 +
.../sqlite/orderby/orderby1_10_2_long3.test_slow | 132 +
.../sqlite/orderby/orderby1_10_2_long4.test_slow | 135 +
.../sqlite/orderby/orderby1_10_2_long5.test_slow | 127 +
.../sqlite/orderby/orderby1_10_2_long6.test_slow | 125 +
.../sqlite/orderby/orderby1_10_2_long7.test_slow | 129 +
.../sql/sqlite/orderby/orderby1_10_3.test_slow | 6208 +++
.../sql/sqlite/orderby/orderby1_10_4.test_slow | 7477 ++++
.../sql/sqlite/orderby/orderby1_10_5.test_slow | 5472 +++
.../sql/sqlite/orderby/orderby1_10_6.test_slow | 6273 +++
.../sql/sqlite/orderby/orderby1_10_7.test_slow | 4841 +++
.../sql/sqlite/orderby/orderby1_10_8.test_slow | 5260 +++
.../sql/sqlite/orderby/orderby1_10_9.test_slow | 6575 +++
.../sql/sqlite/select1/select1.test_native | 12187 ++++++
.../select1/select1_erroneous_res.test_ignored | 2684 ++
.../select1/select1_hashed_results.test_slow | 7774 ++++
.../sql/sqlite/select1/select1_results.test | 1068 +
.../sql/sqlite/select2/select2.test_native | 11215 +++++
.../select2_erroneous_hash_res.test_ignored | 8490 ++++
.../select2/select2_erroneous_res.test_ignored | 223 +
.../sql/sqlite/select2/select2_hashed_results.test | 1085 +
.../sql/sqlite/select2/select2_results.test | 1487 +
.../sql/sqlite/select3/select3.test_native | 40766 +++++++++++++++++++
.../select3/select3_erroneous_hash_res.test_ignore | 19817 +++++++++
.../select3/select3_erroneous_res.test_ignore | 8962 ++++
.../sql/sqlite/select3/select3_results.test_slow | 11906 ++++++
.../sql/subquery/any_all/test_any_all.test | 82 +
.../any_all/test_correlated_any_all.test_ignore | 182 +
.../any_all/test_scalar_any_all.test_ignore | 50 +
.../sql/subquery/any_all/test_scalar_in.test | 38 +
.../subquery/any_all/test_scalar_in.test_ignore | 77 +
.../sql/subquery/any_all/test_simple_not_in.test | 36 +
.../any_all/test_uncorrelated_all_subquery.test | 196 +
.../test_uncorrelated_all_subquery.test_ignore | 217 +
.../any_all/test_uncorrelated_any_subquery.test | 131 +
.../subquery/exists/test_correlated_exists.test | 67 +
.../exists/test_correlated_exists.test_ignore | 94 +
.../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 | 82 +
.../test_complex_correlated_subquery.test_ignore | 162 +
.../test_complex_nested_correlated_subquery.test | 27 +
..._complex_nested_correlated_subquery.test_ignore | 50 +
.../scalar/test_correlated_aggregate_subquery.test | 110 +
.../test_correlated_aggregate_subquery.test_ignore | 359 +
.../subquery/scalar/test_correlated_subquery.test | 137 +
.../scalar/test_correlated_subquery_cte.test | 123 +
.../test_correlated_subquery_cte.test_ignore | 133 +
.../scalar/test_correlated_subquery_where.test | 33 +
.../subquery/scalar/test_count_star_subquery.test | 20 +
.../scalar/test_count_star_subquery.test_ignore | 102 +
.../sql/subquery/scalar/test_delete_subquery.test | 34 +
.../scalar/test_grouped_correlated_subquery.test | 38 +
.../test_grouped_correlated_subquery.test_ignore | 137 +
.../sql/subquery/scalar/test_join_in_subquery.test | 34 +
.../test_many_correlated_columns.test_ignore | 65 +
.../test_nested_correlated_subquery.test_ignore | 329 +
.../sql/subquery/scalar/test_scalar_subquery.test | 134 +
.../subquery/scalar/test_scalar_subquery_cte.test | 141 +
.../scalar/test_tpcds_correlated_subquery.test | 11 +
.../test_tpcds_correlated_subquery.test_ignore | 16 +
.../scalar/test_uncorrelated_scalar_subquery.test | 41 +
.../test_uncorrelated_scalar_subquery.test_ignore | 128 +
.../scalar/test_uncorrelated_varchar_subquery.test | 67 +
.../sql/subquery/scalar/test_update_subquery.test | 61 +
.../scalar/test_update_subquery.test_ignore | 73 +
.../test_varchar_correlated_subquery.test_ignore | 93 +
.../test_window_function_subquery.test_ignore | 79 +
.../sql/subquery/table/test_aliasing.test_ignore | 24 +
.../subquery/table/test_nested_table_subquery.test | 27 +
.../sql/subquery/table/test_subquery_union.test | 13 +
.../sql/subquery/table/test_table_subquery.test | 44 +
.../subquery/table/test_table_subquery.test_ignore | 55 +
.../integrationTest/sql/subquery/test_neumann.test | 59 +
.../sql/types/blob/test_blob.test_ignore | 108 +
.../sql/types/blob/test_blob.test_ignore_old | 106 +
.../sql/types/blob/test_blob_cast.test | 71 +
.../sql/types/blob/test_blob_function.test_ignore | 84 +
.../sql/types/blob/test_blob_operator.test_ignore | 92 +
.../types/blob/test_blob_operator.test_ignore_old | 65 +
.../sql/types/blob/test_blob_string.test_ignore | 35 +
.../sql/types/collections/array.test_ignore | 24 +
.../sql/types/collections/array_agg.test_ignore | 85 +
.../types/collections/array_agg.test_slow_ignore | 41 +
.../sql/types/collections/list.test_slow_ignore | 12 +
.../types/collections/list_aggregates.test_ignore | 63 +
.../sql/types/collections/map.test_ignore | 22 +
.../sql/types/date/date_parsing.test_ignore | 344 +
.../sql/types/date/test_bc_dates.test_ignore | 87 +
.../sql/types/date/test_date.test_ignore | 71 +
.../sql/types/date/test_incorrect_dates.test | 54 +
.../types/decimal/cast_from_decimal.test_ignore | 73 +
.../sql/types/decimal/cast_to_decimal.test_ignore | 249 +
.../types/decimal/decimal_aggregates.test_ignore | 91 +
.../sql/types/decimal/decimal_arithmetic.test | 197 +
.../decimal/decimal_decimal_overflow_cast.test | 94 +
.../sql/types/decimal/decimal_overflow.test | 51 +
.../sql/types/decimal/decimal_overflow_table.test | 39 +
.../sql/types/decimal/large_decimal_constants.test | 43 +
.../sql/types/decimal/test_decimal.test | 151 +
.../sql/types/decimal/test_decimal_cast.test_slow | 87 +
.../sql/types/decimal/test_decimal_ops.test | 250 +
.../types/interval/interval_constants.test_ignore | 153 +
.../sql/types/interval/test_interval.test | 163 +
.../interval/test_interval_addition.test_ignore | 250 +
.../types/interval/test_interval_comparison.test | 31 +
.../types/interval/test_interval_ops.test_ignore | 51 +
.../interval/test_interval_ops.test_ignore_old | 52 +
.../sql/types/null/test_boolean_null.test | 36 +
.../sql/types/null/test_is_null.test_ignore | 24 +
.../sql/types/null/test_null.test_ignore | 66 +
.../sql/types/null/test_null_aggr.test | 52 +
.../sql/types/string/test_big_strings.test | 29 +
.../types/string/test_scan_big_varchar.test_slow | 152 +
.../sql/types/string/test_unicode.test_ignored | 33 +
.../sql/types/time/test_time.test_ignore | 32 +
.../sql/types/time/time_parsing.test_ignore | 62 +
.../sql/types/timestamp/bc_timestamp.test_ignore | 19 +
.../timestamp/test_incorrect_timestamp.test_ignore | 53 +
.../sql/types/timestamp/test_timestamp.test_ignore | 144 +
.../types/timestamp/test_timestamp_ms.test_ignore | 15 +
.../unsigned/test_unsigned_arithmetic.test_ignored | 99 +
.../unsigned/test_unsigned_auto_cast.test_ignored | 197 +
.../unsigned/test_unsigned_conversion.test_ignored | 507 +
.../sql/update/null_update_merge.test | 128 +
.../sql/update/test_big_string_update.test_ignore | 96 +
.../sql/update/test_null_update.test_ignore | 194 +
.../update/test_repeated_string_update.test_ignore | 62 +
.../sql/update/test_string_update.test_ignore | 66 +
.../test_string_update_many_strings.test_ignore | 128 +
.../sql/update/test_string_update_null.test_ignore | 40 +
.../update/test_string_update_rollback.test_ignore | 118 +
.../test_string_update_rollback_null.test_ignore | 89 +
.../sql/update/test_update.test_ignore | 80 +
.../test_update_delete_same_tuple.test_ignore | 44 +
.../sql/update/test_update_from.test_ignore | 160 +
.../update/test_update_many_updaters.test_ignore | 316 +
.../test_update_many_updaters_nulls.test_ignore | 119 +
.../sql/update/test_update_mix.test_ignore | 75 +
.../sql/update/test_update_same_value.test_ignore | 180 +
.../sql/engine/schema/IgniteTableImpl.java | 4 +-
parent/pom.xml | 1 +
357 files changed, 491650 insertions(+), 2 deletions(-)
diff --git a/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sqllogic/ItSqlLogicTest.java b/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sqllogic/ItSqlLogicTest.java
new file mode 100644
index 000000000..519452624
--- /dev/null
+++ b/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sqllogic/ItSqlLogicTest.java
@@ -0,0 +1,318 @@
+/*
+ * 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.sqllogic;
+
+import static java.util.stream.Collectors.toList;
+import static org.apache.ignite.internal.testframework.matchers.CompletableFutureMatcher.willCompleteSuccessfully;
+import static org.hamcrest.MatcherAssert.assertThat;
+import static org.junit.jupiter.api.Assertions.assertTimeoutPreemptively;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+import com.google.common.base.Strings;
+import java.io.IOException;
+import java.nio.file.FileSystem;
+import java.nio.file.FileSystems;
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.time.Duration;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.concurrent.CompletableFuture;
+import java.util.concurrent.atomic.AtomicBoolean;
+import java.util.regex.Pattern;
+import java.util.stream.IntStream;
+import java.util.stream.Stream;
+import org.apache.ignite.Ignite;
+import org.apache.ignite.IgnitionManager;
+import org.apache.ignite.internal.logger.IgniteLogger;
+import org.apache.ignite.internal.logger.Loggers;
+import org.apache.ignite.internal.sqllogic.SqlLogicTestEnvironment.RestartMode;
+import org.apache.ignite.internal.testframework.SystemPropertiesExtension;
+import org.apache.ignite.internal.testframework.WithSystemProperty;
+import org.apache.ignite.internal.testframework.WorkDirectory;
+import org.apache.ignite.internal.testframework.WorkDirectoryExtension;
+import org.apache.ignite.internal.util.CollectionUtils;
+import org.apache.ignite.internal.util.IgniteUtils;
+import org.apache.ignite.lang.IgniteStringFormatter;
+import org.apache.ignite.lang.IgniteSystemProperties;
+import org.apache.ignite.sql.ResultSet;
+import org.apache.ignite.sql.Session;
+import org.apache.ignite.table.Table;
+import org.junit.jupiter.api.AfterAll;
+import org.junit.jupiter.api.BeforeAll;
+import org.junit.jupiter.api.DynamicContainer;
+import org.junit.jupiter.api.DynamicNode;
+import org.junit.jupiter.api.DynamicTest;
+import org.junit.jupiter.api.Tag;
+import org.junit.jupiter.api.TestFactory;
+import org.junit.jupiter.api.extension.ExtendWith;
+
+/**
+ * Test suite to run SQL test scripts.
+ *
+ * <p>By default, only "*.test" scripts are run.
+ * Other files are ignored.
+ *
+ * <p>"*.test_slow" files are run when system property {@code SQL_LOGIC_TEST_INCLUDE_SLOW} is set to {@code true}.
+ *
+ * <p>All test files consist of appropriate collection of queries.
+ * A query record begins with a line of the following form: query <type-string> <sort-mode> <label>
+ *
+ * <p>The SQL for the query is found on second an subsequent lines of the record up to first line of the form "----"
+ * or until the end of the record. Lines following the "----" are expected results of the query, one value per line. If the "----" and/or
+ * the results are omitted, then the query is expected to return an empty set. The "----" and results are also omitted from prototype
+ * scripts and are always ignored when the sqllogictest program is operating in completion mode. Another way of thinking about completion
+ * mode is that it copies the script from input to output, replacing all "----" lines and subsequent result values with the actual results
+ * from running the query.
+ *
+ * <p>The <type-string> argument to the query statement is a short string that specifies the number of result columns and
+ * the expected datatype of each result column. There is one character in the <type-string> for each result column. The characters
+ * codes are "T" for a text result, "I" for an integer result, and "R" for a floating-point result.
+ *
+ * <p>The <sort-mode> argument is optional. If included, it must be one of "nosort", "rowsort", or "valuesort".
+ * The default is "nosort". In nosort mode, the results appear in exactly the order in which they were received from the database engine.
+ * The nosort mode should only be used on queries that have an ORDER BY clause or which only have a single row of result, since otherwise
+ * the order of results is undefined and might vary from one database engine to another. The "rowsort" mode gathers all output from the
+ * database engine then sorts it by rows on the client side. Sort comparisons use strcmp() on the rendered ASCII text representation of the
+ * values. Hence, "9" sorts after "10", not before. The "valuesort" mode works like rowsort except that it does not honor row groupings.
+ * Each individual result value is sorted on its own.
+ *
+ * <p>The <label> argument is also optional. If included, sqllogictest stores a hash of the results of this query under
+ * the given label. If the label is reused, then sqllogictest verifies that the results are the same. This can be used to verify that two or
+ * more queries in the same test script that are logically equivalent always generate the same output.
+ *
+ * <p>In the results section, integer values are rendered as if by printf("%d"). Floating point values are rendered as
+ * if by printf("%.3f"). NULL values are rendered as "NULL". Empty strings are rendered as "(empty)". Within non-empty strings, all control
+ * characters and unprintable characters are rendered as "@".
+ *
+ * @see <a href="https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki">Extended format documentation.</a>
+ */
+@Tag(value = "sqllogic")
+@ExtendWith({WorkDirectoryExtension.class, SystemPropertiesExtension.class})
+@WithSystemProperty(key = "IMPLICIT_PK_ENABLED", value = "true")
+@SqlLogicTestEnvironment(scriptsRoot = "src/integrationTest/sql")
+public class ItSqlLogicTest {
+ private static final String SQL_LOGIC_TEST_INCLUDE_SLOW = "SQL_LOGIC_TEST_INCLUDE_SLOW";
+
+ private static final String NODE_NAME_PREFIX = "sqllogic";
+
+ private static final FileSystem FS = FileSystems.getDefault();
+
+ private static final IgniteLogger LOG = Loggers.forClass(ItSqlLogicTest.class);
+
+ /** Base port number. */
+ private static final int BASE_PORT = 3344;
+
+ /** Nodes bootstrap configuration pattern. */
+ private static final String NODE_BOOTSTRAP_CFG = "{\n"
+ + " \"network\": {\n"
+ + " \"port\":{},\n"
+ + " \"nodeFinder\":{\n"
+ + " \"netClusterNodes\": [ {} ]\n"
+ + " }\n"
+ + " }\n"
+ + "}";
+
+ /** Cluster nodes. */
+ private static final List<Ignite> CLUSTER_NODES = new ArrayList<>();
+
+ /** Work directory. */
+ @WorkDirectory
+ private static Path WORK_DIR;
+
+ /** Root of the tests scripts. */
+ private static Path SCRIPTS_ROOT;
+
+ /** Count of the nodes in the test cluster. */
+ private static int NODES;
+
+ /** Test timeout. */
+ private static long TIMEOUT;
+
+ /** Regexp to filter tests scropts. */
+ private static Pattern TEST_REGEX;
+
+ /** Cluster restart mode. */
+ private static RestartMode RESTART_CLUSTER;
+
+ /** Flag to include '*.test_slow' scripts to tests run. */
+ private static boolean INCLUDE_SLOW = IgniteSystemProperties.getBoolean(SQL_LOGIC_TEST_INCLUDE_SLOW);
+
+ @BeforeAll
+ static void init() {
+ config();
+
+ startNodes();
+ }
+
+ @AfterAll
+ static void shutdown() throws Exception {
+ stopNodes();
+ }
+
+ @TestFactory
+ public Stream<DynamicNode> sql() {
+ assertTrue(Files.isDirectory(SCRIPTS_ROOT));
+
+ return sqlTestsFolder(SCRIPTS_ROOT);
+ }
+
+ @SuppressWarnings("checkstyle:MissingSwitchDefault")
+ private Stream<DynamicNode> sqlTestsFolder(Path dir) {
+ try {
+ AtomicBoolean first = new AtomicBoolean(true);
+
+ return Files.list(dir).sorted()
+ .filter(p -> (Files.isDirectory(p) && isFolderContainsMatch(p)) || fileMatch(p))
+ .map((p) -> {
+ if (Files.isDirectory(p)) {
+ return DynamicContainer.dynamicContainer(
+ SCRIPTS_ROOT.relativize(p).toString(),
+ sqlTestsFolder(p)
+ );
+ } else {
+ boolean restart = (RESTART_CLUSTER == RestartMode.TEST)
+ || (RESTART_CLUSTER == RestartMode.FOLDER && first.getAndSet(false));
+
+ return DynamicTest.dynamicTest(
+ SCRIPTS_ROOT.relativize(p).toString(),
+ p.toUri(),
+ restart ? () -> {
+ restartCluster();
+ run(p);
+ }
+ : () -> run(p)
+ );
+ }
+ });
+ } catch (Exception e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+ private boolean isFolderContainsMatch(Path folder) {
+ try {
+ return Files.walk(folder).anyMatch(p -> !Files.isDirectory(p) && fileMatch(p));
+ } catch (IOException e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+ private boolean fileMatch(Path file) {
+ return (TEST_REGEX != null && TEST_REGEX.matcher(file.toString()).find())
+ || (TEST_REGEX == null && (file.toString().endsWith(".test") || (INCLUDE_SLOW && file.toString().endsWith(".test_slow"))));
+ }
+
+ private void run(Path testPath) {
+ beforeTest();
+
+ LOG.info(">>> Start: " + SCRIPTS_ROOT.relativize(testPath));
+
+ SqlScriptRunner r = new SqlScriptRunner(
+ testPath,
+ CollectionUtils.first(CLUSTER_NODES).sql(),
+ LOG
+ );
+
+ try {
+ if (testPath.toString().endsWith("_slow")) {
+ r.run();
+ } else {
+ assertTimeoutPreemptively(Duration.ofMillis(TIMEOUT), r::run);
+ }
+ } catch (Error | RuntimeException e) {
+ throw e;
+ } catch (Exception e) {
+ throw new RuntimeException(e);
+ }
+ }
+
+ private void beforeTest() {
+ if (RESTART_CLUSTER != RestartMode.TEST) {
+ for (Table t : CLUSTER_NODES.get(0).tables().tables()) {
+ try (Session s = CLUSTER_NODES.get(0).sql().createSession()) {
+ try (ResultSet rs = s.execute(null, "DROP TABLE " + t.name())) {
+ assertTrue(rs.wasApplied());
+ }
+ }
+ }
+ }
+ }
+
+ private static void config() {
+ SqlLogicTestEnvironment env = ItSqlLogicTest.class.getAnnotation(SqlLogicTestEnvironment.class);
+
+ assert env != null;
+ assert !Strings.isNullOrEmpty(env.scriptsRoot());
+
+ SCRIPTS_ROOT = FS.getPath(env.scriptsRoot());
+ NODES = env.nodes();
+ TEST_REGEX = Strings.isNullOrEmpty(env.regex()) ? null : Pattern.compile(env.regex());
+ RESTART_CLUSTER = env.restart();
+ TIMEOUT = env.timeout();
+ }
+
+ private static void restartCluster() throws Exception {
+ stopNodes();
+
+ IgniteUtils.deleteIfExists(WORK_DIR);
+
+ Files.createDirectories(WORK_DIR);
+
+ startNodes();
+ }
+
+ private static void startNodes() {
+ String connectNodeAddr = "\"localhost:" + BASE_PORT + '\"';
+
+ List<CompletableFuture<Ignite>> futures = IntStream.range(0, NODES)
+ .mapToObj(i -> {
+ String nodeName = NODE_NAME_PREFIX + i;
+
+ String config = IgniteStringFormatter.format(NODE_BOOTSTRAP_CFG, BASE_PORT + i, connectNodeAddr);
+
+ return IgnitionManager.start(nodeName, config, WORK_DIR.resolve(nodeName));
+ })
+ .collect(toList());
+
+ String metaStorageNodeName = NODE_NAME_PREFIX + "0";
+
+ IgnitionManager.init(metaStorageNodeName, List.of(metaStorageNodeName), "cluster");
+
+ for (CompletableFuture<Ignite> future : futures) {
+ assertThat(future, willCompleteSuccessfully());
+
+ CLUSTER_NODES.add(future.join());
+ }
+ }
+
+ private static void stopNodes() throws Exception {
+ LOG.info(">>> Stopping cluster...");
+
+ CLUSTER_NODES.clear();
+
+ List<AutoCloseable> closeables = IntStream.range(0, NODES)
+ .mapToObj(i -> NODE_NAME_PREFIX + i)
+ .map(nodeName -> (AutoCloseable) () -> IgnitionManager.stop(nodeName))
+ .collect(toList());
+
+ IgniteUtils.closeAll(closeables);
+
+ LOG.info(">>> Cluster is stopped.");
+ }
+}
diff --git a/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sqllogic/SqlLogicTestEnvironment.java b/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sqllogic/SqlLogicTestEnvironment.java
new file mode 100644
index 000000000..d695bc520
--- /dev/null
+++ b/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sqllogic/SqlLogicTestEnvironment.java
@@ -0,0 +1,80 @@
+/*
+ * 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.sqllogic;
+
+import java.lang.annotation.ElementType;
+import java.lang.annotation.Retention;
+import java.lang.annotation.RetentionPolicy;
+import java.lang.annotation.Target;
+
+/**
+ * Scripts run environment.
+ */
+@Retention(RetentionPolicy.RUNTIME)
+@Target(ElementType.TYPE)
+public @interface SqlLogicTestEnvironment {
+ /**
+ * Test scripts root directory.
+ *
+ * @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 "";
+
+ /**
+ * Ignite nodes count.
+ *
+ * @return Ignite nodes count.
+ */
+ int nodes() default 2;
+
+ /**
+ * Test timeout (3 min by default).
+ *
+ * @return Test timeout in millis.
+ */
+ long timeout() default 3 * 60 * 1000L;
+
+ /**
+ * Cluster restart mode.
+ *
+ * @return Cluster restart mode.
+ */
+ RestartMode restart() default RestartMode.NONE;
+
+ /**
+ * Cluster restart mode.
+ */
+ enum RestartMode {
+ /** Cluster isn't restarted. */
+ NONE,
+
+ /** Restart cluster for each tests' folder. */
+ FOLDER,
+
+ /** Restart cluster for each test script. */
+ TEST
+ }
+}
diff --git a/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sqllogic/SqlScriptRunner.java b/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sqllogic/SqlScriptRunner.java
new file mode 100644
index 000000000..cd211a51a
--- /dev/null
+++ b/modules/runner/src/integrationTest/java/org/apache/ignite/internal/sqllogic/SqlScriptRunner.java
@@ -0,0 +1,761 @@
+/*
+ * 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.sqllogic;
+
+import com.google.common.base.Strings;
+import com.google.common.collect.Streams;
+import java.io.BufferedReader;
+import java.io.IOException;
+import java.math.BigDecimal;
+import java.nio.charset.Charset;
+import java.nio.charset.StandardCharsets;
+import java.nio.file.Files;
+import java.nio.file.Path;
+import java.security.MessageDigest;
+import java.security.NoSuchAlgorithmException;
+import java.util.ArrayList;
+import java.util.Arrays;
+import java.util.Collection;
+import java.util.Collections;
+import java.util.Comparator;
+import java.util.HashMap;
+import java.util.Iterator;
+import java.util.List;
+import java.util.Map;
+import java.util.NoSuchElementException;
+import java.util.Objects;
+import java.util.Set;
+import java.util.TreeMap;
+import java.util.regex.Matcher;
+import java.util.regex.Pattern;
+import java.util.stream.Collectors;
+import org.apache.calcite.avatica.util.ByteString;
+import org.apache.ignite.internal.logger.IgniteLogger;
+import org.apache.ignite.internal.util.ArrayUtils;
+import org.apache.ignite.internal.util.CollectionUtils;
+import org.apache.ignite.internal.util.IgniteUtils;
+import org.apache.ignite.lang.IgniteException;
+import org.apache.ignite.sql.IgniteSql;
+import org.apache.ignite.sql.ResultSet;
+import org.apache.ignite.sql.Session;
+import org.jetbrains.annotations.NotNull;
+
+/**
+ * Runs one SQL test script.
+ */
+public class SqlScriptRunner {
+ /** UTF-8 character name. */
+ static final Charset UTF_8 = StandardCharsets.UTF_8;
+
+ /** 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 = Set.of("PRAGMA");
+
+ /** NULL label. */
+ private static final String NULL = "NULL";
+
+ /** Comparator for "rowsort" sort mode. */
+ private static final Comparator<List<?>> ROW_COMPARATOR = (r1, r2) -> {
+ int rows = r1.size();
+
+ for (int i = 0; i < rows; ++i) {
+ String s1 = toString(r1.get(i));
+ String s2 = toString(r2.get(i));
+
+ int comp = s1.compareTo(s2);
+
+ if (comp != 0) {
+ return comp;
+ }
+ }
+
+ return 0;
+ };
+
+ /** Comparator for "valuesort" sort mode. */
+ private static final Comparator<Object> ITM_COMPARATOR = (r1, r2) -> {
+ String s1 = String.valueOf(r1);
+ String s2 = String.valueOf(r2);
+
+ return s1.compareTo(s2);
+ };
+
+ /** Test script path. */
+ private final Path test;
+
+ /** Query engine. */
+ private final IgniteSql ignSql;
+
+ /** Loop variables. */
+ private final Map<String, Integer> loopVars = new HashMap<>();
+
+ /** Logger. */
+ private final IgniteLogger log;
+
+ /** Script. */
+ private Script script;
+
+ /**
+ * Line separator to bytes representation.
+ * NB: Don't use {@code System.lineSeparator()} here.
+ * The separator is used to calculate hash by SQL results and mustn't be changed on the different platforms.
+ */
+ private static final byte[] LINE_SEPARATOR_BYTES = "\n".getBytes(Charset.forName(UTF_8.name()));
+
+ /** Equivalent results store. */
+ private Map<String, Collection<String>> eqResStorage = new HashMap<>();
+
+ /** Hash algo. */
+ MessageDigest messageDigest;
+
+ /**
+ * Script runner constructor.
+ */
+ public SqlScriptRunner(Path test, IgniteSql ignSql, IgniteLogger log) {
+ this.test = test;
+ this.ignSql = ignSql;
+ this.log = log;
+
+ try {
+ messageDigest = MessageDigest.getInstance("MD5");
+ } catch (NoSuchAlgorithmException e) {
+ throw new IgniteException(e);
+ }
+ }
+
+ /**
+ * Executes SQL file script.
+ */
+ 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);
+
+ try (Session s = ignSql.createSession()) {
+ try (ResultSet rs = s.execute(null, sql)) {
+ if (rs.hasRowSet()) {
+ return Streams.stream(rs).map(
+ r -> {
+ List<?> row = new ArrayList<>();
+
+ for (int i = 0; i < rs.metadata().columns().size(); ++i) {
+ row.add(r.value(i));
+ }
+
+ return row;
+ })
+ .collect(Collectors.toList());
+ } else if (rs.affectedRows() != -1) {
+ return Collections.singletonList(Collections.singletonList(rs.affectedRows()));
+ } else {
+ return Collections.singletonList(Collections.singletonList(rs.wasApplied()));
+ }
+ }
+ }
+ }
+
+ private static String toString(Object res) {
+ if (res instanceof byte[]) {
+ return ByteString.toString((byte[]) res, 16);
+ } else if (res instanceof Map) {
+ return mapToString((Map<?, ?>) res);
+ } else {
+ return String.valueOf(res);
+ }
+ }
+
+ private static String mapToString(Map<?, ?> map) {
+ if (map == null) {
+ return NULL;
+ }
+
+ List<String> entries = (new TreeMap<>(map)).entrySet().stream()
+ .map(e -> toString(e.getKey()) + ":" + toString(e.getValue()))
+ .collect(Collectors.toList());
+
+ return "{" + String.join(", ", entries) + "}";
+ }
+
+ private class Script implements Iterable<Command>, AutoCloseable {
+ private final String fileName;
+
+ private final BufferedReader buffReader;
+
+ private int lineNum;
+
+ Script(Path test) throws IOException {
+ fileName = test.getFileName().toString();
+
+ buffReader = Files.newBufferedReader(test);
+ }
+
+ String nextLine() throws IOException {
+ return nextLineWithoutTrim();
+ }
+
+ String nextLineWithoutTrim() throws IOException {
+ String s = buffReader.readLine();
+
+ lineNum++;
+
+ return s;
+ }
+
+ boolean ready() throws IOException {
+ return buffReader.ready();
+ }
+
+ String positionDescription() {
+ return '(' + fileName + ':' + lineNum + ')';
+ }
+
+ @Override public void close() throws Exception {
+ buffReader.close();
+ }
+
+ private Command nextCommand() {
+ try {
+ while (script.ready()) {
+ String s = script.nextLine();
+
+ if (Strings.isNullOrEmpty(s) || s.startsWith("#")) {
+ continue;
+ }
+
+ String[] tokens = s.split("\\s+");
+
+ assert !ArrayUtils.nullOrEmpty(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<>() {
+ 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 {
+ @Override void execute() {
+ // No-op.
+ }
+ }
+
+ private class Statement extends Command {
+ List<String> queries;
+
+ 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 (Strings.isNullOrEmpty(s)) {
+ break;
+ }
+
+ queries.add(s);
+ }
+ }
+
+ @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);
+ }
+ }
+ }
+ }
+
+ @Override
+ public String toString() {
+ return "Statement ["
+ + "queries=" + queries
+ + ", expected=" + expected
+ + ']';
+ }
+ }
+
+ private class Query extends Command {
+ List<ColumnType> resTypes = new ArrayList<>();
+
+ StringBuilder sql = new StringBuilder();
+
+ List<List<String>> expectedRes;
+
+ String expectedHash;
+
+ int expectedRows;
+
+ /** Sorting algo. */
+ SortType sortType = SortType.NOSORT;
+
+ /** Equality label. */
+ String eqLabel;
+
+ Query(String[] cmd) throws IOException {
+ String resTypesChars = cmd[1];
+
+ if (cmd.length > 2) {
+ sortType = SortType.valueOf(cmd[2].toUpperCase());
+ }
+
+ if (cmd.length > 3) {
+ eqLabel = cmd[3].toLowerCase();
+ }
+
+ 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) + "' at: "
+ + script.positionDescription() + "[cmd=" + Arrays.toString(cmd) + ']');
+ }
+ }
+
+ if (CollectionUtils.nullOrEmpty(resTypes)) {
+ throw new IgniteException("Missing type string at: "
+ + 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("\n");
+ }
+
+ sql.append(s);
+ }
+
+ // Read expected results
+ String s = script.nextLineWithoutTrim();
+ 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 (!Strings.isNullOrEmpty(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 at: "
+ + 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 at: "
+ + script.positionDescription() + "[row=\"" + s + "\", types=" + resTypes + ']', e);
+ }
+
+ s = script.nextLineWithoutTrim();
+ }
+ }
+ }
+
+ @Override void execute() {
+ try {
+ List<List<?>> res = sql(sql.toString());
+
+ checkResult(res);
+ } catch (Throwable e) {
+ throw new IgniteException("Error at: " + posDesc + ". sql: " + sql, e);
+ }
+ }
+
+ void checkResult(List<List<?>> res) {
+ if (sortType == SortType.ROWSORT) {
+ res.sort(ROW_COMPARATOR);
+
+ if (expectedRes != null) {
+ expectedRes.sort(ROW_COMPARATOR);
+ }
+ } else if (sortType == SortType.VALUESORT) {
+ List<Object> flattenRes = new ArrayList<>();
+
+ res.forEach(flattenRes::addAll);
+
+ flattenRes.sort(ITM_COMPARATOR);
+
+ List<List<?>> resSizeAware = new ArrayList<>();
+ int rowLen = resTypes.size();
+ List rowRes = new ArrayList(rowLen);
+
+ for (Object item : flattenRes) {
+ rowRes.add(item);
+
+ if (--rowLen == 0) {
+ resSizeAware.add(rowRes);
+ rowRes = new ArrayList(rowLen);
+ rowLen = resTypes.size();
+ }
+ }
+
+ res = resSizeAware;
+ }
+
+ 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
+ + ". [expectedRows=" + expectedRes.size() + ", actualRows=" + res.size()
+ + ", 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=" + SqlScriptRunner.toString(row.get(j)) + ']',
+ expectedRow.get(j),
+ row.get(j)
+ );
+ }
+ }
+ }
+
+ private void checkEquals(String msg, String expectedStr, Object actual) {
+ if (actual == null && String.valueOf(actual).equalsIgnoreCase(expectedStr)) {
+ return;
+ }
+
+ if (actual != null ^ expectedStr != null) {
+ throw new AssertionError(msg);
+ }
+
+ if (actual instanceof Number) {
+ if ("NaN".equals(expectedStr) || expectedStr.endsWith("Infinity")) {
+ if (!expectedStr.equals(String.valueOf(actual))) {
+ throw new AssertionError(msg);
+ }
+ } else {
+ BigDecimal actDec = new BigDecimal(String.valueOf(actual));
+ BigDecimal expDec = new BigDecimal(expectedStr);
+
+ if (actDec.compareTo(expDec) != 0) {
+ throw new AssertionError(msg);
+ }
+ }
+ } else if (actual instanceof Map) {
+ String actualStr = mapToString((Map<? extends Comparable, ?>) actual);
+
+ if (!expectedStr.equals(actualStr)) {
+ throw new AssertionError(msg);
+ }
+ } else {
+ if (!String.valueOf(expectedStr).equals(SqlScriptRunner.toString(actual))
+ && !("(empty)".equals(expectedStr) && SqlScriptRunner.toString(actual).isEmpty())) {
+ throw new AssertionError(msg);
+ }
+ }
+ }
+
+ private void checkResultsHashed(List<List<?>> res) {
+ Objects.requireNonNull(res, "empty result set");
+
+ messageDigest.reset();
+
+ for (List<?> row : res) {
+ for (Object col : row) {
+ messageDigest.update(SqlScriptRunner.toString(col).getBytes(Charset.forName(UTF_8.name())));
+ messageDigest.update(LINE_SEPARATOR_BYTES);
+ }
+ }
+
+ String res0 = IgniteUtils.toHexString(messageDigest.digest());
+
+ if (eqLabel != null) {
+ if (res0.equals(expectedHash)) {
+ eqResStorage.computeIfAbsent(eqLabel, k -> new ArrayList<>()).add(sql.toString());
+ } else {
+ Collection<String> eq = eqResStorage.get(eqLabel);
+
+ if (eq != null) {
+ throw new AssertionError("Results of queries need to be equal: " + eq
+ + "\n and \n" + sql);
+ }
+ }
+ }
+
+ if (!res0.equalsIgnoreCase(expectedHash)) {
+ throw new AssertionError("Unexpected hash result, error at: " + posDesc
+ + ", expected=" + expectedHash + ", calculated=" + res0
+ + ", expectedRows=" + expectedRows + ", returnedRows=" + res.size() * res.get(0).size());
+ }
+ }
+
+ @Override
+ public String toString() {
+ return "Query ["
+ + "sql=" + sql
+ + ", resTypes=" + resTypes
+ + ", expectedRes=" + expectedRes
+ + ", expectedHash='" + expectedHash + '\''
+ + ", expectedRows=" + expectedRows
+ + ", sortType=" + sortType
+ + ", eqLabel='" + eqLabel + '\''
+ + ']';
+ }
+ }
+
+ private enum ExpectedStatementStatus {
+ OK,
+ ERROR
+ }
+
+ private enum ColumnType {
+ I,
+ T,
+ R
+ }
+
+ private enum SortType {
+ ROWSORT,
+ VALUESORT,
+ NOSORT
+ }
+}
diff --git a/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_aggr_string.test b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_aggr_string.test
new file mode 100644
index 000000000..29c005bc9
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_aggr_string.test
@@ -0,0 +1,82 @@
+# name: test/sql/aggregate/aggregates/test_aggr_string.test
+# description: Test aggregations on strings
+# group: [aggregates]
+
+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 NULLS FIRST
+----
+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 NULLS LAST;
+----
+hello 11.000000
+world 12.000000
+NULL 11.000000
+
+# distinct aggregations on string
+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
+
+# string min/max with long strings 2
+statement ok
+CREATE TABLE test_strings2(s VARCHAR);
+INSERT INTO test_strings2 VALUES ('a'), ('aa'), ('A'), ('AA'), ('D')
+
+query II
+SELECT MAX(s), MIN(s) FROM test_strings2;
+----
+aa A
+
+
diff --git a/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_aggregate_types.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_aggregate_types.test_ignore
new file mode 100644
index 000000000..8dcab2666
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_aggregate_types.test_ignore
@@ -0,0 +1,190 @@
+# name: test/sql/aggregate/aggregates/test_aggregate_types.test
+# description: Test aggregates with many different types
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-17271
+
+statement ok
+CREATE TABLE strings(s VARCHAR, g INTEGER)
+
+statement ok
+INSERT INTO strings VALUES ('hello', 0), ('world', 1), (NULL, 0), ('my', 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, ' ' ORDER BY s ASC), STRING_AGG(s, '' ORDER BY s ASC), STRING_AGG('', ''), STRING_AGG('hello', ' ') FROM strings
+----
+hello my world
+hellomyworld
+(empty)
+hello hello hello hello
+
+# more complex agg (groups)
+query IIITTT
+SELECT g, COUNT(*), COUNT(s), MIN(s), MAX(s), STRING_AGG(s, ' ' ORDER BY s ASC) FROM strings GROUP BY g ORDER BY g
+----
+0
+2
+1
+hello
+hello
+hello
+1
+2
+2
+my
+world
+my world
+
+# empty group
+query IIITTT
+SELECT g, COUNT(*), COUNT(s), MIN(s), MAX(s), STRING_AGG(s, ' ' ORDER BY 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
+my
+world
+my world
+
+# 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
+false
+true
+
+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
+false
+false
+1
+2
+2
+false
+true
+
+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
+true
+true
+
+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/runner/src/integrationTest/sql/aggregate/aggregates/test_aggregate_types_scalar.test_ignored b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_aggregate_types_scalar.test_ignored
new file mode 100644
index 000000000..168ef04f2
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_aggregate_types_scalar.test_ignored
@@ -0,0 +1,110 @@
+# name: test/sql/aggregate/aggregates/test_aggregate_types_scalar.test
+# description: Test scalar aggregates with many different types
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-17271
+
+query IIIII
+SELECT COUNT(1), COUNT(*), COUNT(NULL), COUNT('hello'), COUNT(DATE '1992-02-02')
+----
+1
+1
+0
+1
+1
+
+statement error
+SELECT COUNT(1, 2)
+
+statement error
+SELECT COUNT()
+
+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 true 1992-02-02 2008-01-01 00:00:01.0
+
+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 true 1992-02-02 2008-01-01 00:00:01.0
+
+statement error
+SELECT MAX()
+
+statement error
+SELECT MAX(1, 2)
+
+query IIRTTTT
+SELECT ANY_VALUE(1), ANY_VALUE(NULL), ANY_VALUE(33.3), ANY_VALUE('hello'), ANY_VALUE(True), ANY_VALUE(DATE '1992-02-02'), ANY_VALUE(TIMESTAMP '2008-01-01 00:00:01')
+----
+1 NULL 33.300000 hello true 1992-02-02 2008-01-01 00:00:01.0
+
+statement error
+SELECT ANY_VALUE()
+
+statement error
+SELECT ANY_VALUE(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 hello NULL NULL (empty)
+
+statement error
+SELECT STRING_AGG()
+
+statement error
+SELECT STRING_AGG(1, 2, 3)
diff --git a/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_approx_quantile.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_approx_quantile.test_ignore
new file mode 100644
index 000000000..66cf6cdf1
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_approx_quantile.test_ignore
@@ -0,0 +1,170 @@
+# name: test/sql/aggregate/aggregates/test_approx_quantile.test
+# description: Test approx quantile operator
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14637
+
+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/runner/src/integrationTest/sql/aggregate/aggregates/test_approximate_distinct_count.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_approximate_distinct_count.test_ignore
new file mode 100644
index 000000000..4b14f5151
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_approximate_distinct_count.test_ignore
@@ -0,0 +1,93 @@
+# name: test/sql/aggregate/aggregates/test_approximate_distinct_count.test
+# description: Test approx_count_distinct operator
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14637
+
+
+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/runner/src/integrationTest/sql/aggregate/aggregates/test_arg_min_max.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_arg_min_max.test_ignore
new file mode 100644
index 000000000..f1bbeb7f4
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_arg_min_max.test_ignore
@@ -0,0 +1,131 @@
+# name: test/sql/aggregate/aggregates/test_arg_min_max.test
+# description: Test arg_min and arg_max operator
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14639
+
+#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/runner/src/integrationTest/sql/aggregate/aggregates/test_avg.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_avg.test_ignore
new file mode 100644
index 000000000..1d1dad869
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_avg.test_ignore
@@ -0,0 +1,40 @@
+# name: test/sql/aggregate/aggregates/test_avg.test
+# description: Test AVG operator
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-17271
+
+# scalar average
+query RR
+SELECT AVG(3), AVG(NULL)
+----
+3
+NULL
+
+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/runner/src/integrationTest/sql/aggregate/aggregates/test_avg.test_ignored b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_avg.test_ignored
new file mode 100644
index 000000000..bf32a2613
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_avg.test_ignored
@@ -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/runner/src/integrationTest/sql/aggregate/aggregates/test_bit_and.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_bit_and.test_ignore
new file mode 100644
index 000000000..082fe2cd0
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_bit_and.test_ignore
@@ -0,0 +1,57 @@
+# name: test/sql/aggregate/aggregates/test_bit_and.test
+# description: Test BIT_AND operator
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14640
+
+# 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/runner/src/integrationTest/sql/aggregate/aggregates/test_bit_or.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_bit_or.test_ignore
new file mode 100644
index 000000000..1fe325e77
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_bit_or.test_ignore
@@ -0,0 +1,57 @@
+# name: test/sql/aggregate/aggregates/test_bit_or.test
+# description: Test BIT_OR operator
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14640
+
+# 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/runner/src/integrationTest/sql/aggregate/aggregates/test_bit_xor.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_bit_xor.test_ignore
new file mode 100644
index 000000000..4b9fb0b4f
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_bit_xor.test_ignore
@@ -0,0 +1,57 @@
+# name: test/sql/aggregate/aggregates/test_bit_xor.test
+# description: Test BIT_XOR operator
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14640
+
+# 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/runner/src/integrationTest/sql/aggregate/aggregates/test_count.test b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_count.test
new file mode 100644
index 000000000..63302ff63
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_count.test
@@ -0,0 +1,51 @@
+# name: test/sql/aggregate/aggregates/test_count.test
+# description: Test COUNT operator
+# group: [aggregates]
+
+# 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
+
+# Multiple arguments are not supported as it is not a part of SQL standard.
+statement error
+SELECT COUNT(1, 1)
+
+statement error
+SELECT COUNT(null, 1)
+
+statement error
+SELECT COUNT(1, null)
+
+statement error
+SELECT COUNT(null, null)
diff --git a/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_count_star.test b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_count_star.test
new file mode 100644
index 000000000..4577aa70f
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_count_star.test
@@ -0,0 +1,46 @@
+# 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
+
+statement ok
+INSERT INTO integers VALUES (3, null), (null, 4), (null, null);
+
+query IIIII
+SELECT COUNT(*), COUNT(i), COUNT(j), COUNT(COALESCE(i, j)), COUNT(null) FROM integers
+----
+6
+4
+4
+5
+0
+
+# Multiple arguments are not supported as it is not a part of SQL standard.
+statement error
+SELECT COUNT(null, null) FROM integers
+
+statement error
+SELECT COUNT(1, null) FROM integers
+
+statement error
+SELECT COUNT(i, null) FROM integers
+
+statement error
+SELECT COUNT(null, 1) FROM integers
+
+statement error
+SELECT COUNT(null, j) FROM integers
+
+statement error
+SELECT COUNT(i, j) FROM integers
\ No newline at end of file
diff --git a/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_covar.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_covar.test_ignore
new file mode 100644
index 000000000..60fdab316
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_covar.test_ignore
@@ -0,0 +1,100 @@
+# name: test/sql/aggregate/aggregates/test_covar.test
+# description: Test COVAR operators
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14641
+
+
+# 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/runner/src/integrationTest/sql/aggregate/aggregates/test_distinct_aggr.test b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_distinct_aggr.test
new file mode 100644
index 000000000..6cc863f88
--- /dev/null
+++ b/modules/runner/src/integrationTest/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/runner/src/integrationTest/sql/aggregate/aggregates/test_distinct_string_agg.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_distinct_string_agg.test_ignore
new file mode 100644
index 000000000..090ba5d0b
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_distinct_string_agg.test_ignore
@@ -0,0 +1,22 @@
+# name: test/sql/aggregate/aggregates/test_distinct_string_agg.test
+# description: Test distinct STRING_AGG operator
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-17271
+
+statement ok
+CREATE TABLE strings(i INTEGER, s VARCHAR);
+
+statement ok
+INSERT INTO strings VALUES (3, 'a'), (1, 'b'), (2, 'a');
+
+query TT
+SELECT STRING_AGG(s,',' ORDER BY s ASC), STRING_AGG(DISTINCT s, ',' ORDER BY s ASC) FROM strings
+----
+a,a,b
+a,b
+
+query TT
+SELECT STRING_AGG(s,',' ORDER BY i ASC), STRING_AGG(DISTINCT s, ',' ORDER BY i ASC) FROM strings
+----
+b,a,a
+b,a
diff --git a/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_empty_aggregate.test b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_empty_aggregate.test
new file mode 100644
index 000000000..2c51be598
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_empty_aggregate.test
@@ -0,0 +1,29 @@
+# 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 IIRRRI
+SELECT COUNT(*), COUNT(i), SUM(i), SUM(DISTINCT i), MAX(i), MIN(i) FROM integers WHERE i > 100
+----
+0
+0
+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/runner/src/integrationTest/sql/aggregate/aggregates/test_empty_aggregate.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_empty_aggregate.test_ignore
new file mode 100644
index 000000000..0b655bcbb
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_empty_aggregate.test_ignore
@@ -0,0 +1,31 @@
+# name: test/sql/aggregate/aggregates/test_empty_aggregate.test
+# description: Test aggregate operators on empty set
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14642
+
+statement ok
+CREATE TABLE integers(i INTEGER);
+
+query IIRRRII
+SELECT COUNT(*), COUNT(i), STDDEV_SAMP(i), SUM(i), SUM(DISTINCT i), MAX(i), MIN(i) FROM integers WHERE i > 100
+----
+0
+0
+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/runner/src/integrationTest/sql/aggregate/aggregates/test_group_by_many_groups.test_slow_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_group_by_many_groups.test_slow_ignore
new file mode 100644
index 000000000..8304dd767
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_group_by_many_groups.test_slow_ignore
@@ -0,0 +1,15 @@
+# name: test/sql/aggregate/aggregates/test_group_by_many_groups.test_slow
+# description: Test GROUP BY with many groups
+# group: [aggregates]
+# Ignore: https://issues.apache.org/jira/browse/IGNITE-17268
+
+statement ok
+CREATE TABLE integers AS SELECT x AS i, 1 AS j FROM table(system_range(0, 9999, 1)) UNION ALL SELECT x AS i, 2 j FROM table(system_range(0, 9999, 1));
+
+
+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/runner/src/integrationTest/sql/aggregate/aggregates/test_group_on_expression.test b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_group_on_expression.test
new file mode 100644
index 000000000..d90f78aac
--- /dev/null
+++ b/modules/runner/src/integrationTest/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/runner/src/integrationTest/sql/aggregate/aggregates/test_histogram.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_histogram.test_ignore
new file mode 100644
index 000000000..dc0b8232f
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_histogram.test_ignore
@@ -0,0 +1,94 @@
+# name: test/sql/aggregate/aggregates/test_histogram.test
+# description: Test histogram aggregation
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14637
+
+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/runner/src/integrationTest/sql/aggregate/aggregates/test_mode.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_mode.test_ignore
new file mode 100644
index 000000000..0dc81b6bd
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_mode.test_ignore
@@ -0,0 +1,97 @@
+# name: test/sql/aggregate/aggregates/test_mode.test
+# description: Test mode function
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14637
+
+#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/runner/src/integrationTest/sql/aggregate/aggregates/test_null_aggregates.test b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_null_aggregates.test
new file mode 100644
index 000000000..360b941d1
--- /dev/null
+++ b/modules/runner/src/integrationTest/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 NULLS LAST, 2, 3, 4
+----
+-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
+NULL NULL NULL NULL
diff --git a/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_perfect_ht.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_perfect_ht.test_ignore
new file mode 100644
index 000000000..403e8d5f5
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_perfect_ht.test_ignore
@@ -0,0 +1,178 @@
+# 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-17271
+
+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 NULLS LAST;
+----
+1996 30 2 3
+1997 12 1 1
+2001 30 1 1
+NULL 1 1 1
+
+# use aggregates with destructors
+query II
+SELECT year, STRING_AGG(val ORDER BY val) FROM timeseries GROUP BY year ORDER BY year;
+----
+NULL 1
+1996 10,20
+1997 12
+2001 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 x a, x b, x c, x d, x e from table(system_range(0,1));
+
+query IIIII
+select a, b, c, d, e FROM manycolumns GROUP BY a, b, c, d, e ORDER BY 1
+----
+0 0 0 0 0
+1 1 1 1 1
+
+# test edge cases: multiple tinyints without statistics
+# create a table of tinyints [-127, 127] stored as varchar
+# by forcing a varchar to tinyint cast we lose statistics
+statement ok
+CREATE TABLE tinyints AS SELECT x::TINYINT::VARCHAR AS t FROM table(system_range(-127, 127));
+
+query IIII
+SELECT COUNT(DISTINCT i), MIN(i), MAX(i), SUM(i) / COUNT(i) FROM (SELECT t::TINYINT i FROM tinyints GROUP BY t)
+----
+255 -127 127 0
+
+# now do the same with a single smallint column
+statement ok
+CREATE TABLE smallints AS SELECT x::SMALLINT::VARCHAR AS t FROM table(system_range(-32767, 32767));
+
+query IIII
+SELECT COUNT(DISTINCT i), MIN(i), MAX(i), SUM(i) / COUNT(i) FROM (SELECT t::SMALLINT i FROM smallints GROUP BY t)
+----
+65535 -32767 32767 0
+
+# test result ordering of perfect HT
+statement ok
+PRAGMA disable_verification
+
+statement ok
+create table dates(d date);
+
+statement ok
+insert into dates select date '1992-01-01' + interval x months as d from table(system_range(0, 99));
+
+query II
+select extract(year from d), extract(month from d) from dates group by extract(year from d), extract(month from d) order 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/runner/src/integrationTest/sql/aggregate/aggregates/test_perfect_ht.test_ignore_old b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_perfect_ht.test_ignore_old
new file mode 100644
index 000000000..8aebebaba
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_perfect_ht.test_ignore_old
@@ -0,0 +1,176 @@
+# 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-14730
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14885
+
+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;
+----
+NULL 1 1 1
+1996 30 2 3
+1997 12 1 1
+2001 30 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 x a, x b, x c, x d, x e from table(system_range(0,1));
+
+query IIIII
+select a, b, c, d, e FROM manycolumns GROUP BY a, b, c, d, e ORDER BY 1
+----
+0 0 0 0 0
+1 1 1 1 1
+
+# test edge cases: multiple tinyints without statistics
+# create a table of tinyints [-127, 127] stored as varchar
+# by forcing a varchar to tinyint cast we lose statistics
+statement ok
+CREATE TABLE tinyints AS SELECT x::TINYINT::VARCHAR AS t FROM table(system_range(-127, 127));
+
+query IIII
+SELECT COUNT(DISTINCT i), MIN(i), MAX(i), SUM(i) / COUNT(i) FROM (SELECT t::TINYINT i FROM tinyints GROUP BY t)
+----
+255 -127 127 0
+
+# now do the same with a single smallint column
+statement ok
+CREATE TABLE smallints AS SELECT x::SMALLINT::VARCHAR AS t FROM table(system_range(-32767, 32767));
+
+query IIII
+SELECT COUNT(DISTINCT i), MIN(i), MAX(i), SUM(i) / COUNT(i) FROM (SELECT t::SMALLINT i FROM smallints GROUP BY t)
+----
+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(x, ' months')::interval as d from table(system_range(0, 99));
+
+query II
+select extract(year from d), extract(month from d) from dates group by 1, 2 order 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/runner/src/integrationTest/sql/aggregate/aggregates/test_quantile.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_quantile.test_ignore
new file mode 100644
index 000000000..ff1de494b
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_quantile.test_ignore
@@ -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/runner/src/integrationTest/sql/aggregate/aggregates/test_quantile_list.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_quantile_list.test_ignore
new file mode 100644
index 000000000..e7ecd6815
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_quantile_list.test_ignore
@@ -0,0 +1,115 @@
+# name: test/sql/aggregate/aggregates/test_quantile_list.test
+# description: Test QUANTILE operator with LIST quantiles
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14637
+
+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/runner/src/integrationTest/sql/aggregate/aggregates/test_regression.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_regression.test_ignore
new file mode 100644
index 000000000..c891c56c3
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_regression.test_ignore
@@ -0,0 +1,347 @@
+# name: test/sql/aggregate/aggregates/test_regression.test
+# description: Test Regression Functions
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14643
+
+#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/runner/src/integrationTest/sql/aggregate/aggregates/test_scalar_aggr.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_scalar_aggr.test_ignore
new file mode 100644
index 000000000..272dc6a7c
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_scalar_aggr.test_ignore
@@ -0,0 +1,103 @@
+# name: test/sql/aggregate/aggregates/test_scalar_aggr.test
+# description: Test aggregates with scalar inputs
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-17271
+
+# test aggregate on scalar values
+query IIII
+SELECT COUNT(1), MIN(1), MAX(1), SUM(1)
+----
+1
+1
+1
+1
+
+# test aggregate on scalar NULLs
+query IIII
+SELECT COUNT(NULL), MIN(NULL), MAX(NULL), SUM(NULL)
+----
+0
+NULL
+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 NULLS LAST
+----
+00:00:00 12:34:56 2
+NULL NULL 1
+
+# 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 IIIII
+SELECT COUNT(1), MIN(1), ANY_VALUE(1), MAX(1), SUM(1) FROM integers
+----
+3
+1
+1
+1
+3
+
+# test aggregates on a set of values with scalar NULL values as inputs
+query IIIII
+SELECT COUNT(NULL), MIN(NULL), ANY_VALUE(NULL), MAX(NULL), SUM(NULL) FROM integers
+----
+0
+NULL
+NULL
+NULL
+NULL
+
+# test aggregate on scalar values
+query IIIIT
+SELECT COUNT(1), MIN(1), MAX(1), SUM(1), STRING_AGG('hello', ',')
+----
+1
+1
+1
+1
+hello
+
+# test aggregate on scalar NULLs
+query IIIIT
+SELECT COUNT(NULL), MIN(NULL), MAX(NULL), SUM(NULL), STRING_AGG(NULL, NULL)
+----
+0
+NULL
+NULL
+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
+
+query IIIIIT
+SELECT COUNT(1), MIN(1), ANY_VALUE(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), ANY_VALUE(NULL), MAX(NULL), SUM(NULL), STRING_AGG(NULL, NULL) FROM integers
+----
+0
+NULL
+NULL
+NULL
+NULL
+NULL
diff --git a/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_stddev.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_stddev.test_ignore
new file mode 100644
index 000000000..ae4cccb47
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_stddev.test_ignore
@@ -0,0 +1,123 @@
+# name: test/sql/aggregate/aggregates/test_stddev.test
+# description: STDDEV aggregations
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14642
+
+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/runner/src/integrationTest/sql/aggregate/aggregates/test_string_agg.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_string_agg.test_ignore
new file mode 100644
index 000000000..a1a10beea
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_string_agg.test_ignore
@@ -0,0 +1,154 @@
+# name: test/sql/aggregate/aggregates/test_string_agg.test
+# description: Test STRING_AGG operator
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-17271
+
+# 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
+a
+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, ',' ORDER BY x ASC), STRING_AGG(x, y ORDER BY x ASC) 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, ',' ORDER BY x ASC), STRING_AGG(x, y ORDER BY x ASC) 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 ORDER BY x) FROM strings GROUP BY g ORDER BY g
+----
+1 a,b
+2 i,j
+3 p
+4 x,y,z
+
+# listagg is an alias for string_agg
+query T
+SELECT LISTAGG('a', ',')
+----
+a
+
+query T
+SELECT LISTAGG('a')
+----
+a
+
+query TT
+SELECT g, LISTAGG(x) WITHIN GROUP (ORDER BY x) FROM strings GROUP BY g ORDER BY g
+----
+1 a,b
+2 i,j
+3 p
+4 x,y,z
+
+
+# Tests ordering, distincts, etc.
+statement ok
+CREATE TABLE IF NOT EXISTS trades (id INTEGER,amount INTEGER, xkey VARCHAR(255), name VARCHAR(255),ts TIMESTAMP, PRIMARY KEY(id));
+
+statement ok
+INSERT INTO trades(id, amount, xkey, name, ts) VALUES (1, 150, 'key-1', 'name1', '2021-12-01 12:00:00'), (2, 300, 'key-2', 'name2', '2021-11-01 14:00:00');
+
+statement ok
+INSERT INTO trades(id, amount, xkey, name, ts) VALUES (3, 10, 'key-1', 'name1', '2021-10-01 15:00:00'), (4, 900, 'key-3', 'name1', '2021-08-01 16:00:00');
+
+statement ok
+INSERT INTO trades(id, amount, xkey, name, ts) VALUES (5, 1000, 'key-4', 'name3', '2021-09-01 17:00:00'), (6, 200, 'key-2', 'name11','2021-12-01 18:00:00');
+
+statement ok
+INSERT INTO trades(id, amount, xkey, name, ts) VALUES (7, 10, 'key-4', 'name4', '2021-11-01 19:00:00');
+
+# Simple ordering
+query TTTTTTTT
+SELECT xkey, COUNT(*), COUNT(DISTINCT name), SUM(amount), STRING_AGG(name ORDER BY name), STRING_AGG(name ORDER BY name DESC), STRING_AGG(DISTINCT name ORDER BY name), STRING_AGG(DISTINCT name ORDER BY name DESC) FROM trades GROUP BY xkey ORDER BY xkey
+----
+key-1 2 1 160 name1,name1 name1,name1 name1 name1
+key-2 2 2 500 name11,name2 name2,name11 name11,name2 name2,name11
+key-3 1 1 900 name1 name1 name1 name1
+key-4 2 2 1010 name3,name4 name4,name3 name3,name4 name4,name3
+
+# Collation ordering
+query TTTTTTTT
+SELECT xkey, COUNT(*), COUNT(DISTINCT name), SUM(amount), STRING_AGG(name ORDER BY amount), STRING_AGG(name ORDER BY amount DESC), STRING_AGG(name ORDER BY ts), STRING_AGG(name ORDER BY ts DESC) FROM trades GROUP BY xkey ORDER BY xkey
+----
+key-1 2 1 160 name1,name1 name1,name1 name1,name1 name1,name1
+key-2 2 2 500 name11,name2 name2,name11 name2,name11 name11,name2
+key-3 1 1 900 name1 name1 name1 name1
+key-4 2 2 1010 name4,name3 name3,name4 name3,name4 name4,name3
+
+# Distinct collation ordering
+query TTTTTTTT
+SELECT xkey, COUNT(*), COUNT(DISTINCT name), SUM(amount), STRING_AGG(DISTINCT name ORDER BY amount), STRING_AGG(DISTINCT name ORDER BY amount DESC), STRING_AGG(DISTINCT name ORDER BY ts), STRING_AGG(DISTINCT name ORDER BY ts DESC) FROM trades GROUP BY xkey ORDER BY xkey
+----
+key-1 2 1 160 name1 name1 name1 name1
+key-2 2 2 500 name11,name2 name2,name11 name2,name11 name11,name2
+key-3 1 1 900 name1 name1 name1 name1
+key-4 2 2 1010 name4,name3 name3,name4 name3,name4 name4,name3
diff --git a/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_string_agg_big.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_string_agg_big.test_ignore
new file mode 100644
index 000000000..a54e14a01
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_string_agg_big.test_ignore
@@ -0,0 +1,17 @@
+# name: test/sql/aggregate/aggregates/test_string_agg_big.test
+# description: STRING_AGG big
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-17271
+
+statement ok
+CREATE TABLE strings AS SELECT c::VARCHAR g, (c*10+e)::INTEGER x FROM table(system_range(0, 99, 1)) t1(c), table(system_range(0, 99, 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 ORDER BY x) as s FROM strings GROUP BY g ORDER BY 1, 2
+----
+200 values hashing to b8126ea73f21372cdb3f2dc483106a12
diff --git a/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_string_agg_many_groups.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_string_agg_many_groups.test_ignore
new file mode 100644
index 000000000..a8ad9bf73
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_string_agg_many_groups.test_ignore
@@ -0,0 +1,22 @@
+# name: test/sql/aggregate/aggregates/test_string_agg_many_groups.test
+# description: Test STRING_AGG operator with many groups
+# group: [aggregates]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-17268
+
+# generate a table
+
+statement ok
+CREATE TABLE strings AS SELECT g, 'hello' x FROM table(system_range(0, 9999, 1)) t1(g);
+
+query IT
+SELECT g, STRING_AGG(x ORDER BY x) FROM strings GROUP BY g ORDER BY g
+----
+20000 values hashing to acd848208cc35c7324ece9fcdd507823
+
+query IT
+SELECT 1, STRING_AGG(x ORDER BY x) FROM strings GROUP BY 1 ORDER BY 1
+----
+2 values hashing to 3bd0d16b476d2ffe18a77ebe1098b89c
+
+statement error
+SELECT STRING_AGG(k ORDER BY 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/runner/src/integrationTest/sql/aggregate/aggregates/test_sum.test b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_sum.test
new file mode 100644
index 000000000..8a0b889de
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/aggregates/test_sum.test
@@ -0,0 +1,81 @@
+# 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 table(system_range(0, 999, 1));
+
+# positive numbers
+query I
+SELECT SUM(i) FROM integers;
+----
+499500
+
+# negative numbers
+statement ok
+INSERT INTO integers SELECT * FROM table(system_range(0, -999, -1));
+
+query I
+SELECT SUM(i) FROM integers;
+----
+0
+
+# more negative numbers
+statement ok
+INSERT INTO integers SELECT * FROM table(system_range(0, -999, -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 table(system_range(4611686018427387904, 4611686018427388903, 1));
+
+# sum them up
+query I
+SELECT SUM(b) FROM bigints
+----
+4611686018427388403500
+
+# sum them up
+query T
+SELECT typeof(SUM(b)) FROM bigints
+----
+DECIMAL(32767, 0)
+
+# this is too big for a bigint
+statement error
+SELECT SUM(b)::BIGINT FROM bigints
diff --git a/modules/runner/src/integrationTest/sql/aggregate/distinct/test_distinct.test b/modules/runner/src/integrationTest/sql/aggregate/distinct/test_distinct.test
new file mode 100644
index 000000000..2fec75593
--- /dev/null
+++ b/modules/runner/src/integrationTest/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/runner/src/integrationTest/sql/aggregate/distinct/test_distinct_on.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/distinct/test_distinct_on.test_ignore
new file mode 100644
index 000000000..7c65fd504
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/distinct/test_distinct_on.test_ignore
@@ -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/runner/src/integrationTest/sql/aggregate/distinct/test_distinct_order_by.test b/modules/runner/src/integrationTest/sql/aggregate/distinct/test_distinct_order_by.test
new file mode 100644
index 000000000..e3b1ffa7a
--- /dev/null
+++ b/modules/runner/src/integrationTest/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/runner/src/integrationTest/sql/aggregate/group/test_group_by.test b/modules/runner/src/integrationTest/sql/aggregate/group/test_group_by.test
new file mode 100644
index 000000000..e1fbc5d64
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/group/test_group_by.test
@@ -0,0 +1,160 @@
+# 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 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 ANY_VALUE()
+query IRI
+SELECT i, SUM(j), ANY_VALUE(j) FROM integers GROUP BY i ORDER BY i
+----
+2 4.000000 4
+3 8.000000 4
+
+# 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
+
+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 NULLS FIRST;
+----
+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 NULLS FIRST;
+----
+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 II
+SELECT test.b, SUM(a) FROM test GROUP BY b ORDER BY COUNT(a) DESC;
+----
+21 48
+22 24
diff --git a/modules/runner/src/integrationTest/sql/aggregate/group/test_group_by.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/group/test_group_by.test_ignore
new file mode 100644
index 000000000..22efd582b
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/group/test_group_by.test_ignore
@@ -0,0 +1,182 @@
+# name: test/sql/aggregate/group/test_group_by.test
+# description: Test aggregation/group by statements
+# group: [group]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14885
+
+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 ANY_VALUE()
+query IRI
+SELECT i, SUM(j), ANY_VALUE(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 NULLS FIRST;
+----
+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 NULLS FIRST;
+----
+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 II
+SELECT test.b, SUM(a) FROM test GROUP BY b ORDER BY COUNT(a) DESC;
+----
+21 48
+22 24
+
diff --git a/modules/runner/src/integrationTest/sql/aggregate/group/test_group_by_alias.test b/modules/runner/src/integrationTest/sql/aggregate/group/test_group_by_alias.test
new file mode 100644
index 000000000..4b70467bb
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/group/test_group_by_alias.test
@@ -0,0 +1,38 @@
+# name: test/sql/aggregate/group/test_group_by_alias.test
+# description: Test aliases in group by/aggregation
+# group: [group]
+
+statement ok
+CREATE TABLE integers(i INTEGER)
+
+statement ok
+INSERT INTO integers VALUES (1), (2), (3), (NULL)
+
+# 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;
diff --git a/modules/runner/src/integrationTest/sql/aggregate/group/test_group_by_alias.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/group/test_group_by_alias.test_ignore
new file mode 100644
index 000000000..846468d88
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/group/test_group_by_alias.test_ignore
@@ -0,0 +1,68 @@
+# name: test/sql/aggregate/group/test_group_by_alias.test
+# description: Test aliases in group by/aggregation
+# group: [group]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14885
+
+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
+
+# 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/runner/src/integrationTest/sql/aggregate/group/test_group_by_large_string.test b/modules/runner/src/integrationTest/sql/aggregate/group/test_group_by_large_string.test
new file mode 100644
index 000000000..f6b56e258
--- /dev/null
+++ b/modules/runner/src/integrationTest/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/runner/src/integrationTest/sql/aggregate/group/test_group_by_multi_column.test b/modules/runner/src/integrationTest/sql/aggregate/group/test_group_by_multi_column.test
new file mode 100644
index 000000000..64b6efe7b
--- /dev/null
+++ b/modules/runner/src/integrationTest/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/runner/src/integrationTest/sql/aggregate/group/test_group_null.test b/modules/runner/src/integrationTest/sql/aggregate/group/test_group_null.test
new file mode 100644
index 000000000..a3afef6e9
--- /dev/null
+++ b/modules/runner/src/integrationTest/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/runner/src/integrationTest/sql/aggregate/having/test_corel_subquery_in_having.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/having/test_corel_subquery_in_having.test_ignore
new file mode 100644
index 000000000..24f16d8cc
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/having/test_corel_subquery_in_having.test_ignore
@@ -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/runner/src/integrationTest/sql/aggregate/having/test_having.test b/modules/runner/src/integrationTest/sql/aggregate/having/test_having.test
new file mode 100644
index 000000000..31a23f0eb
--- /dev/null
+++ b/modules/runner/src/integrationTest/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/runner/src/integrationTest/sql/aggregate/having/test_scalar_having.test_ignore b/modules/runner/src/integrationTest/sql/aggregate/having/test_scalar_having.test_ignore
new file mode 100644
index 000000000..c3988a82e
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/aggregate/having/test_scalar_having.test_ignore
@@ -0,0 +1,119 @@
+# 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
+# Ignore https://issues.apache.org/jira/browse/IGNITE-14541
+
+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;
+----
+
+# 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/runner/src/integrationTest/sql/cast/test_boolean_cast.test b/modules/runner/src/integrationTest/sql/cast/test_boolean_cast.test
new file mode 100644
index 000000000..d72fde4f1
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/cast/test_boolean_cast.test
@@ -0,0 +1,130 @@
+# name: test/sql/cast/test_boolean_cast.test
+# description: Test boolean casts
+# group: [cast]
+# Ignored with https://issues.apache.org/jira/browse/IGNITE-15559
+
+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)
+----
+true
+
+query T
+SELECT CAST('TRUE' AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST('false' AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST('FALSE' AS BOOLEAN)
+----
+false
+
+statement error
+SELECT CAST('12345' AS BOOLEAN)
+
+query T
+SELECT CAST(CAST('12345' AS INTEGER) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS INTEGER) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS tinyint) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS tinyint) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS smallint) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS smallint) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS integer) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS integer) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS bigint) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS bigint) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS decimal) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS decimal) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS decimal(1,0)) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS decimal(1,0)) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS decimal(9,0)) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS decimal(9,0)) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS decimal(38,0)) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS decimal(38,0)) AS BOOLEAN)
+----
+false
diff --git a/modules/runner/src/integrationTest/sql/cast/test_boolean_cast.test_ignore b/modules/runner/src/integrationTest/sql/cast/test_boolean_cast.test_ignore
new file mode 100644
index 000000000..79812020a
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/cast/test_boolean_cast.test_ignore
@@ -0,0 +1,160 @@
+# name: test/sql/cast/test_boolean_cast.test
+# description: Test boolean casts
+# group: [cast]
+# Ignored with https://issues.apache.org/jira/browse/IGNITE-15559
+
+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)
+----
+true
+
+query T
+SELECT CAST('TRUE' AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST('false' AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST('FALSE' AS BOOLEAN)
+----
+false
+
+statement error
+SELECT CAST('12345' AS BOOLEAN)
+
+query T
+SELECT CAST(CAST('12345' AS INTEGER) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS INTEGER) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS tinyint) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS tinyint) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS smallint) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS smallint) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS integer) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS integer) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS bigint) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS bigint) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS decimal) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS decimal) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS decimal(1,0)) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS decimal(1,0)) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS decimal(9,0)) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS decimal(9,0)) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS decimal(38,0)) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS decimal(38,0)) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS float) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS float) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS double) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS double) AS BOOLEAN)
+----
+false
+
+query T
+SELECT CAST(CAST('1' AS HUGEINT) AS BOOLEAN)
+----
+true
+
+query T
+SELECT CAST(CAST('0' AS HUGEINT) AS BOOLEAN)
+----
+false
diff --git a/modules/runner/src/integrationTest/sql/cast/test_exponent_in_cast.test_ignore b/modules/runner/src/integrationTest/sql/cast/test_exponent_in_cast.test_ignore
new file mode 100644
index 000000000..f5764ae36
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/cast/test_exponent_in_cast.test_ignore
@@ -0,0 +1,45 @@
+# name: test/sql/cast/test_exponent_in_cast.test
+# description: Test exponents in cast
+# group: [cast]
+# Ignored https://issues.apache.org/jira/browse/IGNITE-15560
+
+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
diff --git a/modules/runner/src/integrationTest/sql/cast/test_string_cast.test b/modules/runner/src/integrationTest/sql/cast/test_string_cast.test
new file mode 100644
index 000000000..a6a1c92a7
--- /dev/null
+++ b/modules/runner/src/integrationTest/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.5
+-128.5
+
+query TTT
+SELECT 2::DOUBLE::VARCHAR, 0.5::DOUBLE::VARCHAR, (-128.5)::DOUBLE::VARCHAR
+----
+2
+0.5
+-128.5
diff --git a/modules/runner/src/integrationTest/sql/cast/test_try_cast.test b/modules/runner/src/integrationTest/sql/cast/test_try_cast.test
new file mode 100644
index 000000000..1205892b1
--- /dev/null
+++ b/modules/runner/src/integrationTest/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/runner/src/integrationTest/sql/delete/test_delete.test b/modules/runner/src/integrationTest/sql/delete/test_delete.test
new file mode 100644
index 000000000..2da09e6a6
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/delete/test_delete.test
@@ -0,0 +1,33 @@
+# 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
+
+
diff --git a/modules/runner/src/integrationTest/sql/delete/test_large_delete.test_ignore b/modules/runner/src/integrationTest/sql/delete/test_large_delete.test_ignore
new file mode 100644
index 000000000..972fb815d
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/delete/test_large_delete.test_ignore
@@ -0,0 +1,15 @@
+# name: test/sql/delete/test_large_delete.test
+# description: Test scan with large deletions
+# group: [delete]
+# Ignore: https://issues.apache.org/jira/browse/IGNITE-17268
+
+statement ok
+CREATE TABLE a AS SELECT * FROM table(system_range(1, 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/runner/src/integrationTest/sql/filter/test_alias_filter.test b/modules/runner/src/integrationTest/sql/filter/test_alias_filter.test
new file mode 100644
index 000000000..0f0c7f497
--- /dev/null
+++ b/modules/runner/src/integrationTest/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/runner/src/integrationTest/sql/filter/test_constant_comparisons.test b/modules/runner/src/integrationTest/sql/filter/test_constant_comparisons.test
new file mode 100644
index 000000000..2c4f7251e
--- /dev/null
+++ b/modules/runner/src/integrationTest/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=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/runner/src/integrationTest/sql/filter/test_constant_comparisons.test_ignore b/modules/runner/src/integrationTest/sql/filter/test_constant_comparisons.test_ignore
new file mode 100644
index 000000000..52e69cd97
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/filter/test_constant_comparisons.test_ignore
@@ -0,0 +1,12 @@
+# name: test/sql/filter/test_constant_comparisons.test_
+# description: Test expressions with constant comparisons
+# group: [filter]
+# Ignore: https://issues.apache.org/jira/browse/IGNITE-14617
+
+statement ok
+PRAGMA enable_verification
+
+query I
+SELECT CASE WHEN 1 THEN 13 ELSE 12 END;
+----
+13
diff --git a/modules/runner/src/integrationTest/sql/filter/test_filter_clause.test_ignore b/modules/runner/src/integrationTest/sql/filter/test_filter_clause.test_ignore
new file mode 100644
index 000000000..40582ad97
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/filter/test_filter_clause.test_ignore
@@ -0,0 +1,460 @@
+# name: test/sql/filter/test_filter_clause.test
+# description: Test aggregation with filter clause
+# group: [filter]
+# Ignore: https://issues.apache.org/jira/browse/IGNITE-17268
+
+statement ok
+create table t as select x i, mod(x,10) j,mod(x,10)*10000000 k from table(system_range(0, 999));
+
+query I
+SELECT count(*) from t;
+----
+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 rowsort
+ 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 rowsort
+ SELECT
+ COUNT(*) AS unfiltered,
+ COUNT(*) FILTER (WHERE i > 5 and i < 10) AS filtered,
+ j
+FROM t
+group by j
+order 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 rowsort
+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 rowsort
+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 rowsort
+SELECT
+ SUM(j) FILTER (WHERE i < 10)
+FROM t
+group by j;
+----
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+
+query I rowsort
+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 rowsort
+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 rowsort
+ 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 rowsort
+ 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 rowsort
+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 rowsort
+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 rowsort
+SELECT
+ SUM(j) FILTER (WHERE i < 10)
+FROM t
+group by k;
+----
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+
+query I rowsort
+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 rowsort
+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 rowsort
+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 rowsort
+ 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 table t_2 as select x a, length(x) b, mod(x,100) c, 5 d, 10000 e from table(system_range(0, 999)) t1(x);
+
+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
+
+#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
diff --git a/modules/runner/src/integrationTest/sql/filter/test_filter_clause.test_ignore_old b/modules/runner/src/integrationTest/sql/filter/test_filter_clause.test_ignore_old
new file mode 100644
index 000000000..adbdd7d26
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/filter/test_filter_clause.test_ignore_old
@@ -0,0 +1,528 @@
+# name: test/sql/filter/test_filter_clause.test
+# description: Test aggregation with filter clause
+# group: [filter]
+# Ignore https://issues.apache.org/jira/browse/IGNITE-15589
+
+statement ok
+create table t as select x i, mod(x,10) j,mod(x,10)*10000000 k from table(system_range(0, 999));
+
+query I
+SELECT count(*) from t;
+----
+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 rowsort
+ 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 rowsort
+ SELECT
+ COUNT(*) AS unfiltered,
+ COUNT(*) FILTER (WHERE i > 5 and i < 10) AS filtered,
+ j
+FROM t
+group by j
+order 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 rowsort
+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 rowsort
+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 rowsort
+SELECT
+ SUM(j) FILTER (WHERE i < 10)
+FROM t
+group by j;
+----
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+
+query I rowsort
+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 rowsort
+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 rowsort
+ 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 rowsort
+ 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 rowsort
+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 rowsort
+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 rowsort
+SELECT
+ SUM(j) FILTER (WHERE i < 10)
+FROM t
+group by k;
+----
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+
+query I rowsort
+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 rowsort
+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 rowsort
+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 rowsort
+ 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 table t_2 as select x a, length(x) b, mod(x,100) c, 5 d, 10000 e from table(system_range(0, 999)) t1(x);
+
+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
diff --git a/modules/runner/src/integrationTest/sql/filter/test_illegal_filters.test b/modules/runner/src/integrationTest/sql/filter/test_illegal_filters.test
new file mode 100644
index 000000000..406f115cf
--- /dev/null
+++ b/modules/runner/src/integrationTest/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/runner/src/integrationTest/sql/filter/test_obsolete_filters.test b/modules/runner/src/integrationTest/sql/filter/test_obsolete_filters.test
new file mode 100644
index 000000000..a8561b6f7
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/filter/test_obsolete_filters.test
@@ -0,0 +1,271 @@
+# 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
+----
+
+# 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/runner/src/integrationTest/sql/filter/test_obsolete_filters.test_ignore b/modules/runner/src/integrationTest/sql/filter/test_obsolete_filters.test_ignore
new file mode 100644
index 000000000..a2ec2e59f
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/filter/test_obsolete_filters.test_ignore
@@ -0,0 +1,281 @@
+# name: test/sql/filter/test_obsolete_filters.test
+# description: Test expressions with obsolete filters
+# group: [filter]
+# Ignored https://issues.apache.org/jira/browse/IGNITE-15561
+
+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/runner/src/integrationTest/sql/filter/test_transitive_filters.test_ignore b/modules/runner/src/integrationTest/sql/filter/test_transitive_filters.test_ignore
new file mode 100644
index 000000000..d860c6af7
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/filter/test_transitive_filters.test_ignore
@@ -0,0 +1,322 @@
+# name: test/sql/filter/test_transitive_filters.test
+# description: Test expressions with transitive filters
+# group: [filter]
+# Ignore: https://issues.apache.org/jira/browse/IGNITE-17268
+
+# 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 table(system_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 j FROM vals1 ORDER by i, j
+
+### constant comparison [=, >, >=, <, <=] followed by j >= i #########################
+
+query II rowsort
+SELECT * FROM vals1 WHERE i=5 AND j>=i
+----
+5 5
+5 6
+
+query II rowsort
+SELECT * FROM vals1 WHERE i>9 AND j>=i
+----
+10 10
+10 11
+11 11
+11 12
+
+query II rowsort
+SELECT * FROM vals1 WHERE i>=10 AND j>=i
+----
+10 10
+10 11
+11 11
+11 12
+
+query II rowsort
+SELECT * FROM vals1 WHERE i<1 AND j>=i
+----
+0 0
+0 1
+
+query II rowsort
+SELECT * FROM vals1 WHERE i<=0 AND j>=i
+----
+0 0
+0 1
+
+### constant comparison [=, >, >=, <, <=] followed by j <= i #########################
+
+query II rowsort
+SELECT * FROM vals1 WHERE i=5 AND j<=i
+----
+5 4
+5 5
+
+query II rowsort
+SELECT * FROM vals1 WHERE i>9 AND j<=i
+----
+11 11
+11 10
+10 10
+10 9
+
+query II rowsort
+SELECT * FROM vals1 WHERE i>=10 AND j<=i
+----
+10 9
+10 10
+11 10
+11 11
+
+query II rowsort
+SELECT * FROM vals1 WHERE i<1 AND j<=i
+----
+0 0
+0 -1
+
+query II rowsort
+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 rowsort
+SELECT * FROM vals1 WHERE i>9 AND j>i
+----
+10 11
+11 12
+
+query II rowsort
+SELECT * FROM vals1 WHERE i>=10 AND j>i
+----
+10 11
+11 12
+
+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 rowsort
+SELECT * FROM vals1 WHERE i>9 AND j<i
+----
+10 9
+11 10
+
+query II rowsort
+SELECT * FROM vals1 WHERE i>=10 AND j<i
+----
+10 9
+11 10
+
+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 rowsort
+SELECT * FROM vals1 WHERE j>=i AND i=5
+----
+5 5
+5 6
+
+query II rowsort
+SELECT * FROM vals1 WHERE j>=i AND i>9
+----
+10 10
+10 11
+11 11
+11 12
+
+query II rowsort
+SELECT * FROM vals1 WHERE j>=i AND i>=10
+----
+10 10
+10 11
+11 11
+11 12
+
+query II rowsort
+SELECT * FROM vals1 WHERE j>=i AND i<1
+----
+0 0
+0 1
+
+query II rowsort
+SELECT * FROM vals1 WHERE j>=i AND i<=0
+----
+0 0
+0 1
+
+### j <= i followed by constant comparison [=, >, >=, <, <=] #########################
+
+query II rowsort
+SELECT * FROM vals1 WHERE j<=i AND i=5
+----
+5 4
+5 5
+
+query II rowsort
+SELECT * FROM vals1 WHERE j<=i AND i>9
+----
+10 9
+10 10
+11 10
+11 11
+
+query II rowsort
+SELECT * FROM vals1 WHERE j<=i AND i>=10
+----
+10 9
+10 10
+11 10
+11 11
+
+query II rowsort
+SELECT * FROM vals1 WHERE j<=i AND i<1
+----
+0 0
+0 -1
+
+query II rowsort
+SELECT * FROM vals1 WHERE j<=i AND i<=0
+----
+0 -1
+0 0
+
+### j > i followed by constant comparison [=, >, >=, <, <=] #########################
+
+query II
+SELECT * FROM vals1 WHERE j>i AND i=5
+----
+5 6
+
+query II rowsort
+SELECT * FROM vals1 WHERE j>i AND i>9
+----
+10 11
+11 12
+
+query II rowsort
+SELECT * FROM vals1 WHERE j>i AND i>=10
+----
+10 11
+11 12
+
+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 rowsort
+SELECT * FROM vals1 WHERE j<i AND i>9
+----
+10 9
+11 10
+
+query II rowsort
+SELECT * FROM vals1 WHERE j<i AND i>=10
+----
+10 9
+11 10
+
+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 rowsort
+SELECT * FROM vals1, vals2 WHERE i>9 AND j<=l AND k>=i AND l<11
+----
+10 9 10 9
+10 9 10 10
+10 9 11 10
+10 10 10 10
+10 10 11 10
+11 10 11 10
+
+
+query IIII
+SELECT * FROM vals1, vals2 WHERE i>9 AND j>=i AND k>=j ORDER BY vals1.j, vals1.i, vals2.k, vals2.l LIMIT 3
+----
+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 ORDER BY vals1.j, vals1.i, vals2.k, vals2.l LIMIT 3
+----
+10 10 10 10
+10 10 10 11
+10 10 11 11
+
+query IIII rowsort
+SELECT * FROM vals1, vals2 WHERE i<1 AND k<=j AND j<=i AND l<=k
+----
+0 0 0 -1
+0 0 0 0
diff --git a/modules/runner/src/integrationTest/sql/filter/test_zonemap.test_ignore b/modules/runner/src/integrationTest/sql/filter/test_zonemap.test_ignore
new file mode 100644
index 000000000..c98d7fb18
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/filter/test_zonemap.test_ignore
@@ -0,0 +1,32 @@
+# name: test/sql/filter/test_zonemap.test_slow
+# description: Test expressions with transitive filters
+# group: [filter]
+# Ignore: https://issues.apache.org/jira/browse/IGNITE-17268
+
+statement ok
+create table t as select x a, length(x) b, mod(x,10000) c, 5 d, 10000 e from table(system_range(0,999));
+
+query I
+select count(*) from t where a > 500 or a <= 700
+----
+1000
+
+query I
+select count(*) from t where (a > 500 and b = 3) or (a > 7000 and b = 2)
+----
+499
+
+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)
+----
+600
+
+query I
+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)
+----
+1000
+
+query I
+select count(*) from t where (a > 500 AND b = 1) OR b < 2
+----
+10
diff --git a/modules/runner/src/integrationTest/sql/function/blob/base64.test b/modules/runner/src/integrationTest/sql/function/blob/base64.test
new file mode 100644
index 000000000..c8a756100
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/function/blob/base64.test
@@ -0,0 +1,27 @@
+# name: test/sql/function/blob/base64.test
+# description: Test blob base64 functions
+# group: [blob]
+
+query I
+SELECT to_base64(NULL)
+----
+NULL
+
+query I
+SELECT to_base64('GRIDGAIN')
+----
+R1JJREdBSU4=
+
+query I
+select from_base64('AAAA');
+----
+000000
+
+# malformed base64
+# must be multiple of 4
+statement error
+SELECT from_base64('ab');
+
+# unknown bytes
+statement error
+SELECT from_base64('üab');
diff --git a/modules/runner/src/integrationTest/sql/function/date/date_part.test b/modules/runner/src/integrationTest/sql/function/date/date_part.test
new file mode 100644
index 000000000..a98de38f1
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/function/date/date_part.test
@@ -0,0 +1,189 @@
+# name: test/sql/function/date/date_part_stats.test
+# description: Test date part stats on empty table
+# group: [date]
+
+statement ok
+CREATE TABLE dates(d DATE);
+
+# test stats propagation from empty table
+query I
+SELECT EXTRACT(year FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(month FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(day FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(decade FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(century FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(millennium FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(microsecond FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(millisecond FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(second FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(minute FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(hour FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(epoch FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(dow FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(isodow FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(week FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(doy FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(quarter FROM d) FROM dates;
+----
+
+query I
+SELECT DAYOFMONTH(d) FROM dates;
+----
+
+query I
+SELECT DAYOFWEEK(d) FROM dates;
+----
+
+statement ok
+INSERT INTO dates VALUES (DATE '1992-01-01'), (DATE '2000-12-31');
+
+query I
+SELECT EXTRACT(YEAR FROM d) as y FROM dates ORDER BY y;
+----
+1992
+2000
+
+query I
+SELECT EXTRACT(month FROM d) as m FROM dates ORDER BY m;
+----
+1
+12
+
+query I
+SELECT EXTRACT(day FROM d) as d FROM dates ORDER BY d;
+----
+1
+31
+
+query I
+SELECT EXTRACT(decade FROM d) as d FROM dates ORDER BY d;
+----
+199
+200
+
+query I
+SELECT EXTRACT(century FROM d) as c FROM dates ORDER BY c;
+----
+20
+20
+
+query I
+SELECT EXTRACT(millennium FROM d) as m FROM dates ORDER BY m;
+----
+2
+2
+
+query I
+SELECT EXTRACT(second FROM d) as s FROM dates ORDER BY s;
+----
+0
+0
+
+query I
+SELECT EXTRACT(microsecond FROM d) FROM dates;
+----
+0
+0
+
+query I
+SELECT EXTRACT(millisecond FROM d) FROM dates;
+----
+0
+0
+
+query I
+SELECT EXTRACT(minute FROM d) as m FROM dates ORDER BY m;
+----
+0
+0
+
+query I
+SELECT EXTRACT(hour FROM d) h FROM dates;
+----
+0
+0
+
+query I
+SELECT EXTRACT(dow FROM d) as d FROM dates ORDER BY d;
+----
+1
+4
+
+query I
+SELECT EXTRACT(isodow FROM d) as d FROM dates ORDER BY d;
+----
+3
+7
+
+query I
+SELECT EXTRACT(week FROM d) as w FROM dates ORDER BY w;
+----
+1
+52
+
+query I
+SELECT EXTRACT(doy FROM d) as d FROM dates ORDER BY d;
+----
+1
+366
+
+query I
+SELECT EXTRACT(quarter FROM d) as q FROM dates ORDER BY q;
+----
+1
+4
+
+query I
+SELECT DAYOFMONTH(d) as d FROM dates ORDER BY d;
+----
+1
+31
+
diff --git a/modules/runner/src/integrationTest/sql/function/date/date_part.test_ignore b/modules/runner/src/integrationTest/sql/function/date/date_part.test_ignore
new file mode 100644
index 000000000..5cab43451
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/function/date/date_part.test_ignore
@@ -0,0 +1,214 @@
+# name: test/sql/function/date/date_part_stats.test
+# description: Test date part stats on empty table
+# group: [date]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14955
+
+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(microsecond FROM d) FROM dates;
+----
+
+query I
+SELECT EXTRACT(millisecond 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
+INSERT INTO dates VALUES (DATE '1992-01-01'), (DATE '2000-12-31');
+
+query I
+SELECT EXTRACT(YEAR FROM d) as y FROM dates ORDER BY y;
+----
+1992
+2000
+
+query I
+SELECT EXTRACT(month FROM d) as m FROM dates ORDER BY m;
+----
+1
+12
+
+query I
+SELECT EXTRACT(day FROM d) as d FROM dates ORDER BY d;
+----
+1
+31
+
+query I
+SELECT EXTRACT(decade FROM d) as d FROM dates ORDER BY d;
+----
+199
+200
+
+query I
+SELECT EXTRACT(century FROM d) as c FROM dates ORDER BY c;
+----
+20
+20
+
+query I
+SELECT EXTRACT(millennium FROM d) as m FROM dates ORDER BY m;
+----
+2
+2
+
+query I
+SELECT EXTRACT(second FROM d) as s FROM dates ORDER BY s;
+----
+0
+0
+
+query I
+SELECT EXTRACT(microsecond FROM d) FROM dates;
+----
+0
+0
+
+query I
+SELECT EXTRACT(millisecond FROM d) FROM dates;
+----
+0
+0
+
+query I
+SELECT EXTRACT(minute FROM d) as m FROM dates ORDER BY m;
+----
+0
+0
+
+query I
+SELECT EXTRACT(hour FROM d) h FROM dates;
+----
+0
+0
+
+query I
+SELECT EXTRACT(dow FROM d) as d FROM dates ORDER BY d;
+----
+1
+4
+
+query I
+SELECT EXTRACT(isodow FROM d) as d FROM dates ORDER BY d;
+----
+3
+7
+
+query I
+SELECT EXTRACT(week FROM d) as w FROM dates ORDER BY w;
+----
+1
+52
+
+query I
+SELECT EXTRACT(doy FROM d) as d FROM dates ORDER BY d;
+----
+1
+366
+
+query I
+SELECT EXTRACT(quarter FROM d) as q FROM dates ORDER BY q;
+----
+1
+4
+
+
+query I
+SELECT DAYOFMONTH(d) as d FROM dates ORDER BY d;
+----
+1
+31
+
+query I
+SELECT stats(YEARWEEK(d)) FROM dates LIMIT 1;
+----
+<REGEX>:.*199201.*200052.*
+
+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/runner/src/integrationTest/sql/function/date/test_date_part.test b/modules/runner/src/integrationTest/sql/function/date/test_date_part.test
new file mode 100644
index 000000000..aecd9d781
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/function/date/test_date_part.test
@@ -0,0 +1,61 @@
+# name: test/sql/function/date/test_date_part.test
+# description: DATE_PART test
+# group: [date]
+
+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');
+
+
+# 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 ORDER BY d;
+----
+1992-01-31
+1992-03-31
+1992-05-31
+
+query T
+SELECT LAST_DAY(d::timestamp) FROM dates ORDER BY d;
+----
+1992-01-31
+1992-03-31
+1992-05-31
+
+# monthname
+query T
+SELECT MONTHNAME(d) FROM dates ORDER BY d;
+----
+January
+March
+May
+
+# dayname
+query T
+SELECT DAYNAME(d) FROM dates ORDER BY d;
+----
+Wednesday
+Tuesday
+Tuesday
+
+# aliases
+query I
+SELECT DAYOFMONTH(d) FROM dates ORDER BY d;
+----
+1
+3
+5
+
+query I
+SELECT DAYOFWEEK(d) FROM dates ORDER BY d;
+----
+4
+3
+3
diff --git a/modules/runner/src/integrationTest/sql/function/date/test_date_part.test_ignore b/modules/runner/src/integrationTest/sql/function/date/test_date_part.test_ignore
new file mode 100644
index 000000000..9e7b2cda6
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/function/date/test_date_part.test_ignore
@@ -0,0 +1,166 @@
+# name: test/sql/function/date/test_date_part.test
+# description: DATE_PART test
+# group: [date]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14955
+
+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 ORDER BY d;
+----
+1992-01-31
+1992-03-31
+1992-05-31
+
+query T
+SELECT LAST_DAY(d::timestamp) FROM dates ORDER BY d;
+----
+1992-01-31
+1992-03-31
+1992-05-31
+
+# monthname
+query T
+SELECT MONTHNAME(d) FROM dates ORDER BY d;
+----
+January
+March
+May
+
+# dayname
+query T
+SELECT DAYNAME(d) FROM dates ORDER BY d;
+----
+Wednesday
+Tuesday
+Tuesday
+
+# yearweek
+query I
+SELECT YEARWEEK(d) FROM dates;
+----
+199201
+199209
+199218
+
+# aliases
+query I
+SELECT DAYOFMONTH(d) FROM dates ORDER BY d;
+----
+1
+3
+5
+
+query I
+SELECT DAYOFWEEK(d) FROM dates ORDER BY d;
+----
+4
+3
+3
+
+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/runner/src/integrationTest/sql/function/date/test_extract.test b/modules/runner/src/integrationTest/sql/function/date/test_extract.test
new file mode 100644
index 000000000..018d470b6
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/function/date/test_extract.test
@@ -0,0 +1,111 @@
+# name: test/sql/function/date/test_extract.test
+# description: Extract function
+# group: [date]
+
+statement ok
+CREATE TABLE dates(i DATE)
+
+statement ok
+INSERT INTO dates VALUES (DATE '1993-08-14'), (NULL)
+
+# extract various parts of the date
+# year
+query I
+SELECT EXTRACT(year FROM i) FROM dates ORDER BY i NULLS LAST
+----
+1993
+NULL
+
+# month
+query I
+SELECT EXTRACT(month FROM i) FROM dates ORDER BY i NULLS LAST
+----
+8
+NULL
+
+# day
+query I
+SELECT EXTRACT(day FROM i) FROM dates ORDER BY i NULLS LAST
+----
+14
+NULL
+
+# decade
+query I
+SELECT EXTRACT(decade FROM i) FROM dates ORDER BY i NULLS LAST
+----
+199
+NULL
+
+# century
+query I
+SELECT EXTRACT(century FROM i) FROM dates ORDER BY i NULLS LAST
+----
+20
+NULL
+
+# day of the week (Sunday = 1, Saturday = 7)
+query I
+SELECT EXTRACT(DOW FROM i) FROM dates ORDER BY i NULLS LAST
+----
+7
+NULL
+
+# day of the year (1 - 365/366)
+query I
+SELECT EXTRACT(DOY FROM i) FROM dates ORDER BY i NULLS LAST
+----
+226
+NULL
+
+# epoch
+query I
+SELECT EXTRACT(epoch FROM i) FROM dates ORDER BY i NULLS LAST
+----
+745286400
+NULL
+
+# isodow (Monday = 1, Sunday = 7)
+query I
+SELECT EXTRACT(ISODOW FROM i) FROM dates ORDER BY i NULLS LAST
+----
+6
+NULL
+
+# millenium (change of millenium is January 1, X001)
+query I
+SELECT EXTRACT(millennium FROM i) FROM dates ORDER BY i NULLS LAST
+----
+2
+NULL
+
+# timestamp variants all give 0 for date
+query I
+SELECT EXTRACT(second FROM i) FROM dates ORDER BY i NULLS LAST
+----
+0
+NULL
+
+query I
+SELECT EXTRACT(minute FROM i) FROM dates ORDER BY i NULLS LAST
+----
+0
+NULL
+
+query I
+SELECT EXTRACT(hour FROM i) FROM dates ORDER BY i NULLS LAST
+----
+0
+NULL
+
+query I
+SELECT EXTRACT(millisecond FROM i) FROM dates ORDER BY i NULLS LAST
+----
+0
+NULL
+
+query I
+SELECT EXTRACT(microsecond FROM i) FROM dates ORDER BY i NULLS LAST
+----
+0
+NULL
diff --git a/modules/runner/src/integrationTest/sql/function/date/test_extract.test_ignore b/modules/runner/src/integrationTest/sql/function/date/test_extract.test_ignore
new file mode 100644
index 000000000..7f104e696
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/function/date/test_extract.test_ignore
@@ -0,0 +1,112 @@
+# name: test/sql/function/date/test_extract.test
+# description: Extract function
+# group: [date]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14955
+
+statement ok
+CREATE TABLE dates(i DATE)
+
+statement ok
+INSERT INTO dates VALUES (DATE '1993-08-14'), (NULL)
+
+# extract various parts of the date
+# year
+query I
+SELECT EXTRACT(year FROM i) FROM dates ORDER BY i
+----
+1993
+NULL
+
+# month
+query I
+SELECT EXTRACT(month FROM i) FROM dates ORDER BY i
+----
+8
+NULL
+
+# day
+query I
+SELECT EXTRACT(day FROM i) FROM dates ORDER BY i
+----
+14
+NULL
+
+# decade
+query I
+SELECT EXTRACT(decade FROM i) FROM dates ORDER BY i
+----
+199
+NULL
+
+# century
+query I
+SELECT EXTRACT(century FROM i) FROM dates ORDER BY i
+----
+20
+NULL
+
+# day of the week (Sunday = 1, Saturday = 7)
+query I
+SELECT EXTRACT(DOW FROM i) FROM dates ORDER BY i
+----
+7
+NULL
+
+# day of the year (1 - 365/366)
+query I
+SELECT EXTRACT(DOY FROM i) FROM dates ORDER BY i
+----
+226
+NULL
+
+# epoch
+query I
+SELECT EXTRACT(epoch FROM i) FROM dates ORDER BY i
+----
+745286400
+NULL
+
+# isodow (Monday = 1, Sunday = 7)
+query I
+SELECT EXTRACT(ISODOW FROM i) FROM dates ORDER BY i
+----
+6
+NULL
+
+# millenium (change of millenium is January 1, X001)
+query I
+SELECT EXTRACT(millennium FROM i) FROM dates ORDER BY i
+----
+2
+NULL
+
+# timestamp variants all give 0 for date
+query I
+SELECT EXTRACT(second FROM i) FROM dates ORDER BY i
+----
+0
+NULL
+
+query I
+SELECT EXTRACT(minute FROM i) FROM dates ORDER BY i
+----
+0
+NULL
+
+query I
+SELECT EXTRACT(hour FROM i) FROM dates ORDER BY i
+----
+0
+NULL
+
+query I
+SELECT EXTRACT(milliseconds FROM i) FROM dates ORDER BY i
+----
+0
+NULL
+
+query I
+SELECT EXTRACT(microsecond FROM i) FROM dates ORDER BY i
+----
+0
+NULL
diff --git a/modules/runner/src/integrationTest/sql/function/date/test_extract_edge_cases.test b/modules/runner/src/integrationTest/sql/function/date/test_extract_edge_cases.test
new file mode 100644
index 000000000..38ac731b2
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/function/date/test_extract_edge_cases.test
@@ -0,0 +1,67 @@
+# 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
+
+query I
+SELECT EXTRACT(dow FROM cast('1793-05-26' AS DATE));
+----
+1
+
+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
+
diff --git a/modules/runner/src/integrationTest/sql/function/date/test_extract_edge_cases.test_ignore b/modules/runner/src/integrationTest/sql/function/date/test_extract_edge_cases.test_ignore
new file mode 100644
index 000000000..45390ed8e
--- /dev/null
+++ b/modules/runner/src/integrationTest/sql/function/date/test_extract_edge_cases.test_ignore
@@ -0,0 +1,342 @@
+# name: test/sql/function/date/test_extract_edge_cases.test
+# description: Extract function edge cases
+# group: [date]
+# Ignored: https://issues.apache.org/jira/browse/IGNITE-14961
+
+# 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);
+----
+5
+
+query I
+SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) - 0);
+----
+5
+
+query I
+SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) + 1);
+----
+6
+
+query I
+SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) - 1);
+----
+4
+
+query I
+SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) + 2);
+----
+7
+
+query I
+SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) - 2);
+----
+3
+
+query I
+SELECT EXTRACT(dow FROM cast('1970-01-01' AS DATE) + 3);
+----
+1
+
+query I
... 484183 lines suppressed ...