Changeset: 2846d6d8fd48 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/2846d6d8fd48
Modified Files:
sql/test/nested/Tests/array_of_comp_with_array.test
sql/test/nested/Tests/bluesky.sql
Branch: nested
Log Message:
add example look up.
diffs (71 lines):
diff --git a/sql/test/nested/Tests/array_of_comp_with_array.test
b/sql/test/nested/Tests/array_of_comp_with_array.test
--- a/sql/test/nested/Tests/array_of_comp_with_array.test
+++ b/sql/test/nested/Tests/array_of_comp_with_array.test
@@ -102,26 +102,34 @@ insert into aca_post values (10, array[(
statement ok
insert into aca_post values (30, '{(300, {"Ca01", "Ca02"}), (310, {"Cb01",
"Cb02"})}'), (40, '{(400, {"Da01", "Da02"}), (410, {"Db01", "Db02"})}')
-## CRASH - values are the expected ones
-#query TI nosort outer-array-unnest
#select arr, k from aca_post, unnest(arr);
-#----
-#({"Aa01", "Aa02"}, 100)
-#10
-#({"Ab01", "Ab02"}, 110)
-#10
-#({"Ba01", "Ba02"}, 200)
-#20
-#({"Bb01", "Bb02"}, 210)
-#20
-#({"Ca01", "Ca02"}, 300)
-#30
-#({"Cb01", "Cb02"}, 310)
-#30
-#({"Da01", "Da02"}, 400)
-#40
-#({"Db01", "Db02"}, 410)
-#40
+query TII nosort outer-array-unnest
+select a.ms, a.n, k from aca_post, unnest(arr) a;
+----
+{"Aa01","Aa02"}
+100
+10
+{"Ab01","Ab02"}
+110
+10
+{"Ba01","Ba02"}
+200
+20
+{"Bb01","Bb02"}
+210
+20
+{"Ca01","Ca02"}
+300
+30
+{"Cb01","Cb02"}
+310
+30
+{"Da01","Da02"}
+400
+40
+{"Db01","Db02"}
+410
+40
query TII nosort full-unnest
select in_arr.elements, out_arr.n, k from aca_post, unnest(arr) as out_arr,
unnest(out_arr.ms) as in_arr
diff --git a/sql/test/nested/Tests/bluesky.sql
b/sql/test/nested/Tests/bluesky.sql
--- a/sql/test/nested/Tests/bluesky.sql
+++ b/sql/test/nested/Tests/bluesky.sql
@@ -28,8 +28,8 @@ SELECT data."commit"."collection" AS eve
-- Q2
SELECT data."commit"."collection" AS event, count(*) AS cnt, count(distinct
data.did) AS users FROM bluesky WHERE data.kind = 'commit' AND
data."commit"."operation" = 'create' GROUP BY event ORDER BY cnt DESC;
--- Q3 CRASH
---SELECT data."commit"."collection" AS event, "hour"(epoch(cast(data.time_us
as bigint)/(1000*1000))) as hour_of_day, count(*) AS cnt FROM bluesky WHERE
data.kind = 'commit' AND data."commit"."operation" = 'create' AND
data."commit"."collection" in ('app.bsky.feed.post', 'app.bsky.feed.repost',
'app.bsky.feed.like') GROUP BY event, hour_of_day ORDER BY hour_of_day, event;
+-- Q3
+SELECT data."commit"."collection" AS event, "hour"(epoch(cast(data.time_us as
bigint)/(1000*1000))) as hour_of_day, count(*) AS cnt FROM bluesky WHERE
data.kind = 'commit' AND data."commit"."operation" = 'create' AND
data."commit"."collection" in ('app.bsky.feed.post', 'app.bsky.feed.repost',
'app.bsky.feed.like') GROUP BY event, hour_of_day ORDER BY hour_of_day, event;
-- Q4
SELECT data.did as user_id, epoch(cast(min(data.time_us) as bigint)/1000000)
as first_post_ts FROM bluesky WHERE data.kind = 'commit' AND
data."commit"."operation" = 'create' AND data."commit"."collection" =
'app.bsky.feed.post' GROUP BY user_id ORDER BY first_post_ts ASC LIMIT 3;
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
