Re: [sqlite] SQLite - potential bug with multiple leftjoin/groupby/count

[Clemens Ladisch]

Harry Beezhold wrote:
> Sqlite  -  What a cool product!

Do you really think buggy products are cool?  ;-)

> The following is a description of an apparent bug in
> the calculation of a row count of a left joined table.

> The leftjoin/count technique seems to work for each join/count, separately.
> However, if done together, the counts appear to have been multiplied

I created a simplified example:

  CREATE TABLE persons(personID);
  INSERT INTO "persons" VALUES('I');
  CREATE TABLE parentschildren(parentID, childID);
  INSERT INTO "parentschildren" VALUES('father','I');
  INSERT INTO "parentschildren" VALUES('mother','I');
  INSERT INTO "parentschildren" VALUES('I','son');
  INSERT INTO "parentschildren" VALUES('I','daughter');

A single join works as expected:

  SELECT personID, parents.parentID
  FROM persons
  LEFT JOIN parentschildren AS parents ON personID = parents.childID;

  personIDparentID
  --  --
  I   father
  I   mother

If you join that with another table, you get more records because _each_
"I" record matches two children records:

  SELECT personID, parents.parentID, children.childID
  FROM persons
  LEFT JOIN parentschildren AS parents ON personID = parents.childID
  LEFT JOIN parentschildren AS children ON personID = children.parentID;

  personIDparentIDchildID
  --  --  --
  I   father  daughter
  I   father  son
  I   mother  daughter
  I   mother  son

This is how SQL joins work.

If you want to get _independent_ parent and children counts, you should
use correlated subqueries instead:

  SELECT personID,
 (SELECT COUNT(*) FROM parentschildren WHERE childID  = personID) AS 
parentCount,
 (SELECT COUNT(*) FROM parentschildren WHERE parentID = personID) AS 
childCount
  FROM persons;

  personIDparentCount  childCount
  --  ---  --
  I   22


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite - potential bug with multiple leftjoin/groupby/count

[Richard Hipp]

On Tue, Sep 3, 2013 at 10:41 AM, Harry Beezhold  wrote:

>
>
> The attached database (view.db) has 3 tables
>

The sqlite-users@sqlite.org mailing list strips off attachments.  Can you
send a link instead?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite - potential bug with multiple leftjoin/groupby/count

[Harry Beezhold]

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

 

10

22

30

42 These numbers look good.

50 The query asks:

61 How many in the parentsChildren table has my personID in their
childID.

71 They would be my parents

82

92

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

 

11

21

31

410   Yes, he did have 10 children

52

60

70

82

92

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

 

11

21

31

42he had 2 wives

50

60

70

81

91

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

 

 

101

222   < bug

301

420   20  < bug   

502

610

710

844   < bug

944   < bug

10   01

11   01

12   20

13   20

14   01

15   01

16   20

17   20

18   010

19   20   20  < bug

20   20

 

**   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

 

101

222   < bug

301

420   20  < bug

502

610

710

844  < bug

944  < bug

10   01

11   01

12   20

13   20

14   01

15   01

16   20

17   20

18   010

19   20   20  < bug

20   20

 

**   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