Debugging reveals that the following code in where.c clobbers the P4 value of the prior instruction which is incorrectly assumed to be an OP_OpenWrite or OP_OpenRead but in fact is not generated at all for virtual tables.
Maybe the wsFlags are not set correctly? Additionally, the VOpen for the virtual table is generated once for each OR expression instead of once per queried table. BTW: SQLite versions are 3.7.4 and 3.7.14.1, the latter generating OP_SorterOpen instead of OP_OpenEphemeral but failing in the same way. #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 ){ 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: Hick Gunter Gesendet: Montag, 03. Dezember 2012 09:11 An: 'sqlite-users@sqlite.org' Betreff: Invalid Code gemerated for Virtual Table Join with OR clause 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 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: h...@scigames.at 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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users