vgi-matchrecognize/test/sql/streaming.test at main · Query-farm/vgi-matchrecognize · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
# name: test/sql/streaming.test
# description: The per-partition streaming producer — empty results, batch coalescing, match numbering.
# group: [sql]

require-env VGI_MATCHRECOGNIZE_WORKER

statement ok
ATTACH 'mr' AS mr (TYPE vgi, LOCATION '${VGI_MATCHRECOGNIZE_WORKER}');

# An empty input relation streams zero batches, not one empty one.
query I
SELECT count(*) FROM mr.main.match_recognize(
       (SELECT 1 AS uid, 1 AS ts WHERE false),
       partition_by := ['uid'],
       order_by     := ['ts'],
       pattern      := 'A+',
       define       := '{"A":"ts >= 0"}',
       measures     := '{"n":"COUNT(*)"}');
----
0

# Rows present but no match: also an empty result.
query I
SELECT count(*) FROM mr.main.match_recognize(
       (SELECT 1 AS uid, i AS ts FROM range(100) t(i)),
       partition_by := ['uid'],
       order_by     := ['ts'],
       pattern      := 'A+',
       define       := '{"A":"ts < 0"}',
       measures     := '{"n":"COUNT(*)"}');
----
0

# Many empty partitions before a matching one: the producer must keep scanning
# rather than stopping at the first partition that yields nothing.
query III
SELECT count(*), min(uid), max(uid) FROM mr.main.match_recognize(
       (SELECT i AS uid, 1 AS ts FROM range(1000) t(i)),
       partition_by := ['uid'],
       order_by     := ['ts'],
       pattern      := 'A',
       define       := '{"A":"uid = 999"}',
       measures     := '{"n":"COUNT(*)"}');
----
1	999	999

# MATCH_NUMBER counts matches WITHIN a partition, so it restarts at 1 for each
# one (SQL:2016) — and batching several partitions into one output batch must not
# change that.
query II
SELECT uid, mn FROM mr.main.match_recognize(
       (SELECT * FROM (VALUES (1,1),(1,2),(2,1),(2,2),(3,1)) AS t(uid, ts)),
       partition_by := ['uid'],
       order_by     := ['ts'],
       pattern      := 'A',
       define       := '{"A":"ts >= 0"}',
       measures     := '{"mn":"MATCH_NUMBER()"}')
ORDER BY uid, mn;
----
1	1
1	2
2	1
2	2
3	1

# Many partitions crossing the batch-coalescing threshold: every match survives
# and the count is exact (100k partitions of 3 rows -> 100k matches).
query II
SELECT count(*), sum(n) FROM mr.main.match_recognize(
       (SELECT (i//3)::BIGINT AS uid, i AS ts FROM range(300000) t(i)),
       partition_by := ['uid'],
       order_by     := ['ts'],
       pattern      := 'A+',
       define       := '{"A":"ts >= 0"}',
       measures     := '{"n":"COUNT(*)"}');
----
100000	300000

# A single partition far larger than the batch target under ALL ROWS PER MATCH:
# one match's rows may overshoot a batch and must not be split or duplicated.
query III
SELECT count(*), min(rn), max(rn) FROM mr.main.match_recognize(
       (SELECT 1 AS uid, i AS ts FROM range(50000) t(i)),
       partition_by := ['uid'],
       order_by     := ['ts'],
       pattern      := 'A+',
       define       := '{"A":"ts >= 0"}',
       measures     := '{"rn":"RUNNING COUNT(*)"}',
       rows         := 'all',
       step_budget  := 50000000);
----
50000	1	50000