dotConnect for Oracle: ESQL ANYELEMENT not translated sometimes

[Dennis Wanke]

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:

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 invoice

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.

[Shalex]

Thank you for your report. We have reproduced the "ORA-01427: single-row subquery returns more than one row" issue and are investigating it. We will notify you about the result.

[Shalex]

The bug with generating SQL for EntitySQL ANYELEMENT is fixed in the new 8.3.146 build of dotConnect for Oracle.

Be aware that the fix may not help in your particular query if you encounter a limitation of Oracle concerning the usage of an identifier, which is declared in the upper level query, in the subquery.

[Dennis Wanke]

The original error (ORA-01427) is gone with the new build.
However, the fix does indeed introduce the problem mentioned, because the correlated sub-queries now get wrapped in an extra SELECT ... WHERE ROWNUM <= 1 (an additional nesting level). The good news, though, Oracle 12c can cope with this problem: http://orasql.org/2014/02/11/oracle-12c ... subqueries