Bug Querying CLOB Columns using Entity SQL.

Bug Querying CLOB Columns using Entity SQL.

Postby mas » Wed 26 Jan 2011 14:25

I have a table, Metadata, with the columns:
    Name: VARCHAR2(50)
    Value: CLOB

When I execute the following query using Entity SQL,
Code: Select all
var results = context.CreateQuery("SELECT VALUE m FROM Metadatas WHERE m.Value = 'Value1'").ToList();

I get the System.Data.EntityCommandExecutionException exception with the following error message: ORA-00932: inconsistent datatypes: expected - got CLOB

However, the following query works:
Code: Select all
var results = context.Metadatas.Where(x => x.Value == "Value1").ToList();

And this query also works:
Code: Select all
results = context.CreateQuery("SELECT VALUE m FROM Metadatas AS m WHERE m.Value = @v", new ObjectParameter("v", "Value1")).ToList();

I looked into DB Monitor to see what was going on, and I found that the first query (the error case) produced the following SQL:
Code: Select all
SELECT
"Extent1".ID AS ID,
"Extent1".NAME AS NAME,
"Extent1".VALUE AS VALUE
FROM METADATA "Extent1"
WHERE "Extent1".VALUE = 'Value1'

The second query:
Code: Select all
SELECT
"Extent1".ID AS ID,
"Extent1".NAME AS NAME,
"Extent1".VALUE AS VALUE
FROM METADATA "Extent1"
WHERE "Extent1".VALUE LIKE TO_CLOB('Value1')

The third query:
Code: Select all
SELECT
"Extent1".ID AS ID,
"Extent1".NAME AS NAME,
"Extent1".VALUE AS VALUE
FROM METADATA "Extent1"
WHERE "Extent1".VALUE LIKE :v

It seems in the first case, there a bug where no special handling done for comparing CLOB values.

I am using Devart dotConnect for Oracle Professional 6.0.86.0.
Last edited by mas on Wed 26 Jan 2011 15:56, edited 1 time in total.
mas
 
Posts: 25
Joined: Mon 15 Nov 2010 12:09

Postby AndreyR » Wed 26 Jan 2011 15:41

Thank you for the report, we have reproduced the error.
I will let you know about the result of our investigation.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby AndreyR » Thu 03 Mar 2011 14:06

We have fixed this error. The fixed build will be available in a week or so.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle