Discuss usage/design issues for SQLAlchemy, a Python SQL toolkit

headers

kris | 3 Aug 2012 21:22

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

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;

--------------------+------+--------+--------+--------+-------------

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;

--------

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 sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

headers

Michael Bayer | 4 Aug 2012 02:00

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

On Aug 3, 2012, at 3:22 PM, kris wrote:

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;

.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 sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

headers

kris | 7 Aug 2012 00:16

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

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?

--
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.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

headers

Michael Bayer | 7 Aug 2012 00:28

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

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.

On Aug 6, 2012, at 6:16 PM, kris wrote:

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?

--
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.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

headers

dnathe4th | 25 Oct 2012 03:35

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

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?

On Monday, August 6, 2012 3:28:47 PM UTC-7, Michael Bayer wrote:

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.

On Aug 6, 2012, at 6:16 PM, kris wrote:

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?

--
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.
To post to this group, send email to sqlal... <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+... <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

--
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/-/4Y0C7p6oSk4J.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

headers

Michael Bayer | 25 Oct 2012 15:58

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

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.

On Monday, August 6, 2012 3:28:47 PM UTC-7, Michael Bayer wrote:
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.

On Aug 6, 2012, at 6:16 PM, kris wrote:

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?

--
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.
To post to this group, send email to sqlal... <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+... <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

--
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/-/4Y0C7p6oSk4J.
To post to this group, send email to sqlalchemy <at> googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+unsubscribe <at> googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.