#1261 Type Coercion Needed

current-release
closed-fixed
1
2013-12-30
2012-10-26
Mark Bonano
No

When running a query that coalesces a bit field, you should be able to supply a default value as follows:

SELECT COALESCE(aTable.someBitField, 1) FROM ...

In the above example, if 'aTable.someBitField' is NULL, 1 should be returned in the result set. Currently, an exception is thrown with the message 'incompatible data types in combination'. In a similar vein, the following query should return the same result set:

SELECT COALESCE(aTable.someBitField, TRUE) FROM ...

Both queries ran and failed against HSQLDB v2.2.9, running in memory with the following connection string:

jdbc:hsqldb:mem:test-DB;sql.syntax_mys=true

Discussion

  • Mark Bonano

    Mark Bonano - 2012-10-26
    • priority: 5 --> 9
     
  • Fred Toussi

    Fred Toussi - 2012-10-26

    It is possible to provide a default value as a bit literal:

    create table t (id int, b bit)
    insert into t values 1, null
    select coalesce(b, B'1') from t

    If you need your suggested usage for MySQL comaptibility, we may be able to support it.

     
  • Mark Bonano

    Mark Bonano - 2012-10-27

    I will submit this as a feature request. Thank you for such a prompt response!!

     
  • Mark Bonano

    Mark Bonano - 2012-10-27
    • status: open --> closed
     
  • Fred Toussi

    Fred Toussi - 2012-10-27

    You don't need to submit a feature request. This is enough. Will be supported in a future version.

     
  • Fred Toussi

    Fred Toussi - 2012-10-27
    • assigned_to: nobody --> fredt
    • milestone: --> current-release
    • priority: 9 --> 1
    • labels: --> engine 2.0
    • status: closed --> open-later
     
  • Fred Toussi

    Fred Toussi - 2013-09-20

    Support added to SVN for version 2.3.1 in MySQL syntax mode only.

     
  • Fred Toussi

    Fred Toussi - 2013-09-20
    • status: open-later --> open-fixed
     
  • Fred Toussi

    Fred Toussi - 2013-12-30
    • status: open-fixed --> closed-fixed
     

Log in to post a comment.