dotConnect for Oracle: ESQL ANYELEMENT not translated sometimes

dotConnect for Oracle: ESQL ANYELEMENT not translated sometimes

Postby Dennis Wanke » 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:

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.
Dennis Wanke
 
Posts: 57
Joined: Tue 11 Mar 2014 07:49

Re: dotConnect for Oracle: ESQL ANYELEMENT not translated sometimes

Postby Shalex » Fri 14 Mar 2014 16:58

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
Devart Team
 
Posts: 7612
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for Oracle: ESQL ANYELEMENT not translated sometimes

Postby Shalex » Thu 24 Apr 2014 16:56

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.
Shalex
Devart Team
 
Posts: 7612
Joined: Thu 14 Aug 2008 12:44

Re: dotConnect for Oracle: ESQL ANYELEMENT not translated sometimes

Postby Dennis Wanke » Fri 25 Apr 2014 11:02

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-scalar-subqueries
Dennis Wanke
 
Posts: 57
Joined: Tue 11 Mar 2014 07:49


Return to dotConnect for Oracle