ORA-12704: Character Mismatch with Devart version 6.70
Posted: Mon 12 Mar 2012 15:42
We have the following LINQ statement:
var adAccount = account as ActiveDirectoryAccount;
return context.Accounts.OfType()
.Where(a => a.Sid == adAccount.Sid)
.Include(a => a.Settings)
.Include(a => a.SearchPatterns)
.SingleOrDefault();
Results in the following SQL-Statement:
SELECT
"UnionAll1".ID AS C1,
"UnionAll1".ID1 AS C2,
"UnionAll1".C2 AS C3,
"UnionAll1".ID2 AS C4,
"UnionAll1".CODE AS C5,
"UnionAll1".ACTIVE_F AS C6,
"UnionAll1".SID AS C7,
"UnionAll1".C1 AS C8,
"UnionAll1".ID3 AS C9,
"UnionAll1".ACCNT_ID AS C10,
"UnionAll1".KEYCODE AS C11,
"UnionAll1".VALUE AS C12,
"UnionAll1".C3 AS C13,
"UnionAll1".C4 AS C14,
"UnionAll1".C5 AS C15,
"UnionAll1".C6 AS C16,
"UnionAll1".C7 AS C17,
"UnionAll1".C8 AS C18,
"UnionAll1".C9 AS C19,
"UnionAll1".C10 AS C20
FROM (SELECT
CASE WHEN "Extent3".ID IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C1,
"Extent2".ID,
"Extent1".ID AS ID1,
'0X0X' AS C2,
"Extent1".ID AS ID2,
"Extent2".CODE,
"Extent2".ACTIVE_F,
"Extent1".SID,
"Extent3".ID AS ID3,
"Extent3".ACCNT_ID,
"Extent3".KEYCODE,
"Extent3".VALUE,
TO_NUMBER(NULL) AS C3,
TO_NCHAR(NULL) AS C4,
TO_NUMBER(NULL) AS C5,
TO_NUMBER(NULL) AS C6,
TO_NCHAR(NULL) AS C7,
TO_NUMBER(NULL) AS C8,
TO_BLOB(NULL) AS C9,
TO_NUMBER(NULL) AS C10
FROM TICON.ACTAD0E "Extent1"
INNER JOIN TICON.ACCNT "Extent2" ON "Extent1".ID = "Extent2".ID
LEFT OUTER JOIN TICON.ACTST "Extent3" ON "Extent1".ID = "Extent3".ACCNT_ID
WHERE "Extent1".SID = :p__linq__0
UNION ALL
SELECT
2 AS C1,
"Extent5".ID,
"Extent4".ID AS ID1,
'0X0X' AS C2,
"Extent4".ID AS ID2,
"Extent5".CODE,
"Extent5".ACTIVE_F,
"Extent4".SID,
TO_NUMBER(NULL) AS C3,
TO_NUMBER(NULL) AS C4,
TO_NCHAR(NULL) AS C5,
TO_NCHAR(NULL) AS C6,
"Extent6".ID AS ID3,
"Extent6".CODE AS CODE1,
"Extent6".TYPE_S,
"Extent6".DISPLAY_TYPE_S,
"Extent6".VALID_COLUMNS,
"Extent6".AUTO_SEARCH_F,
"Extent6".QUERY_BLOB,
"Extent6".ACCNT_ID
FROM TICON.ACTAD0E "Extent4"
INNER JOIN TICON.ACCNT "Extent5" ON "Extent4".ID = "Extent5".ID
INNER JOIN TICON.LISTD "Extent6" ON "Extent4".ID = "Extent6".ACCNT_ID
WHERE "Extent4".SID = :p__linq__0) "UnionAll1"
ORDER BY "UnionAll1".ID ASC, "UnionAll1".ID2 ASC, "UnionAll1".C1 ASC
Previous working version was 6.50 with the following SQL-Statement:
SELECT
"UnionAll1".ID AS C1,
"UnionAll1".ID1 AS C2,
"UnionAll1".C2 AS C3,
"UnionAll1".ID2 AS C4,
"UnionAll1".CODE AS C5,
"UnionAll1".ACTIVE_F AS C6,
"UnionAll1".SID AS C7,
"UnionAll1".C1 AS C8,
"UnionAll1".ID3 AS C9,
"UnionAll1".ACCNT_ID AS C10,
"UnionAll1".KEYCODE AS C11,
"UnionAll1".VALUE AS C12,
"UnionAll1".C3 AS C13,
"UnionAll1".C4 AS C14,
"UnionAll1".C5 AS C15,
"UnionAll1".C6 AS C16,
"UnionAll1".C7 AS C17,
"UnionAll1".C8 AS C18,
"UnionAll1".C9 AS C19,
"UnionAll1".C10 AS C20
FROM (SELECT
CASE WHEN "Extent3".ID IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C1,
"Extent2".ID,
"Extent1".ID AS ID1,
'0X0X' AS C2,
"Extent1".ID AS ID2,
"Extent2".CODE,
"Extent2".ACTIVE_F,
"Extent1".SID,
"Extent3".ID AS ID3,
"Extent3".ACCNT_ID,
"Extent3".KEYCODE,
"Extent3".VALUE,
TO_NUMBER(NULL) AS C3,
TO_CHAR(NULL) AS C4,
TO_NUMBER(NULL) AS C5,
TO_NUMBER(NULL) AS C6,
TO_CHAR(NULL) AS C7,
TO_NUMBER(NULL) AS C8,
TO_BLOB(NULL) AS C9,
TO_NUMBER(NULL) AS C10
FROM TICON.ACTAD0E "Extent1"
INNER JOIN TICON.ACCNT "Extent2" ON "Extent1".ID = "Extent2".ID
LEFT OUTER JOIN TICON.ACTST "Extent3" ON "Extent1".ID = "Extent3".ACCNT_ID
WHERE "Extent1".SID = :p__linq__0
UNION ALL
SELECT
2 AS C1,
"Extent5".ID,
"Extent4".ID AS ID1,
'0X0X' AS C2,
"Extent4".ID AS ID2,
"Extent5".CODE,
"Extent5".ACTIVE_F,
"Extent4".SID,
TO_NUMBER(NULL) AS C3,
TO_NUMBER(NULL) AS C4,
TO_CHAR(NULL) AS C5,
TO_CHAR(NULL) AS C6,
"Extent6".ID AS ID3,
"Extent6".CODE AS CODE1,
"Extent6".TYPE_S,
"Extent6".DISPLAY_TYPE_S,
"Extent6".VALID_COLUMNS,
"Extent6".AUTO_SEARCH_F,
"Extent6".QUERY_BLOB,
"Extent6".ACCNT_ID
FROM TICON.ACTAD0E "Extent4"
INNER JOIN TICON.ACCNT "Extent5" ON "Extent4".ID = "Extent5".ID
INNER JOIN TICON.LISTD "Extent6" ON "Extent4".ID = "Extent6".ACCNT_ID
WHERE "Extent4".SID = :p__linq__0) "UnionAll1"
ORDER BY "UnionAll1".ID ASC, "UnionAll1".ID2 ASC, "UnionAll1".C1 ASC
The main difference is TO_NCHAR instead of TO_CHAR within the union. Even changing the column type in database to NCHAR or NVARCHAR2 does not fix the problem.
var adAccount = account as ActiveDirectoryAccount;
return context.Accounts.OfType()
.Where(a => a.Sid == adAccount.Sid)
.Include(a => a.Settings)
.Include(a => a.SearchPatterns)
.SingleOrDefault();
Results in the following SQL-Statement:
SELECT
"UnionAll1".ID AS C1,
"UnionAll1".ID1 AS C2,
"UnionAll1".C2 AS C3,
"UnionAll1".ID2 AS C4,
"UnionAll1".CODE AS C5,
"UnionAll1".ACTIVE_F AS C6,
"UnionAll1".SID AS C7,
"UnionAll1".C1 AS C8,
"UnionAll1".ID3 AS C9,
"UnionAll1".ACCNT_ID AS C10,
"UnionAll1".KEYCODE AS C11,
"UnionAll1".VALUE AS C12,
"UnionAll1".C3 AS C13,
"UnionAll1".C4 AS C14,
"UnionAll1".C5 AS C15,
"UnionAll1".C6 AS C16,
"UnionAll1".C7 AS C17,
"UnionAll1".C8 AS C18,
"UnionAll1".C9 AS C19,
"UnionAll1".C10 AS C20
FROM (SELECT
CASE WHEN "Extent3".ID IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C1,
"Extent2".ID,
"Extent1".ID AS ID1,
'0X0X' AS C2,
"Extent1".ID AS ID2,
"Extent2".CODE,
"Extent2".ACTIVE_F,
"Extent1".SID,
"Extent3".ID AS ID3,
"Extent3".ACCNT_ID,
"Extent3".KEYCODE,
"Extent3".VALUE,
TO_NUMBER(NULL) AS C3,
TO_NCHAR(NULL) AS C4,
TO_NUMBER(NULL) AS C5,
TO_NUMBER(NULL) AS C6,
TO_NCHAR(NULL) AS C7,
TO_NUMBER(NULL) AS C8,
TO_BLOB(NULL) AS C9,
TO_NUMBER(NULL) AS C10
FROM TICON.ACTAD0E "Extent1"
INNER JOIN TICON.ACCNT "Extent2" ON "Extent1".ID = "Extent2".ID
LEFT OUTER JOIN TICON.ACTST "Extent3" ON "Extent1".ID = "Extent3".ACCNT_ID
WHERE "Extent1".SID = :p__linq__0
UNION ALL
SELECT
2 AS C1,
"Extent5".ID,
"Extent4".ID AS ID1,
'0X0X' AS C2,
"Extent4".ID AS ID2,
"Extent5".CODE,
"Extent5".ACTIVE_F,
"Extent4".SID,
TO_NUMBER(NULL) AS C3,
TO_NUMBER(NULL) AS C4,
TO_NCHAR(NULL) AS C5,
TO_NCHAR(NULL) AS C6,
"Extent6".ID AS ID3,
"Extent6".CODE AS CODE1,
"Extent6".TYPE_S,
"Extent6".DISPLAY_TYPE_S,
"Extent6".VALID_COLUMNS,
"Extent6".AUTO_SEARCH_F,
"Extent6".QUERY_BLOB,
"Extent6".ACCNT_ID
FROM TICON.ACTAD0E "Extent4"
INNER JOIN TICON.ACCNT "Extent5" ON "Extent4".ID = "Extent5".ID
INNER JOIN TICON.LISTD "Extent6" ON "Extent4".ID = "Extent6".ACCNT_ID
WHERE "Extent4".SID = :p__linq__0) "UnionAll1"
ORDER BY "UnionAll1".ID ASC, "UnionAll1".ID2 ASC, "UnionAll1".C1 ASC
Previous working version was 6.50 with the following SQL-Statement:
SELECT
"UnionAll1".ID AS C1,
"UnionAll1".ID1 AS C2,
"UnionAll1".C2 AS C3,
"UnionAll1".ID2 AS C4,
"UnionAll1".CODE AS C5,
"UnionAll1".ACTIVE_F AS C6,
"UnionAll1".SID AS C7,
"UnionAll1".C1 AS C8,
"UnionAll1".ID3 AS C9,
"UnionAll1".ACCNT_ID AS C10,
"UnionAll1".KEYCODE AS C11,
"UnionAll1".VALUE AS C12,
"UnionAll1".C3 AS C13,
"UnionAll1".C4 AS C14,
"UnionAll1".C5 AS C15,
"UnionAll1".C6 AS C16,
"UnionAll1".C7 AS C17,
"UnionAll1".C8 AS C18,
"UnionAll1".C9 AS C19,
"UnionAll1".C10 AS C20
FROM (SELECT
CASE WHEN "Extent3".ID IS NULL THEN TO_NUMBER(NULL) ELSE 1 END AS C1,
"Extent2".ID,
"Extent1".ID AS ID1,
'0X0X' AS C2,
"Extent1".ID AS ID2,
"Extent2".CODE,
"Extent2".ACTIVE_F,
"Extent1".SID,
"Extent3".ID AS ID3,
"Extent3".ACCNT_ID,
"Extent3".KEYCODE,
"Extent3".VALUE,
TO_NUMBER(NULL) AS C3,
TO_CHAR(NULL) AS C4,
TO_NUMBER(NULL) AS C5,
TO_NUMBER(NULL) AS C6,
TO_CHAR(NULL) AS C7,
TO_NUMBER(NULL) AS C8,
TO_BLOB(NULL) AS C9,
TO_NUMBER(NULL) AS C10
FROM TICON.ACTAD0E "Extent1"
INNER JOIN TICON.ACCNT "Extent2" ON "Extent1".ID = "Extent2".ID
LEFT OUTER JOIN TICON.ACTST "Extent3" ON "Extent1".ID = "Extent3".ACCNT_ID
WHERE "Extent1".SID = :p__linq__0
UNION ALL
SELECT
2 AS C1,
"Extent5".ID,
"Extent4".ID AS ID1,
'0X0X' AS C2,
"Extent4".ID AS ID2,
"Extent5".CODE,
"Extent5".ACTIVE_F,
"Extent4".SID,
TO_NUMBER(NULL) AS C3,
TO_NUMBER(NULL) AS C4,
TO_CHAR(NULL) AS C5,
TO_CHAR(NULL) AS C6,
"Extent6".ID AS ID3,
"Extent6".CODE AS CODE1,
"Extent6".TYPE_S,
"Extent6".DISPLAY_TYPE_S,
"Extent6".VALID_COLUMNS,
"Extent6".AUTO_SEARCH_F,
"Extent6".QUERY_BLOB,
"Extent6".ACCNT_ID
FROM TICON.ACTAD0E "Extent4"
INNER JOIN TICON.ACCNT "Extent5" ON "Extent4".ID = "Extent5".ID
INNER JOIN TICON.LISTD "Extent6" ON "Extent4".ID = "Extent6".ACCNT_ID
WHERE "Extent4".SID = :p__linq__0) "UnionAll1"
ORDER BY "UnionAll1".ID ASC, "UnionAll1".ID2 ASC, "UnionAll1".C1 ASC
The main difference is TO_NCHAR instead of TO_CHAR within the union. Even changing the column type in database to NCHAR or NVARCHAR2 does not fix the problem.