Left outter join on pragma tables acts as inner join
(1) By anonymous on 2021-12-29 20:38:42 [source]
Hi,
I'm trying to select a column and it's foreign key constraints from the relevant pragma tables:
sqlite> CREATE TABLE t1 (id INTEGER);
sqlite> CREATE TABLE t2 (id INTEGER, ref INTEGER REFERENCES t1(id));
sqlite> SELECT * FROM pragma_table_info("t2") m LEFT OUTER JOIN pragma_foreign_key_list("t2") fk ON m.name = fk."from";
1|ref|INTEGER|0||0|0|0|t1|ref|id|NO ACTION|NO ACTION|NONE
I only get one column, ref, and I'm expecting to get both columns of t2; id and ref. If I do this instead:
sqlite> SELECT * FROM pragma_table_info("t2") m LEFT OUTER JOIN (SELECT * FROM pragma_foreign_key_list("t2")) fk ON m.name = fk."from";
0|id|INTEGER|0||0||||||||
1|ref|INTEGER|0||0|0|0|t1|ref|id|NO ACTION|NO ACTION|NONE
it works as expected.
I really don't know how to read sql's vm instructions, but I took a look EXPLAIN anyway and it seems to indicate at addr 19 that it's doing an inner join (if .from (addr 13) is not equal to "t2" goto next)
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 41 0 00 Start at 41
...
6 VFilter 0 40 1 00 iplan=r[1] zplan=''
...
11 VFilter 1 36 5 00 iplan=r[5] zplan=''
12 VColumn 0 1 8 00 r[8]=vcolumn(1); pragma_table_info.name
13 VColumn 1 3 9 00 r[9]=vcolumn(3); pragma_foreign_key_list.from
14 Eq 9 17 8 (BINARY) 41 if r[8]==r[9] goto 17
15 VColumn 1 3 9 00 r[9]=vcolumn(3); pragma_foreign_key_list.from
16 NotNull 9 35 0 00 if r[9]!=NULL goto 35
17 Integer 1 4 0 00 r[4]=1; record LEFT JOIN hit
18 VColumn 1 8 9 00 r[9]=vcolumn(8); pragma_foreign_key_list.arg
19 Ne 10 35 9 (BINARY) 53 if r[9]!=r[10] goto 35
20 VColumn 0 0 11 00 r[11]=vcolumn(0); pragma_table_info.cid
...
33 VColumn 1 7 24 00 r[24]=vcolumn(7); pragma_foreign_key_list.match
34 ResultRow 11 14 0 00 output=r[11..24]
35 VNext 1 12 0 00
36 IfPos 4 39 0 00 if r[4]>0 then r[4]-=0, goto 39
37 NullRow 1 0 0 00
38 Goto 0 17 0 00
39 VNext 0 7 0 00
40 Halt 0 0 0 00
41 Transaction 0 0 2 0 01 usesStmtJournal=0
42 String8 0 10 0 t2 00 r[10]='t2'
43 Goto 0 1 0 00
sqlite>
I couldn't find any documentation about pragma tables or outer joins that suggest doing this is disallowed.
I'm on version 3.28.0
Best, AP
(2) By Keith Medcalf (kmedcalf) on 2021-12-29 22:28:35 in reply to 1 [link] [source]
Text Strings are quoted with single quotes.
Identifiers are quoted with double quotes.
So, after fixing your quotations:
SQLite version 3.38.0 2021-12-29 10:11:34
sqlite> CREATE TABLE t1 (id INTEGER);
sqlite> CREATE TABLE t2 (id INTEGER, ref INTEGER REFERENCES t1(id));
sqlite> .eqp full
sqlite> SELECT * FROM pragma_table_info('t2') m LEFT OUTER JOIN pragma_foreign_key_list('t2') fk ON m.name = fk."from";
QUERY PLAN
|--SCAN m VIRTUAL TABLE INDEX 0: (~24 rows)
`--SCAN fk VIRTUAL TABLE INDEX 0: (~24 rows)
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 49 0 0 Start at 49
1 VOpen 0 0 0 vtab:1F556A2A250 0
2 VOpen 1 0 0 vtab:1F556A2A610 0
3 Explain 3 0 0 SCAN m VIRTUAL TABLE INDEX 0: (~24 rows) 0
4 Noop 0 0 0 0 Begin WHERE-loop0: pragma_table_info
5 String8 0 3 0 t2 0 r[3]='t2'
6 Integer 0 1 0 0 r[1]=0
7 Integer 1 2 0 0 r[2]=1
8 VFilter 0 47 1 0 iplan=r[1] zplan=''
9 Explain 9 0 0 SCAN fk VIRTUAL TABLE INDEX 0: (~24 rows) 0
10 Noop 0 0 0 0 Begin WHERE-loop1: pragma_foreign_key_list
11 Integer 0 4 0 0 r[4]=0; init LEFT JOIN no-match flag
12 String8 0 7 0 t2 0 r[7]='t2'
13 Integer 0 5 0 0 r[5]=0
14 Integer 1 6 0 0 r[6]=1
15 VFilter 1 42 5 0 iplan=r[5] zplan=''
16 VColumn 0 1 8 0 r[8]=vcolumn(1); pragma_table_info.name
17 VColumn 1 3 9 0 r[9]=vcolumn(3); pragma_foreign_key_list.from
18 Ne 9 41 8 BINARY-8 81 if r[8]!=r[9] goto 41
19 Integer 1 4 0 0 r[4]=1; record LEFT JOIN hit
20 Noop 0 0 0 0 Begin WHERE-core
21 VColumn 0 0 10 0 r[10]=vcolumn(0); pragma_table_info.cid
22 VColumn 0 1 11 0 r[11]=vcolumn(1); pragma_table_info.name
23 VColumn 0 2 12 0 r[12]=vcolumn(2); pragma_table_info.type
24 VColumn 0 3 13 0 r[13]=vcolumn(3); pragma_table_info.aff
25 VColumn 0 4 14 0 r[14]=vcolumn(4); pragma_table_info.coll
26 VColumn 0 5 15 0 r[15]=vcolumn(5); pragma_table_info.notnull
27 VColumn 0 6 16 0 r[16]=vcolumn(6); pragma_table_info.dflt_value
28 VColumn 0 7 17 0 r[17]=vcolumn(7); pragma_table_info.pk
29 VColumn 0 8 18 0 r[18]=vcolumn(8); pragma_table_info.rowid
30 VColumn 0 9 19 0 r[19]=vcolumn(9); pragma_table_info.autoinc
31 VColumn 1 0 20 0 r[20]=vcolumn(0); pragma_foreign_key_list.id
32 VColumn 1 1 21 0 r[21]=vcolumn(1); pragma_foreign_key_list.seq
33 VColumn 1 2 22 0 r[22]=vcolumn(2); pragma_foreign_key_list.table
34 VColumn 1 3 23 0 r[23]=vcolumn(3); pragma_foreign_key_list.from
35 VColumn 1 4 24 0 r[24]=vcolumn(4); pragma_foreign_key_list.to
36 VColumn 1 5 25 0 r[25]=vcolumn(5); pragma_foreign_key_list.on_update
37 VColumn 1 6 26 0 r[26]=vcolumn(6); pragma_foreign_key_list.on_delete
38 VColumn 1 7 27 0 r[27]=vcolumn(7); pragma_foreign_key_list.match
39 ResultRow 10 18 0 0 output=r[10..27]
40 Noop 0 0 0 0 End WHERE-core
41 VNext 1 16 0 0
42 IfPos 4 45 0 0 if r[4]>0 then r[4]-=0, goto 45
43 NullRow 1 0 0 0
44 Goto 0 19 0 0
45 Noop 0 0 0 0 End WHERE-loop1: pragma_foreign_key_list
46 VNext 0 9 0 0
47 Noop 0 0 0 0 End WHERE-loop0: pragma_table_info
48 Halt 0 0 0 0
49 Transaction 0 0 2 0 1 usesStmtJournal=0
50 Goto 0 1 0 0
┌─────┬──────┬─────────┬─────────┬──────┬─────────┬────────────┬────┬───────┬─────────┬────┬─────┬───────┬──────┬────┬───────────┬───────────┬───────┐
│ cid │ name │ type │ aff │ coll │ notnull │ dflt_value │ pk │ rowid │ autoinc │ id │ seq │ table │ from │ to │ on_update │ on_delete │ match │
├─────┼──────┼─────────┼─────────┼──────┼─────────┼────────────┼────┼───────┼─────────┼────┼─────┼───────┼──────┼────┼───────────┼───────────┼───────┤
│ 0 │ id │ INTEGER │ INTEGER │ │ 0 │ │ 0 │ 0 │ 0 │ │ │ │ │ │ │ │ │
│ 1 │ ref │ INTEGER │ INTEGER │ │ 0 │ │ 0 │ 0 │ 0 │ 0 │ 0 │ t1 │ ref │ id │ NO ACTION │ NO ACTION │ NONE │
└─────┴──────┴─────────┴─────────┴──────┴─────────┴────────────┴────┴───────┴─────────┴────┴─────┴───────┴──────┴────┴───────────┴───────────┴───────┘
sqlite>
Which would seem to indicate that you need a more recent version of SQLite3 where the bug that you have encountered has been fixed since your queries as posited function correctly on the current trunk.
NB My modification to the table_[x]info pragma returns much more information that the built-in implementation.
(3) By anonymous on 2021-12-30 16:17:32 in reply to 2 [link] [source]
Can confirm it works after upgrading to 3.37.0 (the latest version on brew atow)
Thanks for your help!