JPQL EXISTS does not work

Platform 7.2.11

    <data>
        <collection id="usersDc" class="com.haulmont.cuba.security.entity.User">
            <view extends="_minimal"/>
            <loader id="usersDl">
                <query>
                    <![CDATA[select e from sec$User e where exists (select e2 from sec$User e2)]]>
                </query>
            </loader>
        </collection>
        <collection id="languagesDc" class="com.company.xxx.entity.Language">
            <view extends="_local"/>
            <loader id="languagesDl">
                <query>
                    <![CDATA[select e from xxx_Language e where exists (select e2 from xxx_Language e2)]]>
                </query>
            </loader>
        </collection>
    </data>

userDl works fine, but languagesDl fail with

com.haulmont.cuba.core.global.RemoteException:
---
javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.7.3.14-cuba): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Dynamic SQL Error; SQL error code = -804; Data type unknown [SQLState:HY004, ISC error code:335544573]
Error Code: 335544573
Call: SELECT t0.ID, t0.NAME FROM DIC_LANGUAGES t0 WHERE EXISTS (SELECT ? FROM DIC_LANGUAGES t1) 
	bind => [1]
Query: ReadAllQuery(referenceClass=Language sql="SELECT t0.ID, t0.NAME FROM DIC_LANGUAGES t0 WHERE EXISTS (SELECT ? FROM DIC_LANGUAGES t1) ")
FetchGroup(){name, id}
---
org.eclipse.persistence.exceptions.DatabaseException: 
Internal Exception: java.sql.SQLException: Dynamic SQL Error; SQL error code = -804; Data type unknown [SQLState:HY004, ISC error code:335544573]
Error Code: 335544573
Call: SELECT t0.ID, t0.NAME FROM DIC_LANGUAGES t0 WHERE EXISTS (SELECT ? FROM DIC_LANGUAGES t1) 
	bind => [1]
Query: ReadAllQuery(referenceClass=Language sql="SELECT t0.ID, t0.NAME FROM DIC_LANGUAGES t0 WHERE EXISTS (SELECT ? FROM DIC_LANGUAGES t1) ")
FetchGroup(){name, id}
---
java.sql.SQLException: Dynamic SQL Error; SQL error code = -804; Data type unknown [SQLState:HY004, ISC error code:335544573]
---
org.firebirdsql.jdbc.FBSQLExceptionInfo: Dynamic SQL Error

Language class is very simple:

@DdlGeneration(value = DdlGeneration.DbScriptGenerationMode.CREATE_ONLY)
@Table(name = "DIC_LANGUAGES")
@Entity(name = "xxx_Language")
@NamePattern("%s|name")
public class Language extends BaseStringIdEntity {
    private static final long serialVersionUID = 1166412891234927384L;
    @Id
    @Column(name = "ID", nullable = false, length = 32)
    private String id;
    @Column(name = "NAME", nullable = false, length = 24)
    private String name;

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public void setId(String id) {
        this.id = id;
    }

    @Override
    public String getId() {
        return id;
    }
}

The exception is coming from the db. the generated sql contains a ? in the subquery.

Can you

  1. enable more loggign
  2. run the generated sql directly against the db to verify the error is the same
  3. confirm that normal queries (without the subquery) work
  4. change the subquery involving the fields (eg. … EXISTS (select e2.name from xxx_Language e2)
  1. There is no new information in app.log when eclipselink.sql is in DEBUG mode.
  2. When I replace ‘?’ with ‘1’ or ‘t1.ID’ or ‘t1.ID, t1.NAME’ query works fine in ibexpert.
SELECT t0.ID, t0.NAME FROM DIC_LANGUAGES t0 WHERE EXISTS (SELECT 1 FROM DIC_LANGUAGES t1)
SELECT t0.ID, t0.NAME FROM DIC_LANGUAGES t0 WHERE EXISTS (SELECT t1.ID FROM DIC_LANGUAGES t1)
SELECT t0.ID, t0.NAME FROM DIC_LANGUAGES t0 WHERE EXISTS (SELECT t1.ID, t1.NAME FROM DIC_LANGUAGES t1)
  1. Query without EXISTS work fine. Even with ‘IN’ and ‘= ANY’ works fine. Both woks fine, but I need EXIST for more complex query.
<query>
      <![CDATA[select e from xxx_Language e where e.id IN (select e2.id from xxx_Language e2)]]>
</query>
<query>
      <![CDATA[select e from xxx_Language e where e.id = ANY (select e2.id from xxx_Language e2)]]>
</query>
  1. Got same error for any statement:
<query>
      <![CDATA[select e from xxx_Language e where exists (select 1 from xxx_Language e2)]]>
</query>
<query>
      <![CDATA[select e from xxx_Language e where exists (select e2.id from xxx_Language e2)]]>
</query>

Important note: EXIST work fine on main database - PostgreSQL but fail only on additional - Firebird 2.5.
Upgrading jaybird driver from 4.0.1 to actual 4.0.2 has no effect :frowning: