Join with java function: col name not found

Help
2012-12-07
2014-01-19
  • Martin Fischer
    Martin Fischer
    2012-12-07

    Hi all,

    joining with a tables based java function that worked with 2.2.4 does no longer work with 2.2.9.
    I'm creating a java table based function like this:
        "CREATE FUNCTION plusOne(BIGINT, BIGINT)\n" +
        "RETURNS TABLE(c0 BIGINT, c1 BIGINT)\n" +
        "NO SQL DETERMINISTIC\n" +
        "LANGUAGE JAVA PARAMETER STYLE JAVA\n" +
        "EXTERNAL NAME 'CLASSPATH:function.TableFunctionJoinTest.plusOne'"
    that adds one to the first argument and returns the result as a table containing the first argument as c0 and the first argument + 1 as c1.

    Then I join a table that has only a single bigint column to that table function like this:
            SELECT l.c0, b.c1 FROM T1 as l JOIN TABLE(plusOne(l.c0, NULL)) as b ON (true)

    This works fine in 2.2.4 but fails with this error message in 2.2.9:
            bad SQL grammar ;
            nested exception is java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: L.C0

    What's wrong here?

    Regards
    Martin Fischer


    Here is the detailed testcase that works well with 2.2.4:

    @RunWith(SpringJUnit4ClassRunner.class)
    @ContextConfiguration(locations={"/applicationContext.xml"})
    public class TableFunctionJoinTest extends AbstractThreadedDatabaseTest {

    @Before public void createTables() {
    jdbcTemplate.update("CREATE GLOBAL TEMPORARY TABLE T1 (c0 BIGINT)");
    jdbcTemplate.update("CREATE INDEX T1_IDX ON T1(c0)");
    jdbcTemplate.update("CREATE GLOBAL TEMPORARY TABLE RES (c0 BIGINT)");
    }

    @After public void dropTables() {
    jdbcTemplate.update("DROP TABLE T1");
    jdbcTemplate.update("DROP TABLE RES");
    }

    @Test public void joinWithBuiltinTest() throws Throwable {
        jdbcTemplate.update(
        "CREATE FUNCTION plusOne(BIGINT, BIGINT)\n" +
        "RETURNS TABLE(c0 BIGINT, c1 BIGINT)\n" +
        "NO SQL DETERMINISTIC\n" +
        "LANGUAGE JAVA PARAMETER STYLE JAVA\n" +
        "EXTERNAL NAME 'CLASSPATH:function.TableFunctionJoinTest.plusOne'"
            );

        TransactionStatus tx = txManager.getTransaction(defaultTxDefinition);

    jdbcTemplate.update("INSERT INTO T1 VALUES(1)");
    jdbcTemplate.update("INSERT INTO T1 VALUES(2)");
    jdbcTemplate.update("INSERT INTO T1 VALUES(3)");

    System.out.println(jdbcTemplate.queryForList("SELECT l.c0, b.c1 FROM T1 as l JOIN TABLE(plusOne(l.c0, NULL)) as b ON (true)"));

    txManager.commit(tx);
    }

    public static ResultSet plusOne(final Connection a_connection, final Long c0, final Long c1) throws SQLException {
            ResultMetaData meta = ResultMetaData.newSimpleResultMetaData(new Type {Type.SQL_BIGINT, Type.SQL_BIGINT});
            if (c0 == null || c1 != null) throw new SQLException("only BF binding pattern is supported");

            Result result = Result.newDataResult(meta);
            result.setNavigator(new RowSetNavigatorClient());

            Object row = new Object;
        row = c0;
        row = new Long(c0.longValue() + 1);
            result.navigator.add(row);
            result.navigator.reset();

            return new JDBCResultSet((JDBCConnection)a_connection, result, result.metaData);
    }
    }

     
  • Fred Toussi
    Fred Toussi
    2012-12-07

    Thanks, will check with SVN head and fix if the issue is still there.