| Subject: | paginated resultset broken after |
The following resultset works on $rs->all but fails on $rs->search({},{rows=>5})->all
It looks like the sub-select is getting completely borked.
Rumor has it (on IRC) that the bug was introduced by: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/DBIx-
Class.git;a=commitdiff;h=fcb7fcbb6bde5f9a211c62011b3110f07828caec;hp=ee53ca0f8e39882bc30fee5cb6db9e0318d09d59
Thanks!
## DBIC code
$rs = $rs->search(
{
-exists => $other_rs->search(
{ 'inner_order_items.vendor' => $v->id, 'inner_order_items.orderid' => { -ident => 'me.orderid' } },
{ alias => 'inner_order_items', select => ['orderid'] }
)->search( { "inner_order_items.stamp_insert" => { -between => [ $begin, $end ] } } )
}
);
-- working query in v0.0891 ($rs->all)
SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info
FROM orders me
WHERE EXISTS(
SELECT inner_order_items.orderid
FROM order_items inner_order_items
WHERE inner_order_items.stamp_insert BETWEEN '2011-05-05 00:00:00' AND '2011-05-06 00:00:00' AND inner_order_items.orderid = me.orderid
AND inner_order_items.vendor = '3639'
)
---------------------------------------------
-- broken query in v0.0891 ($rs->search({}, {rows => 5})
SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info
FROM(
SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info
FROM order_items inner_order_items
WHERE inner_order_items.stamp_insert BETWEEN '2011-05-05 00:00:00' AND '2011-05-06 00:00:00' AND inner_order_items.orderid = me.orderid
AND inner_order_items.vendor = '3639'
) ) ) ) me
WHERE ROWNUM <= '5'
---------------------------------------------
-- previously working query in v0.08127 ($rs->search({}, {rows => 5})
SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info
FROM (
SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info
FROM orders me
WHERE EXISTS(
SELECT inner_order_items.orderid
FROM order_items inner_order_items
WHERE inner_order_items.stamp_insert BETWEEN '2011-05-05 00:00:00' AND '2011-05-06 00:00:00' AND inner_order_items.orderid =
me.orderid AND inner_order_items.vendor = '3639'
)
) me
WHERE ROWNUM <= 5
Oracle error:
DBI Exception: DBD::Oracle::db prepare_cached failed: ORA-00933: SQL command not properly ended (DBD ERROR: error possibly near <*>
indicator at char 4296 in '
SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info
FROM (
SELECT 1 AS fake -- columns snipped for clarity and proprietary schema info
FROM order_items inner_order_items WHERE ( ( ( inner_order_items.stamp_insert BETWEEN :p1 AND :p2 ) AND ( inner_order_items.orderid =
me.orderid AND inner_order_items.vendor = :p3 ) ) ) )<*>) )
) me WHERE ROWNUM <= :p4