EFOracleProvider Work Item Rss Feed

CREATED ISSUE: Top Values query is incorrect

dbrowne — Thu, 26 Feb 2009 18:22:43 GMT

This query pattern applies the row limiting before the ORDER BY, returning 10 random rows and then ordering them, instead of ordering the rows before selecting the top rows.

This is wrong:

select
"Project1"."Address" AS "Address",
"Project1"."City" AS "City",
"Project1"."Region" AS "Region",
"Project1"."PostalCode" AS "PostalCode",
"Project1"."Country" AS "Country",
"Project1"."Phone" AS "Phone",
"Project1"."Fax" AS "Fax"
FROM ( SELECT
"Extent1"."CustomerID" AS "CustomerID",
"Extent1"."CompanyName" AS "CompanyName",
"Extent1"."ContactName" AS "ContactName",
"Extent1"."ContactTitle" AS "ContactTitle",
"Extent1"."Address" AS "Address",
"Extent1"."City" AS "City",
"Extent1"."Region" AS "Region",
"Extent1"."PostalCode" AS "PostalCode",
"Extent1"."Country" AS "Country",
"Extent1"."Phone" AS "Phone",
"Extent1"."Fax" AS "Fax",
1 AS "C1"
FROM "Customers" "Extent1"
) "Project1"
WHERE (ROWNUM <= (10) )
ORDER BY "Project1"."Address" ASC

the right pattern is:

SELECT
"Project1"."Address" AS "Address",
"Project1"."City" AS "City",
"Project1"."Region" AS "Region",
"Project1"."PostalCode" AS "PostalCode",
"Project1"."Country" AS "Country",
"Project1"."Phone" AS "Phone",
"Project1"."Fax" AS "Fax"
FROM ( SELECT
"Extent1"."CustomerID" AS "CustomerID",
"Extent1"."CompanyName" AS "CompanyName",
"Extent1"."ContactName" AS "ContactName",
"Extent1"."ContactTitle" AS "ContactTitle",
"Extent1"."Address" AS "Address",
"Extent1"."City" AS "City",
"Extent1"."Region" AS "Region",
"Extent1"."PostalCode" AS "PostalCode",
"Extent1"."Country" AS "Country",
"Extent1"."Phone" AS "Phone",
"Extent1"."Fax" AS "Fax",
1 AS "C1"
FROM "Customers" "Extent1"
ORDER BY "Extent1"."Address" ASC
) "Project1"
WHERE (ROWNUM <= (10) )

See, eg http://www.oracle.com/technology/oramag/oracle/06-sep/o56asktom.html
for a discussion of this issue.

David (dbrowne_at_microsoft)

CREATED ISSUE: Inappropriate use of Quoted Identifiers

dbrowne — Thu, 26 Feb 2009 17:53:59 GMT

Oracle is always case-sensitive, including the metadata catalogs. To compensate for this Oracle stores ideintifiers in upper case, and for non-quoted identifiers, Oracle converts the identifier to all caps for compilation. For quoted identifiers, oracle requires exact case matching for objects. You can, for instance have a table named "Orders" and one named ORDERS. the DDL "CREATE TABLE Orders ..." creates a different table than the DDL "CREATE TABLE "Orders" ...". Using quoted identifiers and mixed case identifiers is pretty tricky in Oracle, and is very rarely done.

Typically people use non-quoted identifiers which behave like case-insensitive identifiers because Oracle silently upper cases the identifiers and the comparisons.

First the sample should follow Oracle best practices and not use quoted identifiers for the object names. Second the EF code should not use quoted identifiers in the SQL by default.

David (dbrowne_at_microsoft.com)

CREATED ISSUE: INSTR wrong argument order

szunyog — Sat, 18 Oct 2008 13:13:46 GMT

When you use StringProperty.StartsWith("xxx") in the where clause, it generates the a wrong sql sentence:

INSTR ('xxx', "Extent1"."StringProperty") = 1
The problem is that the INSTR sql function in oracle suspects the arguments in the opposite order, so the generated sql condition should be INSTR ("Extent1"."StringProperty", 'xxx') = 1