xiedeyantu commented on code in PR #4699:
URL: https://github.com/apache/calcite/pull/4699#discussion_r2635443228
##########
core/src/test/resources/sql/blank.iq:
##########
@@ -154,4 +154,81 @@ select * from table1 where j not in (select i from table2)
or j = 3;
!ok
+# [CALCITE-4813] ANY_VALUE assumes that arguments should be comparable
+select any_value(r) over(), s from(select array[f, s] r, s from (select 1 as
f, 2 as s) t) t;
++--------+---+
+| EXPR$0 | S |
++--------+---+
+| [1, 2] | 2 |
++--------+---+
+(1 row)
+
+!ok
+
+select any_value(r) over(), s from(select map[f, s] r, s from (select 1 as f,
2 as s) t) t;
++--------+---+
+| EXPR$0 | S |
++--------+---+
+| {1=2} | 2 |
++--------+---+
+(1 row)
+
+!ok
+
+select any_value(r) over(), s from(select row(f, s) r, s from (select 1 as f,
2 as s) t) t;
++--------+---+
+| EXPR$0 | S |
++--------+---+
+| {1, 2} | 2 |
++--------+---+
+(1 row)
+
+!ok
+
+
+CREATE TABLE complex_t (
+ a INTEGER ARRAY,
+ m MAP<VARCHAR, DOUBLE>,
+ r ROW(r1 VARCHAR, r2 INTEGER, r3 VARCHAR)
+);
+(0 rows modified)
+
+!update
+
+INSERT INTO complex_t VALUES (
+ ARRAY[1, 2, 3, 4, 5],
+ MAP['math', 95.5, 'science', 88.0, 'english', 92.3],
+ ROW('Alice Johnson', 30, 'a')
+),
+(
+ ARRAY[10, 20, 30, 40, 50, 60],
+ MAP['physics', 96.2, 'chemistry', 91.8, 'biology', 89.5,
'computer_science', 98.7],
+ ROW('Bob Smith', 25, 'b')
+),
+(
+ ARRAY[100, 200, 300],
+ MAP['leadership', 88.9, 'teamwork', 94.2, 'communication', 91.5,
'problem_solving', 97.8],
+ ROW('Charlie Chen', 35, 'c')
+);
+(3 rows modified)
+
+!update
+
+select
Review Comment:
Also has verified in duckdb and works well.
```
CREATE TABLE complex_t (
a INTEGER[],
m MAP(VARCHAR, DOUBLE),
r STRUCT(
r1 VARCHAR,
r2 INTEGER,
r3 VARCHAR
)
);
INSERT INTO complex_t VALUES
([1,2,3,4,5],MAP{'math':95.5,'science':88.0,'english':92.3},ROW('Alice
Johnson',30,'a')),
([10,20,30,40,50,60],MAP{'physics':96.2,'chemistry':91.8,'biology':89.5,'computer_science':98.7},ROW('Bob
Smith',25,'b')),
([100,200,300],MAP{'leadership':88.9,'teamwork':94.2,'communication':91.5,'problem_solving':97.8},ROW('Charlie
Chen',35,'c'));
duckdb> SELECT max(a) AS max_a, max(m) AS max_m , max(r) AS max_r, min(a) AS
min_a , min(m) AS min_m, min(r) AS min_r FROM complex_t;
┌─────────────────┬────────────────────────────┬───────────────────────────────────────┬─────────────────┬──────────────────────────────┬──────────────────────────────────────┐
│ max_a ┆ max_m ┆ max_r
┆ min_a ┆ min_m ┆ min_r
│
╞═════════════════╪════════════════════════════╪═══════════════════════════════════════╪═════════════════╪══════════════════════════════╪══════════════════════════════════════╡
│ [100, 200, 300] ┆ {physics: 96.2, chemistry: ┆ {r1: Charlie Chen, r2: 35,
r3: c} ┆ [1, 2, 3, 4, 5] ┆ {leadership: 88.9, teamwork: ┆ {r1: Alice
Johnson, r2: 30, r3: a} │
│ ┆ 91.8, biology: 89.5, ┆
┆ ┆ 94.2, communication: 91.5, ┆
│
│ ┆ computer_science: 98.7} ┆
┆ ┆ problem_solving: 97.8} ┆
│
└─────────────────┴────────────────────────────┴───────────────────────────────────────┴─────────────────┴──────────────────────────────┴──────────────────────────────────────┘
```
--
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]
