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
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
|
# name: test/optimizer/join_reorder_optimizer.test
# description: Make sure we can emit a vaild join order by DPhyp if hypergraph is connected
# group: [optimizer]
statement ok
CREATE TABLE t1(c1 int, c2 int, c3 int, c4 int);
statement ok
INSERT INTO t1 VALUES (1, 1, 1, 1);
statement ok
INSERT INTO t1 VALUES (1, 1, 1, 1);
statement ok
CREATE TABLE t2 AS SELECT * FROM t1;
statement ok
INSERT INTO t2 VALUES (1, 1, 1, 1);
statement ok
CREATE TABLE t3 AS SELECT * FROM t2;
statement ok
INSERT INTO t2 VALUES (1, 1, 1, 1);
statement ok
CREATE TABLE t4 AS SELECT * FROM t3;
statement ok
INSERT INTO t2 VALUES (1, 1, 1, 1);
statement ok
PRAGMA debug_force_no_cross_product=true
statement ok
EXPLAIN
SELECT
COUNT(*)
FROM
t1, t2, t3, t4
WHERE
t1.c1 = t2.c1 AND
t2.c2 = t3.c2 AND
t3.c3 = t4.c3
statement ok
EXPLAIN
SELECT
COUNT(*)
FROM
t1, t2, t3, t4
WHERE
t1.c1 = t2.c1 AND
t2.c2 = t3.c2 AND
t3.c3 = t4.c3 AND
t4.c4 = t1.c4
statement ok
EXPLAIN
SELECT
COUNT(*)
FROM
t1, t2, t3, t4
WHERE
t1.c1 = t2.c1 AND
t2.c2 = t3.c2 AND
t1.c1 + t2.c2 + t3.c3= 3 * t4.c4
statement ok
PRAGMA debug_force_no_cross_product=false
statement ok
with
grid as (
from (values ('ABC'), ('DEF')) as v(data)
select
unnest(split(data, '')) as letter,
row_number() over () as row_id,
generate_subscripts(split(data, ''), 1) AS col_id,
),
search(row_i, col_i, letter_to_match) as (
values (0, 0, 'A'), (0, 1, 'B'),
)
from (from grid cross join search) as grid_searches
select exists(
from grid as grid_to_search
where 1=1
and grid_searches.row_id = grid_to_search.row_id + grid_searches.row_i
and grid_searches.col_id = grid_to_search.col_id + grid_searches.col_i
and grid_searches.letter_to_match = grid_to_search.letter
)
statement ok
with
grid as (
from (values ('ABC', 39), ('DEF', 50)) as v(data, row_id)
select
unnest(split(data, '')) as letter,
row_id,
generate_subscripts(split(data, ''), 1) AS col_id,
),
search(row_i, col_i, letter_to_match) as (
values (0, 0, 'A'), (0, 1, 'B'),
)
from (from grid cross join search) as grid_searches
select exists(
from grid as grid_to_search
where 1=1
and grid_searches.row_id = grid_to_search.row_id + grid_searches.row_i
and grid_searches.col_id = grid_to_search.col_id + grid_searches.col_i
and grid_searches.letter_to_match = grid_to_search.letter
)
|
