eclipselink produces invalid sql syntax in boolean conversation

[cheVron81]

I have upgraded from eclipselink 2.7 to 4 and now it produces invalid sql.

Eclipselink is generating an improved sql.
It uses boolean flags to enable or disable parts in the where clause.

These boolean flags are like AND ( ( ? = ? ) with binding false, true
In generated sql this flags are converted into AND ( ( 0 = 'true' ) and causes an invalid number exception.

Anyone any Idea?

More Details:

I have a Query:

SELECT q FROM MyElement q WHERE q.document.anyId = :" + Document.ID_NAME + " AND q.someCategory.categoryType

This becomes a prepared statement which looks okay:

SELECT
    t1.id,
    t1.any_id,
    t1.o_name,
    t1.category_sub_type,
    t1.category_type,
    t1.sequence_no,
    t1.any_code,
    t1.any_type,
    t1.change_date_time,
    t1.change_user_id,
    t1.change_date_time_second,
    t1.change_user_id_second,
    t1.create_date_time,
    t1.create_user_id,
    t1.channel_id,
    t1.my_category_id,
    t1.rel_id_second,
    t1.prod_eq_id
FROM
    scheme.com_target_system           t0,
    scheme.os_category         t2,
    scheme.os_channel_category t1
WHERE
    ( ( ( ( ( ( t1.any_id = ? )
              AND ( t2.category_type = ? ) )
            AND ( t2.category_sub_type = ? ) )
          AND ( t2.technical_name = ? ) )
        AND ( ( ? = ? )
              OR ( ( t1.any_id IS NULL )
                   OR ( t1.any_id <> ? ) ) ) )
      AND ( ( ( t2.id = t1.my_category_id )
              AND ( ( ? = ? )
                    OR ( ( t2.any_id IS NULL )
                         OR ( t2.any_id <> ? ) ) ) )
            AND ( t0.id = t1.channel_id ) ) )
ORDER BY
    t0.position ASC

In the bindings are the flags correct:

bind => [0, INDEPENDENT, Fee, Costs, false, true, -1, false, true, -1]

The resulting SQL in the end with the wrong converted flags is:

SELECT
    t1.id,
    t1.any_id,
    t1.o_name,
    t1.category_sub_type,
    t1.category_type,
    t1.sequence_no,
    t1.any_code,
    t1.any_type,
    t1.change_date_time,
    t1.change_user_id,
    t1.change_date_time_second,
    t1.change_user_id_second,
    t1.create_date_time,
    t1.create_user_id,
    t1.channel_id,
    t1.my_category_id,
    t1.rel_id_second,
    t1.prod_eq_id
FROM
    scheme.com_target_system           t0,
    scheme.os_category         t2,
    scheme.os_channel_category t1
WHERE
    ( ( ( ( ( ( t1.any_id = 0 )
              AND ( t2.category_type = 'INDEPENDENT' ) )
            AND ( t2.category_sub_type = 'Fee' ) )
          AND ( t2.technical_name = 'Costs ) )
        AND ( ( 0 = 'true' )
              OR ( ( t1.any_id IS NULL )
                   OR ( t1.any_id <> -1 ) ) ) )
      AND ( ( ( t2.id = t1.my_category_id )
              AND ( ( 0 = 'true' )
                    OR ( ( t2.any_id IS NULL )
                         OR ( t2.any_id <> -1 ) ) ) )
            AND ( t0.id = t1.channel_id ) ) )
ORDER BY
    t0.position ASC

[Tomas-Kraus]

Please can you provide us some small application with reproduction scenario?
There are lot of things that are still unknown:

• DB provider and setup
• persistence provider confiruration
• your entity / database model

and they may affect how query is being built.

I made small project to run tests with latest EclipseLink beta and MySQL (https://github.com/Tomas-Kraus/eclipselink-bug) or Oracle DB (https://github.com/Tomas-Kraus/eclipselink-bug/tree/oracle) with one shot so maybe you can just modify it with your model and test case.
If you need JPA 3.1 and Eclipselink 4 compliant version, it's here: https://github.com/Tomas-Kraus/eclipselink-bug/tree/bug-2307-4.x
Or send us your own app.