ORA-12704 error in the latest dotConnect build (202)

ORA-12704 error in the latest dotConnect build (202)

Postby object » Fri 19 Aug 2011 14:02

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.
object
 
Posts: 89
Joined: Tue 26 Oct 2010 08:29

Postby object » Fri 19 Aug 2011 14:11

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
object
 
Posts: 89
Joined: Tue 26 Oct 2010 08:29

Postby object » Mon 22 Aug 2011 08:47

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
object
 
Posts: 89
Joined: Tue 26 Oct 2010 08:29

Postby object » Mon 22 Aug 2011 08:52

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.
object
 
Posts: 89
Joined: Tue 26 Oct 2010 08:29

Postby StanislavK » Wed 24 Aug 2011 13:21

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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby object » Wed 24 Aug 2011 16:21

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.
object
 
Posts: 89
Joined: Tue 26 Oct 2010 08:29

Postby StanislavK » Mon 29 Aug 2011 11:29

Thank you for your assistance, we have reproduced the issue. We will analyze it and inform you about the results as soon as possible.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby object » Mon 29 Aug 2011 11:49

Oh great! Looking forward for the fix.
object
 
Posts: 89
Joined: Tue 26 Oct 2010 08:29

Postby StanislavK » Fri 07 Oct 2011 09:39

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
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for Oracle