Page 1 of 1
ORA-12704 error in the latest dotConnect build (202)
Posted: Fri 19 Aug 2011 14:02
by object
We installed the latest build and some of our LINQ queries failed with ORA-12704 error (charcter set conflict) which I believe occurred before but was fixed in recent dotConnect versions.
Here is the simlified version of the query:
Code: Select all
string seriesId = "828301186000";
var seasons = from s in _entities.Seasons
join se in _entities.Series on s.SerieId equals se.SerieId
join st in _entities.SeasonsTitles on s.SeasonId equals st.SeasonId into stj
from st in stj.DefaultIfEmpty()
where se.PiProgramsCollectionId == seriesId
select new
{
Programmes = from pse in _entities.Seasons
join ps in _entities.Series on pse.SerieId equals ps.SerieId
join p in _entities.Programmes on pse.SeasonId equals p.SeasonId into pj
from p in pj.DefaultIfEmpty()
join tm in _entities.ProgrammesTitles on p.ProgrammeId equals tm.ProgrammeId into tmj
from tm in tmj.DefaultIfEmpty()
join ptm in _entities.ProgrammesTitles on p.ProgrammeId equals ptm.ProgrammeId into ptmj
from ptm in ptmj.DefaultIfEmpty()
where pse.SeasonId == s.SeasonId
select new
{
Title = tm.Title ?? ptm.Title,
}
};
The offending part is in the end: Title = tm.Title ?? ptm.Title. If I replace it as follows:
Code: Select all
select new
{
Title1 = tm.Title,
Title2 = ptm.Title,
}
then everything goes fine. The Oracle data type of Title column is NVARCHAR2(255).
What is strange is that if I run this simple query:
Code: Select all
var titles = from pt1 in _entities.ProgrammesTitles
join pt2 in _entities.ProgrammesTitles on pt1.ProgrammeId equals pt2.ProgrammeId
where pt1.ProgrammeId < 1000
select new
{
Title = pt1.Title ?? pt2.Title
};
then it also runs fine!
If you need generated SQL statements, I can also provide them.
Posted: Fri 19 Aug 2011 14:11
by object
And here is the generated query for failing Linq statement. If I remove the line "WHEN "Join5".SEASON_ID1 IS NULL THEN TO_CHAR(NULL)", then query runs fine. SEASON_ID is "long" (NUMBER(10)).
SELECT "Project1".SERIE_ID AS SERIE_ID,
"Project1".SEASON_ID AS SEASON_ID,
"Project1".C1 AS C1,
"Project1".TITLE_ID AS TITLE_ID,
"Project1".C3 AS C2,
"Project1".C2 AS C3
FROM (SELECT "Extent1".SEASON_ID AS SEASON_ID,
"Extent2".SERIE_ID AS SERIE_ID,
1 AS C1,
CASE
WHEN "Join5".SEASON_ID1 IS NULL THEN TO_CHAR(NULL)
WHEN "Join5".TITLE1 IS NULL THEN "Join5".TITLE2
ELSE "Join5".TITLE1
END AS C2,
"Extent3".TITLE_ID AS TITLE_ID,
CASE
WHEN "Join5".SEASON_ID1 IS NULL THEN TO_NUMBER(NULL)
ELSE 1
END AS C3
FROM SEASONS "Extent1"
INNER JOIN SERIES "Extent2"
ON "Extent1".SERIE_ID = "Extent2".SERIE_ID
LEFT OUTER JOIN SEASONS_TITLES "Extent3"
ON "Extent1".SEASON_ID = "Extent3".SEASON_ID
LEFT OUTER JOIN (SELECT "Extent4".SEASON_ID AS SEASON_ID1,
"Extent4".PRIORITY AS PRIORITY1,
"Extent4".ACTIVE AS ACTIVE1,
"Extent4".EPISODE_TOTAL AS EPISODE_TOTAL,
"Extent4".CREATED AS CREATED1,
"Extent4".CHANGED AS CHANGED1,
"Extent4".MEDIUM AS MEDIUM1,
"Extent4".SEQUENCE_NUMBER AS SEQUENCE_NUMBER,
"Extent4".SERIE_ID AS SERIE_ID,
"Extent4".SEASON_NAME AS SEASON_NAME,
"Extent5".PROGRAMME_ID AS PROGRAMME_ID1,
"Extent5".PI_PROG_ID AS PI_PROG_ID,
"Extent5".EXTERNAL_ID AS EXTERNAL_ID,
"Extent5".EXTERNAL_SYSTEM_ID AS EXTERNAL_SYSTEM_ID,
"Extent5".PRIORITY AS PRIORITY2,
"Extent5".ACTIVE AS ACTIVE2,
"Extent5".EXTRA_MATERIAL AS EXTRA_MATERIAL,
"Extent5".IS_LIVE AS IS_LIVE,
"Extent5".CREATED AS CREATED2,
"Extent5".CHANGED AS CHANGED2,
"Extent5".MEDIUM AS MEDIUM2,
"Extent5".NOTES AS NOTES,
"Extent5".DELETED_IN_PI AS DELETED_IN_PI,
"Extent5".TEXT_CODE AS TEXT_CODE,
"Extent5".SOUND_QUALITY AS SOUND_QUALITY,
"Extent5".VIDEO_FORMAT AS VIDEO_FORMAT,
"Extent5".DURATION AS DURATION,
"Extent5".AGE_LIMIT AS AGE_LIMIT,
"Extent5".SEASON_ID AS SEASON_ID2,
"Extent5".EPISODE_NUMBER AS EPISODE_NUMBER,
"Extent6".TITLE_ID AS TITLE_ID1,
"Extent6".PROGRAMME_ID AS PROGRAMME_ID2,
"Extent6".TITLE AS TITLE1,
"Extent6".TITLE_TYPE AS TITLE_TYPE1,
"Extent6".TITLES_GROUP_TYPE AS TITLES_GROUP_TYPE1,
"Extent6".PURPOSE AS PURPOSE1,
"Extent6".REFERENCE AS REFERENCE1,
"Extent6".LINK AS LINK1,
"Extent6".LABEL AS LABEL1,
"Extent6".RESTRICTION AS RESTRICTION1,
"Extent6".CREATED AS CREATED3,
"Extent6".CHANGED AS CHANGED3,
"Extent7".TITLE_ID AS TITLE_ID2,
"Extent7".PROGRAMME_ID AS PROGRAMME_ID3,
"Extent7".TITLE AS TITLE2,
"Extent7".TITLE_TYPE AS TITLE_TYPE2,
"Extent7".TITLES_GROUP_TYPE AS TITLES_GROUP_TYPE2,
"Extent7".PURPOSE AS PURPOSE2,
"Extent7".REFERENCE AS REFERENCE2,
"Extent7".LINK AS LINK2,
"Extent7".LABEL AS LABEL2,
"Extent7".RESTRICTION AS RESTRICTION2,
"Extent7".CREATED AS CREATED4,
"Extent7".CHANGED AS CHANGED4
FROM SEASONS "Extent4"
LEFT OUTER JOIN PROGRAMMES "Extent5"
ON "Extent4".SEASON_ID = "Extent5".SEASON_ID
LEFT OUTER JOIN PROGRAMMES_TITLES "Extent6"
ON "Extent5".PROGRAMME_ID = "Extent6".PROGRAMME_ID
LEFT OUTER JOIN PROGRAMMES_TITLES "Extent7"
ON "Extent5".PROGRAMME_ID = "Extent7".PROGRAMME_ID) "Join5"
ON "Extent1".SEASON_ID = "Join5".SEASON_ID1
WHERE "Extent2".PI_PROGRAMS_COLLECTION_ID = '828301186000' /* @p__linq__0 */) "Project1"
ORDER BY "Project1".SERIE_ID ASC,
"Project1".SEASON_ID ASC,
"Project1".TITLE_ID ASC,
"Project1".C3 ASC
Posted: Mon 22 Aug 2011 08:47
by object
And just to complete, this is the SQL script that works:
Code: Select all
SELECT "Project1".SERIE_ID AS SERIE_ID,
"Project1".SEASON_ID AS SEASON_ID,
"Project1".C1 AS C1,
"Project1".TITLE_ID AS TITLE_ID,
"Project1".C2 AS C2,
"Project1".TITLE AS TITLE,
"Project1".TITLE1 AS TITLE1
FROM (SELECT "Extent1".SEASON_ID AS SEASON_ID,
"Extent2".SERIE_ID AS SERIE_ID,
1 AS C1,
"Extent3".TITLE_ID AS TITLE_ID,
"Join5".TITLE1 AS TITLE,
"Join5".TITLE2 AS TITLE1,
CASE
WHEN "Join5".SEASON_ID1 IS NULL THEN TO_NUMBER(NULL)
ELSE 1
END AS C2
FROM SEASONS "Extent1"
INNER JOIN SERIES "Extent2"
ON "Extent1".SERIE_ID = "Extent2".SERIE_ID
LEFT OUTER JOIN SEASONS_TITLES "Extent3"
ON "Extent1".SEASON_ID = "Extent3".SEASON_ID
LEFT OUTER JOIN (SELECT "Extent4".SEASON_ID AS SEASON_ID1,
"Extent4".PRIORITY AS PRIORITY1,
"Extent4".ACTIVE AS ACTIVE1,
"Extent4".EPISODE_TOTAL AS EPISODE_TOTAL,
"Extent4".CREATED AS CREATED1,
"Extent4".CHANGED AS CHANGED1,
"Extent4".MEDIUM AS MEDIUM1,
"Extent4".SEQUENCE_NUMBER AS SEQUENCE_NUMBER,
"Extent4".SERIE_ID AS SERIE_ID,
"Extent4".SEASON_NAME AS SEASON_NAME,
"Extent5".PROGRAMME_ID AS PROGRAMME_ID1,
"Extent5".PI_PROG_ID AS PI_PROG_ID,
"Extent5".EXTERNAL_ID AS EXTERNAL_ID,
"Extent5".EXTERNAL_SYSTEM_ID AS EXTERNAL_SYSTEM_ID,
"Extent5".PRIORITY AS PRIORITY2,
"Extent5".ACTIVE AS ACTIVE2,
"Extent5".EXTRA_MATERIAL AS EXTRA_MATERIAL,
"Extent5".IS_LIVE AS IS_LIVE,
"Extent5".CREATED AS CREATED2,
"Extent5".CHANGED AS CHANGED2,
"Extent5".MEDIUM AS MEDIUM2,
"Extent5".NOTES AS NOTES,
"Extent5".DELETED_IN_PI AS DELETED_IN_PI,
"Extent5".TEXT_CODE AS TEXT_CODE,
"Extent5".SOUND_QUALITY AS SOUND_QUALITY,
"Extent5".VIDEO_FORMAT AS VIDEO_FORMAT,
"Extent5".DURATION AS DURATION,
"Extent5".AGE_LIMIT AS AGE_LIMIT,
"Extent5".SEASON_ID AS SEASON_ID2,
"Extent5".EPISODE_NUMBER AS EPISODE_NUMBER,
"Extent6".TITLE_ID AS TITLE_ID1,
"Extent6".PROGRAMME_ID AS PROGRAMME_ID2,
"Extent6".TITLE AS TITLE1,
"Extent6".TITLE_TYPE AS TITLE_TYPE1,
"Extent6".TITLES_GROUP_TYPE AS TITLES_GROUP_TYPE1,
"Extent6".PURPOSE AS PURPOSE1,
"Extent6".REFERENCE AS REFERENCE1,
"Extent6".LINK AS LINK1,
"Extent6".LABEL AS LABEL1,
"Extent6".RESTRICTION AS RESTRICTION1,
"Extent6".CREATED AS CREATED3,
"Extent6".CHANGED AS CHANGED3,
"Extent7".TITLE_ID AS TITLE_ID2,
"Extent7".PROGRAMME_ID AS PROGRAMME_ID3,
"Extent7".TITLE AS TITLE2,
"Extent7".TITLE_TYPE AS TITLE_TYPE2,
"Extent7".TITLES_GROUP_TYPE AS TITLES_GROUP_TYPE2,
"Extent7".PURPOSE AS PURPOSE2,
"Extent7".REFERENCE AS REFERENCE2,
"Extent7".LINK AS LINK2,
"Extent7".LABEL AS LABEL2,
"Extent7".RESTRICTION AS RESTRICTION2,
"Extent7".CREATED AS CREATED4,
"Extent7".CHANGED AS CHANGED4
FROM SEASONS "Extent4"
LEFT OUTER JOIN PROGRAMMES "Extent5"
ON "Extent4".SEASON_ID = "Extent5".SEASON_ID
LEFT OUTER JOIN PROGRAMMES_TITLES "Extent6"
ON "Extent5".PROGRAMME_ID = "Extent6".PROGRAMME_ID
LEFT OUTER JOIN PROGRAMMES_TITLES "Extent7"
ON "Extent5".PROGRAMME_ID = "Extent7".PROGRAMME_ID) "Join5"
ON "Extent1".SEASON_ID = "Join5".SEASON_ID1
WHERE "Extent2".PI_PROGRAMS_COLLECTION_ID = '828301186000' /* @p__linq__0 */) "Project1"
ORDER BY "Project1".SERIE_ID ASC,
"Project1".SEASON_ID ASC,
"Project1".TITLE_ID ASC,
"Project1".C2 ASC
Posted: Mon 22 Aug 2011 08:52
by object
And to point out where the problem lies, compare:
Code: Select all
SELECT "Extent1".SEASON_ID AS SEASON_ID,
"Extent2".SERIE_ID AS SERIE_ID,
1 AS C1,
CASE
WHEN "Join5".SEASON_ID1 IS NULL THEN TO_CHAR(NULL)
WHEN "Join5".TITLE1 IS NULL THEN "Join5".TITLE2
ELSE "Join5".TITLE1
END AS C2,
"Extent3".TITLE_ID AS TITLE_ID,
CASE
WHEN "Join5".SEASON_ID1 IS NULL THEN TO_NUMBER(NULL)
ELSE 1
END AS C3
with
Code: Select all
SELECT "Extent1".SEASON_ID AS SEASON_ID,
"Extent2".SERIE_ID AS SERIE_ID,
1 AS C1,
"Extent3".TITLE_ID AS TITLE_ID,
"Join5".TITLE1 AS TITLE,
"Join5".TITLE2 AS TITLE1,
CASE
WHEN "Join5".SEASON_ID1 IS NULL THEN TO_NUMBER(NULL)
ELSE 1
END AS C2
Now look at "Join5".SEASON_ID1 IS NULL THEN TO_CHAR(NULL) vs. "Join5".SEASON_ID1 IS NULL THEN TO_NUMBER(NULL). I believe this is what causes the problem, and it worked differently in the previous version of dotConnect.
Posted: Wed 24 Aug 2011 13:21
by StanislavK
Could you please specify the script needed to create the database objects used? If possible, please
send us a complete small project with which the issue can be reproduced.
We've tried executing similar queries, and only the 'WHEN "Join".ColumnName IS NULL THEN TO_NUMBER(NULL)' condition was generated in our environment.
Posted: Wed 24 Aug 2011 16:21
by object
I have just uploaded a test project together with database creation script (large, but I didn't want to spend time on shortening it), data initialization program and a couple of tests: failing and successful. I copy here the steps to reproduce the problem:
1. Open the solution.
2. Modify ConnectionStrings.config in project folders.
3. Run the Granitt_model_tables.sql script (in the project root) on your Oracle database.
4. Build and run GranittInitDatabase project that will create some test data.
5. Run unit tests in GranittModel.IntegrationTests folder. One should succeed, the other one should fail.
Let me know if you're missing something.
Posted: Mon 29 Aug 2011 11:29
by StanislavK
Thank you for your assistance, we have reproduced the issue. We will analyze it and inform you about the results as soon as possible.
Posted: Mon 29 Aug 2011 11:49
by object
Oh great! Looking forward for the fix.
Posted: Fri 07 Oct 2011 09:39
by StanislavK
This bug was fixed, the fix is available in the latest 6.50.228 build of dotConnect for Oracle. The new build can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial only) or from Registered Users' Area (for users with active subscription only).
For more information about the fixes and improvements available in the new build, please refer to
http://www.devart.com/forums/viewtopic.php?t=22164