Matt Froncek wrote:
There must be a bug then. Because my TimeTracking Table has 36,000 records
and that is what I get with the First LEFT OUTER JOIN and even with the
second LEFT OUTER JOIN but adding the third one I get 2,000,000 records. The
product of 36,000 and the records duplicated from PriceLevelPerItem).
Matt,
I can't reproduce your problem here. I have created some minimal tables
that I believe match the relevant parts of your schema. When I run the
query I get the correct result. I have included a trace of my test run
below. Note, I'm using a different version of sqlite than you. What do
you get when you when you run the following command in your version?
create table TimeTracking (
ListID integer primary key,
CustomerRefListID integer references customer,
EntityRefListID integer references entity,
t_data text
);
insert into TimeTracking values (1, 1, 1, 't_one');
insert into TimeTracking values (2, 1, 2, 't_two');
create table Customer (
ListID integer primary key,
PriceLevelRefListID intteger references pricelevelperitem,
c_data text
);
insert into Customer values (1, 1, 'c_one');
insert into Customer values (2, 1, 'c_two');
create table Entity (
ListID integer primary key,
e_data text
);
insert into Entity values (1, 'e_one');
insert into Entity values (2, 'e_two');
create table PriceLevelPerItem (
ListID integer primary key,
p_data text
);
insert into PriceLevelPerItem values (1, 'p_one');
insert into PriceLevelPerItem values (2, 'p_two');
explain query plan
SELECT count(TimeTracking.ROWID)
FROM TimeTracking
LEFT OUTER JOIN Customer
ON Customer.ListID = TimeTracking.CustomerRefListID
LEFT OUTER JOIN Entity
ON Entity.ListID = TimeTracking.EntityRefListID
LEFT OUTER JOIN PriceLevelPerItem
ON PriceLevelPerItem.ListID = Customer.PriceLevelRefListID;
.explain on
explain
SELECT count(TimeTracking.ROWID)
FROM TimeTracking
LEFT OUTER JOIN Customer
ON Customer.ListID = TimeTracking.CustomerRefListID
LEFT OUTER JOIN Entity
ON Entity.ListID = TimeTracking.EntityRefListID
LEFT OUTER JOIN PriceLevelPerItem
ON PriceLevelPerItem.ListID = Customer.PriceLevelRefListID;
SELECT count(TimeTracking.ROWID)
FROM TimeTracking
LEFT OUTER JOIN Customer
ON Customer.ListID = TimeTracking.CustomerRefListID
LEFT OUTER JOIN Entity
ON Entity.ListID = TimeTracking.EntityRefListID
LEFT OUTER JOIN PriceLevelPerItem
ON PriceLevelPerItem.ListID = Customer.PriceLevelRefListID;
*************** Test Run below
SQLite version 3.3.5
Enter ".help" for instructions
sqlite> create table TimeTracking (
...> ListID integer primary key,
...> CustomerRefListID integer references customer,
...> EntityRefListID integer references entity,
...> t_data text
...> );
sqlite>
sqlite> insert into TimeTracking values (1, 1, 1, 't_one');
sqlite> insert into TimeTracking values (2, 1, 2, 't_two');
sqlite>
sqlite> create table Customer (
...> ListID integer primary key,
...> PriceLevelRefListID intteger references pricelevelperitem,
...> c_data text
...> );
sqlite>
sqlite> insert into Customer values (1, 1, 'c_one');
sqlite> insert into Customer values (2, 1, 'c_two');
sqlite>
sqlite> create table Entity (
...> ListID integer primary key,
...> e_data text
...> );
sqlite>
sqlite> insert into Entity values (1, 'e_one');
sqlite> insert into Entity values (2, 'e_two');
sqlite>
sqlite> create table PriceLevelPerItem (
...> ListID integer primary key,
...> p_data text
...> );
sqlite>
sqlite> insert into PriceLevelPerItem values (1, 'p_one');
sqlite> insert into PriceLevelPerItem values (2, 'p_two');
sqlite>
sqlite>
sqlite> explain query plan
...> SELECT count(TimeTracking.ROWID)
...> FROM TimeTracking
...> LEFT OUTER JOIN Customer
...> ON Customer.ListID = TimeTracking.CustomerRefListID
...> LEFT OUTER JOIN Entity
...> ON Entity.ListID = TimeTracking.EntityRefListID
...> LEFT OUTER JOIN PriceLevelPerItem
...> ON PriceLevelPerItem.ListID = Customer.PriceLevelRefListID;
0|0|TABLE TimeTracking
1|1|TABLE Customer USING PRIMARY KEY
2|2|TABLE Entity USING PRIMARY KEY
3|3|TABLE PriceLevelPerItem USING PRIMARY KEY
sqlite>
sqlite> .explain on
sqlite> explain
...> SELECT count(TimeTracking.ROWID)
...> FROM TimeTracking
...> LEFT OUTER JOIN Customer
...> ON Customer.ListID = TimeTracking.CustomerRefListID
...> LEFT OUTER JOIN Entity
...> ON Entity.ListID = TimeTracking.EntityRefListID
...> LEFT OUTER JOIN PriceLevelPerItem
...> ON PriceLevelPerItem.ListID = Customer.PriceLevelRefListID;
addr opcode p1 p2 p3
---- -------------- ---------- ----------
---------------------------------
0 MemNull 1 0
1 MemNull 0 0
2 Goto 0 51
3 Integer 0 0
4 OpenRead 0 2
5 SetNumColumns 0 3
6 Integer 0 0
7 OpenRead 1 3
8 SetNumColumns 1 2
9 Integer 0 0
10 OpenRead 2 4
11 SetNumColumns 2 0
12 Integer 0 0
13 OpenRead 3 5
14 SetNumColumns 3 0
15 Rewind 0 43
16 MemInt 0 2
17 Column 0 1
18 MustBeInt 1 39
19 NotExists 1 39
20 MemInt 1 2
21 MemInt 0 3
22 Column 0 2
23 MustBeInt 1 36
24 NotExists 2 36
25 MemInt 1 3
26 MemInt 0 4
27 Column 1 1
28 MustBeInt 1 33
29 NotExists 3 33
30 MemInt 1 4
31 Rowid 0 0
32 AggStep 0 1 count(1)
33 IfMemPos 4 36
34 NullRow 3 0
35 Goto 0 30
36 IfMemPos 3 39
37 NullRow 2 0
38 Goto 0 25
39 IfMemPos 2 42
40 NullRow 1 0
41 Goto 0 20
42 Next 0 16
43 Close 0 0
44 Close 1 0
45 Close 2 0
46 Close 3 0
47 AggFinal 0 1 count(1)
48 MemLoad 0 0
49 Callback 1 0
50 Halt 0 0
51 Transaction 0 0
52 VerifyCookie 0 4
53 Goto 0 3
54 Noop 0 0
sqlite>
sqlite> SELECT count(TimeTracking.ROWID)
...> FROM TimeTracking
...> LEFT OUTER JOIN Customer
...> ON Customer.ListID = TimeTracking.CustomerRefListID
...> LEFT OUTER JOIN Entity
...> ON Entity.ListID = TimeTracking.EntityRefListID
...> LEFT OUTER JOIN PriceLevelPerItem
...> ON PriceLevelPerItem.ListID = Customer.PriceLevelRefListID;
coun
----
2
sqlite>
