On November 20, 2009 08:11:26 pm Simon Slavin wrote:
> On 21 Nov 2009, at 12:57am, Phil Longstaff wrote:
> > I have an sqlite3 database where a SELECT hangs. The statement is:
> >
> > SELECT DISTINCT t.* FROM transactions AS t, splits AS s WHERE
> > s.tx_guid=t.guid AND ((s.account_guid IN
> > ('d076626fc5be59df7e592f8f0d66c7cd')))
> >
> > Are there any tools to help me diagnose the problem? I could download
> > the source and build with debug and try to trace my way through, but
> > would prefer some pre-built diagnostic/debug tools if possible.
>
> I've no idea what's causing your problem so I'm asking questions at random.
> What's the deal with the double bracketing ? I'm not insulting you, I'm
> curious and don't know if it's useful for something. Try removing layers
> of the brackets in case it makes something work.
The statement is generated from a query represented as a list of lists (outer
list is OR, inner list is AND, each item is a term). I add extra parentheses
just to help me keep the statement meaning clear.
>
> Are you executing this in your code, or in the sqlite3 command-line tool ?
> If the former, please try the latter and see if you get the same result.
This comes from my code, but I get the same thing from the sqlite3 command
line.
Interestingly, that statement now, works, but this one (similar) doesn't.
sqlite> SELECT DISTINCT t.* FROM transactions AS t, splits AS s WHERE
s.tx_guid=t.guid AND s.account_guid='e3ea8186deb3a9c160ab3b9409ea618f';
^CSQL error: interrupted
sqlite>
I interrupted it after 2 minutes with no output.
sqlite> explain SELECT DISTINCT t.* FROM transactions AS t, splits AS s WHERE
s.tx_guid=t.guid AND s.account_guid='e3ea8186deb3a9c160ab3b9409ea618f';
0|Trace|0|0|0||00|
1|Noop|0|0|0||00|
2|Integer|0|8|0||00|
3|Integer|0|7|0||00|
4|Gosub|10|57|0||00|
5|String8|0|23|0|e3ea8186deb3a9c160ab3b9409ea618f|00|
6|Goto|0|65|0||00|
7|OpenRead|0|273|0|6|00|
8|OpenRead|3|275|0|keyinfo(1,BINARY)|00|
9|OpenRead|1|185|0|3|00|
10|Rewind|3|40|24|0|00|
11|IdxRowid|3|24|0||00|
12|Seek|0|24|0||00|
13|Rewind|1|39|0||00|
14|Column|1|1|25||00|
15|Column|3|0|26||00|
16|Ne|26|38|25|collseq(BINARY)|6a|
17|Column|1|2|27||00|
18|Ne|23|38|27|collseq(BINARY)|69|
19|Column|3|0|17||00|
20|Column|0|1|18||00|
21|Column|0|2|19||00|
22|Column|0|3|20||00|
23|Column|0|4|21||00|
24|Column|0|5|22||00|
25|Compare|11|17|6|keyinfo(6,BINARY,BINARY)|00|
26|Jump|27|31|27||00|
27|Move|17|11|6||00|
28|Gosub|9|47|0||00|
29|IfPos|8|64|0||00|
30|Gosub|10|57|0||00|
31|Column|3|0|1||00|
32|Column|0|1|2||00|
33|Column|0|2|3||00|
34|Column|0|3|4||00|
35|Column|0|4|5||00|
36|Column|0|5|6||00|
37|Integer|1|7|0||00|
38|Next|1|14|0||01|
39|Next|3|11|0||00|
40|Close|0|0|0||00|
41|Close|3|0|0||00|
42|Close|1|0|0||00|
43|Gosub|9|47|0||00|
44|Goto|0|64|0||00|
45|Integer|1|8|0||00|
46|Return|9|0|0||00|
47|IfPos|7|49|0||00|
48|Return|9|0|0||00|
49|SCopy|1|29|0||00|
50|SCopy|2|30|0||00|
51|SCopy|3|31|0||00|
52|SCopy|4|32|0||00|
53|SCopy|5|33|0||00|
54|SCopy|6|34|0||00|
55|ResultRow|29|6|0||00|
56|Return|9|0|0||00|
57|Null|0|1|0||00|
58|Null|0|2|0||00|
59|Null|0|3|0||00|
60|Null|0|4|0||00|
61|Null|0|5|0||00|
62|Null|0|6|0||00|
63|Return|10|0|0||00|
64|Halt|0|0|0||00|
65|Transaction|0|0|0||00|
66|VerifyCookie|0|83|0||00|
67|TableLock|0|273|0|transactions|00|
68|TableLock|0|185|0|splits|00|
69|Goto|0|7|0||00|
>
> I would try using EXPLAIN on that command: just put 'EXPLAIN ' before the
> SELECT command and see what you get out. It's possible that the EXPLAIN
> command will hang too. Also there's EXPLAIN QUERY PLAN.
sqlite> explain query plan SELECT DISTINCT t.* FROM transactions AS t, splits
AS s WHERE s.tx_guid=t.guid AND
s.account_guid='e3ea8186deb3a9c160ab3b9409ea618f';
0|0|TABLE transactions AS t WITH INDEX sqlite_autoindex_transactions_1 ORDER
BY
1|1|TABLE splits AS s
sqlite>
Hmmm... There are indices. I recently modified the splits table, removing
"NOT NULL" from a column. I then copied the data into the new table using:
CREATE TABLE splits_new (...)
INSERT INTO splits_new SELECT FROM splits
DROP TABLE splits
ALTER TABLE splits_new RENAME TO splits
How can I force the indices to be regenerated?
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
