Groups keyboard shortcuts have been updated
Dismiss
See shortcuts

query .all and .count return different results when used with a subquery

1,636 views
Skip to first unread message

kris

unread,
Aug 3, 2012, 3:22:26 PM8/3/12
to sqlal...@googlegroups.com
SA 0.7.8
I am getting some strange results when using a subquery that returns  Duplicate
rows.  Note in line 7 I am not using distinct, yet I get one object where I would expect
2.  Also if I  query.count I do actually receive 2.  
What's going on here?

Thanks,
kris

vq1 = DBSession.query(Value).filter_by(document_id = 622849) 

In [3]: vq1.all()
Out[3]: 
[<bq.data_service.model.tag_model.Value object at 0x59a2890>,
 <bq.data_service.model.tag_model.Value object at 0x59a2990>]

In [5]: sq1 = vq1.with_labels().subquery()
In [6]: q = DBSession.query (Taggable).filter (Taggable.id == sq1.c.values_valobj)

In [7]: q.all()
Out[7]: [<bq.data_service.model.tag_model.Taggable object at 0x59b0590>]

In [9]: q.count()
Out[9]: 2L

From sql:

select * from values where resource_parent_id = 622849;
 resource_parent_id | indx | valstr | valnum | valobj | document_id 
--------------------+------+--------+--------+--------+-------------
             622849 |    0 |        |        | 622840 |      622849
             622849 |    1 |        |        | 622840 |      622849


=# select id  from taggable, (select * from values where document_id = 622849) as v where taggable.id = v.valobj;
   id   
--------
 622840
 622840

Michael Bayer

unread,
Aug 3, 2012, 8:00:41 PM8/3/12
to sqlal...@googlegroups.com
.all() when used to return ORM instances will de-duplicate based on primary key identity as those instances come in.  this is part of how joined eager loading manages to return only one instance of each parent object even if it comes back as many rows due to joins.




   id   
--------
 622840
 622840


--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/Y6KJNVstnSYJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

kris

unread,
Aug 6, 2012, 6:16:20 PM8/6/12
to sqlal...@googlegroups.com
Well.. I can certainly understand why it's needed, but in my case I actually need to receive the two
duplicate instances.   
Is there any way to remove this de-duplicate behavior or work around? 


Michael Bayer

unread,
Aug 6, 2012, 6:28:47 PM8/6/12
to sqlal...@googlegroups.com
the "uniquing" logic is only enabled when you query for mapped entities, not individual columns, so from the Query the option would be to only query for individual columns.

Query could be modified to allow a disabling option, though I wonder why exactly you'd need duplicate instances back.


--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/bGlkp8JDtBQJ.

Michael Bayer

unread,
Oct 25, 2012, 9:58:18 AM10/25/12
to sqlal...@googlegroups.com

On Oct 24, 2012, at 9:35 PM, dnathe4th wrote:

I'm having a problem related to this as well. Is it possible for a join to get tripped up in the de-dup process or is that guaranteed to only occur for the mapper entity I query on? I am getting the de-duping if I query on the mapped entity, but if I query on the id of the entity I get the full number of rows I am expecting, as you indicated. However beyond that, even if I query on the distinct id's, I am getting back the full number of rows I expected, thus leading me to believe some other entity's primary key is getting de-duplicated. Is that possible?


it de-duplicates on the whole row being returned, not just the first entity.  Only when the row contains full entities.


To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/4Y0C7p6oSk4J.
Reply all
Reply to author
Forward
0 new messages