cboumalh commented on code in PR #52883:
URL: https://github.com/apache/spark/pull/52883#discussion_r2713314243
##########
sql/core/src/test/resources/sql-tests/inputs/tuplesketch.sql:
##########
@@ -0,0 +1,991 @@
+-- Positive test cases
+-- Create tables with key-value pairs for tuple sketches
+
+-- Integer key with double values
+DROP TABLE IF EXISTS t_int_double_1_5_through_7_11;
+CREATE TABLE t_int_double_1_5_through_7_11 AS
+VALUES
+ (1, 1.0D, 5, 5.0D), (2, 2.0D, 6, 6.0D), (3, 3.0D, 7, 7.0D),
+ (4, 4.0D, 8, 8.0D), (5, 5.0D, 9, 9.0D), (6, 6.0D, 10, 10.0D),
+ (7, 7.0D, 11, 11.0D) AS tab(key1, val1, key2, val2);
+
+-- Long key with double values
+DROP TABLE IF EXISTS t_long_double_1_5_through_7_11;
+CREATE TABLE t_long_double_1_5_through_7_11 AS
+VALUES
+ (1L, 1.0D, 5L, 5.00D), (2L, 2.00D, 6L, 6.00D), (3L, 3.00D, 7L, 7.00D),
+ (4L, 4.0D, 8L, 8.00D), (5L, 5.00D, 9L, 9.00D), (6L, 6.00D, 10L, 10.00D),
+ (7L, 7.0D, 11L, 11.00D) AS tab(key1, val1, key2, val2);
+
+-- Double key with double values
+DROP TABLE IF EXISTS t_double_double_1_1_1_4_through_1_5_1_8;
+CREATE TABLE t_double_double_1_1_1_4_through_1_5_1_8 AS
+SELECT CAST(key1 AS DOUBLE) AS key1, CAST(val1 AS DOUBLE) AS val1,
+ CAST(key2 AS DOUBLE) AS key2, CAST(val2 AS DOUBLE) AS val2
+FROM VALUES
+ (1.1, 1.0, 1.4, 4.0), (1.2, 2.0, 1.5, 5.0), (1.3, 3.0, 1.6, 6.0),
+ (1.4, 4.0, 1.7, 7.0), (1.5, 5.0, 1.8, 8.0) AS tab(key1, val1, key2, val2);
+
+-- Float key with double values
+DROP TABLE IF EXISTS t_float_double_1_1_1_4_through_1_5_1_8;
+CREATE TABLE t_float_double_1_1_1_4_through_1_5_1_8 AS
+SELECT CAST(key1 AS FLOAT) key1, CAST(val1 AS DOUBLE) AS val1,
+ CAST(key2 AS FLOAT) key2, CAST(val2 AS DOUBLE) AS val2
+FROM VALUES
+ (1.1, 1.0, 1.4, 4.0), (1.2, 2.0, 1.5, 5.0), (1.3, 3.0, 1.6, 6.0),
+ (1.4, 4.0, 1.7, 7.0), (1.5, 5.0, 1.8, 8.0) AS tab(key1, val1, key2, val2);
+
+-- String key with double values
+DROP TABLE IF EXISTS t_string_double_a_d_through_e_h;
+CREATE TABLE t_string_double_a_d_through_e_h AS
+VALUES
+ ('a', 1.00D, 'd', 4.00D), ('b', 2.00D, 'e', 5.00D), ('c', 3.00D, 'f', 6.00D),
+ ('d', 4.00D, 'g', 7.00D), ('e', 5.00D, 'h', 8.00D) AS tab(key1, val1, key2,
val2);
+
+-- Binary key with double values
+DROP TABLE IF EXISTS t_binary_double_a_b_through_e_f;
+CREATE TABLE t_binary_double_a_b_through_e_f AS
+VALUES
+ (X'A', 1.00D, X'B', 2.00D), (X'B', 2.00D, X'C', 3.00D), (X'C', 3.00D, X'D',
4.00D),
+ (X'D', 4.00D, X'E', 5.00D), (X'E', 5.00D, X'F', 6.00D) AS tab(key1, val1,
key2, val2);
+
+-- Array Integer key with double values
+DROP TABLE IF EXISTS t_array_int_double_1_3_through_4_6;
+CREATE TABLE t_array_int_double_1_3_through_4_6 AS
+VALUES
+ (ARRAY(1), 1.00D, ARRAY(3), 3.00D),
+ (ARRAY(2), 2.00D, ARRAY(4), 4.00D),
+ (ARRAY(3), 3.00D, ARRAY(5), 5.00D),
+ (ARRAY(4), 4.00D, ARRAY(6), 6.00D) AS tab(key1, val1, key2, val2);
+
+-- Array Long key with double values
+DROP TABLE IF EXISTS t_array_long_double_1_3_through_4_6;
+CREATE TABLE t_array_long_double_1_3_through_4_6 AS
+VALUES
+ (ARRAY(1L), 1.00D, ARRAY(3L), 3.00D),
+ (ARRAY(2L), 2.00D, ARRAY(4L), 4.00D),
+ (ARRAY(3L), 3.00D, ARRAY(5L), 5.00D),
+ (ARRAY(4L), 4.00D, ARRAY(6L), 6.00D) AS tab(key1, val1, key2, val2);
+
+-- Integer key with integer values
+DROP TABLE IF EXISTS t_int_int_1_5_through_7_11;
+CREATE TABLE t_int_int_1_5_through_7_11 AS
+VALUES
+ (1, 1, 5, 5), (2, 2, 6, 6), (3, 3, 7, 7),
+ (4, 4, 8, 8), (5, 5, 9, 9), (6, 6, 10, 10),
+ (7, 7, 11, 11) AS tab(key1, val1, key2, val2);
+
+DROP TABLE IF EXISTS t_string_collation;
+CREATE TABLE t_string_collation AS
+VALUES
+ ('', 1.00D), (' ', 2.00D), (CAST(X'C1' AS STRING), 3.00D), (CAST(X'80' AS
STRING), 4.00D),
+ ('\uFFFD', 5.00D), ('Å', 6.00D), ('å', 7.00D), ('a\u030A', 8.00D), ('Å ',
9.00D), ('å ', 10.00D),
+ ('a\u030A ', 11.00D) AS tab(key1, val1);
+
+-- Test basic tuple_sketch_agg_double with IntegerType key and double summary
from table
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1, val1)) AS
result
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_integer with IntegerType key and integer summary from
table
+SELECT tuple_sketch_estimate_integer(tuple_sketch_agg_integer(key1, val1, 12))
AS result
+FROM t_int_int_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_double with ArrayType(IntegerType) key and double
summary from table
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1, val1))
+FROM t_array_int_double_1_3_through_4_6;
+
+-- Test tuple_sketch_agg_double with ArrayType(LongType) key and double
summary from table
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key2, val2))
+FROM t_array_long_double_1_3_through_4_6;
+
+-- Test tuple_sketch_agg_double with BinaryType key and double summary from
table
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1, val1))
+FROM t_binary_double_a_b_through_e_f;
+
+-- Test tuple_sketch_agg_double with DoubleType key and double summary from
table
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1, val1))
+FROM t_double_double_1_1_1_4_through_1_5_1_8;
+
+-- Test tuple_sketch_agg_double with FloatType key and double summary from
table
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key2, val2))
+FROM t_float_double_1_1_1_4_through_1_5_1_8;
+
+-- Test tuple_sketch_agg_double with IntegerType key and explicit lgNomEntries
parameter
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1, val1, 22))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_double with LongType key and double summary
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1, val1))
+FROM t_long_double_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_double with StringType key and double summary
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1, val1))
+FROM t_string_double_a_d_through_e_h;
+
+-- Test tuple_sketch_agg_double with explicit lgNomEntries parameter
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1, val1, 12))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_integer with explicit lgNomEntries parameter
+SELECT tuple_sketch_estimate_integer(tuple_sketch_agg_integer(key1, val1, 12))
+FROM t_int_int_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_double with all parameters including mode - sum
(default)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1, val1, 12,
'sum'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_double with mode - min
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1, val1, 12,
'min'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_double with mode - max
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1, val1, 12,
'max'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_double with mode - alwaysone
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1, val1, 12,
'alwaysone'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_summary_double to aggregate summary values - sum mode
+SELECT tuple_sketch_summary_double(tuple_sketch_agg_double(key1, val1, 12,
'sum'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_summary_double to aggregate summary values - min mode
+SELECT tuple_sketch_summary_double(tuple_sketch_agg_double(key1, val1, 12,
'min'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_summary_double to aggregate summary values - max mode
+SELECT tuple_sketch_summary_double(tuple_sketch_agg_double(key1, val1, 12,
'max'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_summary_double to aggregate summary values - alwaysone
mode
+SELECT tuple_sketch_summary_double(tuple_sketch_agg_double(key1, val1, 12,
'alwaysone'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_summary_integer with integer summary type
+SELECT tuple_sketch_summary_integer(tuple_sketch_agg_integer(key1, val1, 12,
'sum'), 'sum')
+FROM t_int_int_1_5_through_7_11;
+
+-- Test tuple_sketch_theta_double to get theta value from double sketch
+SELECT tuple_sketch_theta_double(tuple_sketch_agg_double(key1, val1))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_theta_double with LongType key
+SELECT tuple_sketch_theta_double(tuple_sketch_agg_double(key1, val1))
+FROM t_long_double_1_5_through_7_11;
+
+-- Test tuple_sketch_theta_double with StringType key
+SELECT tuple_sketch_theta_double(tuple_sketch_agg_double(key1, val1))
+FROM t_string_double_a_d_through_e_h;
+
+-- Test tuple_sketch_theta_integer to get theta value from integer sketch
+SELECT tuple_sketch_theta_integer(tuple_sketch_agg_integer(key1, val1, 12))
+FROM t_int_int_1_5_through_7_11;
+
+-- Test tuple_union_double function with IntegerType key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_union_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2)))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_union_double function with LongType key sketches and explicit
lgNomEntries parameter
+SELECT tuple_sketch_estimate_double(
+ tuple_union_double(
+ tuple_sketch_agg_double(key1, val1, 15),
+ tuple_sketch_agg_double(key2, val2), 15))
+FROM t_long_double_1_5_through_7_11;
+
+-- Test tuple_union_double function with DoubleType key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_union_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2)))
+FROM t_double_double_1_1_1_4_through_1_5_1_8;
+
+-- Test tuple_union_double function with FloatType key sketches and explicit
lgNomEntries parameter
+SELECT tuple_sketch_estimate_double(
+ tuple_union_double(
+ tuple_sketch_agg_double(key1, val1, 6),
+ tuple_sketch_agg_double(key2, val2, 15), 15))
+FROM t_float_double_1_1_1_4_through_1_5_1_8;
+
+-- Test tuple_union_double function with StringType key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_union_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2)))
+FROM t_string_double_a_d_through_e_h;
+
+-- Test tuple_union_double function with BinaryType key sketches and explicit
lgNomEntries parameter
+SELECT tuple_sketch_estimate_double(
+ tuple_union_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2, 20), 20))
+FROM t_binary_double_a_b_through_e_f;
+
+-- Test tuple_union_double function with ArrayType(IntegerType) key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_union_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2)))
+FROM t_array_int_double_1_3_through_4_6;
+
+-- Test tuple_union_double function with ArrayType(LongType) key sketches and
explicit lgNomEntries parameter
+SELECT tuple_sketch_estimate_double(
+ tuple_union_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2, 13), 13))
+FROM t_array_long_double_1_3_through_4_6;
+
+-- Test tuple_union_double with lgNomEntries and mode parameters
+SELECT tuple_sketch_estimate_double(
+ tuple_union_double(
+ tuple_sketch_agg_double(key1, val1, 12, 'sum'),
+ tuple_sketch_agg_double(key2, val2, 12, 'sum'), 12, 'sum'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_union_theta_double to merge tuple sketch with theta sketch
+SELECT tuple_sketch_estimate_double(
+ tuple_union_theta_double(
+ tuple_sketch_agg_double(key1, val1),
+ theta_sketch_agg(key2)))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_union_theta_double with explicit parameters
+SELECT tuple_sketch_estimate_double(
+ tuple_union_theta_double(
+ tuple_sketch_agg_double(key1, val1, 12, 'sum'),
+ theta_sketch_agg(key2, 12), 12, 'sum'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_intersection_double function with IntegerType key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_intersection_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2)))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_intersection_double function with LongType key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_intersection_double(
+ tuple_sketch_agg_double(key1, val1, 5),
+ tuple_sketch_agg_double(key2, val2, 12)))
+FROM t_long_double_1_5_through_7_11;
+
+-- Test tuple_intersection_double function with DoubleType key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_intersection_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2)))
+FROM t_double_double_1_1_1_4_through_1_5_1_8;
+
+-- Test tuple_intersection_double function with FloatType key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_intersection_double(
+ tuple_sketch_agg_double(key1, val1, 5),
+ tuple_sketch_agg_double(key2, val2)))
+FROM t_float_double_1_1_1_4_through_1_5_1_8;
+
+-- Test tuple_intersection_double function with StringType key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_intersection_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2)))
+FROM t_string_double_a_d_through_e_h;
+
+-- Test tuple_intersection_double function with BinaryType key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_intersection_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2, 22)))
+FROM t_binary_double_a_b_through_e_f;
+
+-- Test tuple_intersection_double function with ArrayType(IntegerType) key
sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_intersection_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2)))
+FROM t_array_int_double_1_3_through_4_6;
+
+-- Test tuple_intersection_double function with ArrayType(LongType) key
sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_intersection_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2, 10)))
+FROM t_array_long_double_1_3_through_4_6;
+
+-- Test tuple_intersection_double with mode parameter
+SELECT tuple_sketch_estimate_double(
+ tuple_intersection_double(
+ tuple_sketch_agg_double(key1, val1, 12, 'min'),
+ tuple_sketch_agg_double(key2, val2, 12, 'min'), 'min'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_intersection_theta_double to intersect tuple sketch with theta
sketch
+SELECT tuple_sketch_estimate_double(
+ tuple_intersection_theta_double(
+ tuple_sketch_agg_double(key1, val1),
+ theta_sketch_agg(key2)))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_intersection_theta_double with explicit parameters
+SELECT tuple_sketch_estimate_double(
+ tuple_intersection_theta_double(
+ tuple_sketch_agg_double(key1, val1, 12, 'sum'),
+ theta_sketch_agg(key2, 12), 'sum'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_difference_double function with IntegerType key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_difference_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2)))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_difference_double function with LongType key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_difference_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2, 5)))
+FROM t_long_double_1_5_through_7_11;
+
+-- Test tuple_difference_double function with DoubleType key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_difference_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2)))
+FROM t_double_double_1_1_1_4_through_1_5_1_8;
+
+-- Test tuple_difference_double function with FloatType key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_difference_double(
+ tuple_sketch_agg_double(key1, val1, 12),
+ tuple_sketch_agg_double(key2, val2)))
+FROM t_float_double_1_1_1_4_through_1_5_1_8;
+
+-- Test tuple_difference_double function with StringType key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_difference_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2)))
+FROM t_string_double_a_d_through_e_h;
+
+-- Test tuple_difference_double function with BinaryType key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_difference_double(
+ tuple_sketch_agg_double(key1, val1, 6),
+ tuple_sketch_agg_double(key2, val2, 8)))
+FROM t_binary_double_a_b_through_e_f;
+
+-- Test tuple_difference_double function with ArrayType(IntegerType) key
sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_difference_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2)))
+FROM t_array_int_double_1_3_through_4_6;
+
+-- Test tuple_difference_double function with ArrayType(LongType) key sketches
+SELECT tuple_sketch_estimate_double(
+ tuple_difference_double(
+ tuple_sketch_agg_double(key1, val1),
+ tuple_sketch_agg_double(key2, val2, 4)))
+FROM t_array_long_double_1_3_through_4_6;
+
+-- Test tuple_difference_integer with integer summary type
+SELECT tuple_sketch_estimate_integer(
+ tuple_difference_integer(
+ tuple_sketch_agg_integer(key1, val1, 12, 'sum'),
+ tuple_sketch_agg_integer(key2, val2, 12, 'sum')))
+FROM t_int_int_1_5_through_7_11;
+
+-- Test tuple_difference_theta_double to compute A-NOT-B with theta sketch
+SELECT tuple_sketch_estimate_double(
+ tuple_difference_theta_double(
+ tuple_sketch_agg_double(key1, val1),
+ theta_sketch_agg(key2)))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_difference_theta_double with explicit parameters
+SELECT tuple_sketch_estimate_double(
+ tuple_difference_theta_double(
+ tuple_sketch_agg_double(key1, val1, 12, 'sum'),
+ theta_sketch_agg(key2, 12)))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_union_agg_double with IntegerType key and explicit lgNomEntries
parameter
+SELECT tuple_sketch_estimate_double(tuple_union_agg_double(sketch, 15, 'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_int_double_1_5_through_7_11
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2, 20) as sketch FROM
t_int_double_1_5_through_7_11);
+
+-- Test tuple_union_agg_double with DoubleType key sketches and explicit
lgNomEntries parameter
+SELECT tuple_sketch_estimate_double(tuple_union_agg_double(sketch, 12, 'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_double_double_1_1_1_4_through_1_5_1_8
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_double_double_1_1_1_4_through_1_5_1_8);
+
+-- Test tuple_union_agg_double with StringType key sketches and explicit
lgNomEntries parameter
+SELECT tuple_sketch_estimate_double(tuple_union_agg_double(sketch, 14, 'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_string_double_a_d_through_e_h
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_string_double_a_d_through_e_h);
+
+-- Test tuple_union_agg_double with LongType key sketches and explicit
lgNomEntries parameter
+SELECT tuple_sketch_estimate_double(tuple_union_agg_double(sketch, 10, 'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_long_double_1_5_through_7_11
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_long_double_1_5_through_7_11);
+
+-- Test tuple_union_agg_double with FloatType keysketches and explicit
lgNomEntries parameter
+SELECT tuple_sketch_estimate_double(tuple_union_agg_double(sketch, 6, 'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_float_double_1_1_1_4_through_1_5_1_8
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_float_double_1_1_1_4_through_1_5_1_8);
+
+-- Test tuple_union_agg_double with BinaryType key sketches
+SELECT tuple_sketch_estimate_double(tuple_union_agg_double(sketch, 12, 'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_binary_double_a_b_through_e_f
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_binary_double_a_b_through_e_f);
+
+-- Test tuple_union_agg_double with ArrayType(IntegerType) key sketches and
explicit lgNomEntries parameter
+SELECT tuple_sketch_estimate_double(tuple_union_agg_double(sketch, 12, 'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_array_int_double_1_3_through_4_6
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_array_int_double_1_3_through_4_6);
+
+-- Test tuple_union_agg_double with ArrayType(LongType) key sketches and
explicit lgNomEntries parameter
+SELECT tuple_sketch_estimate_double(tuple_union_agg_double(sketch, 16, 'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_array_long_double_1_3_through_4_6
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_array_long_double_1_3_through_4_6);
+
+-- Test tuple_union_agg_integer with integer summary type
+SELECT tuple_sketch_estimate_integer(tuple_union_agg_integer(sketch, 12,
'sum'))
+FROM (SELECT tuple_sketch_agg_integer(key1, val1, 12, 'sum') as sketch FROM
t_int_int_1_5_through_7_11
+ UNION ALL
+ SELECT tuple_sketch_agg_integer(key2, val2, 12, 'sum') as sketch FROM
t_int_int_1_5_through_7_11);
+
+-- Test tuple_intersection_agg_double with IntegerType key sketches
+SELECT tuple_sketch_estimate_double(tuple_intersection_agg_double(sketch,
'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_int_double_1_5_through_7_11
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_int_double_1_5_through_7_11);
+
+-- Test tuple_intersection_agg_double with LongType key sketches
+SELECT tuple_sketch_estimate_double(tuple_intersection_agg_double(sketch,
'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_long_double_1_5_through_7_11
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_long_double_1_5_through_7_11);
+
+-- Test tuple_intersection_agg_double with FloatType key sketches
+SELECT tuple_sketch_estimate_double(tuple_intersection_agg_double(sketch,
'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_float_double_1_1_1_4_through_1_5_1_8
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_float_double_1_1_1_4_through_1_5_1_8);
+
+-- Test tuple_intersection_agg_double with DoubleType key sketches
+SELECT tuple_sketch_estimate_double(tuple_intersection_agg_double(sketch,
'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_double_double_1_1_1_4_through_1_5_1_8
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_double_double_1_1_1_4_through_1_5_1_8);
+
+-- Test tuple_intersection_agg_double with StringType key sketches
+SELECT tuple_sketch_estimate_double(tuple_intersection_agg_double(sketch,
'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_string_double_a_d_through_e_h
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_string_double_a_d_through_e_h);
+
+-- Test tuple_intersection_agg_double with BinaryType key sketches
+SELECT tuple_sketch_estimate_double(tuple_intersection_agg_double(sketch,
'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_binary_double_a_b_through_e_f
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_binary_double_a_b_through_e_f);
+
+-- Test tuple_intersection_agg_double with ArrayType(IntegerType) key sketches
+SELECT tuple_sketch_estimate_double(tuple_intersection_agg_double(sketch,
'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_array_int_double_1_3_through_4_6
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_array_int_double_1_3_through_4_6);
+
+-- Test tuple_intersection_agg_double with ArrayType(LongType) key sketches
+SELECT tuple_sketch_estimate_double(tuple_intersection_agg_double(sketch,
'sum'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_array_long_double_1_3_through_4_6
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_array_long_double_1_3_through_4_6);
+
+-- Test tuple_intersection_agg_integer with integer summary type
+SELECT tuple_sketch_estimate_integer(tuple_intersection_agg_integer(sketch,
'sum'))
+FROM (SELECT tuple_sketch_agg_integer(key1, val1, 12, 'sum') as sketch FROM
t_int_int_1_5_through_7_11
+ UNION ALL
+ SELECT tuple_sketch_agg_integer(key2, val2, 12, 'sum') as sketch FROM
t_int_int_1_5_through_7_11);
+
+-- Test tuple_sketch_agg_double with IntegerType key and null values (nulls
should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (1, 1.0D), (null, 2.0D), (2, 2.0D), (null, 3.0D), (3, 3.0D)
tab(key, val);
+
+-- Test tuple_sketch_agg_double with StringType key and null values (nulls
should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES ('test', 1.0D), (null, 2.0D), ('null', 3.0D), (null, 4.0D)
tab(key, val);
+
+-- Test tuple_sketch_agg_double with StringType key and Float summary with
null values (nulls should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES ('test', 1.0F), (null, 2.0F), ('null', 3.0F), ('null', 4.0F)
tab(key, val);
+
+-- Test tuple_sketch_agg_double with LongType key yand null values (nulls
should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (100L, 1.0D), (null, 2.0D), (200L, 3.0D), (null, 4.0D), (300L,
5.0D) tab(key, val);
+
+-- Test tuple_sketch_agg_double with DoubleType key and null values (nulls
should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(CAST(key AS
DOUBLE), val))
+FROM VALUES (1.1, 1.0D), (null, 2.0D), (2.2, 3.0D), (null, 4.0D), (3.3, 5.0D)
tab(key, val);
+
+-- Test tuple_sketch_agg_double with FloatType key and null values (nulls
should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(CAST(key AS
FLOAT), val))
+FROM VALUES (1.5, 1.0D), (null, 2.0D), (2.5, 3.0D), (null, 4.0D), (3.5, 5.0D)
tab(key, val);
+
+-- Test tuple_sketch_agg_double with BinaryType key and null values (nulls
should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (X'AA', 1.0D), (null, 2.0D), (X'BB', 3.0D), (null, 4.0D), (X'CC',
5.0D) tab(key, val);
+
+-- Test tuple_sketch_agg_double with ArrayType(IntegerType) key and null
values (nulls should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (ARRAY(1, 2), 1.0D), (null, 2.0D), (ARRAY(3, 4), 3.0D), (null,
4.0D), (ARRAY(5, 6), 5.0D) tab(key, val);
+
+-- Test tuple_sketch_agg_double with ArrayType(LongType) key and null values
(nulls should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (ARRAY(10L, 20L), 1.0), (null, 2.0D), (ARRAY(30L, 40L), 3.0D),
(null, 4.0D), (ARRAY(50L, 60L), 5.0D) tab(key, val);
+
+-- Test tuple_sketch_agg_double with arrays containing null elements
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (ARRAY(1, null), 1.0), (ARRAY(1), 2.0D), (ARRAY(2, null, 3),
3.0D), (ARRAY(4), 4.0D) tab(key, val);
+
+-- Test tuple_sketch_agg_double with arrays containing null elements (LongType)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (ARRAY(10L, null), 1.0D), (ARRAY(10L), 2.0D), (ARRAY(20L, null,
30L), 3.0D), (ARRAY(40L), 4.0D) tab(key, val);
+
+-- Test tuple_sketch_agg_double with null summary values (nulls should be
ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (1, 1.0D), (2, null), (3, 3.0D), (4, null), (5, 5.0D) tab(key,
val);
+
+-- Test tuple_sketch_agg_double with StringType key and null summary values
(nulls should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES ('a', 1.0D), ('b', null), ('c', 3.0D), ('d', null) tab(key, val);
+
+-- Test tuple_sketch_agg_double with LongType key and null summary values
(nulls should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (100L, 1.0D), (200L, null), (300L, 3.0D), (400L, null), (500L,
5.0D) tab(key, val);
+
+-- Test tuple_sketch_agg_double with DoubleType key and null summary values
(nulls should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(CAST(key AS
DOUBLE), val))
+FROM VALUES (1.1, 1.0D), (2.2, null), (3.3, 3.0D), (4.4, null) tab(key, val);
+
+-- Test tuple_sketch_agg_double with FloatType key and null summary values
(nulls should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(CAST(key AS
FLOAT), val))
+FROM VALUES (1.5, 1.0D), (2.5, null), (3.5, 3.0D), (4.5, null) tab(key, val);
+
+-- Test tuple_sketch_agg_double with BinaryType key and null summary values
(nulls should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (X'AA', 1.0D), (X'BB', null), (X'CC', 3.0D), (X'DD', null)
tab(key, val);
+
+-- Test tuple_sketch_agg_double with ArrayType(IntegerType) key and null
summary values (nulls should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (ARRAY(1, 2), 1.0D), (ARRAY(3, 4), null), (ARRAY(5, 6), 3.0D),
(ARRAY(7, 8), null) tab(key, val);
+
+-- Test tuple_sketch_agg_double with ArrayType(LongType) key and null summary
values (nulls should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (ARRAY(10L, 20L), 1.0D), (ARRAY(30L, 40L), null), (ARRAY(50L,
60L), 3.0D) tab(key, val);
+
+-- Test tuple_sketch_agg_integer with null summary values (nulls should be
ignored)
+SELECT tuple_sketch_estimate_integer(tuple_sketch_agg_integer(key, val, 12))
+FROM VALUES (1, 1), (2, null), (3, 3), (4, null), (5, 5) tab(key, val);
+
+-- Test tuple_sketch_agg_double with both null keys and null summaries (all
nulls should be ignored)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (1, 1.0D), (null, 2.0D), (3, null), (null, null), (5, 5.0D)
tab(key, val);
+
+-- Test tuple_sketch_agg_double with all-null keys (should return null or
empty sketch)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (null, 1.0D), (null, 2.0D), (null, 3.0D) tab(key, val);
+
+-- Test tuple_sketch_agg_double with all-null summaries (should return null or
empty sketch)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (1, null), (2, null), (3, null) tab(key, val);
+
+-- Test tuple_sketch_agg_double with all-null keys and summaries (should
return null or empty sketch)
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (null, null), (null, null), (null, null) tab(key, val);
+
+-- Test tuple_sketch_agg_integer with all-null keys (should return null or
empty sketch)
+SELECT tuple_sketch_estimate_integer(tuple_sketch_agg_integer(key, val, 12))
+FROM VALUES (null, 1), (null, 2), (null, 3) tab(key, val);
+
+-- Test tuple_sketch_agg_integer with all-null summaries (should return null
or empty sketch)
+SELECT tuple_sketch_estimate_integer(tuple_sketch_agg_integer(key, val, 12))
+FROM VALUES (1, null), (2, null), (3, null) tab(key, val);
+
+-- Test tuple_sketch_agg_integer with all-null keys and summaries (should
return null or empty sketch)
+SELECT tuple_sketch_estimate_integer(tuple_sketch_agg_integer(key, val, 12))
+FROM VALUES (null, null), (null, null), (null, null) tab(key, val);
+
+-- Test tuple_sketch_summary_double with all-null summaries
+SELECT tuple_sketch_summary_double(tuple_sketch_agg_double(key, val, 12,
'sum'))
+FROM VALUES (1, null), (2, null), (3, null) tab(key, val);
+
+-- Test tuple_sketch_summary_integer with all-null summaries
+SELECT tuple_sketch_summary_integer(tuple_sketch_agg_integer(key, val, 12,
'sum'), 'sum')
+FROM VALUES (1, null), (2, null), (3, null) tab(key, val);
+
+-- Test tuple_sketch_agg_double with empty arrays
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (ARRAY(), 1.0D), (ARRAY(1, 2), 2.0D), (ARRAY(), 3.0D), (ARRAY(3,
4), 4.0D) tab(key, val);
+
+-- Test tuple_sketch_agg_double with empty strings
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES ('', 1.0D), ('a', 2.0D), ('', 3.0D), ('b', 4.0D), ('c', 5.0D)
tab(key, val);
+
+-- Test tuple_sketch_agg_double with empty binary data
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key, val))
+FROM VALUES (X'', 1.0D), (X'01', 2.0D), (X'02', 3.0D), (X'03', 4.0D), (CAST('
' AS BINARY), 5.0D), (X'e280', 6.0D), (X'c1', 7.0D), (X'c120', 8.0D) tab(key,
val);
+
+-- Test tuple_sketch_agg_double with collated string data
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1, val1))
utf8_b FROM t_string_collation;
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1 COLLATE
UTF8_LCASE, val1)) utf8_lc FROM t_string_collation;
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1 COLLATE
UNICODE, val1)) unicode FROM t_string_collation;
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1 COLLATE
UNICODE_CI, val1)) unicode_ci FROM t_string_collation;
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1 COLLATE
UTF8_BINARY_RTRIM, val1)) utf8_b_rt FROM t_string_collation;
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1 COLLATE
UTF8_LCASE_RTRIM, val1)) utf8_lc_rt FROM t_string_collation;
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1 COLLATE
UNICODE_RTRIM, val1)) unicode_rt FROM t_string_collation;
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key1 COLLATE
UNICODE_CI_RTRIM, val1)) unicode_ci_rt FROM t_string_collation;
+
+-- Comprehensive test using all TupleSketch functions in a single query
+WITH sketches AS (
+ SELECT 'int_sketch' as sketch_type, tuple_sketch_agg_double(key1, val1, 12,
'sum') as sketch
+ FROM t_int_double_1_5_through_7_11
+ UNION ALL
+ SELECT 'long_sketch' as sketch_type, tuple_sketch_agg_double(key1, val1, 15,
'sum') as sketch
+ FROM t_long_double_1_5_through_7_11
+ UNION ALL
+ SELECT 'double_sketch' as sketch_type, tuple_sketch_agg_double(key1, val1,
10, 'sum') as sketch
+ FROM t_double_double_1_1_1_4_through_1_5_1_8
+ UNION ALL
+ SELECT 'string_sketch' as sketch_type, tuple_sketch_agg_double(key1, val1,
14, 'sum') as sketch
+ FROM t_string_double_a_d_through_e_h
+),
+union_result AS (
+ SELECT tuple_union_agg_double(sketch, 16, 'sum') as union_sketch FROM
sketches
+),
+individual_sketches AS (
+ SELECT
+ tuple_sketch_agg_double(key1, val1, 12, 'sum') as sketch1,
+ tuple_sketch_agg_double(key2, val2, 12, 'sum') as sketch2
+ FROM t_int_double_1_5_through_7_11
+)
+SELECT
+ -- Basic estimate from union of all sketches
+ tuple_sketch_estimate_double((SELECT union_sketch FROM union_result)) as
union_estimate,
+ -- Summary aggregation from union
+ tuple_sketch_summary_double((SELECT union_sketch FROM union_result), 'sum')
as union_summary,
+ -- Union of two individual sketches
+ tuple_sketch_estimate_double(tuple_union_double(sketch1, sketch2, 15,
'sum')) as binary_union_estimate,
+ -- Intersection of two individual sketches
+ tuple_sketch_estimate_double(tuple_intersection_double(sketch1, sketch2,
'sum')) as intersection_estimate,
+ -- Difference of two individual sketches
+ tuple_sketch_estimate_double(tuple_difference_double(sketch1, sketch2)) as
difference_estimate
+FROM individual_sketches;
+
+-- Named parameter tests for tuple sketch functions
+
+-- Test tuple_sketch_agg_double with named parameters - only required params
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key => key1,
summary => val1))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_double with named parameters - setting only
lgNomEntries
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key => key1,
summary => val1, lgNomEntries => 14))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_double with named parameters - setting only mode
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key => key1,
summary => val1, mode => 'max'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_double with named parameters - setting both
lgNomEntries and mode
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(key => key1,
summary => val1, lgNomEntries => 10, mode => 'min'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_double with named parameters - different order
+SELECT tuple_sketch_estimate_double(tuple_sketch_agg_double(mode => 'max',
lgNomEntries => 15, summary => val1, key => key1))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_integer with named parameters - only required params
+SELECT tuple_sketch_estimate_integer(tuple_sketch_agg_integer(key => key1,
summary => val1))
+FROM t_int_int_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_integer with named parameters - setting only mode
+SELECT tuple_sketch_estimate_integer(tuple_sketch_agg_integer(key => key1,
summary => val1, mode => 'max'))
+FROM t_int_int_1_5_through_7_11;
+
+-- Test tuple_sketch_agg_integer with named parameters - different order
+SELECT tuple_sketch_estimate_integer(tuple_sketch_agg_integer(lgNomEntries =>
14, key => key1, mode => 'sum', summary => val1))
+FROM t_int_int_1_5_through_7_11;
+
+-- Test tuple_union_agg_double with named parameters - only required param
+SELECT tuple_sketch_estimate_double(tuple_union_agg_double(child => sketch))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_int_double_1_5_through_7_11
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_int_double_1_5_through_7_11);
+
+-- Test tuple_union_agg_double with named parameters - setting only
lgNomEntries
+SELECT tuple_sketch_estimate_double(tuple_union_agg_double(child => sketch,
lgNomEntries => 14))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_int_double_1_5_through_7_11
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_int_double_1_5_through_7_11);
+
+-- Test tuple_union_agg_double with named parameters - setting only mode
+SELECT tuple_sketch_estimate_double(tuple_union_agg_double(child => sketch,
mode => 'max'))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_int_double_1_5_through_7_11
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_int_double_1_5_through_7_11);
+
+-- Test tuple_union_agg_double with named parameters - different order
+SELECT tuple_sketch_estimate_double(tuple_union_agg_double(mode => 'min',
lgNomEntries => 13, child => sketch))
+FROM (SELECT tuple_sketch_agg_double(key1, val1) as sketch FROM
t_int_double_1_5_through_7_11
+ UNION ALL
+ SELECT tuple_sketch_agg_double(key2, val2) as sketch FROM
t_int_double_1_5_through_7_11);
+
+-- Test tuple_union_agg_integer with named parameters - setting only mode
+SELECT tuple_sketch_estimate_integer(tuple_union_agg_integer(child => sketch,
mode => 'max'))
+FROM (SELECT tuple_sketch_agg_integer(key1, val1, 12, 'sum') as sketch FROM
t_int_int_1_5_through_7_11
+ UNION ALL
+ SELECT tuple_sketch_agg_integer(key2, val2, 12, 'sum') as sketch FROM
t_int_int_1_5_through_7_11);
+
+-- Test tuple_union_double with named parameters - only required params
+SELECT tuple_sketch_estimate_double(
+ tuple_union_double(
+ first => tuple_sketch_agg_double(key1, val1),
+ second => tuple_sketch_agg_double(key2, val2)))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_union_double with named parameters - setting only lgNomEntries
+SELECT tuple_sketch_estimate_double(
+ tuple_union_double(
+ first => tuple_sketch_agg_double(key1, val1),
+ second => tuple_sketch_agg_double(key2, val2),
+ lgNomEntries => 14))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_union_double with named parameters - setting only mode
+SELECT tuple_sketch_estimate_double(
+ tuple_union_double(
+ first => tuple_sketch_agg_double(key1, val1),
+ second => tuple_sketch_agg_double(key2, val2),
+ mode => 'max'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_union_double with named parameters - different order
+SELECT tuple_sketch_estimate_double(
+ tuple_union_double(
+ mode => 'min',
+ lgNomEntries => 15,
+ second => tuple_sketch_agg_double(key2, val2),
+ first => tuple_sketch_agg_double(key1, val1)))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_union_theta_double with named parameters - setting only mode
+SELECT tuple_sketch_estimate_double(
+ tuple_union_theta_double(
+ first => tuple_sketch_agg_double(key1, val1),
+ second => theta_sketch_agg(key2),
+ mode => 'max'))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_union_theta_double with named parameters - different order
+SELECT tuple_sketch_estimate_double(
+ tuple_union_theta_double(
+ mode => 'min',
+ second => theta_sketch_agg(key2),
+ lgNomEntries => 14,
+ first => tuple_sketch_agg_double(key1, val1)))
+FROM t_int_double_1_5_through_7_11;
+
+-- Test tuple_union_integer with named parameters - setting only lgNomEntries
+SELECT tuple_sketch_estimate_integer(
+ tuple_union_integer(
+ first => tuple_sketch_agg_integer(key1, val1),
+ second => tuple_sketch_agg_integer(key2, val2),
+ lgNomEntries => 14))
+FROM t_int_int_1_5_through_7_11;
+
+-- Test tuple_union_theta_integer with named parameters - different order
+SELECT tuple_sketch_estimate_integer(
+ tuple_union_theta_integer(
+ lgNomEntries => 13,
+ mode => 'max',
+ second => theta_sketch_agg(key2),
+ first => tuple_sketch_agg_integer(key1, val1)))
+FROM t_int_int_1_5_through_7_11;
+
+-- Negative test cases
Review Comment:
added some more tests from this, a good amount were already covered 👍
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
