This is an automated email from the ASF dual-hosted git repository. morningman pushed a commit to branch branch-1.2-lts in repository https://gitbox.apache.org/repos/asf/doris.git
commit 8af09ea396d731d9a49a2ed2a5af63bb4bcdb934 Author: TengJianPing <[email protected]> AuthorDate: Sun Mar 19 11:49:58 2023 +0800 [fix](bitmap) fix wrong result of bitmap count functions for null values (#17849) bitmap count functions result is null when there are null values, which is not right: --- be/src/vec/functions/function.cpp | 8 - be/src/vec/functions/function.h | 8 + be/src/vec/functions/function_bitmap.cpp | 153 +++++++++- be/src/vec/functions/function_bitmap_variadic.cpp | 37 ++- be/test/vec/function/function_bitmap_test.cpp | 6 +- .../bitmap_functions/test_bitmap_function.out | 115 ++++++-- .../bitmap_functions/test_bitmap_function.out | 105 ++++++- .../bitmap_functions/test_bitmap_function.groovy | 307 +++++++++++++++++---- .../bitmap_functions/test_bitmap_function.groovy | 285 ++++++++++++++++--- 9 files changed, 871 insertions(+), 153 deletions(-) diff --git a/be/src/vec/functions/function.cpp b/be/src/vec/functions/function.cpp index 662a2a58af..106b5e8daa 100644 --- a/be/src/vec/functions/function.cpp +++ b/be/src/vec/functions/function.cpp @@ -84,13 +84,6 @@ ColumnPtr wrap_in_nullable(const ColumnPtr& src, const Block& block, const Colum result_null_map_column); } -namespace { - -struct NullPresence { - bool has_nullable = false; - bool has_null_constant = false; -}; - NullPresence get_null_presence(const Block& block, const ColumnNumbers& args) { NullPresence res; @@ -123,7 +116,6 @@ bool allArgumentsAreConstants(const Block& block, const ColumnNumbers& args) { } return true; } -} // namespace Status PreparedFunctionImpl::default_implementation_for_constant_arguments( FunctionContext* context, Block& block, const ColumnNumbers& args, size_t result, diff --git a/be/src/vec/functions/function.h b/be/src/vec/functions/function.h index fa71a38a50..f5b0199ebd 100644 --- a/be/src/vec/functions/function.h +++ b/be/src/vec/functions/function.h @@ -47,6 +47,14 @@ template <typename T> auto has_variadic_argument_types(T&& arg) -> decltype(T::get_variadic_argument_types()) {}; void has_variadic_argument_types(...); +struct NullPresence { + bool has_nullable = false; + bool has_null_constant = false; +}; + +NullPresence get_null_presence(const Block& block, const ColumnNumbers& args); +[[maybe_unused]] NullPresence get_null_presence(const ColumnsWithTypeAndName& args); + /// The simplest executable object. /// Motivation: /// * Prepare something heavy once before main execution loop instead of doing it for each block. diff --git a/be/src/vec/functions/function_bitmap.cpp b/be/src/vec/functions/function_bitmap.cpp index 4350d3b256..9296fb9c10 100644 --- a/be/src/vec/functions/function_bitmap.cpp +++ b/be/src/vec/functions/function_bitmap.cpp @@ -490,9 +490,9 @@ struct BitmapAndNotCount { using T0 = typename LeftDataType::FieldType; using T1 = typename RightDataType::FieldType; using TData = std::vector<BitmapValue>; - using ResTData = typename ColumnVector<Int64>::Container; + using ResTData = typename ColumnVector<Int64>::Container::value_type; - static Status vector_vector(const TData& lvec, const TData& rvec, ResTData& res) { + static Status vector_vector(const TData& lvec, const TData& rvec, ResTData* res) { size_t size = lvec.size(); BitmapValue mid_data; for (size_t i = 0; i < size; ++i) { @@ -505,6 +505,153 @@ struct BitmapAndNotCount { } }; +void update_bitmap_op_count(int64_t* __restrict count, const NullMap& null_map) { + static constexpr int64_t flags[2] = {-1, 0}; + size_t size = null_map.size(); + auto* __restrict null_map_data = null_map.data(); + for (size_t i = 0; i < size; ++i) { + count[i] &= flags[null_map_data[i]]; + } +} + +// for bitmap_and_count, bitmap_xor_count and bitmap_and_not_count, +// result is 0 for rows that if any column is null value +ColumnPtr handle_bitmap_op_count_null_value(ColumnPtr& src, const Block& block, + const ColumnNumbers& args, size_t result, + size_t input_rows_count) { + auto* nullable = assert_cast<const ColumnNullable*>(src.get()); + ColumnPtr src_not_nullable = nullable->get_nested_column_ptr(); + MutableColumnPtr src_not_nullable_mutable = (*std::move(src_not_nullable)).assume_mutable(); + auto* __restrict count_data = + assert_cast<ColumnInt64*>(src_not_nullable_mutable.get())->get_data().data(); + + for (const auto& arg : args) { + const ColumnWithTypeAndName& elem = block.get_by_position(arg); + if (!elem.type->is_nullable()) { + continue; + } + + bool is_const = is_column_const(*elem.column); + /// Const Nullable that are NULL. + if (is_const && assert_cast<const ColumnConst*>(elem.column.get())->only_null()) { + return block.get_by_position(result).type->create_column_const(input_rows_count, 0); + } + if (is_const) { + continue; + } + + if (auto* nullable = assert_cast<const ColumnNullable*>(elem.column.get())) { + const ColumnPtr& null_map_column = nullable->get_null_map_column_ptr(); + const NullMap& src_null_map = + assert_cast<const ColumnUInt8&>(*null_map_column).get_data(); + + update_bitmap_op_count(count_data, src_null_map); + } + } + + return src; +} + +Status execute_bitmap_op_count_null_to_zero( + FunctionContext* context, Block& block, const ColumnNumbers& arguments, size_t result, + size_t input_rows_count, + const std::function<Status(FunctionContext*, Block&, const ColumnNumbers&, size_t, size_t)>& + exec_impl_func) { + NullPresence null_presence = get_null_presence(block, arguments); + + if (null_presence.has_null_constant) { + block.get_by_position(result).column = + block.get_by_position(result).type->create_column_const(input_rows_count, 0); + } else if (null_presence.has_nullable) { + auto [temporary_block, new_args, new_result] = + create_block_with_nested_columns(block, arguments, result); + RETURN_IF_ERROR(exec_impl_func(context, temporary_block, new_args, new_result, + temporary_block.rows())); + block.get_by_position(result).column = handle_bitmap_op_count_null_value( + temporary_block.get_by_position(new_result).column, block, arguments, result, + input_rows_count); + } else { + return exec_impl_func(context, block, arguments, result, input_rows_count); + } + return Status::OK(); +} + +class FunctionBitmapAndNotCount : public IFunction { +public: + using LeftDataType = DataTypeBitMap; + using RightDataType = DataTypeBitMap; + using ResultDataType = typename BitmapAndNotCount<LeftDataType, RightDataType>::ResultDataType; + + static constexpr auto name = "bitmap_and_not_count"; + static FunctionPtr create() { return std::make_shared<FunctionBitmapAndNotCount>(); } + String get_name() const override { return name; } + size_t get_number_of_arguments() const override { return 2; } + DataTypePtr get_return_type_impl(const DataTypes& arguments) const override { + bool return_nullable = false; + // result is nullable only when any columns is nullable for bitmap_and_not_count + for (size_t i = 0; i < arguments.size(); ++i) { + if (arguments[i]->is_nullable()) { + return_nullable = true; + break; + } + } + auto result_type = std::make_shared<ResultDataType>(); + return return_nullable ? make_nullable(result_type) : result_type; + } + + bool use_default_implementation_for_constants() const override { return true; } + + bool use_default_implementation_for_nulls() const override { + // for bitmap_and_not_count, result is always not null, and if the bitmap op result is null, + // the count is 0 + return false; + } + + Status execute_impl(FunctionContext* context, Block& block, const ColumnNumbers& arguments, + size_t result, size_t input_rows_count) override { + DCHECK_EQ(arguments.size(), 2); + + return execute_bitmap_op_count_null_to_zero( + context, block, arguments, result, input_rows_count, + std::bind((Status(FunctionBitmapAndNotCount::*)( + FunctionContext*, Block&, const ColumnNumbers&, size_t, size_t)) & + FunctionBitmapAndNotCount::execute_impl_internal, + this, std::placeholders::_1, std::placeholders::_2, std::placeholders::_3, + std::placeholders::_4, std::placeholders::_5)); + } + + Status execute_impl_internal(FunctionContext* context, Block& block, + const ColumnNumbers& arguments, size_t result, + size_t input_rows_count) { + const auto& left = block.get_by_position(arguments[0]); + auto lcol = left.column->convert_to_full_column_if_const(); + const auto& right = block.get_by_position(arguments[1]); + auto rcol = right.column->convert_to_full_column_if_const(); + + using ResultType = typename ResultDataType::FieldType; + using ColVecResult = ColumnVector<ResultType>; + + typename ColVecResult::MutablePtr col_res = ColVecResult::create(); + auto& vec_res = col_res->get_data(); + vec_res.resize(block.rows()); + + const ColumnBitmap* l_bitmap_col = assert_cast<const ColumnBitmap*>(lcol.get()); + const ColumnBitmap* r_bitmap_col = assert_cast<const ColumnBitmap*>(rcol.get()); + BitmapAndNotCount<LeftDataType, RightDataType>::vector_vector( + l_bitmap_col->get_data(), r_bitmap_col->get_data(), vec_res.data()); + + auto& result_info = block.get_by_position(result); + if (result_info.type->is_nullable()) { + block.replace_by_position( + result, ColumnNullable::create(std::move(col_res), + ColumnUInt8::create(input_rows_count, 0))); + } else { + block.replace_by_position(result, std::move(col_res)); + } + return Status::OK(); + } +}; + struct NameBitmapContains { static constexpr auto name = "bitmap_contains"; }; @@ -781,8 +928,6 @@ using FunctionBitmapNot = FunctionBinaryToType<DataTypeBitMap, DataTypeBitMap, BitmapNot, NameBitmapNot>; using FunctionBitmapAndNot = FunctionBinaryToType<DataTypeBitMap, DataTypeBitMap, BitmapAndNot, NameBitmapAndNot>; -using FunctionBitmapAndNotCount = FunctionBinaryToType<DataTypeBitMap, DataTypeBitMap, - BitmapAndNotCount, NameBitmapAndNotCount>; using FunctionBitmapContains = FunctionBinaryToType<DataTypeBitMap, DataTypeInt64, BitmapContains, NameBitmapContains>; diff --git a/be/src/vec/functions/function_bitmap_variadic.cpp b/be/src/vec/functions/function_bitmap_variadic.cpp index bbc0309808..fbd5942422 100644 --- a/be/src/vec/functions/function_bitmap_variadic.cpp +++ b/be/src/vec/functions/function_bitmap_variadic.cpp @@ -132,6 +132,12 @@ BITMAP_FUNCTION_COUNT_VARIADIC(BitmapOrCount, bitmap_or_count, |=); BITMAP_FUNCTION_COUNT_VARIADIC(BitmapAndCount, bitmap_and_count, &=); BITMAP_FUNCTION_COUNT_VARIADIC(BitmapXorCount, bitmap_xor_count, ^=); +Status execute_bitmap_op_count_null_to_zero( + FunctionContext* context, Block& block, const ColumnNumbers& arguments, size_t result, + size_t input_rows_count, + const std::function<Status(FunctionContext*, Block&, const ColumnNumbers&, size_t, size_t)>& + exec_impl_func); + template <typename Impl> class FunctionBitMapVariadic : public IFunction { public: @@ -147,7 +153,7 @@ public: DataTypePtr get_return_type_impl(const DataTypes& arguments) const override { using ResultDataType = typename Impl::ResultDataType; - if (std::is_same_v<Impl, BitmapOr> || std::is_same_v<Impl, BitmapOrCount>) { + if (std::is_same_v<Impl, BitmapOr> || is_count()) { bool return_nullable = false; // result is nullable only when any columns is nullable for bitmap_or and bitmap_or_count for (size_t i = 0; i < arguments.size(); ++i) { @@ -165,8 +171,10 @@ public: bool use_default_implementation_for_constants() const override { return true; } bool use_default_implementation_for_nulls() const override { - // result is null only when all columns is null for bitmap_or and bitmap_or_count - if (std::is_same_v<Impl, BitmapOr> || std::is_same_v<Impl, BitmapOrCount>) { + // result is null only when all columns is null for bitmap_or. + // for count functions, result is always not null, and if the bitmap op result is null, + // the count is 0 + if (std::is_same_v<Impl, BitmapOr> || is_count()) { return false; } else { return true; @@ -175,6 +183,23 @@ public: Status execute_impl(FunctionContext* context, Block& block, const ColumnNumbers& arguments, size_t result, size_t input_rows_count) override { + if (std::is_same_v<Impl, BitmapAndCount> || std::is_same_v<Impl, BitmapXorCount>) { + return execute_bitmap_op_count_null_to_zero( + context, block, arguments, result, input_rows_count, + std::bind((Status(FunctionBitMapVariadic<Impl>::*)(FunctionContext*, Block&, + const ColumnNumbers&, size_t, + size_t)) & + FunctionBitMapVariadic::execute_impl_internal, + this, std::placeholders::_1, std::placeholders::_2, + std::placeholders::_3, std::placeholders::_4, std::placeholders::_5)); + } else { + return execute_impl_internal(context, block, arguments, result, input_rows_count); + } + } + + Status execute_impl_internal(FunctionContext* context, Block& block, + const ColumnNumbers& arguments, size_t result, + size_t input_rows_count) { size_t argument_size = arguments.size(); ColumnPtr argument_columns[argument_size]; @@ -212,6 +237,12 @@ public: } return Status::OK(); } + +private: + bool is_count() const { + return (std::is_same_v<Impl, BitmapOrCount> || std::is_same_v<Impl, BitmapAndCount> || + std::is_same_v<Impl, BitmapXorCount>); + } }; using FunctionBitmapOr = FunctionBitMapVariadic<BitmapOr>; diff --git a/be/test/vec/function/function_bitmap_test.cpp b/be/test/vec/function/function_bitmap_test.cpp index fadb858242..b58b597aad 100644 --- a/be/test/vec/function/function_bitmap_test.cpp +++ b/be/test/vec/function/function_bitmap_test.cpp @@ -87,7 +87,7 @@ TEST(function_bitmap_test, function_bitmap_and_count) { DataSet data_set = {{{&bitmap1, &bitmap2, &empty_bitmap}, (int64_t)0}, {{&bitmap1, &bitmap2, &bitmap3}, (int64_t)1}, //33 - {{&bitmap1, &bitmap2, Null()}, Null()}, + {{&bitmap1, &bitmap2, Null()}, (int64_t)0}, {{&bitmap1, &bitmap3, &bitmap3}, (int64_t)1}}; //33 check_function<DataTypeInt64, true>(func_name, input_types, data_set); @@ -151,7 +151,7 @@ TEST(function_bitmap_test, function_bitmap_xor_count) { DataSet data_set = { {{&bitmap1, &bitmap2, &empty_bitmap}, (int64_t)5}, //0,1,33,1024,2019 {{&bitmap1, &bitmap2, &bitmap3}, (int64_t)6}, //0,1,5,1024,2019,18446744073709551615 - {{&bitmap1, &empty_bitmap, Null()}, Null()}, + {{&bitmap1, &empty_bitmap, Null()}, (int64_t)0}, {{&bitmap1, &bitmap3, &bitmap3}, (int64_t)3}}; //1,1024,2019 check_function<DataTypeInt64, true>(func_name, input_types, data_set); @@ -167,7 +167,7 @@ TEST(function_bitmap_test, function_bitmap_and_not_count) { BitmapValue empty_bitmap; DataSet data_set = {{{&bitmap1, &empty_bitmap}, (int64_t)3}, //1,2,3 - {{&bitmap2, Null()}, Null()}, + {{&bitmap2, Null()}, (int64_t)0}, {{&bitmap2, &bitmap3}, (int64_t)3}, //0,3,4 {{&bitmap1, &bitmap2}, (int64_t)2}}; //1,2 diff --git a/regression-test/data/query_p0/sql_functions/bitmap_functions/test_bitmap_function.out b/regression-test/data/nereids_p0/sql_functions/bitmap_functions/test_bitmap_function.out similarity index 70% copy from regression-test/data/query_p0/sql_functions/bitmap_functions/test_bitmap_function.out copy to regression-test/data/nereids_p0/sql_functions/bitmap_functions/test_bitmap_function.out index c3cccfbba5..57a71aceb9 100644 --- a/regression-test/data/query_p0/sql_functions/bitmap_functions/test_bitmap_function.out +++ b/regression-test/data/nereids_p0/sql_functions/bitmap_functions/test_bitmap_function.out @@ -189,23 +189,23 @@ true 3 \N \N 5 \N 4 \N \N 5 \N --- !sql -- +-- !sql_bitmap_and_count1 -- 0 --- !sql -- +-- !sql_bitmap_and_count2 -- 3 --- !sql -- +-- !sql_bitmap_and_count3 -- 1 --- !sql -- +-- !sql_bitmap_and_count4 -- 2 --- !sql -- +-- !sql_bitmap_and_count5 -- 0 --- !sql -- -\N +-- !sql_bitmap_and_count6 -- +0 -- !sql_bitmap_or_count1 -- 3 @@ -237,23 +237,95 @@ true -- !sql -- \N --- !sql -- +-- !sql_bitmap_xor_count1 -- 4 --- !sql -- +-- !sql_bitmap_xor_count2 -- 0 --- !sql -- +-- !sql_bitmap_xor_count3 -- 6 --- !sql -- +-- !sql_bitmap_xor_count4 -- 3 --- !sql -- +-- !sql_bitmap_xor_count5 -- 3 --- !sql -- -\N +-- !sql_bitmap_xor_count6 -- +0 + +-- !sql_bitmap_and_count7 -- +1 1 +2 1 +3 0 +4 0 + +-- !sql_bitmap_xor_count7 -- +1 2 +2 2 +3 0 +4 0 + +-- !sql_bitmap_and_not_count3 -- +1 1 +2 1 +3 0 +4 0 + +-- !sql_bitmap_and_count8 -- +1 2 +2 2 +3 1 +4 1 + +-- !sql_bitmap_xor_count8 -- +1 3 +2 3 +3 1 +4 1 + +-- !sql_bitmap_and_not_count4 -- +1 2 +2 2 +3 1 +4 1 + +-- !sql_bitmap_and_count9 -- +1 1 +2 1 + +-- !sql_bitmap_xor_count9 -- +1 2 +2 2 + +-- !sql_bitmap_and_not_count5 -- +1 1 +2 1 + +-- !sql_bitmap_and_count10 -- +1 1 +2 3 + +-- !sql_bitmap_xor_count10 -- +1 2 +2 1 + +-- !sql_bitmap_and_not_count6 -- +1 1 +2 1 + +-- !sql_bitmap_and_count11 -- +1 2 +2 4 + +-- !sql_bitmap_xor_count11 -- +1 3 +2 2 + +-- !sql_bitmap_and_not_count7 -- +1 2 +2 2 -- !sql -- 0 @@ -264,9 +336,12 @@ true -- !sql -- 2 --- !sql -- +-- !sql_bitmap_and_not_count1 -- 2 +-- !sql_bitmap_and_not_count2 -- +0 + -- !sql -- 1,2,3,4,5 @@ -355,16 +430,6 @@ true -- !sql -- 0 --- !sql -- -[1, 2, 3] -[1, 2, 3, 4, 5] - --- !sql -- -[] - --- !sql -- -[3, 4, 100, 200] - -- !sql -- 1,2,3 diff --git a/regression-test/data/query_p0/sql_functions/bitmap_functions/test_bitmap_function.out b/regression-test/data/query_p0/sql_functions/bitmap_functions/test_bitmap_function.out index c3cccfbba5..8cb5e56a7f 100644 --- a/regression-test/data/query_p0/sql_functions/bitmap_functions/test_bitmap_function.out +++ b/regression-test/data/query_p0/sql_functions/bitmap_functions/test_bitmap_function.out @@ -189,23 +189,23 @@ true 3 \N \N 5 \N 4 \N \N 5 \N --- !sql -- +-- !sql_bitmap_and_count1 -- 0 --- !sql -- +-- !sql_bitmap_and_count2 -- 3 --- !sql -- +-- !sql_bitmap_and_count3 -- 1 --- !sql -- +-- !sql_bitmap_and_count4 -- 2 --- !sql -- +-- !sql_bitmap_and_count5 -- 0 --- !sql -- -\N +-- !sql_bitmap_and_count6 -- +0 -- !sql_bitmap_or_count1 -- 3 @@ -237,23 +237,95 @@ true -- !sql -- \N --- !sql -- +-- !sql_bitmap_xor_count1 -- 4 --- !sql -- +-- !sql_bitmap_xor_count2 -- 0 --- !sql -- +-- !sql_bitmap_xor_count3 -- 6 --- !sql -- +-- !sql_bitmap_xor_count4 -- 3 --- !sql -- +-- !sql_bitmap_xor_count5 -- 3 --- !sql -- -\N +-- !sql_bitmap_xor_count6 -- +0 + +-- !sql_bitmap_and_count7 -- +1 1 +2 1 +3 0 +4 0 + +-- !sql_bitmap_xor_count7 -- +1 2 +2 2 +3 0 +4 0 + +-- !sql_bitmap_and_not_count3 -- +1 1 +2 1 +3 0 +4 0 + +-- !sql_bitmap_and_count8 -- +1 2 +2 2 +3 1 +4 1 + +-- !sql_bitmap_xor_count8 -- +1 3 +2 3 +3 1 +4 1 + +-- !sql_bitmap_and_not_count4 -- +1 2 +2 2 +3 1 +4 1 + +-- !sql_bitmap_and_count9 -- +1 1 +2 1 + +-- !sql_bitmap_xor_count9 -- +1 2 +2 2 + +-- !sql_bitmap_and_not_count5 -- +1 1 +2 1 + +-- !sql_bitmap_and_count10 -- +1 1 +2 3 + +-- !sql_bitmap_xor_count10 -- +1 2 +2 1 + +-- !sql_bitmap_and_not_count6 -- +1 1 +2 1 + +-- !sql_bitmap_and_count11 -- +1 2 +2 4 + +-- !sql_bitmap_xor_count11 -- +1 3 +2 2 + +-- !sql_bitmap_and_not_count7 -- +1 2 +2 2 -- !sql -- 0 @@ -264,9 +336,12 @@ true -- !sql -- 2 --- !sql -- +-- !sql_bitmap_and_not_count1 -- 2 +-- !sql_bitmap_and_not_count2 -- +0 + -- !sql -- 1,2,3,4,5 diff --git a/regression-test/suites/query_p0/sql_functions/bitmap_functions/test_bitmap_function.groovy b/regression-test/suites/nereids_p0/sql_functions/bitmap_functions/test_bitmap_function.groovy similarity index 64% copy from regression-test/suites/query_p0/sql_functions/bitmap_functions/test_bitmap_function.groovy copy to regression-test/suites/nereids_p0/sql_functions/bitmap_functions/test_bitmap_function.groovy index d94b6f1cdf..4f8d6416dd 100644 --- a/regression-test/suites/query_p0/sql_functions/bitmap_functions/test_bitmap_function.groovy +++ b/regression-test/suites/nereids_p0/sql_functions/bitmap_functions/test_bitmap_function.groovy @@ -16,9 +16,8 @@ // under the License. suite("test_bitmap_function") { - - sql """ SET enable_vectorized_engine = TRUE; """ - + sql "SET enable_nereids_planner=true" + sql "SET enable_fallback_to_original_planner=false" // BITMAP_AND qt_sql """ select bitmap_count(bitmap_and(to_bitmap(1), to_bitmap(2))) cnt """ qt_sql """ select bitmap_count(bitmap_and(to_bitmap(1), to_bitmap(1))) cnt """ @@ -59,18 +58,20 @@ suite("test_bitmap_function") { // BITMAP_OR qt_sql_bitmap_or1 """ select bitmap_count(bitmap_or(to_bitmap(1), to_bitmap(2))) cnt """ - qt_sql_bitmap_or2 """ select bitmap_count(bitmap_or(to_bitmap(1), to_bitmap(1))) cnt """ - qt_sql_bitmap_or3 """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2))) """ - qt_sql_bitmap_or4 """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2), to_bitmap(10), to_bitmap(0), NULL)) """ - qt_sql_bitmap_or5 """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2), to_bitmap(10), to_bitmap(0), bitmap_empty())) """ - qt_sql_bitmap_or6 """ select bitmap_to_string(bitmap_or(to_bitmap(10), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'))) """ - qt_sql_bitmap_or7 """ select bitmap_count(bitmap_or(to_bitmap(1), null)) cnt """ + qt_sql_bitmap_or2 """ select bitmap_count(bitmap_or(to_bitmap(1), to_bitmap(1))) cnt """ + qt_sql_bitmap_or3 """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2))) """ + // TODO: fix constant fold of bitmap_or and enable this case + // qt_sql_bitmap_or4 """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2), to_bitmap(10), to_bitmap(0), NULL)) """ + qt_sql_bitmap_or5 """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2), to_bitmap(10), to_bitmap(0), bitmap_empty())) """ + qt_sql_bitmap_or6 """ select bitmap_to_string(bitmap_or(to_bitmap(10), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'))) """ + // TODO: fix constant fold of bitmap_or and enable this case + // qt_sql_bitmap_or7 """ select bitmap_count(bitmap_or(to_bitmap(1), null)) cnt """ // bitmap_or of all nullable column - sql """ DROP TABLE IF EXISTS test_bitmap_or1 """ - sql """ DROP TABLE IF EXISTS test_bitmap_or2 """ + sql """ DROP TABLE IF EXISTS test_bitmap1 """ + sql """ DROP TABLE IF EXISTS test_bitmap2 """ sql """ - CREATE TABLE test_bitmap_or1 ( + CREATE TABLE test_bitmap1 ( dt INT(11) NULL, id bitmap BITMAP_UNION NULL ) ENGINE=OLAP @@ -82,7 +83,7 @@ suite("test_bitmap_function") { """ sql """ insert into - test_bitmap_or1 + test_bitmap1 values (1, to_bitmap(11)), (2, to_bitmap(22)), @@ -90,7 +91,7 @@ suite("test_bitmap_function") { (4, to_bitmap(44)); """ sql """ - CREATE TABLE test_bitmap_or2 ( + CREATE TABLE test_bitmap2 ( dt INT(11) NULL, id bitmap BITMAP_UNION NULL ) ENGINE=OLAP @@ -102,7 +103,7 @@ suite("test_bitmap_function") { """ sql """ insert into - test_bitmap_or2 + test_bitmap2 values (1, to_bitmap(111)), (2, to_bitmap(222)), @@ -113,7 +114,7 @@ suite("test_bitmap_function") { l.dt, bitmap_count(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt order by l.dt, count """ @@ -122,7 +123,7 @@ suite("test_bitmap_function") { l.dt, bitmap_or_count(l.id, r.id) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt order by l.dt, count """ @@ -131,7 +132,7 @@ suite("test_bitmap_function") { l.dt, bitmap_count(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt where r.id is not null order by l.dt, count @@ -141,7 +142,7 @@ suite("test_bitmap_function") { l.dt, bitmap_or_count(l.id, r.id) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt where r.id is not null order by l.dt, count @@ -151,7 +152,7 @@ suite("test_bitmap_function") { l.dt, bitmap_to_string(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt order by l.dt """ @@ -160,16 +161,16 @@ suite("test_bitmap_function") { l.dt, bitmap_to_string(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt where r.id is not null order by l.dt """ // bitmap_or of NOT NULLABLE column and nullable column - sql """ DROP TABLE IF EXISTS test_bitmap_or1 """ + sql """ DROP TABLE IF EXISTS test_bitmap1 """ sql """ - CREATE TABLE test_bitmap_or1 ( + CREATE TABLE test_bitmap1 ( dt INT(11) NULL, id bitmap BITMAP_UNION NOT NULL ) ENGINE=OLAP @@ -181,7 +182,7 @@ suite("test_bitmap_function") { """ sql """ insert into - test_bitmap_or1 + test_bitmap1 values (1, to_bitmap(11)), (2, to_bitmap(22)), @@ -193,7 +194,7 @@ suite("test_bitmap_function") { l.dt, bitmap_count(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt order by l.dt, count """ @@ -202,7 +203,7 @@ suite("test_bitmap_function") { l.dt, bitmap_or_count(l.id, r.id) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt order by l.dt, count """ @@ -211,7 +212,7 @@ suite("test_bitmap_function") { l.dt, bitmap_count(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt where r.id is not null order by l.dt, count @@ -221,7 +222,7 @@ suite("test_bitmap_function") { l.dt, bitmap_or_count(l.id, r.id) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt where r.id is not null order by l.dt, count @@ -231,7 +232,7 @@ suite("test_bitmap_function") { l.dt, bitmap_to_string(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt order by l.dt """ @@ -240,7 +241,7 @@ suite("test_bitmap_function") { l.dt, bitmap_to_string(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt where r.id is not null order by l.dt @@ -266,8 +267,8 @@ suite("test_bitmap_function") { r.dt rdt, r.id rid from - test_bitmap_or1 l - left join test_bitmap_or2 r on l.dt = r.dt + test_bitmap1 l + left join test_bitmap2 r on l.dt = r.dt where r.id is null); """ sql """ @@ -278,8 +279,8 @@ suite("test_bitmap_function") { r.dt rdt, r.id rid from - test_bitmap_or1 l - right join test_bitmap_or2 r on l.dt = r.dt + test_bitmap1 l + right join test_bitmap2 r on l.dt = r.dt where l.id is null); """ @@ -291,19 +292,21 @@ suite("test_bitmap_function") { qt_sql_bitmap_or23 """ select v1.ldt, v1.rdt, v2.ldt, v2.rdt, bitmap_to_string(bitmap_or(v1.rid, v2.lid)) from v1, v2 order by v1.ldt, v2.rdt; """ // bitmap_and_count - qt_sql """ select bitmap_and_count(bitmap_from_string('1,2,3'),bitmap_empty()) """ - qt_sql """ select bitmap_and_count(bitmap_from_string('1,2,3'),bitmap_from_string('1,2,3')) """ - qt_sql """ select bitmap_and_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) """ - qt_sql """ select bitmap_and_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5')) """ - qt_sql """ select bitmap_and_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'),bitmap_empty()) """ - qt_sql """ select bitmap_and_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'), NULL) """ + qt_sql_bitmap_and_count1 """ select bitmap_and_count(bitmap_from_string('1,2,3'),bitmap_empty()) """ + qt_sql_bitmap_and_count2 """ select bitmap_and_count(bitmap_from_string('1,2,3'),bitmap_from_string('1,2,3')) """ + qt_sql_bitmap_and_count3 """ select bitmap_and_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) """ + qt_sql_bitmap_and_count4 """ select bitmap_and_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5')) """ + qt_sql_bitmap_and_count5 """ select bitmap_and_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'),bitmap_empty()) """ + // TODO: fix constant fold and enable this case + // qt_sql_bitmap_and_count6 """ select bitmap_and_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'), NULL) """ // bitmap_or_count qt_sql_bitmap_or_count1 """ select bitmap_or_count(bitmap_from_string('1,2,3'),bitmap_empty()) """ qt_sql_bitmap_or_count2 """ select bitmap_or_count(bitmap_from_string('1,2,3'),bitmap_from_string('1,2,3'))""" qt_sql_bitmap_or_count3 """ select bitmap_or_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) """ qt_sql_bitmap_or_count4 """ select bitmap_or_count(bitmap_from_string('1,2,3'), bitmap_from_string('3,4,5'), to_bitmap(100), bitmap_empty()) """ - qt_sql_bitmap_or_count5 """ select bitmap_or_count(bitmap_from_string('1,2,3'), bitmap_from_string('3,4,5'), to_bitmap(100), NULL) """ + // TODO: fix constant fold of bitmap_or and enable this case + // qt_sql_bitmap_or_count5 """ select bitmap_or_count(bitmap_from_string('1,2,3'), bitmap_from_string('3,4,5'), to_bitmap(100), NULL) """ // BITMAP_XOR qt_sql """ select bitmap_count(bitmap_xor(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'))) cnt """ @@ -313,12 +316,209 @@ suite("test_bitmap_function") { qt_sql """ select bitmap_to_string(bitmap_xor(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'),NULL)) """ // BITMAP_XOR_COUNT - qt_sql """ select bitmap_xor_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) """ - qt_sql """ select bitmap_xor_count(bitmap_from_string('1,2,3'),bitmap_from_string('1,2,3')) """ - qt_sql """ select bitmap_xor_count(bitmap_from_string('1,2,3'),bitmap_from_string('4,5,6')) """ - qt_sql """ select (bitmap_xor_count(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'))) """ - qt_sql """ select (bitmap_xor_count(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'),bitmap_empty())) """ - qt_sql """ select (bitmap_xor_count(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'),NULL)) """ + qt_sql_bitmap_xor_count1 """ select bitmap_xor_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) """ + qt_sql_bitmap_xor_count2 """ select bitmap_xor_count(bitmap_from_string('1,2,3'),bitmap_from_string('1,2,3')) """ + qt_sql_bitmap_xor_count3 """ select bitmap_xor_count(bitmap_from_string('1,2,3'),bitmap_from_string('4,5,6')) """ + qt_sql_bitmap_xor_count4 """ select (bitmap_xor_count(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'))) """ + qt_sql_bitmap_xor_count5 """ select (bitmap_xor_count(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'),bitmap_empty())) """ + // TODO: fix constant fold and enable this case + // qt_sql_bitmap_xor_count6 """ select (bitmap_xor_count(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'),NULL)) """ + + // bitmap_and_count, bitmap_xor_count, bitmap_and_not_count of all nullable column + sql """ DROP TABLE IF EXISTS test_bitmap1 """ + sql """ DROP TABLE IF EXISTS test_bitmap2 """ + sql """ + CREATE TABLE test_bitmap1 ( + dt INT(11) NULL, + id bitmap BITMAP_UNION NULL + ) ENGINE=OLAP + AGGREGATE KEY(dt) + DISTRIBUTED BY HASH(dt) BUCKETS 2 + properties ( + "replication_num" = "1" + ); + """ + sql """ + insert into + test_bitmap1 + values + (1, bitmap_from_string("11,111")), + (2, bitmap_from_string("22,222")), + (3, bitmap_from_string("33,333")), + (4, bitmap_from_string("44,444")); + """ + sql """ + CREATE TABLE test_bitmap2 ( + dt INT(11) NULL, + id bitmap BITMAP_UNION NULL + ) ENGINE=OLAP + AGGREGATE KEY(dt) + DISTRIBUTED BY HASH(dt) BUCKETS 2 + properties ( + "replication_num" = "1" + ); + """ + sql """ + insert into + test_bitmap2 + values + (1, bitmap_from_string("11,1111")), + (2, bitmap_from_string("22,2222")), + (5, bitmap_from_string("55,5555")); + """ + qt_sql_bitmap_and_count7 """ + select + l.dt, + bitmap_and_count(l.id, r.id) count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + order by l.dt, count + """ + qt_sql_bitmap_xor_count7 """ + select + l.dt, + bitmap_xor_count(l.id, r.id) count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + order by l.dt, count + """ + qt_sql_bitmap_and_not_count3 """ + select + l.dt, + bitmap_and_not_count(l.id, r.id) count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + order by l.dt, count + """ + qt_sql_bitmap_and_count8 """ + select + l.dt, + bitmap_and_count(l.id, r.id) + 1 count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + order by l.dt, count + """ + qt_sql_bitmap_xor_count8 """ + select + l.dt, + bitmap_xor_count(l.id, r.id) + 1 count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + order by l.dt, count + """ + qt_sql_bitmap_and_not_count4 """ + select + l.dt, + bitmap_and_not_count(l.id, r.id) + 1 count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + order by l.dt, count + """ + qt_sql_bitmap_and_count9 """ + select + l.dt, + bitmap_and_count(l.id, r.id) count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + where r.id is not null + order by l.dt, count + """ + qt_sql_bitmap_xor_count9 """ + select + l.dt, + bitmap_xor_count(l.id, r.id) count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + where r.id is not null + order by l.dt, count + """ + qt_sql_bitmap_and_not_count5 """ + select + l.dt, + bitmap_and_not_count(l.id, r.id) count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + where r.id is not null + order by l.dt, count + """ + // bitmap_and_count, bitmap_xor_count, bitmap_and_not_count of all not nullable column + sql """ DROP TABLE IF EXISTS test_bitmap1 """ + sql """ + CREATE TABLE test_bitmap1 ( + dt INT(11) NOT NULL, + id1 bitmap BITMAP_UNION NOT NULL, + id2 bitmap BITMAP_UNION NOT NULL + ) ENGINE=OLAP + AGGREGATE KEY(dt) + DISTRIBUTED BY HASH(dt) BUCKETS 2 + properties ( + "replication_num" = "1" + ); + """ + sql """ + insert into + test_bitmap1 + values + (1, bitmap_from_string("11,1111"), bitmap_from_string("11,111")), + (2, bitmap_from_string("22,222,2222,22222"), bitmap_from_string("22,222,2222")) + """ + qt_sql_bitmap_and_count10 """ + select + dt, + bitmap_and_count(id1, id2) count + from + test_bitmap1 + order by dt, count + """ + qt_sql_bitmap_xor_count10 """ + select + dt, + bitmap_xor_count(id1, id2) count + from + test_bitmap1 + order by dt, count + """ + qt_sql_bitmap_and_not_count6 """ + select + dt, + bitmap_and_not_count(id1, id2) count + from + test_bitmap1 + order by dt, count + """ + qt_sql_bitmap_and_count11 """ + select + dt, + bitmap_and_count(id1, id2) + 1 count + from + test_bitmap1 + order by dt, count + """ + qt_sql_bitmap_xor_count11 """ + select + dt, + bitmap_xor_count(id1, id2) + 1 count + from + test_bitmap1 + order by dt, count + """ + qt_sql_bitmap_and_not_count7 """ + select + dt, + bitmap_and_not_count(id1, id2) + 1 count + from + test_bitmap1 + order by dt, count + """ // BITMAP_NOT qt_sql """ select bitmap_count(bitmap_not(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'))) cnt """ @@ -328,7 +528,9 @@ suite("test_bitmap_function") { qt_sql """ select bitmap_count(bitmap_and_not(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5'))) cnt """ // BITMAP_AND_NOT_COUNT - qt_sql """ select bitmap_and_not_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) cnt """ + qt_sql_bitmap_and_not_count1 """ select bitmap_and_not_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) cnt """ + // TODO: fix constant fold and enable this case + // qt_sql_bitmap_and_not_count2 """ select bitmap_and_not_count(bitmap_from_string('1,2,3'),null) cnt """ // BITMAP_SUBSET_IN_RANGE qt_sql """ select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,2,3,4,5'), 0, 9)) value """ @@ -408,9 +610,12 @@ suite("test_bitmap_function") { qt_sql """ select orthogonal_bitmap_intersect_count(members, tag_group, 1150000, 1150001, 390006) from ${arthogonalBitmapTable} where tag_group in ( 1150000, 1150001, 390006); """ qt_sql """ select orthogonal_bitmap_union_count(members) from ${arthogonalBitmapTable} where tag_group in ( 1150000, 1150001, 390006); """ - qt_sql """ select bitmap_to_array(user_id) from ${intersectCountTable} order by dt desc; """ - qt_sql """ select bitmap_to_array(bitmap_empty()); """ - qt_sql """ select bitmap_to_array(bitmap_from_string('100,200,3,4')); """ + // Nereids does't support array function + // qt_sql """ select bitmap_to_array(user_id) from ${intersectCountTable} order by dt desc; """ + // Nereids does't support array function + // qt_sql """ select bitmap_to_array(bitmap_empty()); """ + // Nereids does't support array function + // qt_sql """ select bitmap_to_array(bitmap_from_string('100,200,3,4')); """ qt_sql """ select bitmap_to_string(sub_bitmap(bitmap_from_string('1,2,3,4,5'), 0, 3)) value; """ qt_sql """ select bitmap_to_string(sub_bitmap(bitmap_from_string('1'), 0, 3)) value; """ diff --git a/regression-test/suites/query_p0/sql_functions/bitmap_functions/test_bitmap_function.groovy b/regression-test/suites/query_p0/sql_functions/bitmap_functions/test_bitmap_function.groovy index d94b6f1cdf..aefcf6f1df 100644 --- a/regression-test/suites/query_p0/sql_functions/bitmap_functions/test_bitmap_function.groovy +++ b/regression-test/suites/query_p0/sql_functions/bitmap_functions/test_bitmap_function.groovy @@ -59,18 +59,18 @@ suite("test_bitmap_function") { // BITMAP_OR qt_sql_bitmap_or1 """ select bitmap_count(bitmap_or(to_bitmap(1), to_bitmap(2))) cnt """ - qt_sql_bitmap_or2 """ select bitmap_count(bitmap_or(to_bitmap(1), to_bitmap(1))) cnt """ - qt_sql_bitmap_or3 """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2))) """ - qt_sql_bitmap_or4 """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2), to_bitmap(10), to_bitmap(0), NULL)) """ - qt_sql_bitmap_or5 """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2), to_bitmap(10), to_bitmap(0), bitmap_empty())) """ - qt_sql_bitmap_or6 """ select bitmap_to_string(bitmap_or(to_bitmap(10), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'))) """ - qt_sql_bitmap_or7 """ select bitmap_count(bitmap_or(to_bitmap(1), null)) cnt """ + qt_sql_bitmap_or2 """ select bitmap_count(bitmap_or(to_bitmap(1), to_bitmap(1))) cnt """ + qt_sql_bitmap_or3 """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2))) """ + qt_sql_bitmap_or4 """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2), to_bitmap(10), to_bitmap(0), NULL)) """ + qt_sql_bitmap_or5 """ select bitmap_to_string(bitmap_or(to_bitmap(1), to_bitmap(2), to_bitmap(10), to_bitmap(0), bitmap_empty())) """ + qt_sql_bitmap_or6 """ select bitmap_to_string(bitmap_or(to_bitmap(10), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'))) """ + qt_sql_bitmap_or7 """ select bitmap_count(bitmap_or(to_bitmap(1), null)) cnt """ // bitmap_or of all nullable column - sql """ DROP TABLE IF EXISTS test_bitmap_or1 """ - sql """ DROP TABLE IF EXISTS test_bitmap_or2 """ + sql """ DROP TABLE IF EXISTS test_bitmap1 """ + sql """ DROP TABLE IF EXISTS test_bitmap2 """ sql """ - CREATE TABLE test_bitmap_or1 ( + CREATE TABLE test_bitmap1 ( dt INT(11) NULL, id bitmap BITMAP_UNION NULL ) ENGINE=OLAP @@ -82,7 +82,7 @@ suite("test_bitmap_function") { """ sql """ insert into - test_bitmap_or1 + test_bitmap1 values (1, to_bitmap(11)), (2, to_bitmap(22)), @@ -90,7 +90,7 @@ suite("test_bitmap_function") { (4, to_bitmap(44)); """ sql """ - CREATE TABLE test_bitmap_or2 ( + CREATE TABLE test_bitmap2 ( dt INT(11) NULL, id bitmap BITMAP_UNION NULL ) ENGINE=OLAP @@ -102,7 +102,7 @@ suite("test_bitmap_function") { """ sql """ insert into - test_bitmap_or2 + test_bitmap2 values (1, to_bitmap(111)), (2, to_bitmap(222)), @@ -113,7 +113,7 @@ suite("test_bitmap_function") { l.dt, bitmap_count(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt order by l.dt, count """ @@ -122,7 +122,7 @@ suite("test_bitmap_function") { l.dt, bitmap_or_count(l.id, r.id) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt order by l.dt, count """ @@ -131,7 +131,7 @@ suite("test_bitmap_function") { l.dt, bitmap_count(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt where r.id is not null order by l.dt, count @@ -141,7 +141,7 @@ suite("test_bitmap_function") { l.dt, bitmap_or_count(l.id, r.id) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt where r.id is not null order by l.dt, count @@ -151,7 +151,7 @@ suite("test_bitmap_function") { l.dt, bitmap_to_string(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt order by l.dt """ @@ -160,16 +160,16 @@ suite("test_bitmap_function") { l.dt, bitmap_to_string(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt where r.id is not null order by l.dt """ // bitmap_or of NOT NULLABLE column and nullable column - sql """ DROP TABLE IF EXISTS test_bitmap_or1 """ + sql """ DROP TABLE IF EXISTS test_bitmap1 """ sql """ - CREATE TABLE test_bitmap_or1 ( + CREATE TABLE test_bitmap1 ( dt INT(11) NULL, id bitmap BITMAP_UNION NOT NULL ) ENGINE=OLAP @@ -181,7 +181,7 @@ suite("test_bitmap_function") { """ sql """ insert into - test_bitmap_or1 + test_bitmap1 values (1, to_bitmap(11)), (2, to_bitmap(22)), @@ -193,7 +193,7 @@ suite("test_bitmap_function") { l.dt, bitmap_count(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt order by l.dt, count """ @@ -202,7 +202,7 @@ suite("test_bitmap_function") { l.dt, bitmap_or_count(l.id, r.id) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt order by l.dt, count """ @@ -211,7 +211,7 @@ suite("test_bitmap_function") { l.dt, bitmap_count(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt where r.id is not null order by l.dt, count @@ -221,7 +221,7 @@ suite("test_bitmap_function") { l.dt, bitmap_or_count(l.id, r.id) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt where r.id is not null order by l.dt, count @@ -231,7 +231,7 @@ suite("test_bitmap_function") { l.dt, bitmap_to_string(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt order by l.dt """ @@ -240,7 +240,7 @@ suite("test_bitmap_function") { l.dt, bitmap_to_string(bitmap_or(l.id, r.id)) count from - test_bitmap_or1 l left join test_bitmap_or2 r + test_bitmap1 l left join test_bitmap2 r on l.dt = r.dt where r.id is not null order by l.dt @@ -266,8 +266,8 @@ suite("test_bitmap_function") { r.dt rdt, r.id rid from - test_bitmap_or1 l - left join test_bitmap_or2 r on l.dt = r.dt + test_bitmap1 l + left join test_bitmap2 r on l.dt = r.dt where r.id is null); """ sql """ @@ -278,8 +278,8 @@ suite("test_bitmap_function") { r.dt rdt, r.id rid from - test_bitmap_or1 l - right join test_bitmap_or2 r on l.dt = r.dt + test_bitmap1 l + right join test_bitmap2 r on l.dt = r.dt where l.id is null); """ @@ -291,12 +291,12 @@ suite("test_bitmap_function") { qt_sql_bitmap_or23 """ select v1.ldt, v1.rdt, v2.ldt, v2.rdt, bitmap_to_string(bitmap_or(v1.rid, v2.lid)) from v1, v2 order by v1.ldt, v2.rdt; """ // bitmap_and_count - qt_sql """ select bitmap_and_count(bitmap_from_string('1,2,3'),bitmap_empty()) """ - qt_sql """ select bitmap_and_count(bitmap_from_string('1,2,3'),bitmap_from_string('1,2,3')) """ - qt_sql """ select bitmap_and_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) """ - qt_sql """ select bitmap_and_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5')) """ - qt_sql """ select bitmap_and_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'),bitmap_empty()) """ - qt_sql """ select bitmap_and_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'), NULL) """ + qt_sql_bitmap_and_count1 """ select bitmap_and_count(bitmap_from_string('1,2,3'),bitmap_empty()) """ + qt_sql_bitmap_and_count2 """ select bitmap_and_count(bitmap_from_string('1,2,3'),bitmap_from_string('1,2,3')) """ + qt_sql_bitmap_and_count3 """ select bitmap_and_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) """ + qt_sql_bitmap_and_count4 """ select bitmap_and_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5')) """ + qt_sql_bitmap_and_count5 """ select bitmap_and_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'),bitmap_empty()) """ + qt_sql_bitmap_and_count6 """ select bitmap_and_count(bitmap_from_string('1,2,3'), bitmap_from_string('1,2'), bitmap_from_string('1,2,3,4,5'), NULL) """ // bitmap_or_count qt_sql_bitmap_or_count1 """ select bitmap_or_count(bitmap_from_string('1,2,3'),bitmap_empty()) """ @@ -313,12 +313,208 @@ suite("test_bitmap_function") { qt_sql """ select bitmap_to_string(bitmap_xor(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'),NULL)) """ // BITMAP_XOR_COUNT - qt_sql """ select bitmap_xor_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) """ - qt_sql """ select bitmap_xor_count(bitmap_from_string('1,2,3'),bitmap_from_string('1,2,3')) """ - qt_sql """ select bitmap_xor_count(bitmap_from_string('1,2,3'),bitmap_from_string('4,5,6')) """ - qt_sql """ select (bitmap_xor_count(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'))) """ - qt_sql """ select (bitmap_xor_count(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'),bitmap_empty())) """ - qt_sql """ select (bitmap_xor_count(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'),NULL)) """ + qt_sql_bitmap_xor_count1 """ select bitmap_xor_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) """ + qt_sql_bitmap_xor_count2 """ select bitmap_xor_count(bitmap_from_string('1,2,3'),bitmap_from_string('1,2,3')) """ + qt_sql_bitmap_xor_count3 """ select bitmap_xor_count(bitmap_from_string('1,2,3'),bitmap_from_string('4,5,6')) """ + qt_sql_bitmap_xor_count4 """ select (bitmap_xor_count(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'))) """ + qt_sql_bitmap_xor_count5 """ select (bitmap_xor_count(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'),bitmap_empty())) """ + qt_sql_bitmap_xor_count6 """ select (bitmap_xor_count(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'),bitmap_from_string('3,4,5'),NULL)) """ + + // bitmap_and_count, bitmap_xor_count, bitmap_and_not_count of all nullable column + sql """ DROP TABLE IF EXISTS test_bitmap1 """ + sql """ DROP TABLE IF EXISTS test_bitmap2 """ + sql """ + CREATE TABLE test_bitmap1 ( + dt INT(11) NULL, + id bitmap BITMAP_UNION NULL + ) ENGINE=OLAP + AGGREGATE KEY(dt) + DISTRIBUTED BY HASH(dt) BUCKETS 2 + properties ( + "replication_num" = "1" + ); + """ + sql """ + insert into + test_bitmap1 + values + (1, bitmap_from_string("11,111")), + (2, bitmap_from_string("22,222")), + (3, bitmap_from_string("33,333")), + (4, bitmap_from_string("44,444")); + """ + sql """ + CREATE TABLE test_bitmap2 ( + dt INT(11) NULL, + id bitmap BITMAP_UNION NULL + ) ENGINE=OLAP + AGGREGATE KEY(dt) + DISTRIBUTED BY HASH(dt) BUCKETS 2 + properties ( + "replication_num" = "1" + ); + """ + sql """ + insert into + test_bitmap2 + values + (1, bitmap_from_string("11,1111")), + (2, bitmap_from_string("22,2222")), + (5, bitmap_from_string("55,5555")); + """ + qt_sql_bitmap_and_count7 """ + select + l.dt, + bitmap_and_count(l.id, r.id) count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + order by l.dt, count + """ + qt_sql_bitmap_xor_count7 """ + select + l.dt, + bitmap_xor_count(l.id, r.id) count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + order by l.dt, count + """ + qt_sql_bitmap_and_not_count3 """ + select + l.dt, + bitmap_and_not_count(l.id, r.id) count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + order by l.dt, count + """ + qt_sql_bitmap_and_count8 """ + select + l.dt, + bitmap_and_count(l.id, r.id) + 1 count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + order by l.dt, count + """ + qt_sql_bitmap_xor_count8 """ + select + l.dt, + bitmap_xor_count(l.id, r.id) + 1 count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + order by l.dt, count + """ + qt_sql_bitmap_and_not_count4 """ + select + l.dt, + bitmap_and_not_count(l.id, r.id) + 1 count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + order by l.dt, count + """ + qt_sql_bitmap_and_count9 """ + select + l.dt, + bitmap_and_count(l.id, r.id) count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + where r.id is not null + order by l.dt, count + """ + qt_sql_bitmap_xor_count9 """ + select + l.dt, + bitmap_xor_count(l.id, r.id) count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + where r.id is not null + order by l.dt, count + """ + qt_sql_bitmap_and_not_count5 """ + select + l.dt, + bitmap_and_not_count(l.id, r.id) count + from + test_bitmap1 l left join test_bitmap2 r + on l.dt = r.dt + where r.id is not null + order by l.dt, count + """ + // bitmap_and_count, bitmap_xor_count, bitmap_and_not_count of all not nullable column + sql """ DROP TABLE IF EXISTS test_bitmap1 """ + sql """ + CREATE TABLE test_bitmap1 ( + dt INT(11) NOT NULL, + id1 bitmap BITMAP_UNION NOT NULL, + id2 bitmap BITMAP_UNION NOT NULL + ) ENGINE=OLAP + AGGREGATE KEY(dt) + DISTRIBUTED BY HASH(dt) BUCKETS 2 + properties ( + "replication_num" = "1" + ); + """ + sql """ + insert into + test_bitmap1 + values + (1, bitmap_from_string("11,1111"), bitmap_from_string("11,111")), + (2, bitmap_from_string("22,222,2222,22222"), bitmap_from_string("22,222,2222")) + """ + qt_sql_bitmap_and_count10 """ + select + dt, + bitmap_and_count(id1, id2) count + from + test_bitmap1 + order by dt, count + """ + qt_sql_bitmap_xor_count10 """ + select + dt, + bitmap_xor_count(id1, id2) count + from + test_bitmap1 + order by dt, count + """ + qt_sql_bitmap_and_not_count6 """ + select + dt, + bitmap_and_not_count(id1, id2) count + from + test_bitmap1 + order by dt, count + """ + qt_sql_bitmap_and_count11 """ + select + dt, + bitmap_and_count(id1, id2) + 1 count + from + test_bitmap1 + order by dt, count + """ + qt_sql_bitmap_xor_count11 """ + select + dt, + bitmap_xor_count(id1, id2) + 1 count + from + test_bitmap1 + order by dt, count + """ + qt_sql_bitmap_and_not_count7 """ + select + dt, + bitmap_and_not_count(id1, id2) + 1 count + from + test_bitmap1 + order by dt, count + """ // BITMAP_NOT qt_sql """ select bitmap_count(bitmap_not(bitmap_from_string('2,3'),bitmap_from_string('1,2,3,4'))) cnt """ @@ -328,7 +524,8 @@ suite("test_bitmap_function") { qt_sql """ select bitmap_count(bitmap_and_not(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5'))) cnt """ // BITMAP_AND_NOT_COUNT - qt_sql """ select bitmap_and_not_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) cnt """ + qt_sql_bitmap_and_not_count1 """ select bitmap_and_not_count(bitmap_from_string('1,2,3'),bitmap_from_string('3,4,5')) cnt """ + qt_sql_bitmap_and_not_count2 """ select bitmap_and_not_count(bitmap_from_string('1,2,3'),null) cnt """ // BITMAP_SUBSET_IN_RANGE qt_sql """ select bitmap_to_string(bitmap_subset_in_range(bitmap_from_string('1,2,3,4,5'), 0, 9)) value """ --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
