User-defined variable "sticky" if used in view with join

[agoerler]

Hi,

I am observing that a user-defined variable is "sticky" if used in a view with a join.

I can reproduce the issue in the following Java application:

public class VariableInViewIssue {

    private static DataSource ds = JdbcConnectionPool.create("jdbc:h2:mem:test", "sa", "sa");

    public static void main(String[] args) throws SQLException {
        try (Connection conn = ds.getConnection()) {
            Statement stmt = conn.createStatement();
            stmt.execute(
                    "CREATE TABLE Book (ID INTEGER, title VARCHAR(30), PRIMARY KEY(ID))");
            stmt.execute(
                    "CREATE TABLE Book_texts (locale VARCHAR(14), ID INTEGER, title VARCHAR(30), PRIMARY KEY(locale, ID))");
            stmt.execute(
                    "CREATE VIEW localized_texts AS SELECT * FROM Book_texts WHERE locale = @LOCALE");

            stmt.execute("CREATE VIEW localized_Book AS "
                    + "SELECT b.ID, t.title AS title "
                    + "FROM (Book AS b LEFT JOIN Book_texts AS t ON t.ID = b.ID AND t.locale = @LOCALE)");

            stmt.execute("INSERT INTO Book (ID, title) VALUES (1, 'Book 1')");
            stmt.execute("INSERT INTO Book_texts (locale, ID, title) VALUES ('de', 1, 'Buch 1')");
            stmt.execute("INSERT INTO Book_texts (locale, ID, title) VALUES ('fr', 1, 'Libre 1')");
        }
        
        // 1st execution
        checkLocale("de");

        // 2nd execution
        checkLocale("fr");
    }

    private static void checkLocale(String locale) throws SQLException {
        try(Connection conn = ds.getConnection(); Statement stmt = conn.createStatement()) {
            System.out.println("setting @LOCALE to '" + locale + "'");
            stmt.execute("SET @LOCALE = '" + locale + "'");
            System.out.println("SELECT title, locale FROM localized_texts");
            try (ResultSet rs = stmt.executeQuery("SELECT title, locale FROM localized_texts")) {
                rs.next();
                
                System.out.println("title  : " + rs.getString(1));
                System.out.println("@LOCALE: " + rs.getString(2));
            }
            System.out.println("-----");
            
            System.out.println("SELECT title, locale FROM localized_Book");
            try (ResultSet rs = stmt.executeQuery("SELECT title, @LOCALE AS locale FROM localized_Book")) {
                rs.next();
                
                System.out.println("title  : " + rs.getString(1));
                System.out.println("@LOCALE: " + rs.getString(2));
            }
            System.out.println("\n");
        }
    }
}

Running the app yields:

setting @LOCALE to 'de'
SELECT title, locale FROM localized_texts
title  : Buch 1
@LOCALE: de
-----
SELECT title, locale FROM localized_Book
title  : Buch 1
@LOCALE: de


setting @LOCALE to 'fr'
SELECT title, locale FROM localized_texts
title  : Libre 1       // CORRECT
@LOCALE: fr
-----
SELECT title, locale FROM localized_Book
title  : Buch 1       // WRONG
@LOCALE: fr

The user-defined variable @LOCALE is evaluated correctly when reading from the view localized_texts and returns the title in the language as specified by @LOCALE. But, in the second execution, if I read from the view localized_Book, which contains a join, the where condition of the view is not evaluated correctly but uses the value of @LOCALE that was set during the first execution.

[agoerler]

Did anyone have a chance to look into this issue already?

[agoerler]

@katzyn - I have build H2 from master and tested the fix. It solves the issue :-). Thanks a lot!!

My understanding is that the fix will be available with version 2.2.219. Do you happen to know when it will be released?

[katzyn]

Snapshot builds now have own reserved build numbers to distinguish them from released versions, so there will no no release with this number.

I think we'll release a new bugfix release in 2.1 series of releases, but such release isn't planned yet.

[agoerler]

I think we'll release a new bugfix release in 2.1 series of releases, but such release isn't planned yet.

Thanks a lot! This will help us to lift an unwelcome limitation (1.).

[agoerler]

@katzyn
@andreitokar

Hi @Evgenij, Andrei,

do you have any estimate when this bug fix could me included an a release?

Best regards,

Adrian