Debian Codegraph @

mysql-test/suite/ndb/r/ndb_bug30708263.result

raw
1 CREATE TABLE e (
2 pk int NOT NULL,
3 col_int_unique int DEFAULT NULL,
4 UNIQUE KEY ix1 (col_int_unique)
5 ) ENGINE=ndbcluster;
6 INSERT INTO e values (1, 12), (5, 20), (11, 8), (16, 10);
7 # If NDB join pushdown is enabled, the query should use nested loop
8 # instead of hash join (NDB expects joins to be executed using nested
9 # loop).
10 SET ndb_join_pushdown = on;
11 EXPLAIN FORMAT=tree SELECT STRAIGHT_JOIN
12 table1.pk, table2.pk, table3.pk
13 FROM e AS table1
14 LEFT JOIN e AS table2 ON table1.pk = table2.pk
15 LEFT JOIN e AS table3 ON table2.col_int_unique = table3.col_int_unique;
16 EXPLAIN
17 -> Nested loop left join (cost=1254 rows=16)
18 -> Left hash join (table2.pk = table1.pk) (cost=1252 rows=16)
19 -> Table scan on table1 (cost=1000 rows=4)
20 -> Hash
21 -> Table scan on table2 (cost=250 rows=4)
22 -> Single-row index lookup on table3 using ix1 (col_int_unique=table2.col_int_unique) (cost=0.0688 rows=1)
23
24 Warnings:
25 Note 1003 Can't push table 'table2' as child, 'type' must be a 'ref' access
26 Note 1003 Can't push table 'table3' as child of 'table1', it is in a hash-probe-branch which can't be referred.
27 Note 1003 Can't push table 'table3' as child of 'table2', it is in a hash-bucket-branch which can't be referred.
28 SELECT STRAIGHT_JOIN
29 table1.pk, table2.pk, table3.pk
30 FROM e AS table1
31 LEFT JOIN e AS table2 ON table1.pk = table2.pk
32 LEFT JOIN e AS table3 ON table2.col_int_unique = table3.col_int_unique;
33 pk pk pk
34 1 1 1
35 11 11 11
36 16 16 16
37 5 5 5
38 SET ndb_join_pushdown = off;
39 EXPLAIN FORMAT=tree SELECT STRAIGHT_JOIN
40 table1.pk, table2.pk, table3.pk
41 FROM e AS table1
42 LEFT JOIN e AS table2 ON table1.pk = table2.pk
43 LEFT JOIN e AS table3 ON table2.col_int_unique = table3.col_int_unique;
44 EXPLAIN
45 -> Nested loop left join (cost=1254 rows=16)
46 -> Left hash join (table2.pk = table1.pk) (cost=1252 rows=16)
47 -> Table scan on table1 (cost=1000 rows=4)
48 -> Hash
49 -> Table scan on table2 (cost=250 rows=4)
50 -> Single-row index lookup on table3 using ix1 (col_int_unique=table2.col_int_unique) (cost=0.0688 rows=1)
51
52 SELECT STRAIGHT_JOIN
53 table1.pk, table2.pk, table3.pk
54 FROM e AS table1
55 LEFT JOIN e AS table2 ON table1.pk = table2.pk
56 LEFT JOIN e AS table3 ON table2.col_int_unique = table3.col_int_unique;
57 pk pk pk
58 1 1 1
59 11 11 11
60 16 16 16
61 5 5 5
62 DROP TABLE e;