Error: Unknown data type on from-select-from-select... statement

[joseaio]

H2 throws "Unknown data type: 'T1'" Error on my query (see at end),

     This query works fine on Sybase database

I can't reduce this query to simple case of error... sorry

     Any idea? (I think that alias 'T1' lost on nested from-select-from-select...)

es.rbcdexia.ulysses.util.DataAccessException: store.data.find.query (An error happened trying to execute the finder: StatementLine.findEntryLinesOrderAsc)
    at es.rbcdexia.ulysses.thirdparty.HibernateStore$HibernateIterator.init(HibernateStore.java:1302)
    at es.rbcdexia.ulysses.thirdparty.HibernateStore$HibernateIterator.hasNext(HibernateStore.java:1327)
    at es.rbcdexia.ulysses.account.report.StatementEntryLineIterator.initFirst(StatementEntryLineIterator.java:55)
    at es.rbcdexia.ulysses.account.report.StatementEntryLineIterator.<init>(StatementEntryLineIterator.java:41)
    at es.rbcdexia.ulysses.account.report.StatementLineService.internalFindEntryLines(StatementLineService.java:290)
    at es.rbcdexia.ulysses.account.report.StatementLineService.iterateStatementLines(StatementLineService.java:149)
    at es.rbcdexia.ulysses.thirdparty.FilterInterceptor.invoke(FilterInterceptor.java:37)
    at es.rbcdexia.ulysses.account.report.StatementLineServiceTest.testFindAscending(StatementLineServiceTest.java:74)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at junit.framework.TestCase.runTest(TestCase.java:168)
    at junit.framework.TestCase.runBare(TestCase.java:134)
    at junit.framework.TestResult$1.protect(TestResult.java:110)
    at junit.framework.TestResult.runProtected(TestResult.java:128)
    at junit.framework.TestResult.run(TestResult.java:113)
    at junit.framework.TestCase.run(TestCase.java:124)
    at junit.framework.TestSuite.runTest(TestSuite.java:243)
    at junit.framework.TestSuite.run(TestSuite.java:238)
    at org.junit.internal.runners.JUnit38ClassRunner.run(JUnit38ClassRunner.java:83)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)
Caused by: org.hibernate.exception.GenericJDBCException: could not execute query using scroll
    at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140)
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.loader.Loader.scroll(Loader.java:2557)
    at org.hibernate.loader.custom.CustomLoader.scroll(CustomLoader.java:322)
    at org.hibernate.impl.SessionImpl.scrollCustomQuery(SessionImpl.java:1808)
    at org.hibernate.impl.AbstractSessionImpl.scroll(AbstractSessionImpl.java:170)
    at org.hibernate.impl.SQLQueryImpl.scroll(SQLQueryImpl.java:205)
    at es.rbcdexia.ulysses.thirdparty.HibernateStore$HibernateIterator.init(HibernateStore.java:1295)
    ... 26 more
Caused by: org.h2.jdbc.JdbcSQLException: Unknown data type: "T1"; SQL statement:
SELECT R1.*,
                R2.CLASS AS R2_CLASS,
                R2.AUDIT_USER_NAME AS R2_AUDIT_USER_NAME,
                R2.AUDIT_TIMESTAMP AS R2_AUDIT_TIMESTAMP,
                R2.AUDIT_PROCESS_DATE AS R2_AUDIT_PROCESS_DATE,
                R3.TEXT AS R3_TEXT,
                R3.LETTER AS R3_LETTER,
                R3.CODE_AEB43 AS R3_CODE_AEB43,
                R3.STATUS AS R3_STATUS
            FROM
            (
                SELECT  E.REFERENCE, E.BALANCE_ID, E.CURRENCY, E.IBAN, E.ENTITY_CODE, E.BRANCH,
                        E.PROCESS_DATE, E.VALUE_DATE, E.GROUP_REFERENCE, MIN(E.INTERNAL_REFERENCE) AS INTERNAL_REFERENCE, MIN(E.ISIN) AS ISIN, MIN(E.EXTERNAL_REFERENCE) AS EXTERNAL_REFERENCE, MIN(E.CEC) AS CEC, E.APPLICATION,
                        E.SIMPLE_PROCESS_DATE, E.TEXT, E.BANK_ORDER_ID, E.CANCELLED_ENTRY_ID,
                        E.AUDITABLE_ID, E.CANCELLED_ID,
                        MIN(E.ID) AS ID, MIN(E.ENTRY_CONCEPT_ID) AS ENTRY_CONCEPT_ID,
                        MIN(E.DOCUMENT_NUMBER) AS DOCUMENT_NUMBER, SUM(E.AMOUNT) AS AMOUNT
                FROM
                (
                    SELECT
                        T1.ID,
                        (CASE
                            WHEN T1.GROUP_REFERENCE IS NULL THEN 'I' + CONVERT(VARCHAR,T1.ID)
                            ELSE 'R' + CONVERT(VARCHAR,T1.GROUP_REFERENCE)
                        END) AS REFERENCE,
                        T1.BALANCE_ID,
                        T2.CURRENCY, T3.IBAN, T3.ENTITY_CODE, T6.BRANCH,
                        T1.PROCESS_DATE,
                        T1.VALUE_DATE,
                        T1.SIMPLE_PROCESS_DATE,
                        dbo.grouped_text(T1.GROUP_REFERENCE, T1.ENTRY_CONCEPT_ID, T1.TEXT) AS TEXT,
                        T1.ENTRY_CONCEPT_ID,
                        (CASE
                            WHEN T1.GROUP_REFERENCE IS NULL THEN T1.BANK_ORDER_ID
                            ELSE NULL
                        END) AS BANK_ORDER_ID,
                        (CASE WHEN T1.GROUP_REFERENCE IS NULL
                            THEN T1.CANCELLED_ENTRY_ID
                            ELSE NULL
                        END) AS CANCELLED_ENTRY_ID,
                        (CASE
                            WHEN T1.GROUP_REFERENCE IS NULL THEN T6.AUDITABLE_ID
                            ELSE NULL
                        END) AS AUDITABLE_ID,
                        (CASE
                            WHEN T1.GROUP_REFERENCE IS NULL THEN T4.ID
                            ELSE NULL
                        END) AS CANCELLED_ID,
                        T1.DOCUMENT_NUMBER,
                        T1.AMOUNT,
                        T1.GROUP_REFERENCE,
                        T1.INTERNAL_REFERENCE,
                        T1.ISIN,
                        T1.EXTERNAL_REFERENCE,
                        T1.CEC,
                        T7.APPLICATION
                    FROM ENTRY AS T1
                        INNER JOIN BALANCE T2 ON T1.BALANCE_ID = T2.ID
                        INNER JOIN ACCOUNT T3 ON T2.ACCOUNT_ID = T3.ID
                        LEFT OUTER JOIN ENTRY T4  ON T1.ID = T4.CANCELLED_ENTRY_ID
                        LEFT OUTER JOIN ENTRY T5  ON T1.CANCELLED_ENTRY_ID = T5.ID
                        LEFT OUTER JOIN BANK_ORDER T6  ON T1.BANK_ORDER_ID = T6.ID
                        LEFT OUTER JOIN AUDITABLE T7 ON T7.ID = T6.AUDITABLE_ID
                    WHERE
                        (
                        (T1.CANCELLED_ENTRY_ID = NULL) OR
                        (T1.SIMPLE_PROCESS_DATE > T5.SIMPLE_PROCESS_DATE)
                        )
                        AND
                        (
                            (T4.ID = NULL) OR
                            (T4.SIMPLE_PROCESS_DATE > T1.SIMPLE_PROCESS_DATE)
                        )
                        AND T3.IBAN         = ?
                        AND T2.CURRENCY     = ?
                        AND T1.PROCESS_DATE>= ?
                        AND T1.PROCESS_DATE<= ?
                       
                       
                        AND 'AUTHORIZATOR' not in (
                            select roles.rol
                            from ROLE_FLAG roles
                            inner join ACCOUNT_FLAG flags on flags.FLAG = roles.FLAG
                            inner join ACCOUNT accounts on accounts.ID = flags.ACCOUNT_ID
                            where accounts.IBAN=T3.IBAN
                        )
                       
       
                ) E
                GROUP BY
                    E.REFERENCE, E.BALANCE_ID,
                    E.CURRENCY, E.IBAN, E.ENTITY_CODE, E.BRANCH,
                    E.PROCESS_DATE, E.VALUE_DATE, E.GROUP_REFERENCE, E.SIMPLE_PROCESS_DATE,
                    E.TEXT, E.BANK_ORDER_ID, E.CANCELLED_ENTRY_ID,
                    E.AUDITABLE_ID, E.CANCELLED_ID, E.APPLICATION
            ) R1
            LEFT OUTER JOIN AUDITABLE R2 ON R1.AUDITABLE_ID = R2.ID
            LEFT OUTER JOIN ENTRY_CONCEPT R3 ON R1.ENTRY_CONCEPT_ID = R3.ID
            ORDER BY
                R1.PROCESS_DATE asc,
                R1.ID asc [50004-167]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.command.Parser.parseColumnWithType(Parser.java:3664)
    at org.h2.command.Parser.readFunction(Parser.java:2197)
    at org.h2.command.Parser.readTerm(Parser.java:2486)
    at org.h2.command.Parser.readFactor(Parser.java:2047)
    at org.h2.command.Parser.readSum(Parser.java:2037)
    at org.h2.command.Parser.readConcat(Parser.java:2007)
    at org.h2.command.Parser.readCondition(Parser.java:1872)
    at org.h2.command.Parser.readAnd(Parser.java:1853)
    at org.h2.command.Parser.readExpression(Parser.java:1845)
    at org.h2.command.Parser.readWhen(Parser.java:2682)
    at org.h2.command.Parser.readTerm(Parser.java:2480)
    at org.h2.command.Parser.readFactor(Parser.java:2047)
    at org.h2.command.Parser.readSum(Parser.java:2034)
    at org.h2.command.Parser.readConcat(Parser.java:2007)
    at org.h2.command.Parser.readCondition(Parser.java:1872)
    at org.h2.command.Parser.readAnd(Parser.java:1853)
    at org.h2.command.Parser.readExpression(Parser.java:1845)
    at org.h2.command.Parser.readTerm(Parser.java:2568)
    at org.h2.command.Parser.readFactor(Parser.java:2047)
    at org.h2.command.Parser.readSum(Parser.java:2034)
    at org.h2.command.Parser.readConcat(Parser.java:2007)
    at org.h2.command.Parser.readCondition(Parser.java:1872)
    at org.h2.command.Parser.readAnd(Parser.java:1853)
    at org.h2.command.Parser.readExpression(Parser.java:1845)
    at org.h2.command.Parser.parseSelectSimpleSelectPart(Parser.java:1758)
    at org.h2.command.Parser.parseSelectSimple(Parser.java:1790)
    at org.h2.command.Parser.parseSelectSub(Parser.java:1685)
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1528)
    at org.h2.command.Parser.readTableFilter(Parser.java:1027)
    at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1691)
    at org.h2.command.Parser.parseSelectSimple(Parser.java:1798)
    at org.h2.command.Parser.parseSelectSub(Parser.java:1685)
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1528)
    at org.h2.command.Parser.readTableFilter(Parser.java:1027)
    at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1691)
    at org.h2.command.Parser.parseSelectSimple(Parser.java:1798)
    at org.h2.command.Parser.parseSelectSub(Parser.java:1685)
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1528)
    at org.h2.command.Parser.parseSelect(Parser.java:1516)
    at org.h2.command.Parser.parsePrepared(Parser.java:406)
    at org.h2.command.Parser.parse(Parser.java:280)
    at org.h2.command.Parser.parse(Parser.java:252)
    at org.h2.command.Parser.prepareCommand(Parser.java:218)
    at org.h2.engine.Session.prepareLocal(Session.java:415)
    at org.h2.engine.Session.prepareCommand(Session.java:364)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1111)
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:80)
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:628)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at com.mchange.v2.c3p0.stmt.GooGooStatementCache$1StmtAcquireTask.run(GooGooStatementCache.java:525)
    at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

[joseaio]

Please note that I use inverted CONVERT(TYPE,VALUE) described on Issue 274

[Rami Ojares]

> I can't reduce this query to simple case of error... sorry

Does it appear if you only run the inner select?
Try taking pieces out of the query and see what removal makes it work again.
That's how you can narrow down on the problem.

- Rami

[joseaio]

Rami,

   I think that problem is "inverted" convert as Thomas says here

I sure that exist another point on code where expect normal convert function ("inverted" convert is mandatory for Sybase and SQL Server compatibility modes)

  Normal convert function is CONVERT(VALUE, TYPE) and "inverted" is CONVERT(TYPE,VALUE)