4 Replies Latest reply on Mar 28, 2016 10:51 AM by Gabriel Steinberg

    HSQL Syntax error when Teiid sends it a join query with no parenthesis

    Gabriel Steinberg Newbie

      Running the following query through Teiid against an HSQL database,

       

      SELECT R_NAME FROM region LEFT OUTER JOIN (customer INNER JOIN nation ON C_NATIONKEY = N_NATIONKEY) ON R_REGIONKEY = N_REGIONKEY

       

      throws an exception, which is cause by an HSQL syntax error:

          java.sql.SQLSyntaxErrorException: unexpected token: INNER

          at org.hsqldb.jdbc.JDBCUtil.sqlException(JDBCUtil.java:376)

          at org.hsqldb.jdbc.JDBCUtil.sqlException(JDBCUtil.java:247)

          . . .

       

      The reason is that the query that Teiid sends to HSQL has the no parenthesis:

       

      SELECT R_NAME FROM region LEFT OUTER JOIN customer INNER JOIN nation ON C_NATIONKEY = N_NATIONKEY ON R_REGIONKEY = N_REGIONKEY

       

      I verified that HSQL will, indeed, accept the original query with the parenthesis but not the Teiid-generated query, without them, even though it is valid SQL.

       

      Is there any way to convince Teiid to add parenthesis around each join clause, with the left paren just before the left side of the join and the right paren right after the ON clause? It would also make it easier to understand the queries if you ever have to understand them or debug them.

       

      Thanks,

      Gabi.