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.
