1#include <ext/shared_ptr_helper.h>
2
3#include <Parsers/ASTSelectQuery.h>
4#include <Parsers/ParserSelectQuery.h>
5#include <Parsers/parseQuery.h>
6#include <Parsers/queryToString.h>
7#include <Interpreters/InJoinSubqueriesPreprocessor.h>
8#include <Interpreters/Context.h>
9#include <Core/Settings.h>
10#include <Storages/IStorage.h>
11#include <Databases/IDatabase.h>
12#include <Databases/DatabaseOrdinary.h>
13#include <Common/typeid_cast.h>
14
15#include <iostream>
16#include <vector>
17#include <utility>
18#include <string>
19
20
21namespace DB
22{
23 namespace ErrorCodes
24 {
25 extern const int DISTRIBUTED_IN_JOIN_SUBQUERY_DENIED;
26 }
27}
28
29
30/// Simplified version of the StorageDistributed class.
31class StorageDistributedFake : public ext::shared_ptr_helper<StorageDistributedFake>, public DB::IStorage
32{
33 friend struct ext::shared_ptr_helper<StorageDistributedFake>;
34public:
35 std::string getName() const override { return "DistributedFake"; }
36 bool isRemote() const override { return true; }
37 size_t getShardCount() const { return shard_count; }
38 std::string getRemoteDatabaseName() const { return remote_database; }
39 std::string getRemoteTableName() const { return remote_table; }
40
41 std::string getTableName() const override { return ""; }
42 std::string getDatabaseName() const override { return ""; }
43
44protected:
45 StorageDistributedFake(const std::string & remote_database_, const std::string & remote_table_, size_t shard_count_)
46 : remote_database(remote_database_), remote_table(remote_table_), shard_count(shard_count_)
47 {
48 }
49
50private:
51 const std::string remote_database;
52 const std::string remote_table;
53 size_t shard_count;
54};
55
56
57class CheckShardsAndTablesMock : public DB::InJoinSubqueriesPreprocessor::CheckShardsAndTables
58{
59public:
60 bool hasAtLeastTwoShards(const DB::IStorage & table) const override
61 {
62 if (!table.isRemote())
63 return false;
64
65 const StorageDistributedFake * distributed = dynamic_cast<const StorageDistributedFake *>(&table);
66 if (!distributed)
67 return false;
68
69 return distributed->getShardCount() >= 2;
70 }
71
72 std::pair<std::string, std::string>
73 getRemoteDatabaseAndTableName(const DB::IStorage & table) const override
74 {
75 const StorageDistributedFake & distributed = dynamic_cast<const StorageDistributedFake &>(table);
76 return { distributed.getRemoteDatabaseName(), distributed.getRemoteTableName() };
77 }
78};
79
80
81struct TestEntry
82{
83 unsigned int line_num;
84 std::string input;
85 std::string expected_output;
86 size_t shard_count;
87 DB::DistributedProductMode mode;
88 bool expected_success;
89};
90
91using TestEntries = std::vector<TestEntry>;
92using TestResult = std::pair<bool, std::string>;
93
94TestResult check(const TestEntry & entry);
95bool parse(DB::ASTPtr & ast, const std::string & query);
96bool equals(const DB::ASTPtr & lhs, const DB::ASTPtr & rhs);
97void reorder(DB::IAST * ast);
98
99
100TestEntries entries =
101{
102 /// Trivial query.
103
104 {
105 __LINE__,
106 "SELECT 1",
107 "SELECT 1",
108 0,
109 DB::DistributedProductMode::ALLOW,
110 true
111 },
112
113 {
114 __LINE__,
115 "SELECT 1",
116 "SELECT 1",
117 1,
118 DB::DistributedProductMode::ALLOW,
119 true
120 },
121
122 {
123 __LINE__,
124 "SELECT 1",
125 "SELECT 1",
126 2,
127 DB::DistributedProductMode::ALLOW,
128 true
129 },
130
131 {
132 __LINE__,
133 "SELECT 1",
134 "SELECT 1",
135 0,
136 DB::DistributedProductMode::DENY,
137 true
138 },
139
140 {
141 __LINE__,
142 "SELECT 1",
143 "SELECT 1",
144 1,
145 DB::DistributedProductMode::DENY,
146 true
147 },
148
149 {
150 __LINE__,
151 "SELECT 1",
152 "SELECT 1",
153 2,
154 DB::DistributedProductMode::DENY,
155 true
156 },
157
158 {
159 __LINE__,
160 "SELECT 1",
161 "SELECT 1",
162 0,
163 DB::DistributedProductMode::LOCAL,
164 true
165 },
166
167 {
168 __LINE__,
169 "SELECT 1",
170 "SELECT 1",
171 1,
172 DB::DistributedProductMode::LOCAL,
173 true
174 },
175
176 {
177 __LINE__,
178 "SELECT 1",
179 "SELECT 1",
180 2,
181 DB::DistributedProductMode::LOCAL,
182 true
183 },
184
185 {
186 __LINE__,
187 "SELECT 1",
188 "SELECT 1",
189 0,
190 DB::DistributedProductMode::GLOBAL,
191 true
192 },
193
194 {
195 __LINE__,
196 "SELECT 1",
197 "SELECT 1",
198 1,
199 DB::DistributedProductMode::GLOBAL,
200 true
201 },
202
203 {
204 __LINE__,
205 "SELECT 1",
206 "SELECT 1",
207 2,
208 DB::DistributedProductMode::GLOBAL,
209 true
210 },
211
212 /// Section IN / depth 1
213
214 {
215 __LINE__,
216 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM test.visits_all)",
217 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM test.visits_all)",
218 1,
219 DB::DistributedProductMode::ALLOW,
220 true
221 },
222
223 {
224 __LINE__,
225 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM test.visits_all)",
226 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM test.visits_all)",
227 2,
228 DB::DistributedProductMode::ALLOW,
229 true
230 },
231
232 {
233 __LINE__,
234 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM test.visits_all)",
235 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM test.visits_all)",
236 1,
237 DB::DistributedProductMode::DENY,
238 true
239 },
240
241 {
242 __LINE__,
243 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM test.visits_all)",
244 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM test.visits_all)",
245 2,
246 DB::DistributedProductMode::DENY,
247 false
248 },
249
250 {
251 __LINE__,
252 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM test.visits_all)",
253 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM test.visits_all)",
254 2,
255 DB::DistributedProductMode::GLOBAL,
256 true
257 },
258
259 {
260 __LINE__,
261 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote_db.remote_visits)",
262 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote_db.remote_visits)",
263 2,
264 DB::DistributedProductMode::GLOBAL,
265 true
266 },
267
268 {
269 __LINE__,
270 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM test.visits_all)",
271 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote_db.remote_visits)",
272 2,
273 DB::DistributedProductMode::LOCAL,
274 true
275 },
276
277 {
278 __LINE__,
279 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote_db.remote_visits)",
280 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote_db.remote_visits)",
281 2,
282 DB::DistributedProductMode::LOCAL,
283 true
284 },
285
286 /// Section NOT IN / depth 1
287
288 {
289 __LINE__,
290 "SELECT count() FROM test.visits_all WHERE UserID NOT IN (SELECT UserID FROM test.visits_all)",
291 "SELECT count() FROM test.visits_all WHERE UserID NOT IN (SELECT UserID FROM test.visits_all)",
292 2,
293 DB::DistributedProductMode::ALLOW,
294 true
295 },
296
297 {
298 __LINE__,
299 "SELECT count() FROM test.visits_all WHERE UserID NOT IN (SELECT UserID FROM test.visits_all)",
300 "SELECT count() FROM test.visits_all WHERE UserID NOT IN (SELECT UserID FROM test.visits_all)",
301 2,
302 DB::DistributedProductMode::DENY,
303 false
304 },
305
306 {
307 __LINE__,
308 "SELECT count() FROM test.visits_all WHERE UserID NOT IN (SELECT UserID FROM test.visits_all)",
309 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL NOT IN (SELECT UserID FROM test.visits_all)",
310 2,
311 DB::DistributedProductMode::GLOBAL,
312 true
313 },
314
315 {
316 __LINE__,
317 "SELECT count() FROM test.visits_all WHERE UserID NOT IN (SELECT UserID FROM remote_db.remote_visits)",
318 "SELECT count() FROM test.visits_all WHERE UserID NOT IN (SELECT UserID FROM remote_db.remote_visits)",
319 2,
320 DB::DistributedProductMode::GLOBAL,
321 true
322 },
323
324 {
325 __LINE__,
326 "SELECT count() FROM test.visits_all WHERE UserID NOT IN (SELECT UserID FROM test.visits_all)",
327 "SELECT count() FROM test.visits_all WHERE UserID NOT IN (SELECT UserID FROM remote_db.remote_visits)",
328 2,
329 DB::DistributedProductMode::LOCAL,
330 true
331 },
332
333 {
334 __LINE__,
335 "SELECT count() FROM test.visits_all WHERE UserID NOT IN (SELECT UserID FROM remote_db.remote_visits)",
336 "SELECT count() FROM test.visits_all WHERE UserID NOT IN (SELECT UserID FROM remote_db.remote_visits)",
337 2,
338 DB::DistributedProductMode::LOCAL,
339 true
340 },
341
342 /// Section GLOBAL IN / depth 1
343
344 {
345 __LINE__,
346 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM test.visits_all)",
347 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM test.visits_all)",
348 2,
349 DB::DistributedProductMode::ALLOW,
350 true
351 },
352
353 {
354 __LINE__,
355 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM test.visits_all)",
356 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM test.visits_all)",
357 2,
358 DB::DistributedProductMode::DENY,
359 true
360 },
361
362 {
363 __LINE__,
364 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM test.visits_all)",
365 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM test.visits_all)",
366 2,
367 DB::DistributedProductMode::GLOBAL,
368 true
369 },
370
371 {
372 __LINE__,
373 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM test.visits_all)",
374 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM test.visits_all)",
375 2,
376 DB::DistributedProductMode::LOCAL,
377 true
378 },
379
380 /// Section GLOBAL NOT IN / depth 1
381
382 {
383 __LINE__,
384 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL NOT IN (SELECT UserID FROM test.visits_all)",
385 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL NOT IN (SELECT UserID FROM test.visits_all)",
386 2,
387 DB::DistributedProductMode::ALLOW,
388 true
389 },
390
391 {
392 __LINE__,
393 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL NOT IN (SELECT UserID FROM test.visits_all)",
394 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL NOT IN (SELECT UserID FROM test.visits_all)",
395 2,
396 DB::DistributedProductMode::DENY,
397 true
398 },
399
400 {
401 __LINE__,
402 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL NOT IN (SELECT UserID FROM test.visits_all)",
403 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL NOT IN (SELECT UserID FROM test.visits_all)",
404 2,
405 DB::DistributedProductMode::GLOBAL,
406 true
407 },
408
409 {
410 __LINE__,
411 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL NOT IN (SELECT UserID FROM test.visits_all)",
412 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL NOT IN (SELECT UserID FROM test.visits_all)",
413 2,
414 DB::DistributedProductMode::LOCAL,
415 true
416 },
417
418 /// Section JOIN / depth 1
419
420 {
421 __LINE__,
422 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM test.visits_all) USING UserID",
423 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM test.visits_all) USING UserID",
424 2,
425 DB::DistributedProductMode::ALLOW,
426 true
427 },
428
429 {
430 __LINE__,
431 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM test.visits_all) USING UserID",
432 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM test.visits_all) USING UserID",
433 2,
434 DB::DistributedProductMode::DENY,
435 false
436 },
437
438 {
439 __LINE__,
440 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM test.visits_all) USING UserID",
441 "SELECT UserID FROM test.visits_all GLOBAL ALL INNER JOIN (SELECT UserID FROM test.visits_all) USING UserID",
442 2,
443 DB::DistributedProductMode::GLOBAL,
444 true
445 },
446
447 {
448 __LINE__,
449 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM remote_db.remote_visits) USING UserID",
450 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM remote_db.remote_visits) USING UserID",
451 2,
452 DB::DistributedProductMode::GLOBAL,
453 true
454 },
455
456 {
457 __LINE__,
458 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM test.visits_all) USING UserID",
459 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM remote_db.remote_visits) USING UserID",
460 2,
461 DB::DistributedProductMode::LOCAL,
462 true
463 },
464
465 {
466 __LINE__,
467 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM remote_db.remote_visits) USING UserID",
468 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM remote_db.remote_visits) USING UserID",
469 2,
470 DB::DistributedProductMode::LOCAL,
471 true
472 },
473
474 /// Section GLOBAL JOIN / depth 1
475
476 {
477 __LINE__,
478 "SELECT UserID FROM test.visits_all GLOBAL ALL INNER JOIN (SELECT UserID FROM test.visits_all) USING UserID",
479 "SELECT UserID FROM test.visits_all GLOBAL ALL INNER JOIN (SELECT UserID FROM test.visits_all) USING UserID",
480 2,
481 DB::DistributedProductMode::ALLOW,
482 true
483 },
484
485 {
486 __LINE__,
487 "SELECT UserID FROM test.visits_all GLOBAL ALL INNER JOIN (SELECT UserID FROM test.visits_all) USING UserID",
488 "SELECT UserID FROM test.visits_all GLOBAL ALL INNER JOIN (SELECT UserID FROM test.visits_all) USING UserID",
489 2,
490 DB::DistributedProductMode::DENY,
491 true
492 },
493
494 {
495 __LINE__,
496 "SELECT UserID FROM test.visits_all GLOBAL ALL INNER JOIN (SELECT UserID FROM test.visits_all) USING UserID",
497 "SELECT UserID FROM test.visits_all GLOBAL ALL INNER JOIN (SELECT UserID FROM test.visits_all) USING UserID",
498 2,
499 DB::DistributedProductMode::GLOBAL,
500 true
501 },
502
503 {
504 __LINE__,
505 "SELECT UserID FROM test.visits_all GLOBAL ALL INNER JOIN (SELECT UserID FROM test.visits_all) USING UserID",
506 "SELECT UserID FROM test.visits_all GLOBAL ALL INNER JOIN (SELECT UserID FROM test.visits_all) USING UserID",
507 2,
508 DB::DistributedProductMode::LOCAL,
509 true
510 },
511
512 /// Section JOIN / depth 1 / 2 of the subquery.
513
514 {
515 __LINE__,
516 "SELECT UserID FROM (SELECT UserID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE RegionID = 2) USING UserID",
517 "SELECT UserID FROM (SELECT UserID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE RegionID = 2) USING UserID",
518 2,
519 DB::DistributedProductMode::ALLOW,
520 true
521 },
522
523 {
524 __LINE__,
525 "SELECT UserID FROM (SELECT UserID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT UserID FROM remote_db.remote_visits WHERE RegionID = 2) USING UserID",
526 "SELECT UserID FROM (SELECT UserID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT UserID FROM remote_db.remote_visits WHERE RegionID = 2) USING UserID",
527 2,
528 DB::DistributedProductMode::DENY,
529 true
530 },
531
532 {
533 __LINE__,
534 "SELECT UserID FROM (SELECT UserID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE RegionID = 2) USING UserID",
535 "SELECT UserID FROM (SELECT UserID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE RegionID = 2) USING UserID",
536 2,
537 DB::DistributedProductMode::DENY,
538 true
539 },
540
541 {
542 __LINE__,
543 "SELECT UserID FROM (SELECT UserID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE RegionID = 2) USING UserID",
544 "SELECT UserID FROM (SELECT UserID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE RegionID = 2) USING UserID",
545 2,
546 DB::DistributedProductMode::GLOBAL,
547 true
548 },
549
550 {
551 __LINE__,
552 "SELECT UserID FROM (SELECT UserID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE RegionID = 2) USING UserID",
553 "SELECT UserID FROM (SELECT UserID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE RegionID = 2) USING UserID",
554 2,
555 DB::DistributedProductMode::LOCAL,
556 true
557 },
558
559 /// Section IN / depth 1 / table at level 2
560
561 {
562 __LINE__,
563 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all))",
564 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all))",
565 2,
566 DB::DistributedProductMode::ALLOW,
567 true
568 },
569
570 {
571 __LINE__,
572 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all))",
573 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all))",
574 2,
575 DB::DistributedProductMode::DENY,
576 false
577 },
578
579 {
580 __LINE__,
581 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all))",
582 "SELECT count() FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all))",
583 2,
584 DB::DistributedProductMode::GLOBAL,
585 true
586 },
587
588 {
589 __LINE__,
590 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM (SELECT UserID FROM remote_db.remote_visits))",
591 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM (SELECT UserID FROM remote_db.remote_visits))",
592 2,
593 DB::DistributedProductMode::GLOBAL,
594 true
595 },
596
597 {
598 __LINE__,
599 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all))",
600 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM (SELECT UserID FROM remote_db.remote_visits))",
601 2,
602 DB::DistributedProductMode::LOCAL,
603 true
604 },
605
606 {
607 __LINE__,
608 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM (SELECT UserID FROM remote_db.remote_visits))",
609 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM (SELECT UserID FROM remote_db.remote_visits))",
610 2,
611 DB::DistributedProductMode::LOCAL,
612 true
613 },
614
615 /// Section GLOBAL IN / depth 1 / table at level 2
616
617 {
618 __LINE__,
619 "SELECT UserID FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all))",
620 "SELECT UserID FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all))",
621 2,
622 DB::DistributedProductMode::ALLOW,
623 true
624 },
625
626 {
627 __LINE__,
628 "SELECT UserID FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all))",
629 "SELECT UserID FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all))",
630 2,
631 DB::DistributedProductMode::DENY,
632 true
633 },
634
635 {
636 __LINE__,
637 "SELECT UserID FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all))",
638 "SELECT UserID FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all))",
639 2,
640 DB::DistributedProductMode::GLOBAL,
641 true
642 },
643
644 {
645 __LINE__,
646 "SELECT UserID FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all))",
647 "SELECT UserID FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all))",
648 2,
649 DB::DistributedProductMode::LOCAL,
650 true
651 },
652
653 /// Section IN at level 1, GLOBAL IN section at level 2.
654
655 {
656 __LINE__,
657 "SELECT UserID FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote_db.remote_visits WHERE UserID GLOBAL IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all)))",
658 "SELECT UserID FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote_db.remote_visits WHERE UserID GLOBAL IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all)))",
659 2,
660 DB::DistributedProductMode::ALLOW,
661 true
662 },
663
664 {
665 __LINE__,
666 "SELECT UserID FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote_db.remote_visits WHERE UserID GLOBAL IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all)))",
667 "SELECT UserID FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote_db.remote_visits WHERE UserID GLOBAL IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all)))",
668 2,
669 DB::DistributedProductMode::DENY,
670 false
671 },
672
673 {
674 __LINE__,
675 "SELECT UserID FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote_db.remote_visits WHERE UserID GLOBAL IN (SELECT UserID FROM (SELECT UserID FROM test.visits_all)))",
676 "SELECT UserID FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote_db.remote_visits WHERE UserID GLOBAL IN (SELECT UserID FROM (SELECT UserID FROM remote_db.remote_visits)))",
677 2,
678 DB::DistributedProductMode::LOCAL,
679 true
680 },
681
682 /// Section JOIN / depth 1 / table at level 2
683
684 {
685 __LINE__,
686 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM (SELECT UserID FROM test.visits_all)) USING UserID",
687 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM (SELECT UserID FROM test.visits_all)) USING UserID",
688 2,
689 DB::DistributedProductMode::ALLOW,
690 true
691 },
692
693 {
694 __LINE__,
695 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM (SELECT UserID FROM test.visits_all)) USING UserID",
696 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM (SELECT UserID FROM test.visits_all)) USING UserID",
697 2,
698 DB::DistributedProductMode::DENY,
699 false
700 },
701
702 {
703 __LINE__,
704 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM (SELECT UserID FROM test.visits_all)) USING UserID",
705 "SELECT UserID FROM test.visits_all GLOBAL ALL INNER JOIN (SELECT UserID FROM (SELECT UserID FROM test.visits_all)) USING UserID",
706 2,
707 DB::DistributedProductMode::GLOBAL,
708 true
709 },
710
711 {
712 __LINE__,
713 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM (SELECT UserID FROM remote_db.remote_visits)) USING UserID",
714 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM (SELECT UserID FROM remote_db.remote_visits)) USING UserID",
715 2,
716 DB::DistributedProductMode::GLOBAL,
717 true
718 },
719
720 {
721 __LINE__,
722 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM (SELECT UserID FROM test.visits_all)) USING UserID",
723 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM (SELECT UserID FROM remote_db.remote_visits)) USING UserID",
724 2,
725 DB::DistributedProductMode::LOCAL,
726 true
727 },
728
729 {
730 __LINE__,
731 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM (SELECT UserID FROM remote_db.remote_visits)) USING UserID",
732 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM (SELECT UserID FROM remote_db.remote_visits)) USING UserID",
733 2,
734 DB::DistributedProductMode::LOCAL,
735 true
736 },
737
738 /// Section IN / depth 2
739
740 {
741 __LINE__,
742 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM test.visits_all WHERE BrowserID IN (SELECT BrowserID FROM test.visits_all WHERE OtherID = 1))",
743 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM test.visits_all WHERE BrowserID IN (SELECT BrowserID FROM test.visits_all WHERE OtherID = 1))",
744 2,
745 DB::DistributedProductMode::ALLOW,
746 true
747 },
748
749 {
750 __LINE__,
751 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID GLOBAL IN (SELECT CounterID FROM test.visits_all WHERE BrowserID IN (SELECT BrowserID FROM remote_db.remote_visits WHERE OtherID = 1))",
752 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID GLOBAL IN (SELECT CounterID FROM test.visits_all WHERE BrowserID IN (SELECT BrowserID FROM remote_db.remote_visits WHERE OtherID = 1))",
753 2,
754 DB::DistributedProductMode::DENY,
755 true
756 },
757
758 {
759 __LINE__,
760 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM test.visits_all WHERE BrowserID IN (SELECT BrowserID FROM test.visits_all WHERE OtherID = 1))",
761 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM test.visits_all WHERE BrowserID IN (SELECT BrowserID FROM test.visits_all WHERE OtherID = 1))",
762 2,
763 DB::DistributedProductMode::DENY,
764 false
765 },
766
767 {
768 __LINE__,
769 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID GLOBAL IN (SELECT CounterID FROM test.visits_all WHERE BrowserID IN (SELECT BrowserID FROM test.visits_all WHERE OtherID = 1))",
770 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID GLOBAL IN (SELECT CounterID FROM test.visits_all WHERE BrowserID IN (SELECT BrowserID FROM test.visits_all WHERE OtherID = 1))",
771 2,
772 DB::DistributedProductMode::DENY,
773 true
774 },
775
776 {
777 __LINE__,
778 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM test.visits_all WHERE BrowserID IN (SELECT BrowserID FROM remote_db.remote_visits WHERE OtherID = 1))",
779 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID GLOBAL IN (SELECT CounterID FROM test.visits_all WHERE BrowserID IN (SELECT BrowserID FROM remote_db.remote_visits WHERE OtherID = 1))",
780 2,
781 DB::DistributedProductMode::GLOBAL,
782 true
783 },
784
785 {
786 __LINE__,
787 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM test.visits_all WHERE BrowserID IN (SELECT BrowserID FROM test.visits_all WHERE OtherID = 1))",
788 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM remote_db.remote_visits WHERE BrowserID IN (SELECT BrowserID FROM remote_db.remote_visits WHERE OtherID = 1))",
789 2,
790 DB::DistributedProductMode::LOCAL,
791 true
792 },
793
794 {
795 __LINE__,
796 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID GLOBAL IN (SELECT CounterID FROM test.visits_all WHERE BrowserID IN (SELECT BrowserID FROM test.visits_all WHERE OtherID = 1))",
797 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID GLOBAL IN (SELECT CounterID FROM test.visits_all WHERE BrowserID IN (SELECT BrowserID FROM test.visits_all WHERE OtherID = 1))",
798 2,
799 DB::DistributedProductMode::LOCAL,
800 true
801 },
802
803 {
804 __LINE__,
805 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM test.visits_all WHERE BrowserID IN (SELECT BrowserID FROM remote_db.remote_visits WHERE OtherID = 1))",
806 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM remote_db.remote_visits WHERE BrowserID IN (SELECT BrowserID FROM remote_db.remote_visits WHERE OtherID = 1))",
807 2,
808 DB::DistributedProductMode::LOCAL,
809 true
810 },
811
812 /// Section JOIN / depth 2
813
814 {
815 __LINE__,
816 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM test.visits_all ALL INNER JOIN (SELECT CounterID FROM (SELECT CounterID FROM test.visits_all)) USING CounterID)",
817 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM test.visits_all ALL INNER JOIN (SELECT CounterID FROM (SELECT CounterID FROM test.visits_all)) USING CounterID)",
818 2,
819 DB::DistributedProductMode::ALLOW,
820 true
821 },
822
823 {
824 __LINE__,
825 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID GLOBAL IN (SELECT CounterID FROM test.visits_all ALL INNER JOIN (SELECT CounterID FROM (SELECT CounterID FROM remote_db.remote_visits)) USING CounterID)",
826 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID GLOBAL IN (SELECT CounterID FROM test.visits_all ALL INNER JOIN (SELECT CounterID FROM (SELECT CounterID FROM remote_db.remote_visits)) USING CounterID)",
827 2,
828 DB::DistributedProductMode::DENY,
829 true
830 },
831
832 {
833 __LINE__,
834 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID GLOBAL IN (SELECT CounterID FROM test.visits_all ALL INNER JOIN (SELECT CounterID FROM (SELECT CounterID FROM test.visits_all)) USING CounterID)",
835 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID GLOBAL IN (SELECT CounterID FROM test.visits_all ALL INNER JOIN (SELECT CounterID FROM (SELECT CounterID FROM test.visits_all)) USING CounterID)",
836 2,
837 DB::DistributedProductMode::DENY,
838 true
839 },
840
841 {
842 __LINE__,
843 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM test.visits_all ALL INNER JOIN (SELECT CounterID FROM (SELECT CounterID FROM test.visits_all)) USING CounterID)",
844 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID GLOBAL IN (SELECT CounterID FROM test.visits_all GLOBAL ALL INNER JOIN (SELECT CounterID FROM (SELECT CounterID FROM test.visits_all)) USING CounterID)",
845 2,
846 DB::DistributedProductMode::GLOBAL,
847 true
848 },
849
850 {
851 __LINE__,
852 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM test.visits_all ALL INNER JOIN (SELECT CounterID FROM (SELECT CounterID FROM test.visits_all)) USING CounterID)",
853 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM remote_db.remote_visits ALL INNER JOIN (SELECT CounterID FROM (SELECT CounterID FROM remote_db.remote_visits)) USING CounterID)",
854 2,
855 DB::DistributedProductMode::LOCAL,
856 true
857 },
858
859 /// Section JOIN / depth 2
860
861 {
862 __LINE__,
863 "SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM (SELECT OtherID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2) USING OtherID)",
864 "SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM (SELECT OtherID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2) USING OtherID)",
865 2,
866 DB::DistributedProductMode::ALLOW,
867 true
868 },
869
870 {
871 __LINE__,
872 "SELECT UserID FROM test.visits_all WHERE OtherID GLOBAL IN (SELECT OtherID FROM (SELECT OtherID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT OtherID FROM remote_db.remote_visits WHERE RegionID = 2) USING OtherID)",
873 "SELECT UserID FROM test.visits_all WHERE OtherID GLOBAL IN (SELECT OtherID FROM (SELECT OtherID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT OtherID FROM remote_db.remote_visits WHERE RegionID = 2) USING OtherID)",
874 2,
875 DB::DistributedProductMode::DENY,
876 true
877 },
878
879 {
880 __LINE__,
881 "SELECT UserID FROM test.visits_all WHERE OtherID GLOBAL IN (SELECT OtherID FROM (SELECT OtherID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2) USING OtherID)",
882 "SELECT UserID FROM test.visits_all WHERE OtherID GLOBAL IN (SELECT OtherID FROM (SELECT OtherID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2) USING OtherID)",
883 2,
884 DB::DistributedProductMode::DENY,
885 true
886 },
887
888 {
889 __LINE__,
890 "SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM (SELECT OtherID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2) USING OtherID)",
891 "SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM (SELECT OtherID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2) USING OtherID)",
892 2,
893 DB::DistributedProductMode::DENY,
894 false
895 },
896
897 {
898 __LINE__,
899 "SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM (SELECT OtherID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2) USING OtherID)",
900 "SELECT UserID FROM test.visits_all WHERE OtherID GLOBAL IN (SELECT OtherID FROM (SELECT OtherID FROM test.visits_all WHERE RegionID = 1) GLOBAL ALL INNER JOIN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2) USING OtherID)",
901 2,
902 DB::DistributedProductMode::GLOBAL,
903 true
904 },
905
906 {
907 __LINE__,
908 "SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM (SELECT OtherID FROM test.visits_all WHERE RegionID = 1) ALL INNER JOIN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2) USING OtherID)",
909 "SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM (SELECT OtherID FROM remote_db.remote_visits WHERE RegionID = 1) ALL INNER JOIN (SELECT OtherID FROM remote_db.remote_visits WHERE RegionID = 2) USING OtherID)",
910 2,
911 DB::DistributedProductMode::LOCAL,
912 true
913 },
914
915 /// Section JOIN / section IN
916
917 {
918 __LINE__,
919 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2)) USING UserID",
920 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2)) USING UserID",
921 2,
922 DB::DistributedProductMode::ALLOW,
923 true
924 },
925
926 {
927 __LINE__,
928 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2)) USING UserID",
929 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2)) USING UserID",
930 2,
931 DB::DistributedProductMode::DENY,
932 false
933 },
934
935 {
936 __LINE__,
937 "SELECT UserID FROM test.visits_all GLOBAL ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2)) USING UserID",
938 "SELECT UserID FROM test.visits_all GLOBAL ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2)) USING UserID",
939 2,
940 DB::DistributedProductMode::DENY,
941 true
942 },
943
944 {
945 __LINE__,
946 "SELECT UserID FROM test.visits_all GLOBAL ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM remote_db.remote_visits WHERE RegionID = 2)) USING UserID",
947 "SELECT UserID FROM test.visits_all GLOBAL ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM remote_db.remote_visits WHERE RegionID = 2)) USING UserID",
948 2,
949 DB::DistributedProductMode::DENY,
950 true
951 },
952
953 {
954 __LINE__,
955 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM test.visits_all WHERE OtherID IN (SELECT OtherID FROM test.visits_all WHERE RegionID = 2)) USING UserID",
956 "SELECT UserID FROM test.visits_all ALL INNER JOIN (SELECT UserID FROM remote_db.remote_visits WHERE OtherID IN (SELECT OtherID FROM remote_db.remote_visits WHERE RegionID = 2)) USING UserID",
957 2,
958 DB::DistributedProductMode::LOCAL,
959 true
960 },
961
962 /// Table function.
963
964 {
965 __LINE__,
966 "SELECT count() FROM remote('127.0.0.{1,2}', test, visits_all) WHERE UserID IN (SELECT UserID FROM test.visits_all)",
967 "SELECT count() FROM remote('127.0.0.{1,2}', test, visits_all) WHERE UserID IN (SELECT UserID FROM test.visits_all)",
968 2,
969 DB::DistributedProductMode::ALLOW,
970 true
971 },
972
973 {
974 __LINE__,
975 "SELECT count() FROM remote('127.0.0.{1,2}', test, visits_all) WHERE UserID IN (SELECT UserID FROM test.visits_all)",
976 "SELECT count() FROM remote('127.0.0.{1,2}', test, visits_all) WHERE UserID IN (SELECT UserID FROM test.visits_all)",
977 2,
978 DB::DistributedProductMode::DENY,
979 true
980 },
981
982 {
983 __LINE__,
984 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote('127.0.0.{1,2}', test, visits_all))",
985 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote('127.0.0.{1,2}', test, visits_all))",
986 2,
987 DB::DistributedProductMode::DENY,
988 true
989 },
990
991 {
992 __LINE__,
993 "SELECT count() FROM remote('127.0.0.{1,2}', test, visits_all) WHERE UserID IN (SELECT UserID FROM test.visits_all)",
994 "SELECT count() FROM remote('127.0.0.{1,2}', test, visits_all) WHERE UserID IN (SELECT UserID FROM test.visits_all)",
995 2,
996 DB::DistributedProductMode::GLOBAL,
997 true
998 },
999
1000 {
1001 __LINE__,
1002 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote('127.0.0.{1,2}', test, visits_all))",
1003 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote('127.0.0.{1,2}', test, visits_all))",
1004 2,
1005 DB::DistributedProductMode::GLOBAL,
1006 true
1007 },
1008
1009 {
1010 __LINE__,
1011 "SELECT count() FROM remote('127.0.0.{1,2}', test, visits_all) WHERE UserID IN (SELECT UserID FROM test.visits_all)",
1012 "SELECT count() FROM remote('127.0.0.{1,2}', test, visits_all) WHERE UserID IN (SELECT UserID FROM test.visits_all)",
1013 2,
1014 DB::DistributedProductMode::LOCAL,
1015 true
1016 },
1017
1018 {
1019 __LINE__,
1020 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote('127.0.0.{1,2}', test, visits_all))",
1021 "SELECT count() FROM test.visits_all WHERE UserID IN (SELECT UserID FROM remote('127.0.0.{1,2}', test, visits_all))",
1022 2,
1023 DB::DistributedProductMode::LOCAL,
1024 true
1025 },
1026
1027 /// Section IN / depth 2 / two distributed tables
1028
1029 {
1030 __LINE__,
1031 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM test.hits_all WHERE BrowserID IN (SELECT BrowserID FROM test.visits_all WHERE OtherID = 1))",
1032 "SELECT UserID, RegionID FROM test.visits_all WHERE CounterID IN (SELECT CounterID FROM distant_db.distant_hits WHERE BrowserID IN (SELECT BrowserID FROM remote_db.remote_visits WHERE OtherID = 1))",
1033 2,
1034 DB::DistributedProductMode::LOCAL,
1035 true
1036 },
1037
1038 /// Aggregate function.
1039
1040 {
1041 __LINE__,
1042 "SELECT sum(RegionID IN (SELECT RegionID from test.hits_all)) FROM test.visits_all",
1043 "SELECT sum(RegionID IN (SELECT RegionID from test.hits_all)) FROM test.visits_all",
1044 2,
1045 DB::DistributedProductMode::ALLOW,
1046 true
1047 },
1048
1049 {
1050 __LINE__,
1051 "SELECT sum(RegionID IN (SELECT RegionID from test.hits_all)) FROM test.visits_all",
1052 "SELECT sum(RegionID IN (SELECT RegionID from test.hits_all)) FROM test.visits_all",
1053 2,
1054 DB::DistributedProductMode::DENY,
1055 false
1056 },
1057
1058 {
1059 __LINE__,
1060 "SELECT sum(RegionID GLOBAL IN (SELECT RegionID from test.hits_all)) FROM test.visits_all",
1061 "SELECT sum(RegionID GLOBAL IN (SELECT RegionID from test.hits_all)) FROM test.visits_all",
1062 2,
1063 DB::DistributedProductMode::DENY,
1064 true
1065 },
1066
1067 {
1068 __LINE__,
1069 "SELECT sum(RegionID IN (SELECT RegionID from test.hits_all)) FROM test.visits_all",
1070 "SELECT sum(RegionID GLOBAL IN (SELECT RegionID from test.hits_all)) FROM test.visits_all",
1071 2,
1072 DB::DistributedProductMode::GLOBAL,
1073 true
1074 },
1075
1076 {
1077 __LINE__,
1078 "SELECT sum(RegionID GLOBAL IN (SELECT RegionID from test.hits_all)) FROM test.visits_all",
1079 "SELECT sum(RegionID GLOBAL IN (SELECT RegionID from test.hits_all)) FROM test.visits_all",
1080 2,
1081 DB::DistributedProductMode::LOCAL,
1082 true
1083 },
1084
1085 {
1086 __LINE__,
1087 "SELECT sum(RegionID IN (SELECT RegionID from test.hits_all)) FROM test.visits_all",
1088 "SELECT sum(RegionID IN (SELECT RegionID from distant_db.distant_hits)) FROM test.visits_all",
1089 2,
1090 DB::DistributedProductMode::LOCAL,
1091 true
1092 },
1093
1094 /// Miscellaneous.
1095
1096 {
1097 __LINE__,
1098 "SELECT count() FROM test.visits_all WHERE x GLOBAL IN (SELECT x FROM test.visits_all WHERE x GLOBAL IN (SELECT x FROM test.visits_all))",
1099 "SELECT count() FROM test.visits_all WHERE x GLOBAL IN (SELECT x FROM test.visits_all WHERE x GLOBAL IN (SELECT x FROM test.visits_all))",
1100 2,
1101 DB::DistributedProductMode::DENY,
1102 true
1103 },
1104
1105 {
1106 __LINE__,
1107 "SELECT count() FROM test.visits_all WHERE x GLOBAL IN (SELECT x FROM test.visits_all WHERE x GLOBAL IN (SELECT x FROM test.visits_all))",
1108 "SELECT count() FROM test.visits_all WHERE x GLOBAL IN (SELECT x FROM test.visits_all WHERE x GLOBAL IN (SELECT x FROM test.visits_all))",
1109 2,
1110 DB::DistributedProductMode::LOCAL,
1111 true
1112 },
1113
1114 {
1115 __LINE__,
1116 "SELECT count() FROM test.visits_all WHERE x GLOBAL IN (SELECT x FROM test.visits_all WHERE x GLOBAL IN (SELECT x FROM test.visits_all))",
1117 "SELECT count() FROM test.visits_all WHERE x GLOBAL IN (SELECT x FROM test.visits_all WHERE x GLOBAL IN (SELECT x FROM test.visits_all))",
1118 2,
1119 DB::DistributedProductMode::GLOBAL,
1120 true
1121 },
1122
1123 {
1124 __LINE__,
1125 "SELECT UserID FROM (SELECT UserID FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM test.hits_all))",
1126 "SELECT UserID FROM (SELECT UserID FROM test.visits_all WHERE UserID GLOBAL IN (SELECT UserID FROM test.hits_all))",
1127 2,
1128 DB::DistributedProductMode::DENY,
1129 true
1130 }
1131};
1132
1133
1134static bool run()
1135{
1136 unsigned int count = 0;
1137 unsigned int i = 1;
1138
1139 for (const auto & entry : entries)
1140 {
1141 auto res = check(entry);
1142 if (res.first)
1143 {
1144 ++count;
1145 }
1146 else
1147 std::cout << "Test " << i << " at line " << entry.line_num << " failed.\n"
1148 "Expected: " << entry.expected_output << ".\n"
1149 "Received: " << res.second << "\n";
1150
1151 ++i;
1152 }
1153
1154 std::cout << count << " out of " << entries.size() << " test(s) passed.\n";
1155
1156 return count == entries.size();
1157}
1158
1159
1160TestResult check(const TestEntry & entry)
1161{
1162 static DB::Context context = DB::Context::createGlobal();
1163 context.makeGlobalContext();
1164
1165 try
1166 {
1167
1168 auto storage_distributed_visits = StorageDistributedFake::create("remote_db", "remote_visits", entry.shard_count);
1169 auto storage_distributed_hits = StorageDistributedFake::create("distant_db", "distant_hits", entry.shard_count);
1170
1171 DB::DatabasePtr database = std::make_shared<DB::DatabaseOrdinary>("test", "./metadata/test/", context);
1172 context.addDatabase("test", database);
1173 database->attachTable("visits_all", storage_distributed_visits);
1174 database->attachTable("hits_all", storage_distributed_hits);
1175 context.setCurrentDatabase("test");
1176
1177 auto & settings = context.getSettingsRef();
1178 settings.distributed_product_mode = entry.mode;
1179
1180 /// Parse and process the incoming query.
1181 DB::ASTPtr ast_input;
1182 if (!parse(ast_input, entry.input))
1183 return TestResult(false, "parse error");
1184
1185 bool success = true;
1186
1187 try
1188 {
1189 DB::InJoinSubqueriesPreprocessor(context, std::make_unique<CheckShardsAndTablesMock>()).visit(ast_input);
1190 }
1191 catch (const DB::Exception & ex)
1192 {
1193 if (ex.code() == DB::ErrorCodes::DISTRIBUTED_IN_JOIN_SUBQUERY_DENIED)
1194 success = false;
1195 else
1196 throw;
1197 }
1198 catch (...)
1199 {
1200 throw;
1201 }
1202
1203 if (success != entry.expected_success)
1204 return TestResult(false, "unexpected result");
1205
1206 /// Parse the expected result.
1207 DB::ASTPtr ast_expected;
1208 if (!parse(ast_expected, entry.expected_output))
1209 return TestResult(false, "parse error");
1210
1211 /// Compare the processed query and the expected result.
1212 bool res = equals(ast_input, ast_expected);
1213 std::string output = DB::queryToString(ast_input);
1214
1215 context.detachDatabase("test");
1216 return TestResult(res, output);
1217 }
1218 catch (DB::Exception & e)
1219 {
1220 context.detachDatabase("test");
1221 return TestResult(false, e.displayText());
1222 }
1223}
1224
1225bool parse(DB::ASTPtr & ast, const std::string & query)
1226{
1227 DB::ParserSelectQuery parser;
1228 std::string message;
1229 auto begin = query.data();
1230 auto end = begin + query.size();
1231 ast = DB::tryParseQuery(parser, begin, end, message, false, "", false, 0);
1232 return ast != nullptr;
1233}
1234
1235bool equals(const DB::ASTPtr & lhs, const DB::ASTPtr & rhs)
1236{
1237 DB::ASTPtr lhs_reordered = lhs->clone();
1238 reorder(&*lhs_reordered);
1239
1240 DB::ASTPtr rhs_reordered = rhs->clone();
1241 reorder(&*rhs_reordered);
1242
1243 return lhs_reordered->getTreeHash() == rhs_reordered->getTreeHash();
1244}
1245
1246void reorder(DB::IAST * ast)
1247{
1248 if (ast == nullptr)
1249 return;
1250
1251 auto & children = ast->children;
1252 if (children.empty())
1253 return;
1254
1255 for (auto & child : children)
1256 reorder(&*child);
1257
1258 std::sort(children.begin(), children.end(), [](const DB::ASTPtr & lhs, const DB::ASTPtr & rhs)
1259 {
1260 return lhs->getTreeHash() < rhs->getTreeHash();
1261 });
1262}
1263
1264int main()
1265{
1266 return run() ? EXIT_SUCCESS : EXIT_FAILURE;
1267}
1268