dotConnect for Oracle: ESQL ANYELEMENT not translated sometimes
Posted: Tue 11 Mar 2014 14:44
Another issue we’ve encountered recently when evaluating dotConnect for Oracle 8.3.115 (in Direct Mode) with Entity Framework 5.0 is Entity SQL operator ANYELEMENT not being properly translated sometimes causing error "ORA-01427: single-row subquery returns more than one row".
Given a typical Invoice/InvoiceLine entity model, the following ESQL query causes the mentioned error:
Note this is a deliberately fictitious query just to demonstrate the problem.
Both SQL Server and SQL CE providers translate the query correctly using TOP (1) clause for the entire subquery.
If two subqueries with UNION are replaced with a single one, the entire query works well. It also works when “VALUE” is omitted in both subqueries (“wrapped select”). And it works even in case the entire invoice line is selected in both subqueries, i.e. “SELECT VALUE line" is used instead of “SELECT VALUE line.InvoiceId”.
The issue is not observed with both SQL Server and SQL CE data providers.
Please confirm that it is a bug in dotConnect for Oracle and provide some estimation if it can (and is going to be) fixed in the coming releases. Your answer will help us making a decision about adopting your provider for our product.
Given a typical Invoice/InvoiceLine entity model, the following ESQL query causes the mentioned error:
Code: Select all
SELECT invoice.Id as InvoiceId
, ANYELEMENT (
(SELECT VALUE line.InvoiceId FROM invoice.Lines AS line)
UNION ALL
(SELECT VALUE line.InvoiceId FROM invoice.Lines AS line)
) AS LineId
FROM InvoiceData.Invoices AS invoiceBoth SQL Server and SQL CE providers translate the query correctly using TOP (1) clause for the entire subquery.
If two subqueries with UNION are replaced with a single one, the entire query works well. It also works when “VALUE” is omitted in both subqueries (“wrapped select”). And it works even in case the entire invoice line is selected in both subqueries, i.e. “SELECT VALUE line" is used instead of “SELECT VALUE line.InvoiceId”.
The issue is not observed with both SQL Server and SQL CE data providers.
Please confirm that it is a bug in dotConnect for Oracle and provide some estimation if it can (and is going to be) fixed in the coming releases. Your answer will help us making a decision about adopting your provider for our product.