glad it works, like I said it wasn't a well attended-to feature so the basic issue was fairly obvious. Just not sure if it takes more elaborate contingencies into account.
On Jan 30, 2010, at 5:29 PM, Kent wrote: > Wow. > > I am very impressed. The patch seems to have fixed this... I'll > inspect it closer later to make sure it all looks correct. > > That was very fast. Thanks for taking the time even on a Saturday for > this. > > I'm amazed at how fast you responded and fixed that. > > Thanks, > > Kent > > > > On Jan 30, 2:03 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: >> On Jan 30, 2010, at 1:05 PM, Kent wrote: >> >>> While its certainly possible I've done something that isn't designed >>> for, I am getting inequivalent SQL statements when I flip to >>> use_ansi=False (unfortunately, I need to support oracle 8i). >> >> I've committed a fix in r6712 which changes how the traversal of joins is >> performed when it searches for its new WHERE criterion. The traversal is >> simplified to only look for joins and their immediate sub-joins in the given >> select, without traversing any deeper, including within further subqueries >> in those joins. This fixes a rudimentary test case I was able to produce >> which is similar, though less verbose, than your example here, although I >> think it will allow your example to work. I don't know that the simplified >> traversal of joins will do what's expected in all cases, though it works for >> the half dozen tests we have for use_ansi=False. >> >> >> >>> I may try duplicating the problem with a more simple query so I can >>> post the tables & mappers here. >> >>> This is the SQL output I designed for (which works well) and also the >>> results I get with use_ansi set to True: >> >>> SELECT anon_1.orders_orderid AS anon_1_orders_orderid, >>> anon_1.orders_orderdate AS anon_1_orders_orderdate, >>> anon_1.orders_customerid AS anon_1_orders_customerid, anon_1.totalsale >>> AS anon_1_totalsale, anon_1.ordersummary AS anon_1_ordersummary, >>> customers_1.customerid AS customers_1_customerid, >>> customers_1.phonenumber AS customers_1_phonenumber, >>> customers_1.firstname AS customers_1_firstname, customers_1.lastname >>> AS customers_1_lastname, customers_1.address1 AS customers_1_address1, >>> customers_1.address2 AS customers_1_address2, customers_1.city AS >>> customers_1_city, customers_1.state AS customers_1_state, >>> customers_1.zip AS customers_1_zip, customers_1.email AS >>> customers_1_email >>> FROM ( >>> SELECT orders_orderid, orders_orderdate, orders_customerid, >>> totalsale, ordersummary >>> FROM ( >>> SELECT orders.orderid AS orders_orderid, orders.orderdate AS >>> orders_orderdate, orders.customerid AS orders_customerid, >>> ( >>> SELECT sum(od__a.qtyordered * od__a.saleprice) AS sum_1 >>> FROM orderdetails od__a >>> WHERE orders.orderid = od__a.orderid >>> ) AS totalsale, >>> ( >>> SELECT max(CASE ranks.seq WHEN 0 THEN ranks.description >>> ELSE '' >>> END) || max(CASE ranks.seq WHEN 1 THEN ', ' || ranks.description ELSE >>> '' END) || max(CASE ranks.seq WHEN 2 THEN ', ' || ranks.description >>> ELSE '' END) || max(CASE ranks.seq WHEN 3 THEN ', ...' ELSE '' END) AS >>> anon_2 >>> FROM ( >>> SELECT p__a.description AS description, >>> (SELECT count(*) AS count_1 >>> FROM orderdetails od__b JOIN products >>> p__b ON p__b.productid = >>> od__b.productid >>> WHERE od__b.orderid = od__a.orderid AND >>> (p__b.regular > >>> p__a.regular OR p__a.regular = p__b.regular AND p__a.productid > >>> p__b.productid) >>> ) AS seq, od__a.orderid AS orderid >>> FROM orderdetails od__a JOIN products p__a >>> ON p__a.productid = od__a.productid >>> ) ranks >>> WHERE ranks.orderid = orders.orderid >>> ) AS ordersummary >>> FROM orders >>> ) >>> WHERE ROWNUM <= 100 >>> ) anon_1 LEFT OUTER JOIN customers customers_1 ON >>> customers_1.customerid = anon_1.orders_customerid >> >>> When flip to use_ansi=False, I get this (I've put ****> <**** around >>> the part that is causing me grief): >> >>> SELECT anon_1.orders_orderid AS anon_1_orders_orderid, >>> anon_1.orders_orderdate AS anon_1_orders_orderdate, >>> anon_1.orders_customerid AS anon_1_orders_customerid, anon_1.totalsale >>> AS anon_1_totalsale, anon_1.ordersummary AS anon_1_ordersummary, >>> customers_1.customerid AS customers_1_customerid, >>> customers_1.phonenumber AS customers_1_phonenumber, >>> customers_1.firstname AS customers_1_firstname, customers_1.lastname >>> AS customers_1_lastname, customers_1.address1 AS customers_1_address1, >>> customers_1.address2 AS customers_1_address2, customers_1.city AS >>> customers_1_city, customers_1.state AS customers_1_state, >>> customers_1.zip AS customers_1_zip, customers_1.email AS >>> customers_1_email >>> FROM ( >>> SELECT orders_orderid, orders_orderdate, orders_customerid, >>> totalsale, ordersummary >>> FROM ( >>> SELECT orders.orderid AS orders_orderid, orders.orderdate AS >>> orders_orderdate, orders.customerid AS orders_customerid, >>> ( >>> SELECT sum(od__a.qtyordered * od__a.saleprice) AS sum_1 >>> FROM orderdetails od__a >>> WHERE orders.orderid = od__a.orderid >>> ) AS totalsale, >>> ( >>> SELECT max(CASE ranks.seq WHEN 0 THEN ranks.description >>> ELSE '' >>> END) || max(CASE ranks.seq WHEN 1 THEN ', ' || ranks.description ELSE >>> '' END) || max(CASE ranks.seq WHEN 2 THEN ', ' || ranks.description >>> ELSE '' END) || max(CASE ranks.seq WHEN 3 THEN ', ...' ELSE '' END) AS >>> anon_2 >>> FROM ( >>> SELECT p__a.description AS description, >>> (SELECT count(*) AS count_1 >>> FROM orderdetails od__b, products p__b >>> WHERE od__b.orderid = od__a.orderid AND >>> (p__b.regular > >>> p__a.regular OR p__a.regular = p__b.regular AND p__a.productid > >>> p__b.productid) AND p__b.productid = od__b.productid >>> ) AS seq, od__a.orderid AS orderid >>> FROM orderdetails od__a, products p__a >>> WHERE p__a.productid = od__a.productid >>> ) ranks >>> ****>, products p__a, orderdetails od__a, products p__b, >>> orderdetails od__b<**** >>> WHERE ranks.orderid = orders.orderid ****>AND >>> p__a.productid = >>> od__a.productid AND p__b.productid = od__b.productid<**** >>> ) AS ordersummary >>> FROM orders >>> ) >>> WHERE ROWNUM <= 100 >>> ) anon_1, customers customers_1, ****>products p__a, orderdetails >>> od__a, products p__b, orderdetails od__b<**** >>> WHERE customers_1.customerid(+) = anon_1.orders_customerid ****>AND >>> p__a.productid = od__a.productid AND p__b.productid = >>> od__b.productid<**** >> >>> p__a, od__a, p__b, and od__b are aliases I've defined in order to work >>> out this fairly complex query >> >>> These joined aliases are being added to several of the outer selects >>> where I didn't intend for them. This is causing a cartesian product >>> and killing the query. >> >>> Any ideas? (Instinct tells me you'd like a more concrete example, >>> which tables, mappers, objects, etc..., but I was hoping this may be >>> enough) >> >>> -- >>> You received this message because you are subscribed to the Google Groups >>> "sqlalchemy" group. >>> To post to this group, send email to sqlalch...@googlegroups.com. >>> To unsubscribe from this group, send email to >>> sqlalchemy+unsubscr...@googlegroups.com. >>> For more options, visit this group >>> athttp://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 sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@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 sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
