Re: [sqlalchemy] Oracle use_ansi=False inner join problem on inline views
Kent jkentbo...@gmail.com wrote: Mike, When using use_ansi=False for Oracle (8) in conjunction with joinedload-ing an inline view property, SQLAlchemy-0.8.7 renders an inner join instead of an outer join. This has been fixed in SQLAlchemy-0.9.0, but, as I'm not prepared for the migration yet, I was hoping and failing to find the bug ticket and hopefully a patch. Do you know when/where this was fixed and whether the fix would be patch'able in 0.7 or at least 0.8? The attached script runs on 0.9.0+ but the assertion fails on 0.8.7. The only difference in SQL output is the outer join (+): SELECT products.productid AS products_productid, anon_1.productid AS anon_1_productid, anon_1.siteid AS anon_1_siteid, anon_1.qty AS anon_1_qty FROM products, (SELECT inventory.productid AS productid, inventory.siteid AS siteid, sum(inventory.qty) AS qty FROM inventory GROUP BY inventory.productid, inventory.siteid) anon_1 WHERE anon_1.productid(+) = products.productid ORDER BY anon_1.siteid Interestingly, use-ansi=True correctly renders LEFT OUTER JOIN in 0.8.7 but it fails to render as an outer join with use-ansi=False. Thanks for you time and exceptional software, Kent no specific fixes are logged, however http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1 refers to a very large change in how the ORM decides to join things.That would cause some kinds of joinedloads to render differently , which would impact how (+) comes out as well, but i wouldn’t think it would have the effect that the missing (+) is the only change, it would be more than that. So I have no better idea than you, so the method I’d do is just to git bisect (http://git-scm.com/docs/git-bisect) through revisions until you find the fix. If it’s a big merge revision, I can look into it to find something specific, but if you can get me a rev ID that would be a good start. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Oracle use_ansi=False inner join problem on inline views
Mike,
When using use_ansi=False for Oracle (8) in conjunction with joinedload-ing
an inline view property, SQLAlchemy-0.8.7 renders an inner join instead of
an outer join. This has been fixed in SQLAlchemy-0.9.0, but, as I'm not
prepared for the migration yet, I was hoping and failing to find the bug
ticket and hopefully a patch. Do you know when/where this was fixed and
whether the fix would be patch'able in 0.7 or at least 0.8?
The attached script runs on 0.9.0+ but the assertion fails on 0.8.7.
The only difference in SQL output is the outer join (+):
SELECT products.productid AS products_productid, anon_1.productid AS
anon_1_productid, anon_1.siteid AS anon_1_siteid, anon_1.qty AS anon_1_qty
FROM products, (SELECT inventory.productid AS productid, inventory.siteid
AS siteid, sum(inventory.qty) AS qty
FROM inventory GROUP BY inventory.productid, inventory.siteid) anon_1
WHERE anon_1.productid*(+)* = products.productid ORDER BY anon_1.siteid
Interestingly, use-ansi=True correctly renders LEFT OUTER JOIN in 0.8.7
but it fails to render as an outer join with use-ansi=False.
Thanks for you time and exceptional software,
Kent
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
from sqlalchemy import *
from sqlalchemy.orm import *
eng_url = 'oracle://kent:kent@localhost:1521/xe?use_ansi=False'
engine = create_engine(eng_url, echo=True)
metadata = MetaData(engine)
Session = sessionmaker(bind=engine)
products_table = Table(products, metadata,
Column(productid, Unicode(255), primary_key=True),
)
inventory_table = Table(inventory, metadata,
Column(inventoryid, Integer, primary_key=True),
Column(productid, Unicode(255), ForeignKey('products.productid'), nullable=False),
Column(siteid, Unicode(255), nullable=False),
Column(qty, Integer, nullable=False),
)
def repr_attrs(obj, *attrs):
return '%s: ' % obj.__class__.__name__ + \
' '.join('{0[%s]}=[{1[%s]}]' % (i,i) for i in range(len(attrs)))\
.format(attrs, map(obj.__dict__.get, attrs)) +
class Base(object):
def __init__(self, session, **attrs):
self.__dict__.update(attrs)
session.add(self)
class SiteStockLevel(object):
def __repr__(self):
return repr_attrs(self,'productid','siteid','qty')
class Product(Base):
def __repr__(self):
return repr_attrs(self,'productid')
class Inventory(Base):
pass
sitestocklevels_view = select([
inventory_table.c.productid,
inventory_table.c.siteid,
func.sum(inventory_table.c.qty).label('qty')],
group_by=[inventory_table.c.productid, inventory_table.c.siteid]).alias('sitestocklevels')
mapper(Inventory, inventory_table)
mapper(Product, products_table,
properties={
'sitestocklevels': relationship(SiteStockLevel,
primaryjoin=sitestocklevels_view.c.productid==products_table.c.productid,
order_by=sitestocklevels_view.c.siteid,
viewonly=True),
})
mapper(SiteStockLevel, sitestocklevels_view,
primary_key=[sitestocklevels_view.c.productid, sitestocklevels_view.c.siteid])
metadata.create_all()
try:
sess = Session()
Product(sess, productid=u'SKUA')
Product(sess, productid=u'SKUB')
sess.commit()
Inventory(sess, inventoryid=1, productid=u'SKUA', siteid=u'S1', qty=1)
Inventory(sess, inventoryid=2, productid=u'SKUA', siteid=u'S1', qty=2)
Inventory(sess, inventoryid=3, productid=u'SKUA', siteid=u'S1', qty=3)
Inventory(sess, inventoryid=4, productid=u'SKUA', siteid=u'S2', qty=1)
sess.commit()
allproducts = sess.query(Product).options(joinedload(Product.sitestocklevels)).all()
assert len(allproducts) == 2
finally:
metadata.drop_all()
Re: [sqlalchemy] Oracle use_ansi=False inner join problem on inline views
So, in 0.7 expression.py has a non-public, underscored class _FromGrouping instead of the public class FromGrouping That should be just fine to use, correct? On Wednesday, January 21, 2015 at 3:56:12 PM UTC-5, Michael Bayer wrote: wow that is awful, how often do I fix a full blown bug, even write tests for it, and don’t put anything in the changelog, no bug report or anything. You can probably patch it to 0.8. Not sure about 0.7, but if 0.7 doesn’t have FromGrouping there should still be some similar concept that can be tested for here.I’m not maintaining either except for security issues back to 0.8. there’s a new website section coming soon that will show this stuff. Kent jkent...@gmail.com javascript: wrote: Here it is: commit 85368d25ed158c85bd19f4a63400884ab1cda26a Author: Mike Bayer m... Date: Sat Jun 8 18:54:14 2013 -0400 get nested joins to render on oracle 8 Sounds like the right commit notes. You still maintaining 0.8? Should that change be patchable in 0.7? On Wednesday, January 21, 2015 at 1:13:21 PM UTC-5, Michael Bayer wrote: Kent jkent...@gmail.com wrote: Mike, When using use_ansi=False for Oracle (8) in conjunction with joinedload-ing an inline view property, SQLAlchemy-0.8.7 renders an inner join instead of an outer join. This has been fixed in SQLAlchemy-0.9.0, but, as I'm not prepared for the migration yet, I was hoping and failing to find the bug ticket and hopefully a patch. Do you know when/where this was fixed and whether the fix would be patch'able in 0.7 or at least 0.8? The attached script runs on 0.9.0+ but the assertion fails on 0.8.7. The only difference in SQL output is the outer join (+): SELECT products.productid AS products_productid, anon_1.productid AS anon_1_productid, anon_1.siteid AS anon_1_siteid, anon_1.qty AS anon_1_qty FROM products, (SELECT inventory.productid AS productid, inventory.siteid AS siteid, sum(inventory.qty) AS qty FROM inventory GROUP BY inventory.productid, inventory.siteid) anon_1 WHERE anon_1.productid(+) = products.productid ORDER BY anon_1.siteid Interestingly, use-ansi=True correctly renders LEFT OUTER JOIN in 0.8.7 but it fails to render as an outer join with use-ansi=False. Thanks for you time and exceptional software, Kent no specific fixes are logged, however http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1 refers to a very large change in how the ORM decides to join things. That would cause some kinds of joinedloads to render differently , which would impact how (+) comes out as well, but i wouldn’t think it would have the effect that the missing (+) is the only change, it would be more than that. So I have no better idea than you, so the method I’d do is just to git bisect (http://git-scm.com/docs/git-bisect) through revisions until you find the fix. If it’s a big merge revision, I can look into it to find something specific, but if you can get me a rev ID that would be a good start. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.com javascript:. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Oracle use_ansi=False inner join problem on inline views
Here it is: commit 85368d25ed158c85bd19f4a63400884ab1cda26a Author: Mike Bayer m... Date: Sat Jun 8 18:54:14 2013 -0400 get nested joins to render on oracle 8 Sounds like the right commit notes. You still maintaining 0.8? Should that change be patchable in 0.7? On Wednesday, January 21, 2015 at 1:13:21 PM UTC-5, Michael Bayer wrote: Kent jkent...@gmail.com javascript: wrote: Mike, When using use_ansi=False for Oracle (8) in conjunction with joinedload-ing an inline view property, SQLAlchemy-0.8.7 renders an inner join instead of an outer join. This has been fixed in SQLAlchemy-0.9.0, but, as I'm not prepared for the migration yet, I was hoping and failing to find the bug ticket and hopefully a patch. Do you know when/where this was fixed and whether the fix would be patch'able in 0.7 or at least 0.8? The attached script runs on 0.9.0+ but the assertion fails on 0.8.7. The only difference in SQL output is the outer join (+): SELECT products.productid AS products_productid, anon_1.productid AS anon_1_productid, anon_1.siteid AS anon_1_siteid, anon_1.qty AS anon_1_qty FROM products, (SELECT inventory.productid AS productid, inventory.siteid AS siteid, sum(inventory.qty) AS qty FROM inventory GROUP BY inventory.productid, inventory.siteid) anon_1 WHERE anon_1.productid(+) = products.productid ORDER BY anon_1.siteid Interestingly, use-ansi=True correctly renders LEFT OUTER JOIN in 0.8.7 but it fails to render as an outer join with use-ansi=False. Thanks for you time and exceptional software, Kent no specific fixes are logged, however http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1 refers to a very large change in how the ORM decides to join things. That would cause some kinds of joinedloads to render differently , which would impact how (+) comes out as well, but i wouldn’t think it would have the effect that the missing (+) is the only change, it would be more than that. So I have no better idea than you, so the method I’d do is just to git bisect (http://git-scm.com/docs/git-bisect) through revisions until you find the fix. If it’s a big merge revision, I can look into it to find something specific, but if you can get me a rev ID that would be a good start. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Oracle use_ansi=False inner join problem on inline views
Hmmm 0.7 is missing expression.FromGrouping... I imagine that is a big deal, isn't it, like not really patchable? On Wednesday, January 21, 2015 at 3:11:29 PM UTC-5, Kent wrote: Here it is: commit 85368d25ed158c85bd19f4a63400884ab1cda26a Author: Mike Bayer m... Date: Sat Jun 8 18:54:14 2013 -0400 get nested joins to render on oracle 8 Sounds like the right commit notes. You still maintaining 0.8? Should that change be patchable in 0.7? On Wednesday, January 21, 2015 at 1:13:21 PM UTC-5, Michael Bayer wrote: Kent jkent...@gmail.com wrote: Mike, When using use_ansi=False for Oracle (8) in conjunction with joinedload-ing an inline view property, SQLAlchemy-0.8.7 renders an inner join instead of an outer join. This has been fixed in SQLAlchemy-0.9.0, but, as I'm not prepared for the migration yet, I was hoping and failing to find the bug ticket and hopefully a patch. Do you know when/where this was fixed and whether the fix would be patch'able in 0.7 or at least 0.8? The attached script runs on 0.9.0+ but the assertion fails on 0.8.7. The only difference in SQL output is the outer join (+): SELECT products.productid AS products_productid, anon_1.productid AS anon_1_productid, anon_1.siteid AS anon_1_siteid, anon_1.qty AS anon_1_qty FROM products, (SELECT inventory.productid AS productid, inventory.siteid AS siteid, sum(inventory.qty) AS qty FROM inventory GROUP BY inventory.productid, inventory.siteid) anon_1 WHERE anon_1.productid(+) = products.productid ORDER BY anon_1.siteid Interestingly, use-ansi=True correctly renders LEFT OUTER JOIN in 0.8.7 but it fails to render as an outer join with use-ansi=False. Thanks for you time and exceptional software, Kent no specific fixes are logged, however http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1 refers to a very large change in how the ORM decides to join things. That would cause some kinds of joinedloads to render differently , which would impact how (+) comes out as well, but i wouldn’t think it would have the effect that the missing (+) is the only change, it would be more than that. So I have no better idea than you, so the method I’d do is just to git bisect (http://git-scm.com/docs/git-bisect) through revisions until you find the fix. If it’s a big merge revision, I can look into it to find something specific, but if you can get me a rev ID that would be a good start. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Oracle use_ansi=False inner join problem on inline views
try it! Kent jkentbo...@gmail.com wrote: So, in 0.7 expression.py has a non-public, underscored class _FromGrouping instead of the public class FromGrouping That should be just fine to use, correct? On Wednesday, January 21, 2015 at 3:56:12 PM UTC-5, Michael Bayer wrote: wow that is awful, how often do I fix a full blown bug, even write tests for it, and don’t put anything in the changelog, no bug report or anything. You can probably patch it to 0.8. Not sure about 0.7, but if 0.7 doesn’t have FromGrouping there should still be some similar concept that can be tested for here.I’m not maintaining either except for security issues back to 0.8. there’s a new website section coming soon that will show this stuff. Kent jkent...@gmail.com wrote: Here it is: commit 85368d25ed158c85bd19f4a63400884ab1cda26a Author: Mike Bayer m... Date: Sat Jun 8 18:54:14 2013 -0400 get nested joins to render on oracle 8 Sounds like the right commit notes. You still maintaining 0.8? Should that change be patchable in 0.7? On Wednesday, January 21, 2015 at 1:13:21 PM UTC-5, Michael Bayer wrote: Kent jkent...@gmail.com wrote: Mike, When using use_ansi=False for Oracle (8) in conjunction with joinedload-ing an inline view property, SQLAlchemy-0.8.7 renders an inner join instead of an outer join. This has been fixed in SQLAlchemy-0.9.0, but, as I'm not prepared for the migration yet, I was hoping and failing to find the bug ticket and hopefully a patch. Do you know when/where this was fixed and whether the fix would be patch'able in 0.7 or at least 0.8? The attached script runs on 0.9.0+ but the assertion fails on 0.8.7. The only difference in SQL output is the outer join (+): SELECT products.productid AS products_productid, anon_1.productid AS anon_1_productid, anon_1.siteid AS anon_1_siteid, anon_1.qty AS anon_1_qty FROM products, (SELECT inventory.productid AS productid, inventory.siteid AS siteid, sum(inventory.qty) AS qty FROM inventory GROUP BY inventory.productid, inventory.siteid) anon_1 WHERE anon_1.productid(+) = products.productid ORDER BY anon_1.siteid Interestingly, use-ansi=True correctly renders LEFT OUTER JOIN in 0.8.7 but it fails to render as an outer join with use-ansi=False. Thanks for you time and exceptional software, Kent no specific fixes are logged, however http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1 refers to a very large change in how the ORM decides to join things. That would cause some kinds of joinedloads to render differently , which would impact how (+) comes out as well, but i wouldn’t think it would have the effect that the missing (+) is the only change, it would be more than that. So I have no better idea than you, so the method I’d do is just to git bisect (http://git-scm.com/docs/git-bisect) through revisions until you find the fix. If it’s a big merge revision, I can look into it to find something specific, but if you can get me a rev ID that would be a good start. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Oracle use_ansi=False inner join problem on inline views
wow that is awful, how often do I fix a full blown bug, even write tests for it, and don’t put anything in the changelog, no bug report or anything. You can probably patch it to 0.8. Not sure about 0.7, but if 0.7 doesn’t have FromGrouping there should still be some similar concept that can be tested for here.I’m not maintaining either except for security issues back to 0.8. there’s a new website section coming soon that will show this stuff. Kent jkentbo...@gmail.com wrote: Here it is: commit 85368d25ed158c85bd19f4a63400884ab1cda26a Author: Mike Bayer m... Date: Sat Jun 8 18:54:14 2013 -0400 get nested joins to render on oracle 8 Sounds like the right commit notes. You still maintaining 0.8? Should that change be patchable in 0.7? On Wednesday, January 21, 2015 at 1:13:21 PM UTC-5, Michael Bayer wrote: Kent jkent...@gmail.com wrote: Mike, When using use_ansi=False for Oracle (8) in conjunction with joinedload-ing an inline view property, SQLAlchemy-0.8.7 renders an inner join instead of an outer join. This has been fixed in SQLAlchemy-0.9.0, but, as I'm not prepared for the migration yet, I was hoping and failing to find the bug ticket and hopefully a patch. Do you know when/where this was fixed and whether the fix would be patch'able in 0.7 or at least 0.8? The attached script runs on 0.9.0+ but the assertion fails on 0.8.7. The only difference in SQL output is the outer join (+): SELECT products.productid AS products_productid, anon_1.productid AS anon_1_productid, anon_1.siteid AS anon_1_siteid, anon_1.qty AS anon_1_qty FROM products, (SELECT inventory.productid AS productid, inventory.siteid AS siteid, sum(inventory.qty) AS qty FROM inventory GROUP BY inventory.productid, inventory.siteid) anon_1 WHERE anon_1.productid(+) = products.productid ORDER BY anon_1.siteid Interestingly, use-ansi=True correctly renders LEFT OUTER JOIN in 0.8.7 but it fails to render as an outer join with use-ansi=False. Thanks for you time and exceptional software, Kent no specific fixes are logged, however http://docs.sqlalchemy.org/en/rel_0_9/changelog/migration_09.html#many-join-and-left-outer-join-expressions-will-no-longer-be-wrapped-in-select-from-as-anon-1 refers to a very large change in how the ORM decides to join things.That would cause some kinds of joinedloads to render differently , which would impact how (+) comes out as well, but i wouldn’t think it would have the effect that the missing (+) is the only change, it would be more than that. So I have no better idea than you, so the method I’d do is just to git bisect (http://git-scm.com/docs/git-bisect) through revisions until you find the fix. If it’s a big merge revision, I can look into it to find something specific, but if you can get me a rev ID that would be a good start. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
