________________________________
Von: Hick Gunter
Gesendet: Montag, 03. Dezember 2012 15:28
An: 'Richard Hipp'
Betreff: AW: [sqlite] Invalid Code gemerated for Virtual Table Join with OR
clause
Thank you for your reply.
As indicated in the second message, I have located the code where the error
manifests itself in where.c and in the meantime also found a workaround.
The sqlite3OpenTable function generates nothing for a virtual table, so no
attempt to modify a P4 value should be made.
I think that only one VOpen per virtual table is required.
#ifndef SQLITE_OMIT_VIRTUALTABLE
if( (pLevel->plan.wsFlags & WHERE_VIRTUALTABLE)!=0 ){
const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
int iCur = pTabItem->iCursor;
sqlite3VdbeAddOp4(v, OP_VOpen, iCur, 0, 0, pVTab, P4_VTAB);
}else
#endif
if( (pLevel->plan.wsFlags & WHERE_IDX_ONLY)==0
&& (wctrlFlags & WHERE_OMIT_OPEN_CLOSE)==0 ){
int op = pWInfo->okOnePass ? OP_OpenWrite : OP_OpenRead;
sqlite3OpenTable(pParse, pTabItem->iCursor, iDb, pTab, op);
testcase( pTab->nCol==BMS-1 );
testcase( pTab->nCol==BMS );
if( !pWInfo->okOnePass && pTab->nCol<BMS && !IsVirtual(pTab) ){
Bitmask b = pTabItem->colUsed;
int n = 0;
for(; b; b=b>>1, n++){}
sqlite3VdbeChangeP4(v, sqlite3VdbeCurrentAddr(v)-1,
SQLITE_INT_TO_PTR(n), P4_INT32);
assert( n<=pTab->nCol );
}
}else{
sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
}
________________________________
Von: Richard Hipp [mailto:[email protected]]
Gesendet: Montag, 03. Dezember 2012 14:35
An: General Discussion of SQLite Database
Cc: Hick Gunter
Betreff: Re: [sqlite] Invalid Code gemerated for Virtual Table Join with OR
clause
On Mon, Dec 3, 2012 at 3:11 AM, Hick Gunter
<[email protected]<mailto:[email protected]>> wrote:
The following test case produces correct code for native tables, but
substituting virtual tables seems to clobber the P4 Keyinfo to be propagated to
OpenEphemeral at #59 by the spurious P4 to Goto at #8
Thank you for the bug report.
Nevertheless, please recognize that what you have provided us is not a "test
case", for at least two reasons:
(1) You only provided code that works, not code that fails
(2) You have not given us any data or expected results
Hence, we have no easy way to validate your claim that SQLite is
malfunctioning. We will look into this, but because our investigation will
involve a lot of reverse engineering trying to reconstruct the failure scenario
from your hints, the investigation will take place at a lower priority than it
would otherwise.
If you want, you can help us to expedite the investigation and resolution of
your issue by providing us with a true "test case" - that is to say, providing
us with some SQL that generates an incorrect result. Thanks.
create temp table a (f1 integer, f2 integer);
create temp table b (f1 integer, f2 integer);
create unique index ai on a(f1,f2);
create unique index bi on b(f1,f2);
select
cast (f1 as integer) f1,
cast (f2 as integer) f2
from (
select f1, f2
from a
where (f1 = 7 and f2 = 11) or
(f1 = 15 and f2 = 11)
union all
select f1, f2
from b
where (f1 = 7 and f2 = 11) or
(f1 = 15 and f2 = 11)
)
order by f1, f2;
generated code (difference best seen in a side by side comparison):
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00 NULL
1 Goto 0 137 0 00 NULL
2 Noop 0 0 0 00 Begin coroutine for
left SELECT
3 OpenEphemeral 3 4 0 keyinfo(2,BINARY,BINARY) 00 NULL
4 Integer 7 7 0 00 NULL
5 Integer 11 8 0 00 NULL
6 Integer 15 9 0 00 NULL
7 Integer 11 10 0 00 NULL
8 Goto 0 147 0 00 NULL
9 OpenRead 2 2 1 2 00 a
10 Null 0 12 0 00 NULL
11 Integer 32 11 0 00 NULL
12 OpenRead 4 4 1 keyinfo(2,BINARY,BINARY) 00 ai
13 SCopy 7 14 0 00 NULL
14 SCopy 8 15 0 00 NULL
15 SeekGe 4 22 14 2 00 NULL
16 IdxGE 4 22 14 2 01 NULL
17 IdxRowid 4 16 0 00 NULL
18 Seek 2 16 0 00 NULL
19 RowSetTest 12 21 16 0 00 NULL
20 Gosub 11 33 0 00 NULL
21 Next 4 16 0 00 NULL
22 OpenRead 5 4 1 keyinfo(2,BINARY,BINARY) 00 ai
23 SCopy 9 17 0 00 NULL
24 SCopy 10 18 0 00 NULL
25 SeekGe 5 32 17 2 00 NULL
26 IdxGE 5 32 17 2 01 NULL
27 IdxRowid 5 19 0 00 NULL
28 Seek 2 19 0 00 NULL
29 RowSetTest 12 31 19 -1 00 NULL
30 Gosub 11 33 0 00 NULL
31 Next 5 26 0 00 NULL
32 Goto 0 47 0 00 NULL
33 Column 2 0 20 00 a.f1
34 ToInt 20 0 0 00 NULL
35 Column 2 1 21 00 a.f2
36 ToInt 21 0 0 00 NULL
37 MakeRecord 20 2 22 00 NULL
38 Column 2 0 23 00 a.f1
39 ToInt 23 0 0 00 NULL
40 Column 2 1 24 00 a.f2
41 ToInt 24 0 0 00 NULL
42 Sequence 3 25 0 00 NULL
43 Move 22 26 1 00 NULL
44 MakeRecord 23 4 27 00 NULL
45 IdxInsert 3 27 0 00 NULL
46 Return 11 0 0 00 NULL
47 Close 2 0 0 00 NULL
48 OpenPseudo 6 22 2 00 NULL
49 Sort 3 55 0 00 NULL
50 Column 3 3 22 00 NULL
51 Column 6 0 20 20 NULL
52 Column 6 1 21 00 NULL
53 Yield 1 0 0 00 NULL
54 Next 3 50 0 00 NULL
55 Close 6 0 0 00 NULL
56 Integer 1 2 0 00 NULL
57 Yield 1 0 0 00 NULL
58 Noop 0 0 0 00 End coroutine for
left SELECT
59 Noop 0 0 0 00 Begin coroutine for
right SELECT
60 OpenEphemeral 7 4 0 keyinfo(2,BINARY,BINARY) 00 NULL
61 OpenRead 1 3 1 2 00 b
62 Null 0 29 0 00 NULL
63 Integer 84 28 0 00 NULL
64 OpenRead 8 5 1 keyinfo(2,BINARY,BINARY) 00 bi
65 Integer 7 31 0 00 NULL
66 Integer 11 32 0 00 NULL
67 SeekGe 8 74 31 2 00 NULL
68 IdxGE 8 74 31 2 01 NULL
69 IdxRowid 8 22 0 00 NULL
70 Seek 1 22 0 00 NULL
71 RowSetTest 29 73 22 0 00 NULL
72 Gosub 28 85 0 00 NULL
73 Next 8 68 0 00 NULL
74 OpenRead 9 5 1 keyinfo(2,BINARY,BINARY) 00 bi
75 Integer 15 33 0 00 NULL
76 Integer 11 34 0 00 NULL
77 SeekGe 9 84 33 2 00 NULL
78 IdxGE 9 84 33 2 01 NULL
79 IdxRowid 9 27 0 00 NULL
80 Seek 1 27 0 00 NULL
81 RowSetTest 29 83 27 -1 00 NULL
82 Gosub 28 85 0 00 NULL
83 Next 9 78 0 00 NULL
84 Goto 0 99 0 00 NULL
85 Column 1 0 35 00 b.f1
86 ToInt 35 0 0 00 NULL
87 Column 1 1 36 00 b.f2
88 ToInt 36 0 0 00 NULL
89 MakeRecord 35 2 37 00 NULL
90 Column 1 0 23 00 b.f1
91 ToInt 23 0 0 00 NULL
92 Column 1 1 24 00 b.f2
93 ToInt 24 0 0 00 NULL
94 Sequence 7 25 0 00 NULL
95 Move 37 26 1 00 NULL
96 MakeRecord 23 4 38 00 NULL
97 IdxInsert 7 38 0 00 NULL
98 Return 28 0 0 00 NULL
99 Close 1 0 0 00 NULL
100 OpenPseudo 10 37 2 00 NULL
101 Sort 7 107 0 00 NULL
102 Column 7 3 37 00 NULL
103 Column 10 0 35 20 NULL
104 Column 10 1 36 00 NULL
105 Yield 3 0 0 00 NULL
106 Next 7 102 0 00 NULL
107 Close 10 0 0 00 NULL
108 Integer 1 4 0 00 NULL
109 Yield 3 0 0 00 NULL
110 Noop 0 0 0 00 End coroutine for
right SELECT
111 Noop 0 0 0 00 Output routine for A
112 ResultRow 20 2 0 00 NULL
113 Return 5 0 0 00 NULL
114 Noop 0 0 0 00 Output routine for B
115 ResultRow 35 2 0 00 NULL
116 Return 6 0 0 00 NULL
117 Noop 0 0 0 00 eof-A subroutine
118 If 4 146 0 00 NULL
119 Gosub 6 115 0 00 NULL
120 Yield 3 0 0 00 NULL
121 Goto 0 118 0 00 NULL
122 Noop 0 0 0 00 eof-B subroutine
123 If 2 146 0 00 NULL
124 Gosub 5 112 0 00 NULL
125 Yield 1 0 0 00 NULL
126 Goto 0 123 0 00 NULL
127 Noop 0 0 0 00 A-lt-B subroutine
128 Gosub 5 112 0 00 NULL
129 Yield 1 0 0 00 NULL
130 If 2 118 0 00 NULL
131 Goto 0 143 0 00 NULL
132 Noop 0 0 0 00 A-gt-B subroutine
133 Gosub 6 115 0 00 NULL
134 Yield 3 0 0 00 NULL
135 If 4 123 0 00 NULL
136 Goto 0 143 0 00 NULL
137 Integer 0 2 0 00 NULL
138 Integer 0 4 0 00 NULL
139 Gosub 1 2 0 00 NULL
140 Gosub 3 59 0 00 NULL
141 If 2 118 0 00 NULL
142 If 4 123 0 00 NULL
143 Permutation 0 0 0 intarray 00 NULL
144 Compare 20 35 2 keyinfo(2,BINARY,BINARY) 00 NULL
145 Jump 128 128 133 00 NULL
146 Halt 0 0 0 00 NULL
147 Transaction 1 0 0 00 NULL
148 VerifyCookie 1 4 0 00 NULL
149 TableLock 1 2 0 a 00 NULL
150 TableLock 1 3 0 b 00 NULL
151 Goto 0 9 0 00 NULL
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Trace 0 0 0 00 NULL
1 Goto 0 135 0 00 NULL
2 Noop 0 0 0 00 Begin coroutine for
left SELECT
3 OpenEphemeral 3 4 0 keyinfo(2,BINARY,BINARY) 00 NULL
4 Integer 7 7 0 00 NULL
5 Integer 11 8 0 00 NULL
6 Integer 15 9 0 00 NULL
7 Integer 11 10 0 00 NULL
8 Goto 0 145 0 3 00 NULL
9 Null 0 12 0 00 NULL
10 Integer 31 11 0 00 NULL
11 VOpen 2 0 0 vtab:108362E8:2AD925E92D80 00 NULL
12 Copy 7 16 0 00 NULL
13 Copy 8 17 0 00 NULL
14 Integer 2 14 0 00 NULL
15 Integer 2 15 0 00 NULL
16 VFilter 2 21 14 00 NULL
17 Rowid 2 13 0 00 NULL
18 RowSetTest 12 20 13 0 00 NULL
19 Gosub 11 32 0 00 NULL
20 VNext 2 17 0 00 NULL
21 VOpen 2 0 0 vtab:108362E8:2AD925E92D80 00 NULL
22 Copy 9 16 0 00 NULL
23 Copy 10 17 0 00 NULL
24 Integer 2 14 0 00 NULL
25 Integer 2 15 0 00 NULL
26 VFilter 2 31 14 00 NULL
27 Rowid 2 13 0 00 NULL
28 RowSetTest 12 30 13 -1 00 NULL
29 Gosub 11 32 0 00 NULL
30 VNext 2 27 0 00 NULL
31 Goto 0 46 0 00 NULL
32 VColumn 2 1 18 00
sgb_ct_draw_share_count.game_no
33 ToInt 18 0 0 00 NULL
34 VColumn 2 2 19 00
sgb_ct_draw_share_count.draw_no
35 ToInt 19 0 0 00 NULL
36 MakeRecord 18 2 20 00 NULL
37 VColumn 2 1 14 00
sgb_ct_draw_share_count.game_no
38 ToInt 14 0 0 00 NULL
39 VColumn 2 2 15 00
sgb_ct_draw_share_count.draw_no
40 ToInt 15 0 0 00 NULL
41 Sequence 3 16 0 00 NULL
42 Move 20 17 1 00 NULL
43 MakeRecord 14 4 21 00 NULL
44 IdxInsert 3 21 0 00 NULL
45 Return 11 0 0 00 NULL
46 Close 2 0 0 00 NULL
47 OpenPseudo 4 20 2 00 NULL
48 Sort 3 54 0 00 NULL
49 Column 3 3 20 00 NULL
50 Column 4 0 18 20 NULL
51 Column 4 1 19 00 NULL
52 Yield 1 0 0 00 NULL
53 Next 3 49 0 00 NULL
54 Close 4 0 0 00 NULL
55 Integer 1 2 0 00 NULL
56 Yield 1 0 0 00 NULL
57 Noop 0 0 0 00 End coroutine for
left SELECT
58 Noop 0 0 0 00 Begin coroutine for
right SELECT
59 OpenEphemeral 5 4 0 3 00 NULL
60 Null 0 23 0 00 NULL
61 Integer 82 22 0 00 NULL
62 VOpen 1 0 0 vtab:10843658:2AD925E92D80 00 NULL
63 Integer 7 16 0 00 NULL
64 Integer 11 17 0 00 NULL
65 Integer 2 14 0 00 NULL
66 Integer 2 15 0 00 NULL
67 VFilter 1 72 14 00 NULL
68 Rowid 1 24 0 00 NULL
69 RowSetTest 23 71 24 0 00 NULL
70 Gosub 22 83 0 00 NULL
71 VNext 1 68 0 00 NULL
72 VOpen 1 0 0 vtab:10843658:2AD925E92D80 00 NULL
73 Integer 15 16 0 00 NULL
74 Integer 11 17 0 00 NULL
75 Integer 2 14 0 00 NULL
76 Integer 2 15 0 00 NULL
77 VFilter 1 82 14 00 NULL
78 Rowid 1 24 0 00 NULL
79 RowSetTest 23 81 24 -1 00 NULL
80 Gosub 22 83 0 00 NULL
81 VNext 1 78 0 00 NULL
82 Goto 0 97 0 00 NULL
83 VColumn 1 1 25 00
sgb_ct_draw_share_count_echannel.game_no
84 ToInt 25 0 0 00 NULL
85 VColumn 1 2 26 00
sgb_ct_draw_share_count_echannel.draw_no
86 ToInt 26 0 0 00 NULL
87 MakeRecord 25 2 20 00 NULL
88 VColumn 1 1 14 00
sgb_ct_draw_share_count_echannel.game_no
89 ToInt 14 0 0 00 NULL
90 VColumn 1 2 15 00
sgb_ct_draw_share_count_echannel.draw_no
91 ToInt 15 0 0 00 NULL
92 Sequence 5 16 0 00 NULL
93 Move 20 17 1 00 NULL
94 MakeRecord 14 4 21 00 NULL
95 IdxInsert 5 21 0 00 NULL
96 Return 22 0 0 00 NULL
97 Close 1 0 0 00 NULL
98 OpenPseudo 6 20 2 00 NULL
99 Sort 5 105 0 00 NULL
100 Column 5 3 20 00 NULL
101 Column 6 0 25 20 NULL
102 Column 6 1 26 00 NULL
103 Yield 3 0 0 00 NULL
104 Next 5 100 0 00 NULL
105 Close 6 0 0 00 NULL
106 Integer 1 4 0 00 NULL
107 Yield 3 0 0 00 NULL
108 Noop 0 0 0 00 End coroutine for
right SELECT
109 Noop 0 0 0 00 Output routine for A
110 ResultRow 18 2 0 00 NULL
111 Return 5 0 0 00 NULL
112 Noop 0 0 0 00 Output routine for B
113 ResultRow 25 2 0 00 NULL
114 Return 6 0 0 00 NULL
115 Noop 0 0 0 00 eof-A subroutine
116 If 4 144 0 00 NULL
117 Gosub 6 113 0 00 NULL
118 Yield 3 0 0 00 NULL
119 Goto 0 116 0 00 NULL
120 Noop 0 0 0 00 eof-B subroutine
121 If 2 144 0 00 NULL
122 Gosub 5 110 0 00 NULL
123 Yield 1 0 0 00 NULL
124 Goto 0 121 0 00 NULL
125 Noop 0 0 0 00 A-lt-B subroutine
126 Gosub 5 110 0 00 NULL
127 Yield 1 0 0 00 NULL
128 If 2 116 0 00 NULL
129 Goto 0 141 0 00 NULL
130 Noop 0 0 0 00 A-gt-B subroutine
131 Gosub 6 113 0 00 NULL
132 Yield 3 0 0 00 NULL
133 If 4 121 0 00 NULL
134 Goto 0 141 0 00 NULL
135 Integer 0 2 0 00 NULL
136 Integer 0 4 0 00 NULL
137 Gosub 1 2 0 00 NULL
138 Gosub 3 58 0 00 NULL
139 If 2 116 0 00 NULL
140 If 4 121 0 00 NULL
141 Permutation 0 0 0 intarray 00 NULL
142 Compare 18 25 2 keyinfo(2,BINARY,BINARY) 00 NULL
143 Jump 126 126 131 00 NULL
144 Halt 0 0 0 00 NULL
145 Transaction 0 0 0 00 NULL
146 VerifyCookie 0 1223 0 00 NULL
147 Goto 0 9 0 00 NULL
________________________________
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 - 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: [email protected]<mailto:[email protected]>
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then delete this message from
your system. Please do not copy it or use it for any purposes, or disclose its
contents to any person as to do so could be a breach of confidence. Thank you
for your cooperation.
_______________________________________________
sqlite-users mailing list
[email protected]<mailto:[email protected]>
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
--
D. Richard Hipp
[email protected]<mailto:[email protected]>
________________________________
Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 - 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: [email protected]
This e-mail is confidential and may well also be legally privileged. If you
have received it in error, you are on notice as to its status and accordingly
please notify us immediately by reply e-mail and then delete this message from
your system. Please do not copy it or use it for any purposes, or disclose its
contents to any person as to do so could be a breach of confidence. Thank you
for your cooperation.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
