2

Problem

I have a SQL statement with an LEFT OUTER JOIN which works fine on our Microsoft SQL Server. My problem is that i have to be compatible with H2 Database and this one got a bug with OUTER JOINS.


SQL Query

SELECT *
FROM tSysNls
WHERE nlsGuid IN
    ( SELECT nlsGuid = CASE 
    WHEN de_AT.nlsGuid IS NOT NULL THEN de_AT.nlsGuid 
    WHEN de.nlsGuid IS NOT NULL THEN de.nlsGuid ELSE en.nlsGuid 
    END
    FROM tSysNLS en
    LEFT OUTER JOIN tSysNLS de ON en.nlsAttribute=de.nlsAttribute
        AND en.nlsClazz=de.nlsClazz
        AND de.nlsLocale= 'de'
    LEFT OUTER JOIN tSysNLS de_AT ON de.nlsAttribute=de_AT.nlsAttribute
        AND de.nlsClazz=de_AT.nlsClazz
        AND de_AT.nlsLocale= 'de_AT'
    WHERE en.nlsLocale= 'en'
        AND en.nlsClazz= 'Contact'
        AND en.nlsAttribute= 'firstName')

Table

CREATE TABLE tsysNLS
(
   nlsGuid           nvarchar(207)   NOT NULL,
   nlsLocale         nvarchar(5)     NOT NULL,
   nlsClazz          nvarchar(100)   NOT NULL,
   nlsAttribute      nvarchar(100)   NOT NULL,
   nlsDisplayName    nvarchar(255)   NOT NULL,
   nlsOldname        nvarchar(50),
   nlsDescription    nvarchar(255),
   nlsShapefilename  nvarchar(10)
);

Example data

INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.en', N'en', N'Contact', N'firstName', N'Name - First Name', N'conFirstName', N'Name - First Name', NULL);
INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.de', N'de', N'Contact', N'firstName', N'Vorname', N'conFirstName', NULL, N'ConNamVor');
INSERT INTO tsysNLS(nlsGuid, nlsLocale, nlsClazz, nlsAttribute, nlsDisplayName, nlsOldname, nlsDescription, nlsShapefilename)
VALUES(N'Contact.firstName.de_AT', N'de_AT', N'Contact', N'firstName', N'Vorname (AT)', N'conFirstName', NULL, N'ConNamVor');
1
  • 1
    You can always rewrite outer joins with UNION and some NOT EXISTS subqueries - but it's going to be a much longer (in code) query. Sep 22, 2014 at 13:55

3 Answers 3

3

You can rewrite the LEFT JOIN to a subquery, but it may still compile as an outer join query - I have no knowledge of those aspects of the H2 platform.

SELECT *
FROM tSysNls
WHERE nlsGuid IN (
    SELECT COALESCE(
           --- first option:
           (SELECT de_AT.nlsGuid
            FROM tSysNLS AS de_AT
            WHERE de_AT.nlsClazz=x.nlsClazz AND
                  de_AT.nlsAttribute=en.nlsAttribute AND
                  de_AT.nlsLocale='de_AT'),
           --- second option:
           (SELECT de.nlsGuid
            FROM tSysNLS AS de
            WHERE de.nlsClazz=x.nlsClazz AND
                  de.nlsAttribute=en.nlsAttribute AND
                  de.nlsLocale='de'),
           --- third option:
           en.nlsGuid)
    FROM tSysNLS AS en
    WHERE en.nlsLocale='en'
        AND en.nlsClazz='Contact'
        AND en.nlsAttribute='firstName');

Or you could build some type of temp table (or a regular table if H2 doesn't support temp tables), then populate it in three steps. Again, this will probably still compile using an anti-semijoin or some other type of outer join, but maybe it'll function as a workaround for your bug:

--- 1. de_AT, if there are any:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='de_AT' AND nlsClazz='Contact' AND nlsAttribute='firstName';

--- 2. de for those that don't have de_AT:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='de' AND nlsClazz='Contact' AND nlsAttribute='firstName'
      AND nlsGuid NOT IN (SELECT nlsGuid FROM #temptable);

--- 3. en for the remaining records:
INSERT INTO #temptable SELECT * FROM tSysNls
WHERE nlsLocale='en' AND nlsClazz='Contact' AND nlsAttribute='firstName'
      AND nlsGuid NOT IN (SELECT nlsGuid FROM #temptable);

SELECT * FROM #temptable;

But these are not pretty solutions.

1

Solution

this is not exactly what i asked for but it solves the problem of the original query to fail on H2 Database thanks ypercube your suggestion is correct

SELECT *
FROM tSysNls
WHERE nlsGuid IN
    ( SELECT CASE 
    WHEN de_AT.nlsGuid IS NOT NULL THEN de_AT.nlsGuid 
    WHEN de.nlsGuid IS NOT NULL THEN de.nlsGuid ELSE en.nlsGuid 
    END AS nlsGuid
    FROM tSysNLS en
    LEFT OUTER JOIN tSysNLS de ON en.nlsAttribute=de.nlsAttribute
        AND en.nlsClazz=de.nlsClazz
        AND de.nlsLocale= 'de'
    LEFT OUTER JOIN tSysNLS de_AT ON de.nlsAttribute=de_AT.nlsAttribute
        AND de.nlsClazz=de_AT.nlsClazz
        AND de_AT.nlsLocale= 'de_AT'
    WHERE en.nlsLocale= 'en'
        AND en.nlsClazz= 'Contact'
        AND en.nlsAttribute= 'firstName')

results in a correct result.

but due (lot) better performance i process the whole table to Objects in my Application now and grab the Objects (select which one is the right one by code)

-4

i had two queries without outer join, which worked fast and correct on Microsoft SQL Server but both did not work out on H2 Database one of em because MSSQL uses + to concatenate strings and H2 uses CONCAT (which is crap because afaik this does not match the SQL specifications)

my final solution is to create one single query in my code and find the correct stuff with code.


One of the queries i tried was this one (works on Microsoft SQL Server but not on H2 due different concatenate syntax)


SELECT * FROM tSysNLS
WHERE nlsGuid = CASE
WHEN exists(select * from tSysNLS where nlsClazz = ?4 and nlsAttribute = ?5 and nlsLocale = ?2) then ?4+'.'+?5+'.'+?2
WHEN exists(select * from tSysNLS where nlsClazz = ?4 and nlsAttribute = ?5 and nlsLocale = ?1) then ?4+'.'+?5+'.'+?1
ELSE ?4+'.'+?5+'.'+?3
END

where ?# are parameters
?1 = 'de_AT'
?2 = 'de'
?3 = 'en'
?4 = 'Contact'
?5 = 'firstName'

11
  • 1
    H2 also uses || which is the standard SQL concatenation operator, as others have mentioned in the comments. If CONCAT() is crap, then so must + be because that is proprietary as well (to SQL-Server). The alias = expression syntax is also SQL-Server only. I suppose that's why you were getting those errors and not due to some bug. Sep 23, 2014 at 9:14
  • Also in an answer, it's usual to include a working query, a solution, not one that doesn't work. Sep 23, 2014 at 9:15
  • including a query makes sense iff there is a query used. select * from tsysnls <- ther you go. hope you are happy now.
    – lumo
    Sep 23, 2014 at 11:28
  • Sorry but no. Your question had several flaws and I was hoping the answer would remedy them. But the answer failed to admit that there was no bug found in H2SQL's implementation of outer joins. It fails to admit that the differences in SQL syntax between SQL-Server, H2SQL and the standard (that you claim to try following) was the source of the problems. You failed to provide code that works and is equivalent to the code in the question. And the only thing in the answer that looked like an answer (that you used as a workaround 1-3 queries depending on the data) was removed in the last edit. Sep 23, 2014 at 11:38
  • 1
    Sorry if my message sounded insulting. I did not mean it that way. You claim a known bug but you haven't shown a link to a page that describes the bug. Or a very simple demonstration (here is the code, here is the error message from H2). Then people would believe you hit a bug. Sep 24, 2014 at 6:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.