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