Hi,
Sqlite - What a cool product!
The following is a description of an apparent bug in
the calculation of a row count of a left joined table.
In the intended application I plan to use this type of query to feed and
filter the
list on the "choose a person" popup in a genealogy program. I have a
workaround
so I AM NOT IN A HURRY FOR A RESPONSE.
The attached database (view.db) has 3 tables
1, persons (personID)
2. parentsChildren (parentID, childID)
3. spouses (spouse1ID, spouseID)
The leftjoin/count technique seems to work for each join/count, separately.
However, if done together, the counts appear to have been multiplied;
Example: person #4 should have 2 parents, 10 children and 2 spouses.
Instead it gets 20,20 for 2 joins and 40,40,40 for 3 joins.
I hope the following queries will tell the story.
I can be contacted at:
harry beezhold
ha...@beezhold.com
708-459-8600
****************************** JOIN parents ****************************
****************************** looks OK ****************************
****************************** #4 = 2 ****************************
SELECT persons.personID,
count(parents.parentID) as parentCount
FROM persons
LEFT JOIN parentsChildren AS parents ON personID = parents.childID
GROUP BY personID
1 0
2 2
3 0
4 2 These numbers look good.
5 0 The query asks:
6 1 How many in the parentsChildren table has my personID in their
childID.
7 1 They would be my parents
8 2
9 2
10 0
11 0
12 2
13 2
14 0
15 0
I stopped it
****************************** JOIN children ************************
****************************** looks OK ****************************
****************************** #4 = 10 ****************************
SELECT persons.personID,
count(children.childID) as childCount
FROM persons
LEFT JOIN parentsChildren AS children ON personID = children.parentID
GROUP BY personID
1 1
2 1
3 1
4 10 Yes, he did have 10 children
5 2
6 0
7 0
8 2
9 2
10 1
11 1
12 0
13 0
14 1
15 1
***************************** JOIN spouses ****************************
****************************** looks OK ****************************
****************************** #4 = 2 ****************************
SELECT persons.personID,
count(spouses.spouse1ID) as spouseCount
FROM persons
LEFT JOIN spouses ON ((personID = spouse1ID) OR (personID = spouse2ID))
GROUP BY personID
1 1
2 1
3 1
4 2 he had 2 wives
5 0
6 0
7 0
8 1
9 1
10 1
11 1
12 1
13 1
14 1
15 1
****************************** JOIN 2 from same table ****************
****************************** the problem shows
****************************
****************************** *****************
***********************
SELECT persons.personID,
count(parents.parentID) as parentCount,
count(children.childID) as childCount
FROM persons
LEFT JOIN parentsChildren AS parents ON personID = parents.childID
LEFT JOIN parentsChildren AS children ON personID = children.parentID
GROUP BY personID
1 0 1
2 2 2 < bug
3 0 1
4 20 20 < bug
5 0 2
6 1 0
7 1 0
8 4 4 < bug
9 4 4 < bug
10 0 1
11 0 1
12 2 0
13 2 0
14 0 1
15 0 1
16 2 0
17 2 0
18 0 10
19 20 20 < bug
20 2 0
****************************** JOIN 2 diff tables/seq ****************
****************************** the problem shows
****************************
****************************** *****************
***********************
SELECT persons.personID,
count(spouses.spouse1ID) as spouceCount,
count(children.childID) as childCount
FROM persons
LEFT JOIN spouses ON ((personID = spouse1ID) OR (personID = spouse2ID))
LEFT JOIN parentsChildren AS children ON personID = children.parentID
GROUP BY personID
1 0 1
2 2 2 < bug
3 0 1
4 20 20 < bug
5 0 2
6 1 0
7 1 0
8 4 4 < bug
9 4 4 < bug
10 0 1
11 0 1
12 2 0
13 2 0
14 0 1
15 0 1
16 2 0
17 2 0
18 0 10
19 20 20 < bug
20 2 0
****************************** 3 JOIN ****************************
****************************** the problem shows
****************************
****************************** *****************
***********************
SELECT persons.personID,
count(parents.parentID) as parentCount,
count(children.childID) as childCount,
count(spouses.spouse1ID) as spouseCount
FROM (((persons
LEFT JOIN parentsChildren AS parents ON personID = parents.childID)
LEFT JOIN parentsChildren AS children ON personID = children.parentID)
LEFT JOIN spouses ON ((personID = spouse1ID) OR (personID = spouse2ID)))
GROUP BY personID
1 0 1 1
2 2 2 2 < bug
3 0 1 1
4 40 40 40 < bug
5 0 2 0
6 1 0 0
7 1 0 0
8 4 4 4 < bug
9 4 4 4 < bug
10 0 1 1
11 0 1 1
12 2 0 2
13 2 0 2
14 0 1 1
15 0 1 1
16 2 0 0
17 2 0 0
18 0 20 20
19 20 20 20 < bug
20 2 0 0
==============================================
Thank you.
Harry Beezhold
708-459-8600
<mailto:ha...@beezhold.com> ha...@beezhold.com
http://Beezhold.com/harry/resume.doc
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
