This is an automated email from the ASF dual-hosted git repository.
morrysnow pushed a commit to branch branch-3.1
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-3.1 by this push:
new 34ec3336971 branch-3.1: [improve](function) support any_value function
with complex type #49419 (#53429)
34ec3336971 is described below
commit 34ec333697158f873e8f74d26d5dfb2fe41f482d
Author: zhangstar333 <[email protected]>
AuthorDate: Fri Jul 18 10:47:13 2025 +0800
branch-3.1: [improve](function) support any_value function with complex
type #49419 (#53429)
pick from #49419
---
.../aggregate_function_min_max.cpp | 31 +++-
.../aggregate_function_min_max.h | 105 +++++++++++-
.../data/nereids_syntax_p0/any_value.out | Bin 145 -> 1452 bytes
.../suites/nereids_syntax_p0/any_value.groovy | 179 ++++++++++++++++++++
.../suites/nereids_syntax_p0/cast.groovy | 180 ++++++++++-----------
5 files changed, 402 insertions(+), 93 deletions(-)
diff --git a/be/src/vec/aggregate_functions/aggregate_function_min_max.cpp
b/be/src/vec/aggregate_functions/aggregate_function_min_max.cpp
index 45027a899e8..9877d944033 100644
--- a/be/src/vec/aggregate_functions/aggregate_function_min_max.cpp
+++ b/be/src/vec/aggregate_functions/aggregate_function_min_max.cpp
@@ -20,14 +20,16 @@
#include "vec/aggregate_functions/aggregate_function_min_max.h"
+#include "runtime/define_primitive_type.h"
#include "vec/aggregate_functions/aggregate_function_simple_factory.h"
#include "vec/aggregate_functions/factory_helpers.h"
#include "vec/aggregate_functions/helpers.h"
#include "vec/core/types.h"
+#include "vec/data_types/data_type.h"
#include "vec/data_types/data_type_nullable.h"
namespace doris::vectorized {
-/// min, max, any
+/// min, max
template <template <typename> class Data>
AggregateFunctionPtr create_aggregate_function_single_value(const String& name,
const DataTypes&
argument_types,
@@ -88,13 +90,38 @@ AggregateFunctionPtr
create_aggregate_function_single_value(const String& name,
return nullptr;
}
+// any_value
+template <template <typename> class Data>
+AggregateFunctionPtr create_aggregate_function_single_value_any_value_function(
+ const String& name, const DataTypes& argument_types, const bool
result_is_nullable,
+ const AggregateFunctionAttr& attr) {
+ AggregateFunctionPtr res = create_aggregate_function_single_value<Data>(
+ name, argument_types, result_is_nullable, attr);
+ if (res) {
+ return res;
+ }
+ const DataTypePtr& argument_type = remove_nullable(argument_types[0]);
+ WhichDataType which(argument_type);
+ if (which.idx == TypeIndex::Array || which.idx == TypeIndex::Map ||
+ which.idx == TypeIndex::Struct || which.idx == TypeIndex::AggState ||
+ which.idx == TypeIndex::BitMap || which.idx == TypeIndex::HLL ||
+ which.idx == TypeIndex::QuantileState) {
+ return creator_without_type::create<
+
AggregateFunctionsSingleValue<SingleValueDataComplexType>>(argument_types,
+
result_is_nullable);
+ }
+
+ return nullptr;
+}
+
void register_aggregate_function_minmax(AggregateFunctionSimpleFactory&
factory) {
factory.register_function_both(
"max",
create_aggregate_function_single_value<AggregateFunctionMaxData>);
factory.register_function_both(
"min",
create_aggregate_function_single_value<AggregateFunctionMinData>);
factory.register_function_both(
- "any",
create_aggregate_function_single_value<AggregateFunctionAnyData>);
+ "any",
+
create_aggregate_function_single_value_any_value_function<AggregateFunctionAnyData>);
factory.register_alias("any", "any_value");
}
diff --git a/be/src/vec/aggregate_functions/aggregate_function_min_max.h
b/be/src/vec/aggregate_functions/aggregate_function_min_max.h
index efc2854ff14..eaa5e1c34f3 100644
--- a/be/src/vec/aggregate_functions/aggregate_function_min_max.h
+++ b/be/src/vec/aggregate_functions/aggregate_function_min_max.h
@@ -21,9 +21,12 @@
#pragma once
#include <fmt/format.h>
+#include <glog/logging.h>
#include <string.h>
#include <memory>
+#include <string>
+#include <type_traits>
#include <vector>
#include "common/logging.h"
@@ -519,18 +522,104 @@ struct AggregateFunctionMinData : Data {
static const char* name() { return "min"; }
};
+// this is used for plain type about any_value function
template <typename Data>
struct AggregateFunctionAnyData : Data {
using Self = AggregateFunctionAnyData;
using Data::IsFixedLength;
+ static const char* name() { return "any"; }
constexpr static bool IS_ANY = true;
-
void change_if_better(const IColumn& column, size_t row_num, Arena*) {
this->change_first_time(column, row_num, nullptr);
}
+
void change_if_better(const Self& to, Arena*) {
this->change_first_time(to, nullptr); }
+};
+// this is used for complex type about any_value function
+struct SingleValueDataComplexType {
static const char* name() { return "any"; }
+ constexpr static bool IS_ANY = true;
+ constexpr static bool IsFixedLength = false;
+ using Self = SingleValueDataComplexType;
+
+ SingleValueDataComplexType() = default;
+
+ SingleValueDataComplexType(const DataTypes& argument_types, int
be_version) {
+ column_type = argument_types[0];
+ column_data = column_type->create_column();
+ be_exec_version = be_version;
+ }
+
+ bool has() const { return has_value; }
+
+ void change_first_time(const IColumn& column, size_t row_num, Arena*) {
+ if (UNLIKELY(!has())) {
+ change_impl(column, row_num);
+ }
+ }
+
+ void change_first_time(const Self& to, Arena*) {
+ if (UNLIKELY(!has() && to.has())) {
+ change_impl(*to.column_data, 0);
+ }
+ }
+
+ void change_impl(const IColumn& column, size_t row_num) {
+ DCHECK_EQ(column_data->size(), 0);
+ column_data->insert_from(column, row_num);
+ has_value = true;
+ }
+
+ void insert_result_into(IColumn& to) const {
+ if (has()) {
+ to.insert_from(*column_data, 0);
+ } else {
+ to.insert_default();
+ }
+ }
+
+ void reset() {
+ column_data->clear();
+ has_value = false;
+ }
+
+ void write(BufferWritable& buf) const {
+ write_binary(has(), buf);
+ if (!has()) {
+ return;
+ }
+ auto size_bytes =
+ column_type->get_uncompressed_serialized_bytes(*column_data,
be_exec_version);
+ std::string memory_buffer(size_bytes, '0');
+ auto* p = column_type->serialize(*column_data, memory_buffer.data(),
be_exec_version);
+ write_binary(memory_buffer, buf);
+ DCHECK_EQ(p, memory_buffer.data() + size_bytes);
+ }
+
+ void read(BufferReadable& buf, Arena* arena) {
+ read_binary(has_value, buf);
+ if (!has()) {
+ return;
+ }
+ std::string memory_buffer;
+ read_binary(memory_buffer, buf);
+ const auto* p =
+ column_type->deserialize(memory_buffer.data(), &column_data,
be_exec_version);
+ DCHECK_EQ(p, memory_buffer.data() + memory_buffer.size());
+ }
+
+ void change_if_better(const IColumn& column, size_t row_num, Arena* arena)
{
+ this->change_first_time(column, row_num, nullptr);
+ }
+
+ void change_if_better(const Self& to, Arena* arena) {
this->change_first_time(to, nullptr); }
+
+private:
+ bool has_value = false;
+ MutableColumnPtr column_data;
+ DataTypePtr column_type;
+ int be_exec_version = -1;
};
template <typename Data>
@@ -539,6 +628,7 @@ class AggregateFunctionsSingleValue final
private:
DataTypePtr& type;
using Base = IAggregateFunctionDataHelper<Data,
AggregateFunctionsSingleValue<Data>>;
+ using IAggregateFunction::argument_types;
public:
AggregateFunctionsSingleValue(const DataTypes& arguments)
@@ -555,6 +645,14 @@ public:
}
}
+ void create(AggregateDataPtr __restrict place) const override {
+ if constexpr (std::is_same_v<Data, SingleValueDataComplexType>) {
+ new (place) Data(argument_types, IAggregateFunction::version);
+ } else {
+ new (place) Data;
+ }
+ }
+
String get_name() const override { return Data::name(); }
DataTypePtr get_return_type() const override { return type; }
@@ -716,4 +814,9 @@ AggregateFunctionPtr
create_aggregate_function_single_value(const String& name,
const DataTypes&
argument_types,
const bool
result_is_nullable,
const
AggregateFunctionAttr& attr = {});
+
+template <template <typename> class Data>
+AggregateFunctionPtr create_aggregate_function_single_value_any_value_function(
+ const String& name, const DataTypes& argument_types, const bool
result_is_nullable,
+ const AggregateFunctionAttr& attr = {});
} // namespace doris::vectorized
diff --git a/regression-test/data/nereids_syntax_p0/any_value.out
b/regression-test/data/nereids_syntax_p0/any_value.out
index ac9836e0630..4013f891c03 100644
Binary files a/regression-test/data/nereids_syntax_p0/any_value.out and
b/regression-test/data/nereids_syntax_p0/any_value.out differ
diff --git a/regression-test/suites/nereids_syntax_p0/any_value.groovy
b/regression-test/suites/nereids_syntax_p0/any_value.groovy
index c838efe1c1d..f05b3b36d32 100644
--- a/regression-test/suites/nereids_syntax_p0/any_value.groovy
+++ b/regression-test/suites/nereids_syntax_p0/any_value.groovy
@@ -26,4 +26,183 @@ suite("any_value") {
qt_sql_max """select max(cast(concat(number, ":00:00") as time)) from
numbers("number" = "100");"""
qt_sql_min """select min(cast(concat(number, ":00:00") as time)) from
numbers("number" = "100");"""
sql """select any(cast(concat(number, ":00:00") as time)) from
numbers("number" = "100");"""
+
+ sql """DROP TABLE IF EXISTS any_test"""
+ sql """
+ CREATE TABLE `any_test` (
+ `id` int(11) NULL,
+ `c_array1` ARRAY<int(11)> NULL,
+ `c_array2` ARRAY<int(11)> NOT NULL,
+ `c_array3` ARRAY<string> NULL,
+ `c_array4` ARRAY<string> NOT NULL,
+ `s_info1` STRUCT<s_id:int(11), s_name:string, s_address:string> NULL,
+ `s_info2` STRUCT<s_id:int(11), s_name:string, s_address:string> NOT
NULL,
+ `m1` Map<STRING, INT> NULL,
+ `m2` Map<STRING, INT> NOT NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`id`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`id`) BUCKETS 10
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1",
+ "in_memory" = "false",
+ "storage_format" = "V2"
+ );
+ """
+
+ qt_sql_any1 """ select
any(c_array1),any(c_array2),any(c_array3),any(c_array4),any(s_info1),any(s_info2),any(m1),any(m2)
from any_test; """
+ qt_sql_any2 """ select
any(c_array1),any(c_array2),any(c_array3),any(c_array4),any(s_info1),any(s_info2),any(m1),any(m2)
from any_test group by id; """
+ sql """ insert into any_test values(1, array(1,2,3), array(4,5,6),
array('a','b','c'), array('d','e','f'), named_struct('s_id', 1, 's_name', 'a',
's_address', 'b'), named_struct('s_id', 2, 's_name', 'c', 's_address', 'd'),
map('a', 1, 'b', 2), map('c', 3, 'd', 4)); """
+ qt_sql_any3 """ select
any(c_array1),any(c_array2),any(c_array3),any(c_array4),any(s_info1),any(s_info2),any(m1),any(m2)
from any_test; """
+ sql """ insert into any_test values(2, array(4,5,6), array(7,8,9),
array('d','e','f'), array('g','h','i'), named_struct('s_id', 3, 's_name', 'e',
's_address', 'f'), named_struct('s_id', 4, 's_name', 'g', 's_address', 'h'),
map('e', 5, 'f', 6), map('g', 7, 'h', 8)); """
+ qt_sql_any4 """ select
any(c_array1),any(c_array2),any(c_array3),any(c_array4),any(s_info1),any(s_info2),any(m1),any(m2)
from any_test group by id order by id; """
+
+ sql """DROP TABLE IF EXISTS baseall_any"""
+ sql """
+ CREATE TABLE `baseall_any` (
+ `k1` tinyint NULL,
+ `k2` smallint NULL,
+ `k3` int NULL,
+ `k4` bigint NULL,
+ `k5` decimal(9,3) NULL,
+ `k6` char(5) NULL,
+ `k10` date NULL,
+ `k11` datetime NULL,
+ `k7` varchar(20) NULL,
+ `k8` double MAX NULL,
+ `k9` float SUM NULL
+ ) ENGINE=OLAP
+ AGGREGATE KEY(`k1`, `k2`, `k3`, `k4`, `k5`, `k6`, `k10`, `k11`, `k7`)
+ DISTRIBUTED BY HASH(`k1`) BUCKETS 5
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ qt_sql_any5 """ select
any(k1),any(k2),any(k3),any(k4),any(k5),any(k6),any(k10),any(k11),any(k7),any(k8),any(k9)
from baseall_any; """
+ qt_sql_any6 """ select
any(k1),any(k2),any(k3),any(k4),any(k5),any(k6),any(k10),any(k11),any(k7),any(k8),any(k9)
from baseall_any group by k1; """
+ sql """
+ insert into baseall_any values(1, 1, 1, 1, 1.1, 'a', '2021-01-01',
'2021-01-01 00:00:00', 'a', 1.1, 1.1);
+ """
+ qt_sql_any7 """ select
any(k1),any(k2),any(k3),any(k4),any(k5),any(k6),any(k10),any(k11),any(k7),any(k8),any(k9)
from baseall_any; """
+ sql """
+ insert into baseall_any values(2, 2, 2, 2, 2.2, 'b', '2021-02-02',
'2021-02-02 00:00:00', 'b', 2.2, 2.2);
+ """
+ qt_sql_any8 """ select
any(k1),any(k2),any(k3),any(k4),any(k5),any(k6),any(k10),any(k11),any(k7),any(k8),any(k9)
from baseall_any group by k1 order by k1; """
+
+ sql """DROP TABLE IF EXISTS pv_bitmap_any"""
+ sql """
+ CREATE TABLE `pv_bitmap_any` (
+ `dt` int(11) NULL COMMENT "",
+ `page` varchar(10) NULL COMMENT "",
+ `user_id` bitmap BITMAP_UNION NOT NULL COMMENT ""
+ ) ENGINE=OLAP
+ AGGREGATE KEY(`dt`, `page`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`dt`) BUCKETS 2
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );
+ """
+ qt_sql_any9 """ select bitmap_to_string(any(user_id)) from pv_bitmap_any;
"""
+ qt_sql_any10 """ select dt, bitmap_to_string(any(user_id)) from
pv_bitmap_any group by dt order by dt; """
+
+ sql """
+ insert into pv_bitmap_any values(20230101, 'a',
bitmap_from_string('1,2,3'));
+ """
+ qt_sql_any11 """ select bitmap_to_string(any(user_id)) from pv_bitmap_any;
"""
+ sql """
+ insert into pv_bitmap_any values(20230102, 'b',
bitmap_from_string('4,5,6'));
+ """
+ qt_sql_any12 """ select dt, bitmap_to_string(any(user_id)) from
pv_bitmap_any group by dt order by dt; """
+
+ sql """DROP TABLE IF EXISTS test_hll_any"""
+ sql """
+ create table test_hll_any(
+ id int,
+ uv hll hll_union
+ )
+ Aggregate KEY (id)
+ distributed by hash(id) buckets 10
+ PROPERTIES(
+ "replication_num" = "1"
+ );
+ """
+ qt_sql_any13 """ select HLL_CARDINALITY(any(uv)) from test_hll_any; """
+ qt_sql_any14 """ select id, HLL_CARDINALITY(any(uv)) from test_hll_any
group by id order by id; """
+ sql """
+ insert into test_hll_any values(1, hll_hash(1));
+ """
+ qt_sql_any15 """ select HLL_CARDINALITY(any(uv)) from test_hll_any; """
+ sql """
+ insert into test_hll_any values(2, hll_hash(2));
+ """
+ qt_sql_any16 """ select id, HLL_CARDINALITY(any(uv)) from test_hll_any
group by id order by id; """
+
+
+ sql """DROP TABLE IF EXISTS quantile_state_agg_test"""
+ sql """
+ CREATE TABLE IF NOT EXISTS quantile_state_agg_test (
+ `dt` int(11) NULL COMMENT "",
+ `id` int(11) NULL COMMENT "",
+ `price` quantile_state QUANTILE_UNION NOT NULL COMMENT ""
+ ) ENGINE=OLAP
+ AGGREGATE KEY(`dt`, `id`)
+ COMMENT "OLAP"
+ DISTRIBUTED BY HASH(`dt`) BUCKETS 1
+ PROPERTIES ("replication_num" = "1");
+ """
+ qt_sql_any17 """ select quantile_percent(any(price),1) from
quantile_state_agg_test; """
+ qt_sql_any18 """ select dt, quantile_percent(any(price),1) from
quantile_state_agg_test group by dt order by dt; """
+ sql """
+ insert into quantile_state_agg_test values(20230101, 1,
to_quantile_state(1,2048));
+ """
+ qt_sql_any19 """ select quantile_percent(any(price),1) from
quantile_state_agg_test; """
+ sql """
+ insert into quantile_state_agg_test values(20230102, 2,
to_quantile_state(200,2048));
+ """
+ qt_sql_any20 """ select dt, quantile_percent(any(price),1) from
quantile_state_agg_test group by dt order by dt; """
+
+ sql """ set enable_agg_state=true"""
+ sql """DROP TABLE IF EXISTS a_table_any"""
+ sql """
+ create table a_table_any(
+ k1 int null,
+ k2 agg_state<max_by(int not null,int)> generic,
+ k3 agg_state<group_concat(string)> generic
+ )
+ aggregate key (k1)
+ distributed BY hash(k1) buckets 3
+ properties("replication_num" = "1");
+ """
+ qt_sql_any21 """ select any(k1),any(k2),any(k3) from a_table_any; """
+ qt_sql_any22 """ select k1,any(k2),any(k3) from a_table_any group by k1
order by k1; """
+ sql """
+ insert into a_table_any values(1, max_by_state(1,1),
group_concat_state('a'));
+ """
+ qt_sql_any23 """ select max_by_merge(u2),group_concat_merge(u3) from (
+ select k1,max_by_union(k2) as u2,group_concat_union(k3) u3 from
a_table_any group by k1 order by k1
+ ) t; """
+ sql """
+ insert into a_table_any values(2, max_by_state(2,2),
group_concat_state('a'));
+ """
+ qt_sql_any24 """ select max_by_merge(u2),group_concat_merge(u3) from (
+ select k1,max_by_union(k2) as u2,group_concat_union(k3) u3 from
a_table_any group by k1 order by k1
+ ) t; """
+
+ sql """drop table if exists test_table_any;"""
+ sql """CREATE TABLE `test_table_any` (
+ `ordernum` varchar(65533) NOT NULL ,
+ `dnt` datetime NOT NULL ,
+ `data` json NULL
+ ) ENGINE=OLAP
+ DUPLICATE KEY(`ordernum`, `dnt`)
+ COMMENT 'OLAP'
+ DISTRIBUTED BY HASH(`ordernum`) BUCKETS 3
+ PROPERTIES (
+ "replication_allocation" = "tag.location.default: 1"
+ );"""
+
+ sql """insert into test_table_any values('cib2205045_1_1s','2023/6/10
3:55:33','{"DB1":168939,"DNT":"2023-06-10 03:55:33"}');"""
+ qt_sql_any25 """ select any(data) from test_table_any; """
+
}
\ No newline at end of file
diff --git a/regression-test/suites/nereids_syntax_p0/cast.groovy
b/regression-test/suites/nereids_syntax_p0/cast.groovy
index 5354648801a..5cda1a0dd56 100644
--- a/regression-test/suites/nereids_syntax_p0/cast.groovy
+++ b/regression-test/suites/nereids_syntax_p0/cast.groovy
@@ -16,22 +16,22 @@
// under the License.
suite("cast") {
- def tableName1 ="test"
- def tableName2 ="baseall"
+ def tableName1 ="cast_test"
+ def tableName2 ="cast_baseall"
sql 'set enable_nereids_planner=true'
sql 'set enable_fallback_to_original_planner=false'
sql """
- drop table if exists test
+ drop table if exists cast_test
"""
sql """
- drop table if exists baseall
+ drop table if exists cast_baseall
"""
sql """
- CREATE TABLE IF NOT EXISTS `test` (
+ CREATE TABLE IF NOT EXISTS `cast_test` (
`k0` boolean NULL,
`k1` tinyint(4) NULL,
`k2` smallint(6) NULL,
@@ -59,7 +59,7 @@ suite("cast") {
"""
sql """
- CREATE TABLE IF NOT EXISTS `baseall` (
+ CREATE TABLE IF NOT EXISTS `cast_baseall` (
`k0` boolean NULL,
`k1` tinyint(4) NULL,
`k2` smallint(6) NULL,
@@ -87,14 +87,14 @@ suite("cast") {
"""
sql """
- insert into test values
+ insert into cast_test values
(0, 1, 1989, 1001, 11011902, 123.123, 'true', '1989-03-21',
'1989-03-21 13:00:00', 'wangjuoo4', 0.1, 6.333, 'string12345',
170141183460469231731687303715884105727),
(0, 2, 1986, 1001, 11011903, 1243.500, 'false', '1901-12-31',
'1989-03-21 13:00:00', 'wangynnsf', 20.268, 789.25, 'string12345',
-170141183460469231731687303715884105727),
(0, 3, 1989, 1002, 11011905, 24453.325, 'false', '2012-03-14',
'2000-01-01 00:00:00', 'yunlj8@nk', 78945.0, 3654.0, 'string12345', 0);
"""
sql """
- insert into baseall values
+ insert into cast_baseall values
(1, 10, 1991, 5014, 9223372036854775807, -258.369, 'false',
'2015-04-02', '2013-04-02 15:16:52', 'wangynnsf', -123456.54, 0.235,
'string12345', -11011903),
(1, 12, 32767, -2147483647, 9223372036854775807, 243.325, 'false',
'1991-08-11', '2013-04-02 15:16:52', 'lifsno', -564.898, 3.1415927,
'string12345', 1701604692317316873037158),
(0, 6, 32767, 3021, 123456, 604587.000, 'true', '2014-11-11',
'2015-03-13 12:36:38', 'yanavnd', 0.1, 80699.0, 'string12345', 20220104),
@@ -103,91 +103,91 @@ suite("cast") {
"""
// order by
- qt_orderBy1 "select k1, k10 from test order by 1, 2 limit 1000"
- qt_orderBy2 "select k1, k8 from test order by 1, 2 desc limit 1000"
- qt_orderBy3 "select k4, k10 from (select k4, k10 from test order by 1, 2
limit 1000000) as i order by 1, 2 limit 1000"
- qt_orderBy4 "select * from test where k1<-1000 order by k1"
+ qt_orderBy1 "select k1, k10 from cast_test order by 1, 2 limit 1000"
+ qt_orderBy2 "select k1, k8 from cast_test order by 1, 2 desc limit 1000"
+ qt_orderBy3 "select k4, k10 from (select k4, k10 from cast_test order by
1, 2 limit 1000000) as i order by 1, 2 limit 1000"
+ qt_orderBy4 "select * from cast_test where k1<-1000 order by k1"
// group
- qt_group1 "select min(k5) from test"
- qt_group2 "select max(k5) from test"
- qt_group3 "select avg(k5) from test"
- qt_group4 "select sum(k5) from test"
- qt_group5 "select count(k5) from test"
- qt_group6 "select min(k5) from test group by k2 order by min(k5)"
- qt_group7 "select max(k5) from test group by k1 order by max(k5)"
- qt_group8 "select avg(k5) from test group by k1 order by avg(k5)"
- qt_group9 "select sum(k5) from test group by k1 order by sum(k5)"
- qt_group10 "select count(k5) from test group by k1 order by count(k5)"
- qt_group11 "select lower(k6), avg(k8), sum(k8),count(k8), min(k8),
max(k8) from test group by lower(k6) order by avg(k8), sum(k8),count(k8),
min(k8), max(k8)"
- qt_group12 "select k2, avg(k8) from test group by k2 order by k2, avg(k8)"
- qt_group13 "select k2, sum(k8) from test group by k2 order by k2, sum(k8)"
- qt_group14 "select k2, count(k8) from test group by k2 order by k2,
count(k8)"
- qt_group15 "select k2, min(k8) from test group by k2 order by k2, min(k8)"
- qt_group16 "select k2, max(k8) from test group by k2 order by k2, max(k8)"
- qt_group17 "select k6, avg(k8) from test group by k6 having k6='true'
order by k6, avg(k8)"
- qt_group18 "select k6, sum(k8) from test group by k6 having k6='true'
order by k6, sum(k8)"
- qt_group19 "select k6, count(k8) from test group by k6 having k6='true'
order by k6, count(k8)"
- qt_group20 "select k6, min(k8) from test group by k6 having k6='true'
order by k6, min(k8)"
- qt_group21 "select k6, max(k8) from test group by k6 having k6='true'
order by k6, max(k8)"
- qt_group22 "select k2, avg(k8) from test group by k2 having k2<=1989 order
by k2, avg(k8)"
- qt_group23 "select k2, sum(k8) from test group by k2 having k2<=1989 order
by k2, sum(k8)"
- qt_group24 "select k2, count(k8) from test group by k2 having k2<=1989
order by k2, count(k8)"
- qt_group25 "select k2, min(k8) from test group by k2 having k2<=1989 order
by k2, min(k8)"
- qt_group26 "select k2, max(k8) from test group by k2 having k2<=1989 order
by k2, max(k8)"
- qt_group27 "select count(ALL *) from test where k5 is not null group by
k1%10 order by 1"
- qt_group28 "select k5, k5 * 2, count(*) from test group by 1, 2 order by
1, 2,3"
- qt_group29 "select k1 % 3, k2 % 3, count(*) from test where k4 > 0 group
by 2, 1 order by 1, 2, 3"
- qt_group30 "select k1 % 2, k2 % 2, k3 % 3, k4 % 3, k11, count(*) from test
where (k11 = '2015-03-13 12:36:38' or k11 = '2000-01-01 00:00:00') and k5 is
not null group by 1, 2, 3, 4, 5 order by 1, 2, 3, 4, 5"
- qt_group31 "select count(*) from test where (k11='2015-03-13 12:36:38' or
k11 = '2000-01-01 00:00:00') and k5 is not null group by k1%2, k2%2, k3%3,
k4%3, k11%2 order by 1"
- qt_group32 "select count(*), min(k1), max(k1), sum(k1), avg(k1) from test
where k1=10000 order by 1"
- qt_group33 "select k1 % 7, count(*), avg(k1) from test where k4 > 0 group
by 1 having avg(k1) > 2 or count(*) > 5 order by 1, 2, 3"
- qt_group34 "select k10, count(*) from test where k5 is not null group by
k10 having k10 < cast('2010-01-01 01:05:20' as datetime) order by 1, 2"
- qt_group35 "select k1 * k1, k1 + k1 as c from test group by k1 * k1, k1 +
k1, k1 * k1 having (c) < 5 order by 1, 2 limit 10"
- qt_group36 "select 1 from (select count(k4) c from test having min(k1) is
not null) as t where c is not null"
- qt_group37 "select count(k1), sum(k1 * k2) from test order by 1, 2"
- qt_group38 "select k1 % 2, k2 + 1, k3 from test where k3 > 10000 group by
1,2,3 order by 1,2,3"
- qt_group39 "select extract(year from k10) as wj, extract(month from k10)
as dyk, sum(k1) from test group by 1, 2 order by 1, 2, 3"
+ qt_group1 "select min(k5) from cast_test"
+ qt_group2 "select max(k5) from cast_test"
+ qt_group3 "select avg(k5) from cast_test"
+ qt_group4 "select sum(k5) from cast_test"
+ qt_group5 "select count(k5) from cast_test"
+ qt_group6 "select min(k5) from cast_test group by k2 order by min(k5)"
+ qt_group7 "select max(k5) from cast_test group by k1 order by max(k5)"
+ qt_group8 "select avg(k5) from cast_test group by k1 order by avg(k5)"
+ qt_group9 "select sum(k5) from cast_test group by k1 order by sum(k5)"
+ qt_group10 "select count(k5) from cast_test group by k1 order by count(k5)"
+ qt_group11 "select lower(k6), avg(k8), sum(k8),count(k8), min(k8),
max(k8) from cast_test group by lower(k6) order by avg(k8), sum(k8),count(k8),
min(k8), max(k8)"
+ qt_group12 "select k2, avg(k8) from cast_test group by k2 order by k2,
avg(k8)"
+ qt_group13 "select k2, sum(k8) from cast_test group by k2 order by k2,
sum(k8)"
+ qt_group14 "select k2, count(k8) from cast_test group by k2 order by k2,
count(k8)"
+ qt_group15 "select k2, min(k8) from cast_test group by k2 order by k2,
min(k8)"
+ qt_group16 "select k2, max(k8) from cast_test group by k2 order by k2,
max(k8)"
+ qt_group17 "select k6, avg(k8) from cast_test group by k6 having k6='true'
order by k6, avg(k8)"
+ qt_group18 "select k6, sum(k8) from cast_test group by k6 having k6='true'
order by k6, sum(k8)"
+ qt_group19 "select k6, count(k8) from cast_test group by k6 having
k6='true' order by k6, count(k8)"
+ qt_group20 "select k6, min(k8) from cast_test group by k6 having k6='true'
order by k6, min(k8)"
+ qt_group21 "select k6, max(k8) from cast_test group by k6 having k6='true'
order by k6, max(k8)"
+ qt_group22 "select k2, avg(k8) from cast_test group by k2 having k2<=1989
order by k2, avg(k8)"
+ qt_group23 "select k2, sum(k8) from cast_test group by k2 having k2<=1989
order by k2, sum(k8)"
+ qt_group24 "select k2, count(k8) from cast_test group by k2 having
k2<=1989 order by k2, count(k8)"
+ qt_group25 "select k2, min(k8) from cast_test group by k2 having k2<=1989
order by k2, min(k8)"
+ qt_group26 "select k2, max(k8) from cast_test group by k2 having k2<=1989
order by k2, max(k8)"
+ qt_group27 "select count(ALL *) from cast_test where k5 is not null group
by k1%10 order by 1"
+ qt_group28 "select k5, k5 * 2, count(*) from cast_test group by 1, 2 order
by 1, 2,3"
+ qt_group29 "select k1 % 3, k2 % 3, count(*) from cast_test where k4 > 0
group by 2, 1 order by 1, 2, 3"
+ qt_group30 "select k1 % 2, k2 % 2, k3 % 3, k4 % 3, k11, count(*) from
cast_test where (k11 = '2015-03-13 12:36:38' or k11 = '2000-01-01 00:00:00')
and k5 is not null group by 1, 2, 3, 4, 5 order by 1, 2, 3, 4, 5"
+ qt_group31 "select count(*) from cast_test where (k11='2015-03-13
12:36:38' or k11 = '2000-01-01 00:00:00') and k5 is not null group by k1%2,
k2%2, k3%3, k4%3, k11%2 order by 1"
+ qt_group32 "select count(*), min(k1), max(k1), sum(k1), avg(k1) from
cast_test where k1=10000 order by 1"
+ qt_group33 "select k1 % 7, count(*), avg(k1) from cast_test where k4 > 0
group by 1 having avg(k1) > 2 or count(*) > 5 order by 1, 2, 3"
+ qt_group34 "select k10, count(*) from cast_test where k5 is not null group
by k10 having k10 < cast('2010-01-01 01:05:20' as datetime) order by 1, 2"
+ qt_group35 "select k1 * k1, k1 + k1 as c from cast_test group by k1 * k1,
k1 + k1, k1 * k1 having (c) < 5 order by 1, 2 limit 10"
+ qt_group36 "select 1 from (select count(k4) c from cast_test having
min(k1) is not null) as t where c is not null"
+ qt_group37 "select count(k1), sum(k1 * k2) from cast_test order by 1, 2"
+ qt_group38 "select k1 % 2, k2 + 1, k3 from cast_test where k3 > 10000
group by 1,2,3 order by 1,2,3"
+ qt_group39 "select extract(year from k10) as wj, extract(month from k10)
as dyk, sum(k1) from cast_test group by 1, 2 order by 1, 2, 3"
// with having
- qt_group40 "select avg(k1) as a from test group by k2 having a > 10 order
by a"
- qt_group41 "select avg(k5) as a from test group by k1 having a > 100 order
by a"
- qt_group42 "select sum(k5) as a from test group by k1 having a < 100.0
order by a"
- qt_group43 "select sum(k8) as a from test group by k1 having a > 100 order
by a"
- qt_group44 "select avg(k9) as a from test group by k1 having a < 100.0
order by a"
+ qt_group40 "select avg(k1) as a from cast_test group by k2 having a > 10
order by a"
+ qt_group41 "select avg(k5) as a from cast_test group by k1 having a > 100
order by a"
+ qt_group42 "select sum(k5) as a from cast_test group by k1 having a <
100.0 order by a"
+ qt_group43 "select sum(k8) as a from cast_test group by k1 having a > 100
order by a"
+ qt_group44 "select avg(k9) as a from cast_test group by k1 having a <
100.0 order by a"
// order 2
- qt_order8 "select k1, k2 from (select k1, max(k2) as k2 from test where k1
> 0 group by k1 order by k1)a where k1 > 0 and k1 < 10 order by k1"
- qt_order9 "select k1, k2 from (select k1, max(k2) as k2 from test where k1
> 0 group by k1 order by k1)a left join (select k1 as k3, k2 as k4 from
baseall) b on a.k1 = b.k3 where k1 > 0 and k1 < 10 order by k1, k2"
- qt_order10 "select k1, count(*) from test group by 1 order by 1 limit 10"
- qt_order11 "select a.k1, b.k1, a.k6 from baseall a join test b on a.k1 =
b.k1 where a.k2 > 0 and a.k1 + b.k1 > 20 and b.k6 = 'false' order by a.k1"
- qt_order12 "select k1 from baseall order by k1 % 5, k1"
- qt_order13 "select k1 from (select k1, k2 from baseall order by k1 limit
10) a where k1 > 5 order by k1 limit 10"
- qt_order14 "select k1 from (select k1, k2 from baseall order by k1) a
where k1 > 5 order by k1 limit 10"
- qt_order15 "select k1 from (select k1, k2 from baseall order by k1 limit
10 offset 3) a where k1 > 5 order by k1 limit 5 offset 2"
- qt_order16 "select a.k1, a.k2, b.k1 from baseall a join (select * from
test where k6 = 'false' order by k1 limit 3 offset 2) b on a.k1 = b.k1 where
a.k2 > 0 order by 1"
+ qt_order8 "select k1, k2 from (select k1, max(k2) as k2 from cast_test
where k1 > 0 group by k1 order by k1)a where k1 > 0 and k1 < 10 order by k1"
+ qt_order9 "select k1, k2 from (select k1, max(k2) as k2 from cast_test
where k1 > 0 group by k1 order by k1)a left join (select k1 as k3, k2 as k4
from cast_baseall) b on a.k1 = b.k3 where k1 > 0 and k1 < 10 order by k1, k2"
+ qt_order10 "select k1, count(*) from cast_test group by 1 order by 1 limit
10"
+ qt_order11 "select a.k1, b.k1, a.k6 from cast_baseall a join cast_test b
on a.k1 = b.k1 where a.k2 > 0 and a.k1 + b.k1 > 20 and b.k6 = 'false' order by
a.k1"
+ qt_order12 "select k1 from cast_baseall order by k1 % 5, k1"
+ qt_order13 "select k1 from (select k1, k2 from cast_baseall order by k1
limit 10) a where k1 > 5 order by k1 limit 10"
+ qt_order14 "select k1 from (select k1, k2 from cast_baseall order by k1) a
where k1 > 5 order by k1 limit 10"
+ qt_order15 "select k1 from (select k1, k2 from cast_baseall order by k1
limit 10 offset 3) a where k1 > 5 order by k1 limit 5 offset 2"
+ qt_order16 "select a.k1, a.k2, b.k1 from cast_baseall a join (select *
from cast_test where k6 = 'false' order by k1 limit 3 offset 2) b on a.k1 =
b.k1 where a.k2 > 0 order by 1"
- qt_orderBy_withNull_1 "select k4 + k5 from test order by 1 nulls first"
+ qt_orderBy_withNull_1 "select k4 + k5 from cast_test order by 1 nulls
first"
// NULL结果
- qt_orderBy_withNull_2 "select k5, k5 + k6 from test where lower(k6) not
like 'na%' and upper(k6) not like 'INF%' order by k5 nulls first"
+ qt_orderBy_withNull_2 "select k5, k5 + k6 from cast_test where lower(k6)
not like 'na%' and upper(k6) not like 'INF%' order by k5 nulls first"
// null 和非null
- qt_orderBy_withNull_3 " select a.k1 ak1, b.k1 bk1 from test a right join
baseall b on a.k1=b.k1 and b.k1>10 order by ak1 desc nulls first, bk1"
+ qt_orderBy_withNull_3 " select a.k1 ak1, b.k1 bk1 from cast_test a right
join cast_baseall b on a.k1=b.k1 and b.k1>10 order by ak1 desc nulls first, bk1"
// NULL列group by
- qt_orderBy_withNull_4 "select k5 + k4 as nu, sum(k1) from test group by nu
order by nu nulls first"
- qt_orderBy_withNull_5 "select k6 + k5 as nu from test group by nu"
- qt_orderBy_withNull_6 "select k6 + k5 as nu, sum(1) from test group by nu
order by nu desc limit 5"
- qt_orderBy_withNull_7 "select k6 + k5 as nu, sum(1) from test group by nu
order by nu limit 5"
+ qt_orderBy_withNull_4 "select k5 + k4 as nu, sum(k1) from cast_test group
by nu order by nu nulls first"
+ qt_orderBy_withNull_5 "select k6 + k5 as nu from cast_test group by nu"
+ qt_orderBy_withNull_6 "select k6 + k5 as nu, sum(1) from cast_test group
by nu order by nu desc limit 5"
+ qt_orderBy_withNull_7 "select k6 + k5 as nu, sum(1) from cast_test group
by nu order by nu limit 5"
- qt_orderBy_withNull_8 "select k4 + k5 as sum, k5 + k6 as nu from test
where lower(k6) not like 'na%' and upper(k6) not like 'INF%' order by sum nulls
last"
- qt_orderBy_withNull_9 "select k4 + k5 as nu from test order by nu nulls
last"
+ qt_orderBy_withNull_8 "select k4 + k5 as sum, k5 + k6 as nu from cast_test
where lower(k6) not like 'na%' and upper(k6) not like 'INF%' order by sum
nulls last"
+ qt_orderBy_withNull_9 "select k4 + k5 as nu from cast_test order by nu
nulls last"
//null 和非null
- qt_orderBy_withNull_10 " select a.k1 ak1, b.k1 bk1 from test a right join
baseall b on a.k1=b.k1 and b.k1 > 10 order by ak1 nulls last, bk1"
+ qt_orderBy_withNull_10 " select a.k1 ak1, b.k1 bk1 from cast_test a right
join cast_baseall b on a.k1=b.k1 and b.k1 > 10 order by ak1 nulls last, bk1"
- qt_group31 "select count(*) from test where (k11='2015-03-13 12:36:38' or
k11 = '2000-01-01 00:00:00') and k5 is not null group by k1%2, k2%2, k3%3,
k4%3, k11%2 order by count(*)"
+ qt_group31 "select count(*) from cast_test where (k11='2015-03-13
12:36:38' or k11 = '2000-01-01 00:00:00') and k5 is not null group by k1%2,
k2%2, k3%3, k4%3, k11%2 order by count(*)"
test {
sql "select true + 1 + 'x'"
@@ -197,16 +197,16 @@ suite("cast") {
qt_sql_test_DecimalV3_mode """select cast(1 as DECIMALV3(1, 0)) % 2.1;""";
// test cast to time
- qt_tinyint """select cast(k1 as time) ct from test order by ct;"""
- qt_smallint """select cast(k2 as time) ct from test order by ct;"""
- qt_int """select cast(k3 as time) ct from test order by ct;"""
- qt_bigint """select cast(k4 as time) ct from test order by ct;"""
- qt_largeint """select cast(k13 as time) ct from test order by ct;"""
- qt_float """select cast(k9 as time) ct from test order by ct;"""
- qt_double """select cast(k8 as time) ct from test order by ct;"""
- qt_char """select cast(k6 as time) ct from test order by ct;"""
- qt_varchar """select cast(k7 as time) ct from test order by ct;"""
- qt_string """select cast(k12 as time) ct from test order by ct;"""
+ qt_tinyint """select cast(k1 as time) ct from cast_test order by ct;"""
+ qt_smallint """select cast(k2 as time) ct from cast_test order by ct;"""
+ qt_int """select cast(k3 as time) ct from cast_test order by ct;"""
+ qt_bigint """select cast(k4 as time) ct from cast_test order by ct;"""
+ qt_largeint """select cast(k13 as time) ct from cast_test order by ct;"""
+ qt_float """select cast(k9 as time) ct from cast_test order by ct;"""
+ qt_double """select cast(k8 as time) ct from cast_test order by ct;"""
+ qt_char """select cast(k6 as time) ct from cast_test order by ct;"""
+ qt_varchar """select cast(k7 as time) ct from cast_test order by ct;"""
+ qt_string """select cast(k12 as time) ct from cast_test order by ct;"""
qt_tinyint """select cast(cast(1 as tinyint) as time)"""
qt_smallint """select cast(cast(1 as smallint) as time)"""
@@ -225,12 +225,12 @@ suite("cast") {
// boolean
test {
- sql """select cast(k0 as time) ct from test order by ct;"""
+ sql """select cast(k0 as time) ct from cast_test order by ct;"""
exception "cannot cast"
}
// decimal
test {
- sql """select cast(k5 as time) ct from test order by ct;"""
+ sql """select cast(k5 as time) ct from cast_test order by ct;"""
exception "cannot cast"
}
test {
@@ -240,12 +240,12 @@ suite("cast") {
// date
test {
- sql """select cast(k10 as time) ct from test order by ct;"""
+ sql """select cast(k10 as time) ct from cast_test order by ct;"""
exception "cannot cast"
}
// datetime
test {
- sql """select cast(k11 as time) ct from test order by ct;"""
+ sql """select cast(k11 as time) ct from cast_test order by ct;"""
exception "cannot cast"
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
