ORA-00907 missing right parenthesis on Oracle 12

[rohlicher]

Hi,

The LINQ statement below works fine with an Oracle 11 database but fails on an Oracle 12 database, a "missing right parenthesis" Oracle error is thrown.

Oracle exception:
ORA-00907 missing right parenthesis

Code sample:

Code: Select all

const string id = "some id";
DateTime date = DateTime.Today;
var pricesQuery = database.SdOtcFxOptionSet
  .Where(sdOtcFxOption => sdOtcFxOption.Id == id && sdOtcFxOption.ValuationDate.Value <= date);

var askPricesQuery = pricesQuery
  .Where(price => price.BaseMktPricePercAsk != null)
  .Select(price => new SearchResult
  {
    PriceDate = price.ValuationDate,
    PriceAsk = price.BaseMktPricePercAsk,
    PriceBid = null
  })
  .OrderByDescending(price => price.PriceDate)
  .Take(1);

var bidPricesQuery = pricesQuery
  .Where(price => price.BaseMktPricePercBid != null)
  .Select(price => new SearchResult
  {
    PriceDate = price.ValuationDate,
    PriceAsk = null,
    PriceBid = price.BaseMktPricePercBid
  })
  .OrderByDescending(price => price.PriceDate)
  .Take(1);

var allPricesQuery = askPricesQuery.Concat(bidPricesQuery);
var result = allPricesQuery.ToList();

The .Concat() method (close to the end of the code sample above) results in an UNION statement which is different from a "paranthesis" POV on Oracle 11 and Oracle 12.

Can you please investigate this issue?

Thanks,
robert

[Shalex]

Please give us the following information:
a) your ORM (Entity Framework or LinqConnect)
b) a generated SQL which fails to execute (turn on dbMonitor)
c) send us a small test project with the corresponding DDL script

[rohlicher]

Hello,

We are using EntityFramework 6.1.3 and Devart 8.5.506.

Generated queries:
/* Oracle 11 */

Code: Select all

SELECT 
"UnionAll1".C1,
"UnionAll1".C2,
"UnionAll1".VALUATION_DATE AS C3,
"UnionAll1".BASE_MKT_PRICE_PERC_ASK AS C4,
"UnionAll1".C3 AS C5
FROM  (SELECT 
	"top".C3 AS C1,
	"top".C1 AS C2,
	"top".VALUATION_DATE,
	"top".BASE_MKT_PRICE_PERC_ASK,
	"top".C2 AS C3
	FROM ( SELECT 
		"Project1".VALUATION_DATE,
		"Project1".BASE_MKT_PRICE_PERC_ASK,
		"Project1".C1,
		"Project1".C2,
		"Project1".C3
		FROM ( SELECT 
			"Extent1".VALUATION_DATE,
			"Extent1".BASE_MKT_PRICE_PERC_ASK,
			:p__linq__3 AS C1,
			TO_NUMBER(NULL) AS C2,
			1 AS C3
			FROM SD_OTC_FX_OPTION "Extent1"
			WHERE (("Extent1".GAIN_ID = :p__linq__0) AND ((:p__linq__1 IS NULL) OR ("Extent1".VALUATION_DATE <= :p__linq__2))) AND ("Extent1".BASE_MKT_PRICE_PERC_ASK IS NOT NULL)
		)  "Project1"
		ORDER BY "Project1".VALUATION_DATE DESC
	)  "top"
	WHERE  ROWNUM <= 1
UNION ALL
	SELECT 
	"top".C3 AS C1,
	"top".C1 AS C2,
	"top".VALUATION_DATE,
	"top".C2 AS C3,
	"top".BASE_MKT_PRICE_PERC_BID
	FROM ( SELECT 
		"Project3".VALUATION_DATE,
		"Project3".BASE_MKT_PRICE_PERC_BID,
		"Project3".C1,
		"Project3".C2,
		"Project3".C3
		FROM ( SELECT 
			"Extent2".VALUATION_DATE,
			"Extent2".BASE_MKT_PRICE_PERC_BID,
			:p__linq__7 AS C1,
			TO_NUMBER(NULL) AS C2,
			1 AS C3
			FROM SD_OTC_FX_OPTION "Extent2"
			WHERE (("Extent2".GAIN_ID = :p__linq__4) AND ((:p__linq__5 IS NULL) OR ("Extent2".VALUATION_DATE <= :p__linq__6))) AND ("Extent2".BASE_MKT_PRICE_PERC_BID IS NOT NULL)
		)  "Project3"
		ORDER BY "Project3".VALUATION_DATE DESC
	)  "top"
	WHERE  ROWNUM <= 1) "UnionAll1"

/* Oracle 12 */

Code: Select all

SELECT 
"UnionAll1".C1,
"UnionAll1".C2,
"UnionAll1".VALUATION_DATE AS C3,
"UnionAll1".BASE_MKT_PRICE_PERC_ASK AS C4,
"UnionAll1".C3 AS C5
FROM  (SELECT 
	"Project1".C3 AS C1,
	"Project1".C1 AS C2,
	"Project1".VALUATION_DATE,
	"Project1".BASE_MKT_PRICE_PERC_ASK,
	"Project1".C2 AS C3
	FROM ( SELECT 
		"Extent1".VALUATION_DATE,
		"Extent1".BASE_MKT_PRICE_PERC_ASK,
		:p__linq__3 AS C1,
		TO_NUMBER(NULL) AS C2,
		1 AS C3
		FROM SD_OTC_FX_OPTION "Extent1"
		WHERE (("Extent1".GAIN_ID = :p__linq__0) AND ((:p__linq__1 IS NULL) OR ("Extent1".VALUATION_DATE <= :p__linq__2))) AND ("Extent1".BASE_MKT_PRICE_PERC_ASK IS NOT NULL)
	)  "Project1"
	ORDER BY "Project1".VALUATION_DATE DESC
	FETCH FIRST 1 ROWS ONLY
UNION ALL
	SELECT 
	"Project3".C3 AS C1,
	"Project3".C1 AS C2,
	"Project3".VALUATION_DATE,
	"Project3".C2 AS C3,
	"Project3".BASE_MKT_PRICE_PERC_BID
	FROM ( SELECT 
		"Extent2".VALUATION_DATE,
		"Extent2".BASE_MKT_PRICE_PERC_BID,
		:p__linq__7 AS C1,
		TO_NUMBER(NULL) AS C2,
		1 AS C3
		FROM SD_OTC_FX_OPTION "Extent2"
		WHERE (("Extent2".GAIN_ID = :p__linq__4) AND ((:p__linq__5 IS NULL) OR ("Extent2".VALUATION_DATE <= :p__linq__6))) AND ("Extent2".BASE_MKT_PRICE_PERC_BID IS NOT NULL)
	)  "Project3"
	ORDER BY "Project3".VALUATION_DATE DESC
	FETCH FIRST 1 ROWS ONLY) "UnionAll1"

I'll try to come up with a sample but I'm curious why you also need a sample project, I mentioned the used LINQ statement in the first forum entry and now you have the resulting queries.

Regards,
robert

[Shalex]

I'll try to come up with a sample but I'm curious why you also need a sample project, I mentioned the used LINQ statement in the first forum entry and now you have the resulting queries.

We need the model to run your code with.
The corresponding DDL script is not necessary (we will run Update To Database wizard in Entity Developer with your model).

[rohlicher]

Sample created and sent as requested.

Regards,
robert

[Shalex]

We have reproduced the issue and are investigating it. We will notify you when the bug is fixed.

[Shalex]

The bug with using extension methods Take and Skip with Concat/Except/Intersect when talking to Oracle 12c is fixed. We will notify you when the corresponding build of dotConnect for Oracle is available for download.

[Shalex]

The new build of dotConnect for Oracle 8.5.535 is available for download now: http://forums.devart.com/viewtopic.php?f=1&t=32778.