Converting cast functions to cast (src_type as dest_type)

[Walid CHAIB]

Hi Lukas,

Is there a parameter I have to set it, so I can parse cast functions (  to_char(),to_number() ...) to cast( x as varchar  ) , cast (x as int)  because when specifying     .withInterpreterDialect(SQLDialect.POSTGRES_12), jooq doesn't parse it, and when i didn't specify the dialect interpreter i get problems with parsing other functions like decode ?

[Lukas Eder]

Hi Walid,

You can safely ignore the interpreter dialect. It has nothing to do with your current work. You only need to work with the parse dialect. But that is only used to resolve ambiguous syntax (e.g. select a = b in PostgreSQL vs SQL Server).

Now, let's rewind and look at what you're actually trying to do?

Cheers,

Lukas

On Thu, Apr 1, 2021 at 1:46 PM Walid CHAIB <walidc...@gmail.com> wrote:

Hi Lukas,

Is there a parameter I have to set it, so I can parse cast functions (  to_char(),to_number() ...) to cast( x as varchar  ) , cast (x as int)  because when specifying     .withInterpreterDialect(SQLDialect.POSTGRES_12), jooq doesn't parse it, and when i didn't specify the dialect interpreter i get problems with parsing other functions like decode ?

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAOP4XxiF9P6aaD%2Bb6pwO8mPsUe_GpCxuytzs6b1Y5UFnHefFBw%40mail.gmail.com.

[Walid CHAIB]

When I don't specify the interpreter dialect Joqq doesn't parse the oracle function like nvl and decode ...

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7%3DLVVA_ByhWW3oqMfF07GjUd156GcAfjmMUZYtsYVqow%40mail.gmail.com.

[Lukas Eder]

Hi Walid,

That's surprising, there isn't a direct link between interpreting (DDL) and parsing. Can you show some example code that helps reproduce what you're seeing? Perhaps there's a more complex interaction in your setup that can't be easily derived from your description.

Thanks,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAOP4Xxhr1aUvCbA6ewyfTYKSrd3rcMfJpHD56EcgJXdLY4qJmg%40mail.gmail.com.

[Walid CHAIB]

Hi Lukas,

1- When I specify the interpreter dialect, Jooq parses the oracle functions like decode , nvl ... but it doesn't parse the oracle cast functions like to_char(bigint) , to_number(varchar)

2- When I don't specify the interpreter dialect, Jooq parse the oracle cast functions to cast( ... as ...) but it doesn't parse the predefined functions like decode as you can see in example  :

SELECT DECODE(1,1 , 'One') From dual
Exception in thread "main" org.postgresql.util.PSQLException: ERREUR: la fonction decode(bigint, bigint, character varying) n'existe pas
  Indice : Aucune fonction ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.
  Position : 8
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
at org.jooq.impl.ParsingStatement.executeQuery(ParsingStatement.java:339)
at com.tbs.db.TestPostgreSQL.main(TestPostgreSQL.java:31)

Even with the website https://www.jooq.org/translate/, It's the same behaviour ...

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO4G%3Dt4L3YJ%2BoS%3DLUSEiWTrr7fzgOG6VgnERQM7LQ6TfDQ%40mail.gmail.com.

[Rob Sargent]

On 4/1/21 8:40 AM, Walid CHAIB wrote:

functions like decode as you can see in example  :

SELECT DECODE(1,1 , 'One') From dual
Exception in thread "main" org.postgresql.util.PSQLException: ERREUR: la fonction decode(bigint, bigint, character varying) n'existe pas
  Indice : Aucune fonction ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.

oracle syntax, postgres server?

[Walid CHAIB]

yes

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/3469f824-d63c-e80c-ea25-7ce19b8bc76a%40gmail.com.

[Lukas Eder]

Walid,

The website doesn't offer specifying the interpreter dialect. What you see there as input dialect is the parse dialect.

But I can only really hypothesise what you're doing :) Why don't you show me your code from your TestPostgreSQL class? If I can see the *exact* code you tried, then I will be able to help you. If I don't see that code, then I can only guess what it is you're trying to do.

Thanks,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAOP4Xxh3aaoiDsn1zu_040esYhAaEWxjuf%3D5AzFTu2MtHZCkhA%40mail.gmail.com.

[Walid CHAIB]

Hi Lukas,

It's just an execution of a  preparedStatement.

You can check it out in the attached files.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO6ROZAqkx0NQej-CZhPA_YdjexvN%3D6QCJ9Nwbq8ZBza7A%40mail.gmail.com.

[Lukas Eder]

Hi Walid,

Thanks for the files. Being able to reproduce the problem is always the best way to help...

The missing thing that makes it work for me is to add .set(SQLDialect.POSTGRES) to your configuration. If you're not doing that, the configured SQLDialect is just SQLDialect.DEFAULT, which produces unspecified output (mostly just what you're using as input). The interpreter dialect was indeed a distraction, it is not related to this topic.

So, try this:

Settings settings = new Settings()
        .withParseDialect(SQLDialect.ORACLE)
        .withParseUnknownFunctions(ParseUnknownFunctions.IGNORE)
        .withTransformTableListsToAnsiJoin(true)
        .withTransformUnneededArithmeticExpressions(TransformUnneededArithmeticExpressions.ALWAYS)
        .withTransformRownum(true)
        .withParamType(ParamType.NAMED)
        .withParamCastMode(ParamCastMode.ALWAYS);
Configuration jooqConfig = new DefaultConfiguration()
        .set(getConnection())
        .set(SQLDialect.POSTGRES) // Line added by me
        .set(settings)
        .set(ParseListener.onParseField(ctx -> {
            if (ctx.parseKeywordIf("SYSDATE")) {
                return DSL.field("localtimestamp");
            }
            return null;
        }));

That will make DECODE work as you said. TO_NUMBER() seems to work for me, irrespective of whether this is set or not, because we just parse that into a CAST for all dialects. I guess it's worth looking into supporting that more formally: https://github.com/jOOQ/jOOQ/issues/11746

However, indeed, TO_CHAR(1) stops working with PostgreSQL complaining about:

  ERROR: function to_char(integer) does not exist

It seems a format is strictly required:

https://www.postgresql.org/docs/current/functions-formatting.html

So that's a bug:

https://github.com/jOOQ/jOOQ/issues/11747

As a workaround, just parse the TO_CHAR() function as well, as you have already parsed SYSDATE:

        .set(ParseListener.onParseField(ctx -> {
            if (ctx.parseKeywordIf("SYSDATE")) {
                return DSL.field("localtimestamp");
            }
            else if (ctx.parseFunctionNameIf("TO_CHAR")) {
                ctx.parse('(');
                Field<?> f1 = ctx.parseField();
                Field<?> f2 = ctx.parseIf(',') ? ctx.parseField() : null;
                ctx.parse(')');

                return f2 == null ? f1.cast(SQLDataType.VARCHAR) : DSL.toChar((Field) f1, (Field) f2);
            }
            return null;
        }));

Thanks for your patience in this matter. Please let me know if I can be of any further assistance.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAOP4Xxgyp6PcEM%2Bm1nrtiijYa8g48huv0A-_jSr80aPWxH6HQQ%40mail.gmail.com.

[Walid CHAIB]

Thanks so much Lukas for your help.

I really appreciate your assistance,

I have a weird issue :

When i concatenate(using the double pipe ||) null with a string in oracle returns the string, but in postgresql it returns null

I tried to design a regex pattern to replace || with the function concat because it returns the desired result (concat (string , null ) = string)  but Jooq parse it into the double pipe operator so I am stuck at the same issue of null result.

You can see the request in the oracle dialect :
select NOM || chr(9)|| NUM_PER || chr(9)|| TYPE_PER || chr(9)|| ADRESSE1_ADR || chr(9)|| ADRESSE2_ADR || chr(9)|| CD_PST || chr(9)|| VILLE_ADR || chr(9)|| CD_CATEG || chr(9)|| SITE || chr(9)|| FONCT || chr(9)|| ID_PER || chr(9)|| EMAIL_PER || chr(9)|| IMMAT_PER || chr(9)|| NOM2_PER || chr(9) AA
from (select CONCAT(CONCAT(p.nom_per, ' '), p.prenom_per) NOM, p.NUM_PER, p.type_per, a.ADRESSE1_ADR, a.ADRESSE2_ADR, a.cd_pst, a.VILLE_ADR , c.cd_categ, p.telport_per SITE, p.telprinc_per FONCT, p.id_per, p.email_per, p.immat_per, p.nom2_per
from PERSONNE p
inner join CATEGPER c on
p.id_per = c.ID_PER
left outer join ADRESSE a on
p.id_per = TO_NUMBER(a.IDTAB_ADR)
and a.TAB_ADR = 'PERSONNE'
and a.cd_cadr = 'PRINCENVOI'
where 1 = 1
and (upper(p.num_per) = :RECH1)
and (c.dtfaff_catp >= sysdate
or c.dtfaff_catp is null)
and cd_categ in (select cd_categ
from lesfiltres filt, utilisateur u
where filt.CD_GRP = u.cd_grp
and u.cd_util = 'IGA')
and (not exists(select 1
from dossier_multiniv dmn_
where dmn_.id_dossier = to_char(p.id_per)
and dmn_.type_dossier = 'PERSONNE')
or exists(select 1
from v_droits_multiniv vdmn_
where vdmn_.id_dossier = to_char(p.id_per)
and vdmn_.type_dossier = 'PERSONNE')));

I wonder if there's a fix for that ?

Thanks in advance.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO7A9B-ZCi1S3MiSouUWbzjuomYjmcrEHBToQHfcH93pjg%40mail.gmail.com.

[Lukas Eder]

Hi Walid,

Thanks for your message. Oracle is not SQL standards compliant here as it does not distinguish between CAST(NULL AS VARCHAR2(1)) and '', i.e. the empty string and the null value are the same thing in Oracle - which is why Oracle calls the type VARCHAR2 as they might fix VARCHAR in the future, to be standards compliant.

This is usually not a problem when designing a vendor agnostic application with jOOQ's DSL API, because we hardly rely on the NULL string concatenation behaviour of PostgreSQL (and all other RDBMS). We usually concatenate empty strings to something. But of course, when migrating from Oracle to PostgreSQL, that's quite annoying. I don't think that's a parser related issue - we could just add a compatibility mode that replaces all expressions X in concatenations by COALESCE(X, '') to emulate Oracle's behaviour. I think this is worth fixing right away, everyone who uses the parsing connection to migrate off Oracle will need this feature. I've created a feature request for it. It should be available in the next nightly build of jOOQ 3.15.0-SNAPSHOT:

https://github.com/jOOQ/jOOQ/issues/11757

In a first implementation, we cannot detect for all expressions whether the COALESCE() function is required, i.e. we can't detect whether a concatenation operand is nullable or not, except if it's a non-null literal, such as ' ' in your example. This will be improved in the future, but not in 3.15 anymore: https://github.com/jOOQ/jOOQ/issues/11070

Thanks again,

Lukas

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAOP4XxhJab9OizFn3paMKU10CM0LhyiK8Uge5_eOKMUZGsaMVQ%40mail.gmail.com.