Learn how easy it is to sync an existing GitHub or Google Code repo to a SourceForge project! See Demo

Close

A coalesce function on a foreign key

Help
Stephane
2012-12-17
2014-01-19
  • Stephane
    Stephane
    2012-12-17

    Hi,

    I would like to have nulls first on an order by statement. Since this statement is part of a DAO that also has to be compatible with Oracle, the function coalesce(documentCategory, '0') is being used to obtain the nulls first. The DAO is a Maven project which may be run against the following database servers: MySql, H2, Oracle and HSQLDB. The statement works fine on all of them except on HSQLDB.

            public Page<Document> findPublished(final int pageNumber, final int pageSize) {
                    String query = "from Document where hide != :hide order by coalesce(documentCategory, '0')";
                    Map<String, Object> parameters = new HashMap<String, Object>();
                    parameters.put("hide", true);
                    Page<Document> page = getPage(pageNumber, pageSize, query, parameters, getSession());
                    return page;
            }

    After trying different options I noticed that the coalesce use on a key, be it the id primary key of the table, or the documentCategory foreign key of the table, makes the statement being not executable. But using the coalesce function on other columns of the same table, columns that are not the primary key nor the foreign key, the statement becomes executable. Therefore there is something with using coalesce on a key that makes HSQLDB not happy.

    Anyone has some experience on this ?

    Here is the full sql statement issued by the DAO method:

    select document0_.id as id99_, document0_.version as version99_, document0_.reference as reference99_, document0_.description as descript4_99_, document0_.filename as filename99_, document0_.hide as hide99_, document0_.secured as secured99_, document0_.list_order as list8_99_, document0_.category_id as category9_99_ from document document0_ where document0_.hide<>? order by coalesce(document0_.category_id, '0'), document0_.list_order

    I could of course, create an HSQLDB specific DAO method but for now I'm trying to have only one such method compatible with MySql, Oracle, H2 and HSQLDB even if it's more for the exploit than any actual business requirement.

    After shooting in the dark, I noticed that if the coalesce function is used on a column that is not the primery id key nor a foreign key, then the sql statement works fine.

    But if it is being used on the primery id key or a foreign key as in my case, then it gives the error.

    I'm running against the HSQLDB 2.2.9 version.

    Here are all the statements being issued:

        insert
        into
            document
            (version, filename, hide, secured, list_order, id)
        values
            (?, ?, ?, ?, ?, default)
    2012-12-10 13:50:24,800 TRACE   binding parameter  as  - 0
    2012-12-10 13:50:24,801 TRACE   binding parameter  as  - pdf1.pdf
    2012-12-10 13:50:24,802 TRACE   binding parameter  as  - false
    2012-12-10 13:50:24,805 TRACE   binding parameter  as  - false
    2012-12-10 13:50:24,806 TRACE   binding parameter  as  - 2

        insert
        into
            document_category
            (version, name, list_order, id)
        values
            (?, ?, ?, default)
    2012-12-10 13:50:24,813 TRACE   binding parameter  as  - 0
    2012-12-10 13:50:24,813 TRACE   binding parameter  as  - images
    2012-12-10 13:50:24,814 TRACE   binding parameter  as  - 1

        insert
        into
            document
            (version, filename, hide, secured, list_order, category_id, id)
        values
            (?, ?, ?, ?, ?, ?, default)
    2012-12-10 13:50:24,815 TRACE   binding parameter  as  - 0
    2012-12-10 13:50:24,816 TRACE   binding parameter  as  - image0.png
    2012-12-10 13:50:24,816 TRACE   binding parameter  as  - false
    2012-12-10 13:50:24,816 TRACE   binding parameter  as  - false
    2012-12-10 13:50:24,817 TRACE   binding parameter  as  - 1
    2012-12-10 13:50:24,817 TRACE   binding parameter  as  - 1

        insert
        into
            document
            (version, filename, hide, secured, list_order, category_id, id)
        values
            (?, ?, ?, ?, ?, ?, default)
    2012-12-10 13:50:24,820 TRACE   binding parameter  as  - 0
    2012-12-10 13:50:24,820 TRACE   binding parameter  as  - pdf2.pdf
    2012-12-10 13:50:24,820 TRACE   binding parameter  as  - false
    2012-12-10 13:50:24,821 TRACE   binding parameter  as  - false
    2012-12-10 13:50:24,821 TRACE   binding parameter  as  - 3
    2012-12-10 13:50:24,821 TRACE   binding parameter  as  - 1

        update
            document
        set
            version=?,
            hide=?
        where
            id=?
            and version=?
    2012-12-10 13:50:25,062 TRACE   binding parameter  as  - 1
    2012-12-10 13:50:25,062 TRACE   binding parameter  as  - true
    2012-12-10 13:50:25,063 TRACE   binding parameter  as  - 2
    2012-12-10 13:50:25,063 TRACE   binding parameter  as  - 0

        select
            document0_.id as id99_,
            document0_.version as version99_,
            document0_.reference as reference99_,
            document0_.description as descript4_99_,
            document0_.filename as filename99_,
            document0_.hide as hide99_,
            document0_.secured as secured99_,
            document0_.list_order as list8_99_,
            document0_.category_id as category9_99_
        from
            document document0_
        where
            document0_.hide<>?
        order by
            coalesce(document0_.category_id,
            '0')

    And here is the Hibernate mapping based on which the table structure is created at runtime for the in memory database instance:

        <class name="com.thalasoft.learnintouch.core.domain.Document" table="document" dynamic-insert="true" dynamic-update="true">
            <id name="id" type="java.lang.Integer">
                <column name="id" />
                <generator class="native">sq_id_document</generator>
            </id>
            <version name="version" type="int">
                <column name="version" not-null="true" />
            </version>
            <property name="reference" type="string">
                <column name="reference" length="50" />
            </property>
            <property name="description" type="string">
                <column name="description" />
            </property>
            <property name="filename" type="string">
                <column name="filename" length="50" not-null="true" />
            </property>
            <property name="hide" type="boolean">
                <column name="hide" not-null="true" />
            </property>
            <property name="secured" type="boolean">
                <column name="secured" not-null="true" />
            </property>
            <property name="listOrder" type="int">
                <column name="list_order" not-null="true" />
            </property>
            <many-to-one name="documentCategory" class="com.thalasoft.learnintouch.core.domain.DocumentCategory" cascade="all">
                <column name="category_id" />
            </many-to-one>
        </class>

    Any clue ?

    Kind Regards,

     
  • Fred Toussi
    Fred Toussi
    2012-12-17

    1. What happens when the statement does not work? Is there an error message? What is the message?
    2. What is the CREATE TABLE statement for the tables used in the SELECT? This is required for any further comment.

     
  • Stephane
    Stephane
    2013-02-06

    Hi,

    Sorry the the late answer, I was not notified of yours.

    So, here is the table structure in the MySql database:

    CREATE TABLE `document` (
      `id` int(10) unsigned NOT NULL auto_increment,
      `version` int(10) unsigned NOT NULL,
      `reference` varchar(50) default NULL,
      `description` varchar(255) default NULL,
      `filename` varchar(50) default NULL,
      `hide` tinyint(1) NOT NULL,
      `secured` tinyint(1) NOT NULL,
      `category_id` int(10) unsigned default NULL,
      `list_order` int(10) unsigned NOT NULL,
      PRIMARY KEY  (`id`),
      UNIQUE KEY `id` (`id`),
      KEY `category_id` (`category_id`),
      CONSTRAINT `document_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `document_category` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1

    As of the table structure in the HSQLDB database, I shall run it soon against the file based server and connect with a client to have a look at it. More soon…

    Kind Regards,

     
  • Stephane
    Stephane
    2013-02-06

    I could solve the issue by using a criteria:

            Criteria criteria = getSession().createCriteria(getPersistentClass());
            criteria.add(Restrictions.ne("hide", true));
            criteria.addOrder(Order.asc("documentCategory")).addOrder(Order.asc("listOrder"));
            Page<Document> page = getPage(pageNumber, pageSize, criteria);
            return page;
    

    This way it works as expected on all four database servers.

    I shall see why the coalesce does not work on HSQLDB though and let you know.

    Kind Regards,

     
  • Stephane
    Stephane
    2013-02-06

    After running the DAO with the coalesce code I can see the following error message:

    org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [selec
    t document0_.id as id99_, document0_.version as version99_, document0_.reference as reference99_, do
    cument0_.description as descript4_99_, document0_.filename as filename99_, document0_.hide as hide99
    _, document0_.secured as secured99_, document0_.list_order as list8_99_, document0_.category_id as c
    ategory9_99_ from document document0_ where document0_.hide<>? order by coalesce(document0_.category
    _id, '0')]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query

    Caused by: org.hsqldb.HsqlException: incompatible data types in combination

    I will try to run the ablove sql statement in an HSQLDB client.

    Kind Regards,

     
  • Stephane
    Stephane
    2013-02-06

    After running the HSQLDB as a file based server I could use the client to output the following table specs:

    DOCUMENT                          ID                                     INTEGER   32          0              NO
    DOCUMENT                          VERSION                                INTEGER   32          0              NO
    DOCUMENT                          REFERENCE                              VARCHAR   50          (null)         YES
    DOCUMENT                          DESCRIPTION                            VARCHAR   255         (null)         YES
    DOCUMENT                          FILENAME                               VARCHAR   50          (null)         NO
    DOCUMENT                          HIDE                                   BIT       1           (null)         NO
    DOCUMENT                          SECURED                                BIT       1           (null)         NO
    DOCUMENT                          LIST_ORDER                             INTEGER   32          0              NO
    DOCUMENT                          CATEGORY_ID                            INTEGER   32          0              YES
    
     
  • Stephane
    Stephane
    2013-02-06

    When running in the HSQLDB client the following sql statement:

    select document0_.id as id99_, document0_.version as version99_, document0_.reference as reference99_, document0_.description as descript4_99_, document0_.filename as filename99_, document0_.hide as hide99_, document0_.secured as secured99_, document0_.list_order as list8_99_, document0_.category_id as category9_99_ from document document0_ where document0_.hide<>? order by coalesce(document0_.category_id, '0');

    I got the error message:

    Incompatible data types in combination. Error code 5562 / State 42562

    On the same session, I could see the table was empty though:
    The sql statement
    select * from document;
    returned nothing and no error.

     
  • Fred Toussi
    Fred Toussi
    2013-02-06

    Thanks for the report.

    Please note the easy way to obtain the CREATE TABLE statement in a client is the SCRIPT statement which returns all database settings followed by all CREATE TABLE and other DDL statements.

    The problem is with your coalesce(documentCategory, '0') expression, which is converted to coalesce(document0_.category_id, '0'). The category_id column type is INT, therefore the coalesce must include an int, instead of a string. Use coalesce(document0_.category_id, 0) instead. This is the correct way of using this function, even with other databases.

    Your coalesce() function works with the VARCHAR columns, but not with INT columns. It is not related to the column being PRIMARY KEY or not.

     
  • Stephane
    Stephane
    2013-02-06

    Indeed, all my 3 other coalesce use cases were against a string type property.

    And the build passes. Thank you.