On Mon, Dec 3, 2012 at 3:11 AM, Hick Gunter <[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] > > 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 > -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
