-
Notifications
You must be signed in to change notification settings - Fork 3.8k
/
inverted_join_geospatial_dist
209 lines (179 loc) · 20.6 KB
/
inverted_join_geospatial_dist
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
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
# LogicTest: 5node-default-configs !5node-metadata
statement ok
CREATE TABLE ltable(
lk int primary key,
geom1 geometry,
geom2 geometry
)
statement ok
INSERT INTO ltable VALUES
(1, 'POINT(3.0 3.0)', 'POINT(3.0 3.0)'),
(2, 'POINT(4.5 4.5)', 'POINT(3.0 3.0)'),
(3, 'POINT(1.5 1.5)', 'POINT(3.0 3.0)')
statement ok
CREATE TABLE rtable(
rk int primary key,
geom geometry,
INVERTED INDEX geom_index(geom)
)
statement ok
INSERT INTO rtable VALUES
(11, 'POINT(1.0 1.0)'),
(12, 'LINESTRING(1.0 1.0, 2.0 2.0)'),
(13, 'POINT(3.0 3.0)'),
(14, 'LINESTRING(4.0 4.0, 5.0 5.0)'),
(15, 'LINESTRING(40.0 40.0, 41.0 41.0)'),
(16, 'POLYGON((1.0 1.0, 5.0 1.0, 5.0 5.0, 1.0 5.0, 1.0 1.0))')
statement ok
ALTER TABLE ltable SPLIT AT VALUES (2), (3)
statement ok
ALTER TABLE ltable EXPERIMENTAL_RELOCATE VALUES (ARRAY[1], 1), (ARRAY[2], 2), (ARRAY[3], 3)
query TTTI colnames
SELECT start_key, end_key, replicas, lease_holder from [SHOW EXPERIMENTAL_RANGES FROM TABLE ltable] ORDER BY lease_holder
----
start_key end_key replicas lease_holder
NULL /2 {1} 1
/2 /3 {2} 2
/3 NULL {3} 3
query II
SELECT lk, rk FROM ltable JOIN rtable@geom_index ON ST_Intersects(ltable.geom1, rtable.geom) ORDER BY (lk, rk)
----
1 13
1 16
2 14
2 16
3 12
3 16
query T
SELECT url FROM [EXPLAIN (DISTSQL) SELECT lk, rk FROM ltable JOIN rtable@geom_index
ON ST_Intersects(ltable.geom1, rtable.geom) ORDER BY (lk, rk)]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzElF1v2jwYhs_fX2E9R0U1b3AC_chRtpVJqRjpoAebqqjKkkdd1mBntjN1qvjvUxI2CAUn7EOcYew79-VHl_wM6msGLszHk_GbW1LIjLydBe_I3fjDzeSVPyUnV_78dv5-0iOrI9kjJfKxPpXp6FOG5Drwp0RWv70HFIv7lCf4RIIpUfo-5Rqlwlirk_r4_-URRleBatUjwexqPCOvP5KT-vu9EChwkeA0WqAC9w4YULCBggMhhVyKGJUSstx6rg76yRO4Awopzwtd_h1SiIVEcJ9BpzpDcOG2bJxhlKC0BkAhQR2lWfX5ms3LZbqI5HegMM8jrlzSt8rSoNAu8Rj1bAiXFESh1yVKRw8ILlvS7iA-_4ZSY3ItUo7ScposL0YJ9Fdi_JTLrbF6NiXesNfApJ6zl9Q-hLQkXE3sbCflemITIR6LnHwRKSeCu8Qrr_VCgop21KDdj-ocgjoXUqO0LpuYHjulnn0KFBaRjj-TDLlL2N7G4d7GdZGQCUpMdveEyx1oU9EXucXsRmQfwqiBwLorzbopbdn9yriDpW5B2ZJ6eESpW0g3pD4_ttQtqCup2eAvWm13V8ruqJTT_x2hWkC2hBodUagW0g2hLo4tVAvqT6HYP3omd1TOUOWCK-z0-g3K9xOTB6zfXCUKGeONFHFVUy-DKlf9kaDS9S6rFz6vt0rAzTAzhm1z2DaGHXPYMYaHjTDbDg_Nd26pHhnTZ-bwmTF8bg6fG8MX5vDFn0zs0jyxQYsmLZK1WWbWjLV4xsyibd88XP73IwAA___909Eu
query T
SELECT url FROM [EXPLAIN (DISTSQL) SELECT lk, rk FROM ltable JOIN rtable@geom_index
ON ST_Intersects(rtable.geom, ltable.geom1) OR ST_DWithin(ltable.geom1, rtable.geom, 2) ORDER BY (lk, rk)]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzUVVFv2jwUff9-hXWfQDUFO9DSPKXfSiUqRjrgYVOFqoxcdVlTO7PN1qnqf5-coJVQsOn60r5h-x7uPcfnOA-gf-QQwnQwGnyYkaXKyfkk_kiuBp8vR6fDMWmcDaez6adRk6xK8ltK1G1VlZvka47kIh6OiSp_Rzco764zkeI9icdEm-tMGFQaF0Y3qpJDW0JX2HLBmiSe2Nr0V2a-ZaKxfkZJDcZt7dlgQv7_QhrVLM05UBAyxXFyhxrCK2BAgQOFAOYUCiUXqLVU9uihLBym9xB2KGSiWBq7PaewkAohfACTmRwhhJltOsEkRdXuAIUUTZLl5d9X40WFyu4S9RsoTItE6JC02rZpvDQhiRiNOMwfKcileWqiTXKDELJHuv8gQ_ETlcH0QmYCVTuoz_JMdqB_EYP7Qm1cQcQpibqbele7lPDDThiG56P4dNZv1qjQKNjJhr-EzVQqg6p9VGcRsQMaBQc7WwQvaWGFWl3cyVaxni5uJOXtsiDfZSaIFCGJrLrPfBv1KIn4VtF6DtF2K9bdSeeJhVQpKky36MStTlt4j2VLFm3Ga5BdI_RqI7D9s8D2y0Kbt0qrvjgNnlE20tB942nwsFml4fg1afC0WEsD67yHOPD9vcj39GLQ-hcnegbZcGLvjTvRw2blxP5rnOhpse5E9h6c6PnOTFAXUmjc673t2Bcb0xusXnktl2qBl0ouyjbVMi5x5UaK2lSnrFoMRXVkB1wHMyeYu8HcCQ7c4MAJ7tbAbBPcdXP2tO450Udu8JETfOwGHzvBfTe4_xrFTtyKdTw28ZjM5zK3zZjHZ8xttE3m88f__gQAAP__5mgOKA==
query T
SELECT url FROM [EXPLAIN (DISTSQL) SELECT lk, rk FROM ltable JOIN rtable@geom_index
ON ST_Intersects(ltable.geom1, rtable.geom) AND ST_DWithin(rtable.geom, ltable.geom1, 2) ORDER BY (lk, rk)]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzUVV1vGjsQfb-_wponUEzAXkiInzb3hkhEXDYFHlpFKNqyo2Sbxd7apk0V8d-r3aUNy4eX9ENK3rBnDuec4Yx5AvM5AQHj3qD334QsdEIuR8H_5Kb3_npw3h-S2kV_PBm_G9TJqiV5oEQ_FF2JDT8mSK6C_pDo_LN_h2p-G8sIH0kwJMbextKiNjizpla0H2ctjK4A-alOzocXWXP0Nbb3sayt1Sgpw3idBKOL3oj8-4HUCjH1KVCQKsJhOEcD4gYYUOBAwYMphVSrGRqjdFZ6yhv70SOIFoVYpgubXU8pzJRGEE9gY5sgCJhkrCMMI9TNFlCI0IZxkn99ochPdTwP9TegME5DaQRpNDPSYGEF8Rn1OUyXFNTCPpMYG94hCLakhwvpyy-oLUZXKpaom15Zy9bcgf5E9B5TvfEb-JwSv7018OKaEn7cEkJcDoLzSbde8kJ9b68d_hI7mY3VWE92Wnke60Cph0VKPqlYEiUF8TPvW7HKtXe2LXUoyUp7Le33473Ez1hpi7p5VvbisyPq8yOgMA_t7J4kKAVhexnbexmfiZSOUGO0m2e63CFtqBoqbTJeguyT0ClJYIcvBztsOZq8kWf3xetRIWVjPdqvfT0q7Kytx-mbWI8KP6v1YK0_uB_88HDyA8PpNX4lmhVCNqLZee3RrLCzFs3um4hmhZ8f0WR_6eneQTlCkypp8KAXuZW96RjdYfE_YNRCz_Baq1lOUxyDHJdfRGhsUWXFoS-LUiZwHcycYO4GcyfYc4M9J7hdArNNcNvtuYK640SfuMEnTvCpG3zqBHfd4O7vTOzMPbFWRUwqQlaVMnfMWEXOmDtom86ny3--BwAA__-jCRy4
query T
SELECT url FROM [EXPLAIN (DISTSQL)
SELECT lk, rk FROM ltable JOIN rtable@geom_index
ON ST_Intersects(ltable.geom1, rtable.geom) AND ST_Covers(ltable.geom2, rtable.geom)
AND (ST_DFullyWithin(rtable.geom, ltable.geom1, 100) OR ST_Intersects('POINT(1.0 1.0)', rtable.geom))]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzsVUFv2jAYve9XWN-lIBmIE6DUp1QrTKlo0gGHTRWqMuJ1WVM7s52uVcV_n5JUKwlgUk27wQnb7_l9ed_35BdQvxKgMB9Pxx8XKJMJmsyCK3Qz_nI9Pfd81Lrw5ov552kbvUKSe4zkfYlKdPgtYegy8Hwki__uHRMPtzGP2BMKfNRS-jbmmknFVlq1Snw3xxD8yihWbXTuXyClb1fikckK0K4CS2R-b_Q9S5Ln37H-EfPWBgSjqgyxrDYKZqhaysl14PmLFulaiHSt9klNZQkYuIiYHz4wBfQGCGCwAYMDSwypFCumlJD50UsB9KInoBaGmKeZzreXGFZCMqAvoGOdMKCwyAVmLIyY7FmAIWI6jJPi-rJkN5XxQyifAcM8DbmiqNOzYbnGIDL9dq_S4R0DSta4ubbHH5nULLoUMWey51Tlt5oH-C9j_JTKeiNdGyN3UO-a6xS7e1pUcop-dC1K6WQanC9Gu1pjEYtYW7-J5Uzq6xNK6adxcDVezL6W2oAhyDRFLsGujV0Hu_29BtrvMTA37rV3w53mvfVuKsR9lqKfIuZIcIrcfl7VVhoKQ4Y7TRzuNXGIUc77jyYOqybut8_Za9-baxkXMmKSRRXLlusdBvuiI9LeWQ24W7pfkSbNY0eaxa5nd3pOw-AdUK8Fr38MXj14BwzcCN7pMXjbwbObT7_dcPqdTsPZP6Bdm_3Bcfbrs3_AwI3ZHx1n3_zo7LBvxlQquGKN3hQrf5RYdMfKF0yJTK7YtRSrQqZcBgWv2IiY0uUpKRceL4_yAjfJxEi2zWTbSHYqZFInO-ayLbN038gemMkDI3loJg__5aNPjeSRWXlkJJ-ZyWfvKnu5_vAnAAD__96MMnU=
# This query performs a semi-join, which is converted to an inner join by the
# optimizer.
query T
SELECT url FROM [EXPLAIN (DISTSQL)
SELECT lk FROM ltable WHERE EXISTS (SELECT * FROM rtable WHERE ST_Intersects(ltable.geom2, rtable.geom))]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzElVFP2zAQx9_3Kax7opu71klaIE-ZRqZ16lrWIg0JVSg0J8gIcWY7Ewj1u09JKtKG1k4Ho49J7uf7-ey_8gjydwwuTP2h__mMZCImXybj7-TCPz8dfhqMyMHJYHo2_TFskWVJfFtWxCq4ipH8_OpPfOKf51XkYFnzviwRqyVSXUaJQiFxruRBSX-8Rn5n0WVh8dRqzYBCwkMcBXcowb0ABhQsoGDDjEIq-Byl5CL_9FgUDsJ7cLsUoiTNVP56RmHOBYL7CCpSMYILZ3mDCQYhik4XKISogiguli9VvFREd4F4AArTNEikS9qdvOk4Uy7xGPVsmC0o8ExVTaQKrhFctqDNRQbJHxQKw288SlB07HWXchBePojLKAnxHugT4d-nojZFz6LEc1qrmpbO1NrFNDdcTqy_0bKa2JDz2ywlv3iUEJ64xMu3NR5tsu2t225VtXdRPYmkipK56hyvi3r51RmLEAWGecNat2qBqwdyE8ibZ_RsURk5W42qdXjZq77Oh3KhrdrMMng_jWzrvHoN7LJkk99GtRFv87TDerXKzb37a71Z8ySyZknsWO0iKDtn0aBSy6KzxywaTFeyeLjvLBpUq0vdfbMwslcNo_3KYbSaB8JqGAi7_S9xMIjU4tDbYxwMpitxONp3HAyq1bVibxYH61Xj4PzHf9OGxhOUKU8kNvrzdHN1DK-x3KrkmZjjqeDzok35OC644kWIUpVfWfkwSMpPueAqzLSwpYctLWzrYbsOs1XYWYPZbjDrvoju6WlHu2sD3NMfVl8_s76WPtTDh1r4SA8faeFjPXz8kqPWw6ajNtCG02L6bJlofbiYIV1MHy9myBd7dsvXcceAP7vmuxyagTadmgk3DV6fsjo9W7z7GwAA__9Kzz7E
# Left joins are converted to paired joins by the optimizer.
query T
SELECT url FROM [EXPLAIN (DISTSQL)
SELECT lk, rk FROM ltable LEFT JOIN rtable ON ST_Intersects(ltable.geom1, rtable.geom)]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzMlNFv2jAQxt_3V1j31EpmkARoyVOmLZVSMdJBJk2qoiojJ5QR7Mx2JirE_z4lRoOwYsL2wqPt-3zfd_rpNiB_5uDCzB_7HyNSipw8TMPP5Nn_9jT-EEzIzadgFs2-jG_JriRfUiKWuipXyfccydh_iMhjGEyI0BfhhEj1kjGFQuJcyRtd-H6BfGXRXVV9uo2BAuMpTpIVSnCfwQIKNlBwIKZQCD5HKbmonjZ1YZCuwe1RyFhRquo6pjDnAsHdgMpUjuBCVP0_xSRF0e0BhRRVkuX199qJV4hslYhXoDArEiZd0ulWTcNSucSzqGdDvKXAS7VvIlWyQHCtLW1vJGC_UChMH3nGUHSdppfotUBXTy_8GvnTeoZAQc_Hq-bzkrEU10D__OSvC3E0XM-mxOvfNuxTz6He4GQI-5IQlfndMIcXBdgPecz5sizID54xwplLvGoSf1FSBxk2gjgnIzgnI-ydl4yLFAWmDdvx9o2QE97hRXd0VPh2636jtdWeRasdi127U6NyMY1nrBzR2L9OGs-EOKDx7lpptNsjYbdEwun8CxBnjBwBMbhOIM6EOADi_lqBOLNhpygLziS22j69an1hukC96yQvxRyfBJ_XbfQxrHX1RYpS6VdLHwKmnyqDh2LLKLbNYtsodhpi61jsmG33zK37RvXALB4YxUOzePg_oe-M4ntz53ujeGQWjy6yHW_f_Q4AAP__DHBHsA==
query T
SELECT url FROM [EXPLAIN (DISTSQL)
SELECT lk, rk FROM ltable LEFT JOIN rtable@geom_index
ON ST_Intersects(rtable.geom, ltable.geom1) OR ST_DWithin(ltable.geom1, rtable.geom, 2) ORDER BY (lk, rk)]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzUVV1v2jAUfd-vsO4TqKZgByj1U7o1lagY6SCTNlWoyshVmzXEmWO2VhX_fUrCBqHgwD4eeIx9j885vufGL5B-i0DA2Bk47zwyVxG5Grnvya3z6WZw0R-S2mV_7I0_DOpkWRI9UqIei6pI-18iJAPnyiPXbn9IVL5g36Oc3YVxgE_EHZJU34WxRpXiVKe1ouQ0K6HLA_IPVifuKKsNfoT6IYxr63uUlGA8q710RuTtZ1IrBNUnQCGWAQ79GaYgboEBBQ4ULJhQSJScYppKlW295IX94AlEi0IYJ3OdLU8oTKVCEC-gQx0hCPAy0hH6AapmCygEqP0wyo8v5NmJCme-egYK48SPU0EazYzUnWtBbEZtDpMFBTnXK5JU-_cIgi3o_kL68XdUGoNrGcaomlZZi_ecoCja4H70nFHeDKDwqh1Af5_kPCVqozU2p8Rub_ahWKWEn7aEEFcD98Lr1UsWqW1Ru7PTKD_EaGZweeHdg0yuGjGQ8nGekK8yjImMBbGz23qVQ7tLic23mu0azFo7bVqH2BxLpVE1z8sWbXZCbX4CFGa-nj6QCGNB2E7G9k7GFZFUASoMtvNMFlukDWVDJk3GS5BdEjolCWz_aWL7TVOTN_KwHzxPFVI25ql9vPNUYXRtns6OeZ4qbC7nibX-4UDx_dPM90yz1fiTLFcI2chy53izXGF0Lcu9Y85yhc1fWWb_6XHYQjnCNJFxinv981vZq4HBPRYvTSrnaoo3Sk5zmuLTzXH5QoCpLnZZ8dGPi61M4DqYGcHcDOZGsGUGW0ZwuwRmm-C22XMFdceI7prBXSP4zAw-M4J7ZnDvb27s3HxjrYqYVISsKmXmmLGKnDFz0DadTxZvfgYAAP__VoRGEg==
query T
SELECT url FROM [EXPLAIN (DISTSQL)
SELECT lk, rk FROM ltable LEFT JOIN rtable@geom_index
ON ST_Intersects(ltable.geom1, rtable.geom) OR ST_DWithin(rtable.geom, ltable.geom2, 2) ORDER BY (lk, rk)]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzUVU1v2zgQve-vIOZkI3RsUvJHeFJ2owAOvFbW1gItAiNQrUGiRhZVim4TBP7vhSS3sZWYUtD24CPJeXzzRu-Jz5B9iUHA3J24__hkrWJyOfP-JTfuh-vJ-XhKWhfjuT__b9Im25L4gRL1UFbFOvgUI5m4lz658sZToooN5w7l6jZKQnwk3pRk-jZKNKoMlzprlZjTvITRLaBYtYk3y2vDb5G-j5LWzhElOyhOCc9rL9wZ-fsjaZUNtRdAIZEhToMVZiBugAEFDhQsWFBIlVxilkmVHz0XhePwEUSPQpSka51vLygspUIQz6AjHSMI8HPWGQYhqm4PKISogyguri87clIVrQL1BBTmaZBkgnS6HBYbCnKtX-7NdHCHINiGNuceJ19RaQyvZJSg6lr79P5TiqKcvPe_786K-QOFV18A6M-b3MdUVb6Gwylx-tXRO1a-Swk_7QkhLifeuT9qAwVvrQVxGHU4dSzq2NQZHNTK36M117gd8-BdOl_GP5HyYZ2SzzJKiEwEcey84ar7Cr3DV3qHlOSaD-q1D8q03iNzLpVG1T3bl-iwE-rwE6CwCvTynsSYCMIOMtoHGV-IpApRYfg2z2LzRmtT2ZFpl_E9yKEW-nstsOYZYs0y1OWdrtUwRTXslRTZR52iGq07KRoec4pqZG5TxHq_MUa8uYd5Qw9bnYYOruGuOLh_1A6u0brj4NExO7hG5g8Hsz_0ELxBOcMslUmGjf7vvfyFwPAOy1clk2u1xGsllwVNufQKXLERYqbLU1Yuxkl5lDe4C2ZGMDeDuRFsmcGWEWzvgVkVbJs111D3jeiBGTwwgodm8NAIHpnBo1-Z2Jl5Yr0am9SYrM5lZpuxGp8xs9Gqyhebv74HAAD__-ssPts=
query T
SELECT url FROM [EXPLAIN (DISTSQL)
WITH q AS (
SELECT * FROM ltable WHERE lk > 2
)
SELECT count(*), (SELECT count(*) FROM q) FROM (
SELECT lk, rk
FROM q
LEFT JOIN rtable ON ST_Intersects(q.geom1, rtable.geom)
) GROUP BY lk]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJyUk81um0AQx-99itGcTLSqDaQ9cMJNSUNEIcVYaZQii8AEUZNdvLtUiSK_e8VH0sSSrfqE5uM_85th9hnVpkYHF17gnSXQyhrO4-g73Ho_r4K5H8Lkq79IFj8CA6795AI2MF_AZMw-GXJrnd3VBNcXXuxBvYZf7WxmE1gGjHm5aLmenBjsVfniGQpsxu9LtF4zkOsxBoF3nsBl5Icgh0ZRCEqvKq5JKsq1mmw-liQeTDYm9JZhwLc4Wl7Blxuo1yky5KKgMHsghc4tmpgybKTISSkhO9dzn-AXj-jMGFa8aXXnThnmQhI6z6grXRM6WIs8q0HlGYe79v6eJMymM2RYkM6qui8ftdoB12SuhemWoWj1v3JKZyWhY27Z_7f0-R-SmopLUXGSU_N9t-SpIWfYU7RMvLjfFjIc1uF261hVvKBHZK-VvMdG7qzRtRi4pwayN_jMtZn7ae8Q1jFDdPAxZQXJqXXUAI2sHjL5hAwDIdZtA79FxUFwB1y7o929h36Qz28G2ctvH8M_L0tJZaaFnNrv-d3uh8zDm1UYJatwGQQT1-zan0XLMFnF0fVi0pkx8YKkAx3feOsvD6G79Y2xl_P0GM6YVCO4oneM-yrPtilDKkoa3oASrczpSoq8bzOYUa_rHQUpPUTNwfD5EOoA34rNg2LrsNg6KLYPi-2D4tMdcbr98DcAAP__xbuWhg==
# Anti joins are also converted to paired joins by the optimizer.
query T
SELECT url FROM [EXPLAIN (DISTSQL)
SELECT lk FROM ltable WHERE NOT EXISTS (SELECT * FROM rtable WHERE ST_Intersects(ltable.geom2, rtable.geom))]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzclFFvmzAUhd_3K6z7lEzOEiBJU56oOqpRMegC1SpVUcXCVcVKbWabqVWV_z4ZojWJWodsL1Ufwef4nnP1yU8gf5XgQuKH_mlKalGSs3n8lVz7VxfhSRCR3ucgSZNvYZ-sJeVdqyhV9qNE8v2LP_dJFKfEv9JK0lvrPrYysSmT6qZgCoXEpZK99oZPt8jvbboWNl_9_gIoMJ5jlN2jBPcaLKBgAwUHFhQqwZcoJRf66KkRBvkDuCMKBatqpX8vKCy5QHCfQBWqRHAh1QPmmOUohiOgkKPKirK5vo3iVaK4z8QjUEiqjEmXDIZ6aFwrl3gW9RxYrCjwWj0PkSq7RXCtFe0eJGC_USjMz3nBUAyd7SzpY4UuCf2zlMSXqT8n53EQAYV2QZ5e0E3BcnwA-vcm_6ESO9v1bEq8cX8zvk09h3qTV0vYh5TQ4dfLnB5U4HnJIed3dUV-8oIRzlzi6U3E0UtFprrIWVEqFFpHgqRhTsMZRKdpy9rpSZL2osswJCcJCaJ0ttH-1dLOq6Wfu9aMixwF5ltFF6sX1hLxAa-GxzvCl0ePt0Zb3em1utE7tAcNXAfzuyfKDr_jt8nvnhIb_B69H37t7hDZHSFyBv-C0J4gOwhN3iZCe0psIDR7PwjteffnKCvOJHZ64Ub6icT8Ftv3VPJaLPFC8GUzpv2MG1_zI0ep2lOr_QhYe6QDbpoto9k2m22j2dkyW7tmxxx7ZB49NronZvPEaJ6azdP_KX1kNM_Mk2dG87HZfHxQ7MXqw58AAAD__572dag=
query T
SELECT url FROM [EXPLAIN (DISTSQL)
SELECT lk FROM ltable
WHERE NOT EXISTS (
SELECT * FROM rtable WHERE ST_Covers(ltable.geom1, rtable.geom) AND lk > 5 AND rk > 12
) ORDER BY lk]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzclV9vm0gUxd_3U1zdJ7w7XhuwHYcnvAnRElFIgaipUiui5iqiJgwdIEoU-btXgBv_UT222zc_zsw93HPu_DS8YfE9RQMDy7EuQqhECle-9wHurbsbZ2K7oFzaQRh8dDqwLEnnbUVaRl9Tgk__W74FrheCdVdXgrKs-7stE-tlSlE-zPgziUJp5f8-En9S2bKqWXVg4l6Cks7hS9Xv6wTDznJLvG-pWqcDnn9p-fDfZ0jnU2SY8Zjc6IkKNO5RRYYaMtRxyjAXfEZFwUV99NYU2vELGn2GSZZXZb09ZTjjgtB4wzIpU0IDw9qST1FMotdHhjGVUZI2n2_Nm7lIniLxigyDPMoKA7q9uqlXlQaYKjM1nC4Y8qpcNSnK6JHQUBfscCN29kyipPiaJxmJnr7pJXzNyQDHugrBuw0tH64920WG7UjNeqQPSRbTC7L3L1kvuYDVXZgaA3PQqa27oJjq--QNw7DdcLycv6mv5v_zZCMuM3VmDneG1o4JXYddDn90VODVpTicz6scvvEkA54ZYOptwq3gozrEVZKWJOoasIMG55p7270IW4wvJkGouLeOA5MAtpLvDKzvDLzKyUVMguLNiKb6D04Xv5iKy7s8751vVO_qPtjorh4Ou3oY7D2t27B4NO57rGzhPjgN3PeEXsP97DRw1w4HTjsQOL37O7jtMbKF2_A0cNsTeg238Wngtud34lOR86ygg17Ofv30UvxI7VNd8ErM6EbwWdOmXXqNrtmIqSjbU7Vd2Fl7VBtcF6tSsSYXa1KxviFWt8W63HZf3nogVQ_l4qFUPJKLR38S-kwqHss7j6Xic7n4_Cjb08VfPwIAAP__Ufmavg==
# Bounding box operations.
statement ok
SET CLUSTER SETTING sql.spatial.experimental_box2d_comparison_operators.enabled = on
query T
SELECT url FROM [EXPLAIN (DISTSQL)
SELECT lk, rk FROM ltable JOIN rtable@geom_index ON ltable.geom1 ~ rtable.geom]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzElMGO2jwQgO__U1hz2pXMD0mAhZxStVTKiiZb4FBpFa1SPEIpwU5tZ0WF2GevkqBCKJjQSuUW2_N5Pnsm3oD6noIL09F49H5GcpmSj5PwE3kefXkav_MDcvfBn86mn8f3ZBeSLimRyyoq1fHXFMlj6AdElt_eAsXqJeEM1yQMdgH_F5MWedvFlMMIKHDBMIhXqMB9Bgso2EDBgYhCJsUclRKyWNqUgT5bg9uhkPAs18V0RGEuJIK7AZ3oFMGFWbH9BGOGst0BCgx1nKTl9pWJl8lkFcsfQGGaxVy5pNUukoa5dolnUc-GaEtB5HqfROl4geBaW9pcxOevKDWyR5FwlG2n7vLbVQH9RYzWmSRKv8zFK0p159mUeN37miL1nLOW9jWWhd3utvonDfe3NRZimWfkm0g4EdwlXnGkMCCeTd6I1zv0Oy_nnJXbO-VcSIYSWU0o2p7QD0RLZO3hUeDp1N1aaqt5G1nN2qhtt8oqX91IF1SOGql7o0a6YHnQSA__vpHs5tW0G1bTaf1JLS-IHNWyd6NaXrA8qOXgto_CCbkJqkxwhY3--U7xaCBbYPXCKJHLOT5JMS_TVMOw5MoJhkpXq1Y18Hm1VAgewpYRts2wbYSdGmwdw45Zu2NO3TXSPTPcM8J9M9z_m0M_GOGBOfPACA_N8PAq7Wj7388AAAD__5r_BqA=
query T
SELECT url FROM [EXPLAIN (DISTSQL)
SELECT lk, rk FROM ltable JOIN rtable@geom_index ON rtable.geom ~ ltable.geom1]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzElFFv2jAQx9_3Kax7aiUzSAIU8pRpY1IqRjrgYVIVVSk5oYxgZ7ZTUSH62ScnbBAGJmzSeIvP9_f_57uL1yB_pODCZDAcfJySXKTk8zj4Qh4H3x6GH_wRufnkT6aTr8Nbsk1JF5SIRZmVqug5RXIf-CMiim9vjnz5lLAYVyT4FXyvg-Rtm16srBAoMB7jKFqiBPcRLKBgAwUHQgqZ4DOUkgu9tS4S_XgFbotCwrJc6XBIYcYFgrsGlagUwYWpPn-MUYyi2QIKMaooSYvjS28vE8kyEq9AYZJFTLqk0dSmQa5c4lnUsyHcUOC52plIFc0RXGtD64P47AWFwvieJwxF06my_FEqoL8Vg1UmiFRPM_6CAuPn1xvPpsRr31YoqeecBLUvAdWA24J1j0LuCjbkfJFn5DtPGOHMJZ6-VTAiXoe8Ea9SxdNwzkm4HVPOuIj15StA4eYI_og3eNbsHyQet25XrK36k2TVm6Sm3SgaffEsnUE5mKX29WbpDOjeLN39_1my6zfUrtlQp_E37TwDctDOzvXaeQZ0r5296z4NR-DGKDPOJNb681v66cB4juU7I3kuZvgg-KywKZdBoSsCMUpV7lrlwmfllgbcF1tGsW0W20axUxFbh2LHjN0yW7eN6o5Z3DGKu2Zx918ufWcU98zOPaO4bxb3L8ION-9-BgAA__9vVwoz
query T
SELECT url FROM [EXPLAIN (DISTSQL)
SELECT lk, rk FROM ltable JOIN rtable@geom_index ON rtable.geom && ltable.geom1]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzMlM-O2jAQh-99CmtOu5Ip-QMs5JSqpVJWlGyBQ6VttErxCKUEO7Wdigrx7lUSWggFE9oDvSBi-_N89m_kDahvKXgwHY6Gb2cklyl5Pwk_kOfhp6fRm2BM7t4F09n04-ie7JakS0rkslqV6vhLiuQxDMZElv_9BYrVS8IZrkn4a_B1MUg-55bl9KrfHVlO2BFQ4ILhOF6hAu8ZbKDgAAUXIgqZFHNUSshialMuDNgaPItCwrNcF8MRhbmQCN4GdKJTBA9mxf4TjBnKtgUUGOo4Scvtq9p-JpNVLH8AhWkWc-WRVrsoGubaI75NfQeiLQWR630RpeMFgmdvaXORgH9HqZE9ioSjbLt1lz9uDehvYrjOJFH6JeEapcK5Vne-Q4nfua9pUt89a-pcY1oY7m6sd9Jyf2MjIZZ5Rr6KhBPBPeIXxwrHxO_WY_ZrN3re0z3rudfLuZAMJbKaW7Q9cZKxaImsPThaeLp0p1babt5VdrOuajutMvSr--qCylFfdW7YVxdMD_rq4aZ95TQP12kYrtv6m2gviBxF271htBdMD6Lt_zdPxgnPCapMcIWNXgSreFKQLbB6f5TI5RyfpJiXZarPsOTKAYZKV7N29RHwaqoQPIRtI-yYYccIuzXYPoZds7ZlLt0x0l0z3DXCPTPc-5dDPxjhvrly3wgPzPDgKu1o--pnAAAA___waBaG
# Tests where the table with the inverted index has multiple columns in the primary
# key.
statement ok
CREATE TABLE rtable2(
rk1 int,
geom geometry,
rk2 int,
primary key (rk1, rk2),
INVERTED INDEX geom_index(geom)
)
statement ok
INSERT INTO rtable2 VALUES
(11, 'POINT(1.0 1.0)', 22),
(12, 'LINESTRING(1.0 1.0, 2.0 2.0)', 24),
(13, 'POINT(3.0 3.0)', 26),
(14, 'LINESTRING(4.0 4.0, 5.0 5.0)', 28),
(15, 'LINESTRING(40.0 40.0, 41.0 41.0)', 30),
(16, 'POLYGON((1.0 1.0, 5.0 1.0, 5.0 5.0, 1.0 5.0, 1.0 1.0))', 32)
query T
SELECT url FROM [EXPLAIN (DISTSQL)
SELECT lk, rk1, rk2 FROM ltable JOIN rtable2@geom_index
ON ST_Intersects(ltable.geom1, rtable2.geom) ORDER BY (lk, rk1, rk2)]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzMlF1v2jAUhu_3K6xzVVQzsAO0zVW2lUlUDDroxaYKVRk56rIGO7OdqVPFf5-cdCvJGifsQ-0NIsl58z45euQ70F8T8GE5no7fXJBMJeTtYv6OXI4_nE9fTWbk4HSyvFi-n3bI_UhyQ4m6YfaHF7OJCT8lSM7mkxlR-X8eXKPcXMUiwlsynxFtrmJhUGlcG31QzL-0I_Y1RSK_7JD54nS8IK8_koPdns4KKAgZ4SzcoAb_EhhQ4EDBgxWFVMk1ai2VfXSXD06iW_D7FGKRZsbeXlFYS4Xg34GJTYLgw4UtXmAYoer1gUKEJoyT_PUFYpCqeBOq70BhmYZC-6Tbs6XzzPgkYDTgsNpSkJl5KNEmvEbw2Za2B5mIb6gMRmcyFqh6Xpnl95UC_RUZ36aqst6AUxIMOiVOGng0GNbS8n1oLeX91kaPkz6sbSrlTZaSLzIWRAqfWIyBJas6kUOPStD5aB2xtw_xUiqDqndSpg3YIQ34IQ28Q6CwCc36M0lQ-ITVtg5qWx_KpIpQYVTftdo-gjiTXZn2GC_F6jCGJQzW3nLWzvIe7-YS7u15A0rF88ETe95Au-P50TPxvIH43nPW_8ei8_aG8ZaGed0_8asBpOLX8In9aqDd8ev4mfjVQPzTL_YfD9JHaheoUyk0tjob-_Z0xegai1NZy0yt8VzJdV5TXM7zXH4jQm2Kp6y4mIjikQXcDTNnmLvD3Bn23GHPGR6UwqwaHri_uaF66EyP3OGRM3zkDh85w8fu8PHfbOzEvbF-gyYNkjVZ5taMNXjG3KJVv3y1ffEjAAD__z3N3qQ=
query T
SELECT url FROM [EXPLAIN (DISTSQL)
SELECT lk, rk1, rk2 FROM ltable LEFT JOIN rtable2@geom_index
ON ST_Intersects(ltable.geom1, rtable2.geom) ORDER BY (lk, rk1, rk2)]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzMVF1v2jwYvX9_hfVcFdW8YIePNlfZ1lSiYqQDJm2qUJWRR13WYGe2mVpV_PfJSTcIKw7sQ-ImUmwfn3MeneMn0F8z8GESDsM3U7JUGbkcR2_JTfjhevhqMCInF4PJdPJu2CDPR7J7StQ9sx9ens1M_ClDMgwvp-QqGoyIKhZ4cIdycZuKBB9INCLa3KbCoNI4N_qkBP1vj9i7SkTx2yDR-CIck9cfyckmWWMGFIRMcBQvUIN_AwwocKDgwYxCruQctZbKbj0VBwfJA_htCqnIl8YuzyjMpULwn8CkJkPwYWqJxxgnqFptoJCgidOsuL6UGOQqXcTqEShM8lhonzRbljRaGp8EjAYcZisKcmnWJNrEdwg-W9H9hQzEN1QGkyuZClQtr6pl-pijX444ej8Nx8WggcKvowb686rwIVdbYw84JUGnUdFPA48GXRr0dhrhhxixBp4H2jvMxHrSQynvlzn5IlNBpPCJVdixordjVPjpV_wUR3c58Q5xMpHKoGqdV10E7JQG_JQG3ilQWMRm_plkKHzCdrJ2drKuyaRKUGGym2u2ekHiSDZl3mK8Atslo1uRwfYvBtuvGC3eLHJ7cDVqpGxVo3O81agxslGN_pFXo8bJczVY-y93g-8fSr5nKL3m70SyRshWJLvHG8kaIxuRPDvySNY4-RFJ9g-f6xdox6hzKTTu9QK37RuOyR2Wb7-WSzXHayXnBU35GxW4YiFBbcpdVv4MRLllBW6CmRPM3WDuBHtusOcEdypgtg3uuD3XUHed6J4b3HOC-25w3wk-c4PP_mRi5-6JtWtiUhOyupS5Y8ZqcsbcQdt2Plv99z0AAP__hl4Jaw==
query T
SELECT url FROM [EXPLAIN (DISTSQL)
SELECT lk FROM ltable WHERE EXISTS (SELECT * FROM rtable2@geom_index
WHERE ST_Intersects(ltable.geom1, rtable2.geom)) ORDER BY lk]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzMlVFP2zwUhu-_X2GdK_rNXeskLZCrbCPTOnUta5HGhCoUGgsyQpzZzgRC_e-Tk440KbXT0QGXic_j8-bYj3IP4mcMLkz9of_hBGU8Rh8n4y_ozD89Hr4bjNDe0WB6Mv06bKFlSXxdVMQyuIgp-vbJn_jIP1VVaG9Z839RwvMSy7uk7OY8SkJ6uywX8jxKJOWCzqXYK3Z6q6oI_gPlj60WGk-O_Al6_x3F1zPAkLCQjoIbKsA9AwIYLMBgwwxDytmcCsG4WrrPCwfhLbhdDFGSZlK9nmGYM07BvQcZyZiCCyeq24QGIeWdLmAIqQyiON--yOWlPLoJ-B1gmKZBIlzU7qim40y6yCPYs2C2wMAyWTYRMrik4JIFbh5kkPyiXNLwM4sSyjt2Ncv6KAE_IP5tymsz9SyMPKdVyYk9G3u9jWmtbdKqlMup9R9PWo5tyNh1lqIfLEoQS1ykYjgq2eix0P1Ws-Ha28Q9ioSMkrnsHFbDeuoKjXlIOQ1Vw1q3coOLO3QViKs1erYoEzkbE5X7sKJXfZ83xUYbYxPLkPthZBvn1dt5uhFrs7RDepXyTe37lfakuZSkmZQdq507s7WWhig1LZ0X1tKQdkXL_degpSFueb-7z-Yl2amX9o69tJqLYTUUw27_jRaGIDUtei-shSHtihYHr0ELQ9zyepFn08LaqRbOP_xdPdJ4QkXKEkEb_Ym6KjoNL2nxqYJlfE6POZvnbYrHcc7lL0IqZLFKiodBUiypgKsw0cKWHra0sK2H7TpMVmGnApPtYNJ9Et3T0472qw1wT39Yff3M-lp6Xw_va-EDPXyghQ_18OFTjloPm47aQBtOi-jdMtF6uYjBLqLXixj8Imu3vIo7Bnztmm9zaAbadGom3DR4vWV1erb473cAAAD__7TDS8A=
query T
SELECT url FROM [EXPLAIN (DISTSQL)
SELECT lk FROM ltable WHERE NOT EXISTS (SELECT * FROM rtable2@geom_index
WHERE ST_Intersects(ltable.geom1, rtable2.geom)) ORDER BY lk]
----
https://cockroachdb.github.io/distsqlplan/decode.html#eJzclVFvmzAUhd_3K6z7lGzOEiBJU57oWqpRMeiAap2qqGLhqmKlmNnO1KrKf58M2ZpkjUO2l6qPxOf4nnP1yXkE8aMAG2LXd48TMucFOY3CT-TKvTz3j7yAdE68OIk_-12ylBS3jaKQ6bcCyZePbuSSIEyIe6mUpLPUvW1kvJaZzg2yu-u8zPB-aRHyOi8lcoEzKTrNbe-VyqC_TfVnt0vC6MSNyIevpLidAoWSZRikdyjAvgIDKJhAwYIphYqzGQrBuDp6rIVedg_2gEJeVnOpfp5SmDGOYD-CzGWBYEOipkWYZsj7A6CQoUzzor6-yeVUPL9L-QNQiKu0FDbp9dXQcC5t4hjUMWG6oMDm8mmIkOkNgm0saPsgXvkTucTsjOUl8r61niV5qNAmvnuakPAicSNyFnoBUPh7xUD_XOXeV3xj145JiTPsruWnjkWdEXXGW4uY-xRRBZYLHe9X4mnTPmO384p8Z3lJWGkTlXCoQgfP9TlQfU7zQiJXUuLFNZSKXi84ThoYj4_ipBNc-D45iokXJJOVJWwtbm0t_tSX8Qw5ZutVHeMdTBfPbCdgPVb1D9fU26YP16Yb7UE22oHcN3s1Z3ujvCPKBsrDl4vyjiIrKB-8LpTN9jCZLWGyev-C0o4gGyiNXi5KO4qsoDR5XSjt-DuIUFSsFNjqxRuoJxOzG2yeWMHmfIbnnM3qMc1nWPvqHzIUsjk1mg-vbI5UwFWzoTWberOpNVtrZmPTbOljD_Sjh1r3SG8eac1jvXn8P6UPtOaJfvJEaz7Umw_3ij1dvPkVAAD__y4agqc=