HHH-18359 Fixes to Informix functions errors

[VladoKuruc]

Cumulative PR for all subtasks [HHH-18360, HHH-18361, HHH-18362, HHH-18363, HHH-18364, HHH-18365, HHH-18168, HHH-18366, HHH-18367, HHH-18368, HHH-18369]

---

By submitting this pull request, I confirm that my contribution is made under the terms of the Apache 2.0 license and can be relicensed under the terms of the LGPL v2.1 license in the future at the maintainers' discretion. For more information on licensing, please check here.

---

[hibernate-github-bot[bot]]

Thanks for your pull request!

This pull request appears to follow the contribution rules.

› This message was automatically generated.

[beikov]

Can you please try to rebase on top of https://github.com/hibernate/hibernate-orm/pull/8675 and make use of the SqlAstNodeRenderingMode.WRAP_ALL_PARAMETERS for nvl, coalesce and case expressions? That should work according to https://github.com/jOOQ/jOOQ/issues/12064

[VladoKuruc]

Can you please try to rebase on top of #8675 and make use of the SqlAstNodeRenderingMode.WRAP_ALL_PARAMETERS for nvl, coalesce and case expressions? That should work according to jOOQ/jOOQ#12064

Can you help me with this rebase as you suggested? I'm not sure how to rebase on top of pull request #8675. In my local clone of the repository, I added your remote fork https://github.com/hibernate/hibernate-orm. Then I fetched from it and finally rebases onto the remotes/beikov/wrap-parameter-rendering-mode branch. After my code changes, should I just push to my remote origin branch from which my pull request is created to get the expected result?

[VladoKuruc]

After changing to functionFactory.coalesce( SqlAstNodeRenderingMode.WRAP_ALL_PARAMETERS ); , a java.lang.StackOverflowError occurs.

	at org.hibernate.sql.ast.spi.AbstractSqlAstTranslator.visitParameter(AbstractSqlAstTranslator.java:7057)
	at org.hibernate.sql.exec.internal.AbstractJdbcParameter.accept(AbstractJdbcParameter.java:63)
	at org.hibernate.sql.ast.spi.AbstractSqlAstTranslator.render(AbstractSqlAstTranslator.java:7113)
	at org.hibernate.sql.ast.spi.AbstractSqlAstTranslator.renderWrappedParameter(AbstractSqlAstTranslator.java:7085)
	at org.hibernate.sql.ast.spi.AbstractSqlAstTranslator.visitParameter(AbstractSqlAstTranslator.java:7057)

[VladoKuruc]

After making small changes to avoid cycling, I tested using rendering mode WRAP_ALL_PARAMETERS in the coalesce function. This is a workaround for unsupported host variables in Informix. The problem is with older Informix 11, where it is syntactically correct after this, but the database incorrectly determines metadata about columns with expressions (I can send a protocol trace) and therefore returns incorrect values. I isolated a simple test:

TypedQuery<String> query = session.createQuery( "select :name", String.class );
query.setParameter("name", "Johannes");
List<String> resultList = query.getResultList();
assertThat(resultList, hasItem("Johannes"));

Which ends with the error:

    select
        cast(? as varchar(255)) 
    from
        systables 
    where
        tabid=1
[subsystem] TRACE g.hibernate.orm.jdbc.bind JdbcBindingLogging:24 - binding parameter (1:VARCHAR) <- [Johannes]
[subsystem] TRACE ibernate.orm.jdbc.extract JdbcExtractingLogging:24 - extracted value (1:VARCHAR) -> [J]
java.lang.AssertionError: 
Expected: a collection containing "Johannes"
     but: was "J"

Informix 14 did not have this problem.

For the concat() function in the specified test, the result remains similar to the DEFAULT rendering mode.

    select
        case 
            when te1_0.id is not null 
                then concat((select
                    cast(? as varchar(255))), '_1') 
            else 'Empty' 
    end,
    trim(BOTH from concat((select
        cast(? as varchar(255))), 'Test   ')) 
from
    TEST_ENTITY te1_0

There is no syntax problem, but again it is the metadata. The changes are taken into account according to the cast type, but it is still not good. The latest Informix 14 also has a problem with this. Therefore, I stick to my proposal. After all, I assume that there may be problems with this metadata in other SQL selects as well, as contained in the tests.

[beikov]

Hi Vlado, sorry for not responding in a while. I was working hard on some performance improvements and had to focus a while. I just merged the PR I mentioned with a fix for the cycle, so please revert the changes you did in AbstractSqlAstTranslator. Can you please rebase your work on top of it?

There is no syntax problem, but again it is the metadata. The changes are taken into account according to the cast type, but it is still not good. The latest Informix 14 also has a problem with this. Therefore, I stick to my proposal. After all, I assume that there may be problems with this metadata in other SQL selects as well, as contained in the tests.

I just tested this myself on Informix 14 and it seems to work fine. What am I missing?

session.createNativeQuery( "select case when 1 is not null then concat((select cast(? as varchar(255))), '_1') else 'Empty' end,trim(BOTH from concat((select cast(? as varchar(255))), 'Test   '))  from systables where tabid=1" ).setParameter( 1, "abc" ).setParameter( 2, "abc" ).getResultList()

[VladoKuruc]

Hi Chris,

I just got back from vacation on Tuesday evening, so the longer silence didn't bother me. Thanks for your support. I tried the rebase as you recommended. You solved the looping issue, but now in the resulting WRAP_ALL_PARAMETERS rendering mode, it's coalesce((select ?), p1_0.name) instead of the expected coalesce((select cast(? as varchar(255))), p1_0.name). That's why I used renderCasted() in my version.

I just tested this myself on Informix 14 and it seems to work fine. What am I missing?

session.createNativeQuery( "select case when 1 is not null then concat((select cast(? as varchar(255))), '_1') else 'Empty' end,trim(BOTH from concat((select cast(? as varchar(255))), 'Test   '))  from systables where tabid=1" ).setParameter( 1, "abc" ).setParameter( 2, "abc" ).getResultList()

Try using my exact scenario. Run the test org.hibernate.orm.test.jpa.criteria.basic.ConcatTest.testSelectCaseWithConcat in your branch with functionFactory.concat( SqlAstNodeRenderingMode.WRAP_ALL_ PARAMETERS ); set in the InformixDialect. For this scenario, I've extended CommonFunctionFactory.java:

public void concat() {
    concat(SqlAstNodeRenderingMode.DEFAULT);
}

public void concat(SqlAstNodeRenderingMode inferenceArgumentRenderingMode) {
    functionRegistry.namedDescriptorBuilder("concat")
            .setInvariantType(stringType)
            .setMinArgumentCount(1)
            .setArgumentTypeResolver(
                    StandardFunctionArgumentTypeResolvers.impliedOrInvariant(typeConfiguration, STRING)
            )
            .setArgumentListSignature("(STRING string0[,   
 STRING string1[, ...]])")
            .setArgumentRenderingMode(inferenceArgumentRenderingMode)
            .register();
}

[beikov]

Please rebase your PR to resolve the conflicts. Also, please change the code to use renderCasted like you described.

[VladoKuruc]

I've done a rebase, but I can't directly use just renderCasted() because it will cause the infinite loop that you've fixed once before.

[VladoKuruc]

As I predicted, problems with parameters are common in SqlSelections. Another example is from the test org.hibernate.orm.test.query.hql.FunctionTests.testTrimFunctionParameters

org.hibernate.exception.SQLGrammarException: could not prepare statement [A syntax error has occurred.] [select trim(BOTH from ?) from (select 0 from systables where tabid=1) dual]

Therefore, I propose a different, more radical solution: in Informix, for SqlSelections, set the default parameter rendering mode to INLINE_ALL_PARAMETERS.

[VladoKuruc]

I omitted the fixes for subtasks HHH-18366 Informix concat pipe operator error and HHH-18368 Informix function coalesce() error due to an ambiguous problematic solution.

[beikov]

I also found this documentation saying:

In general, you cannot use variables (for example, host variables in an Informix ESQL/C application) in the select list by themselves. A variable is valid in the select list, however, if an arithmetic or concatenation operator connects it to a constant.

How about connecting it with an empty string in case the parameter is a string and +0 if it's a number? Maybe you can add 0 seconds to timestamps parameters as well?

[beikov]

Thanks. Superseded by https://github.com/hibernate/hibernate-orm/pull/9343