[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-4738) getSingleResult returning SQLException in DerbyDialect

[Stephan Vollmer (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-4738?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=37279#action_37279 ] 

Stephan Vollmer commented on HHH-4738:
--------------------------------------

I just encountered the same problem with Hibernate 3.5.2 and Derby 10.6.0.1. It is visible when these conditions are satisfied:

* The JPA QL is {{"select count(t) from MyEntity t"}}. When the Query is {{select count(\*) from MyEntity}}, then everything works fine.
* The entity {{MyEntity}} has a composite ID that consists of 2 (or probably more) columns.

Example: 
{code}
@Entity
public class PsCsBatRow {
    @Id
    private PsCsBatRowId id;
}


@Embeddable
public class PsCsBatRowId implements Serializable {
    private static final long serialVersionUID = 1L;

    @Column(length = 20, nullable = false)
    private String csBaEinwId;

    @Column(length = 20, nullable = false)
    private String csBaEinwLfdnr;
}
{code}

* Hibernate creates the following SQL statement. It seems like the 2 columns in the {{count(...)}} expression are the cause:
{code:sql}
 /* select
        count(t) 
    from
        PsCsBatRow t */ select
            count((pscsbatrow0_.cs_ba_einw_id,
            pscsbatrow0_.cs_ba_einw_lfdnr)) as col_0_0_ 
        from
            ps_cs_bat_row pscsbatrow0_ fetch first 2 rows only
{code}
* Derby throws this exception:
{noformat}
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "," at line 1, column 81.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
	at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
	at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
	at org.apache.derby.iapi.jdbc.BrokeredConnection.prepareStatement(Unknown Source)
{noformat}

I hope this helps!

> getSingleResult returning SQLException in DerbyDialect
> ------------------------------------------------------
>
>                 Key: HHH-4738
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-4738
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: query-hql
>    Affects Versions: 3.5.0-Beta-2
>         Environment: hibernate 3.5.0-0.2, Glassfish v3, Derby
>            Reporter: Samuel Halliday
>            Assignee: Gail Badner
>         Attachments: WebJpa.zip
>
>
> I am encountering problems when issuing getSingleResult queries using the Glassfish v3 "hibernate 3.5.0-0.2". These queries work fine if I use hibernate 3.3.1.GA, or a PostgreSQL backend/dialect. Something between 3.3.1 and 3.5 has therefore introduced this bug.
> The queries are along the lines of
> [code]
> 	Query q = em.createQuery("SELECT COUNT(s) FROM MyTable s");
> 	Long result = (Long) q.getSingleResult();
> [/code]
> and
> [code]
> 	Query query = em.createQuery("SELECT s FROM MyTable s ORDER BY s.id");
> 	query.setFirstResult(id);
> 	query.setMaxResults(1);
> 	MyTable random = (MyTable) query.getSingleResult();
> [/code]
> The exception I am seeing is below, when used with (at least) the Java Derby DB. The message is "The column position '1' is out of range.  The number of columns for this ResultSet is '0'."
> [code]
> Caused by: java.sql.SQLException: The column position '1' is out of range.  The number of columns for this ResultSet is '0'.
>         at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
>         at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
>         at org.apache.derby.client.am.ColumnMetaData.getColumnType(Unknown Source)
>         at org.apache.derby.client.am.PreparedStatement.setInt(Unknown Source)
>         at org.hibernate.loader.Loader.bindLimitParameters(Loader.java:1669)
>         at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1594)
>         at org.hibernate.loader.Loader.doQuery(Loader.java:694)
>         at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:257)
>         at org.hibernate.loader.Loader.doList(Loader.java:2232)
>         ... 42 more
> Caused by: org.apache.derby.client.am.SqlException: The column position '1' is out of range.  The number of columns for this ResultSet is '0'.
>         at org.apache.derby.client.am.ColumnMetaData.checkForValidColumnIndex(Unknown Source)
>         ... 49 more
> [/code]

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira