#287 Lack support of LOB columns

closed
engine (144)
1
2013-01-23
2012-12-13
No

When trying to select records (with group by) with columns that has BLOB types receives error:
Caused by: java.sql.SQLSyntaxErrorException: column of LOB or ARRAY type cannot be used in operation

The same works well on MySQL.

Discussion

  • Fred Toussi

    Fred Toussi - 2012-12-14

    Can you provide an example of a query that really needs GROUP BY on such columns.

     
  • Łukasz Nowak

    Łukasz Nowak - 2012-12-14

    Sure:

    SELECT ps.*
    FROM mycloud.proxy_server ps LEFT OUTER JOIN mycloud.vpn_connection vc ON ps.id = vc.primary_proxy_server_id
    WHERE IFNULL(vc.active, 1) = true
    GROUP BY ps.name
    HAVING COUNT(vc.id) < ps.capacity
    ORDER BY ps.name

    I am just returning here all columns because I want hibernate to map them to entity object automatically. And that generates exception.

     
  • Łukasz Nowak

    Łukasz Nowak - 2012-12-14

    This is the actual query for hsqldb. The previous was for mysql.

    StringBuilder sql = new StringBuilder();
    sql.append("SELECT ps.id, ps.name, ps.capacity FROM ");
    sql.append(getPersistentClass().getName());
    sql.append(" ps LEFT OUTER JOIN ps.secondaryVpnConnections vc ");
    sql.append("WHERE (vc.active=true OR vc.active is NULL) AND ps.id!=?1 ");
    sql.append("GROUP BY ps.id, ps.capacity ");
    sql.append("HAVING COUNT(vc.id) < ps.capacity ");
    sql.append("ORDER BY ps.name");

     
  • Fred Toussi

    Fred Toussi - 2012-12-14

    Which column in the query is a LOB or ARRAY column?

     
  • Łukasz Nowak

    Łukasz Nowak - 2012-12-14

    For example column ps.serverCertificate is defined as BLOB. I do not use it in GROUP BY clause, but want to only return it.

    StringBuilder sql = new StringBuilder();
    sql.append("SELECT ps.id, ps.name, ps.capacity, ps.serverCertificate FROM ");
    sql.append(getPersistentClass().getName());
    sql.append(" ps LEFT OUTER JOIN ps.secondaryVpnConnections vc ");
    sql.append("WHERE (vc.active=true OR vc.active is NULL) AND ps.id!=?1 ");
    sql.append("GROUP BY ps.id, ps.capacity ");
    sql.append("HAVING COUNT(vc.id) < ps.capacity ");
    sql.append("ORDER BY ps.name");

     
  • Jeremy Huiskamp

    Jeremy Huiskamp - 2012-12-21

    I have a failing query that seems to be related. I get the same error, but only when adding a DISTINCT modifier to my select statement.
    My queries and DDL are generated by Hibernate which I am using via JPA.

    My schema:

    create table registryobject (
    id varchar(255) not null,
    content clob,
    primary key (id)
    )

    create table registryobject_properties (
    registryobject_id varchar(255) not null,
    key varchar(255),
    value varchar(255)
    )

    create index registryobject_indexed_property_value_index on registryobject_properties (value)

    create index registryobject_indexed_property_key_index on registryobject_properties (key)

    alter table registryobject_properties
    add constraint FKD57BC396C5287B69
    foreign key (registryobject_id)
    references registryobject

    A query that works:

    select
    jparegistr0_.id as id0_,
    jparegistr0_.content as content0_
    from
    registryobject jparegistr0_
    inner join
    registryobject_properties properties1_
    on jparegistr0_.id=properties1_.registryobject_id
    where
    properties1_.key=?
    and (
    properties1_.value in (
    ?
    )
    )
    and (
    jparegistr0_.id in (
    ? , ?
    )
    )

    A query that fails (only difference is the addition of the distinct modifier):

    select
    distinct jparegistr0_.id as id0_,
    jparegistr0_.content as content0_
    from
    registryobject jparegistr0_
    inner join
    registryobject_properties properties1_
    on jparegistr0_.id=properties1_.registryobject_id
    where
    properties1_.key=?
    and (
    properties1_.value in (
    ?
    )
    )
    and (
    jparegistr0_.id in (
    ? , ?
    )
    )

    My error:

    org.hsqldb.HsqlException: column of LOB or ARRAY type cannot be used in operation
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.QuerySpecification.checkLobUsage(Unknown Source)
    at org.hsqldb.QuerySpecification.resolveTypesPartTwo(Unknown Source)
    at org.hsqldb.QueryExpression.resolve(Unknown Source)
    at org.hsqldb.ParserDQL.compileCursorSpecification(Unknown Source)
    at org.hsqldb.ParserCommand.compilePart(Unknown Source)
    at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
    at org.hsqldb.Session.compileStatement(Unknown Source)
    at org.hsqldb.StatementManager.compile(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 49 more

     
  • Jeremy Huiskamp

    Jeremy Huiskamp - 2012-12-21

    I think my complaint is invalid, I was thinking that the distinct modifier applies to only the id in my case instead of the entire result row. I was thrown off because this doesn't fail on postgres, where the type of the the content column is actually an oid which can be easily compared.

    Sorry for the spam...

     
  • Fred Toussi

    Fred Toussi - 2012-12-21

    The GROUP BY and DISTINCT examples are different in this respect.

    In theory it is possible to use DISTINCT on a SELECT that has a LOB column. The current restriction may be removed in the next version.

    The GROUP BY uage depends on which columns are in GROUP BY.

     
  • Fred Toussi

    Fred Toussi - 2012-12-22

    Your Feature Request has been accepted and is pending assignment.

    Thank you for your support of the hsqldb Engine Project!!!

     
  • Fred Toussi

    Fred Toussi - 2012-12-22

    I have added support for BLOB and CLOB in both GROUP BY and DISTINCT queries. The next jar snapshot will support this.

    The example given by Lukasz depends on ps.id being the PRIMARY KEY column of the table.

     
  • Fred Toussi

    Fred Toussi - 2012-12-22
    • priority: 5 --> 1
    • assigned_to: nobody --> fredt
     
  • Fred Toussi

    Fred Toussi - 2013-01-23

    Supported in the latest 2.3.0 snapshots.

     
  • Fred Toussi

    Fred Toussi - 2013-01-23
    • status: open --> closed
     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks