Incorrect DEFAULT values generated for Informix

[lukaseder]

Informix doesn't list DEFAULT values in SYSDEFAULTS that can be used right away, see:
https://www.ibm.com/support/pages/returning-literal-default-sysdefaults-default-column

For example:

select colname, coltype, default
from informix.syscolumns c
join informix.sysdefaults d
on c.tabid = d.tabid and c.colno = d.colno;

Resulting in:

|colname        |coltype|default                                                                                                                                                                                                                                                         |
|---------------|-------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|title          |269    |no title                                                                                                                                                                                                                                                        |
|language_id    |258    |AAAAAQ 1                                                                                                                                                                                                                                                        |
|obj_owner      |0      |informix                                                                                                                                                                                                                                                        |
|sequence       |2      |AAAAAA 0                                                                                                                                                                                                                                                        |
|obj_kind       |2      |AAAAAA 0                                                                                                                                                                                                                                                        |
|create_can_fail|41     |f                                                                                                                                                                                                                                                               |
|drop_can_fail  |41     |f                                                                                                                                                                                                                                                               |
|alter_can_fail |41     |f                                                                                                                                                                                                                                                               |
|state          |0      |U                                                                                                                                                                                                                                                               |
|temp_state     |0      |U                                                                                                                                                                                                                                                               |
|final_state    |0      |U                                                                                                                                                                                                                                                               |
|obj_kind       |2      |AAAAAA 0                                                                                                                                                                                                                                                        |
|obj_owner      |0      |informix                                                                                                                                                                                                                                                        |
|need_obj_kind  |2      |AAAAAA 0                                                                                                                                                                                                                                                        |
|need_obj_owner |0      |informix                                                                                                                                                                                                                                                        |
|state          |0      |U                                                                                                                                                                                                                                                               |
|temp_state     |0      |U                                                                                                                                                                                                                                                               |
|final_state    |0      |U                                                                                                                                                                                                                                                               |
|id             |258    |AAAAAQ 1                                                                                                                                                                                                                                                        |
|cd             |10     |                                                                                                                                                                                                                                                                |

This seems to be getting close:

select 
  colname, 
  coltype, 
  informix.schema_coltypename(coltype, extended_id),
  trim(CASE WHEN informix.schema_coltypename(coltype, extended_id) IN ('BOOLEAN', 'CHAR', 'VARCHAR', 'LVARCHAR') THEN default ELSE substr(default, instr(default, ' ')) END),
  default
from informix.syscolumns c
join informix.sysdefaults d
on c.tabid = d.tabid and c.colno = d.colno;

Resulting in:

|colname|coltype|(expression)      |(expression)      |default                                                                                                                                                                                                                                                         |
|-------|-------|------------------|------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|c1     |52     |BIGINT            |1                 |AAAAAAAAAAE 1                                                                                                                                                                                                                                                   |
|c2     |41     |BLOB              |                  |                                                                                                                                                                                                                                                                |
|c3     |41     |BOOLEAN           |t                 |t                                                                                                                                                                                                                                                               |
|c4     |11     |BYTE              |                  |                                                                                                                                                                                                                                                                |
|c5     |0      |CHAR              |c                 |c                                                                                                                                                                                                                                                               |
|c6     |7      |DATE              |2000-01-01        |AACOrQ 2000-01-01                                                                                                                                                                                                                                               |
|c8     |5      |DECIMAL           |1.1000000000000000|wQEKAAAAAAAAAA 1.1000000000000000                                                                                                                                                                                                                               |
|c9     |3      |FLOAT             |1.1000000000000000|mpmZmZmZ8T8 1.1000000000000000                                                                                                                                                                                                                                  |
|c10    |17     |INT8              |1                 |AAEAAAABAAAAAA 1                                                                                                                                                                                                                                                |
|c11    |2      |INTEGER           |1                 |AAAAAQ 1                                                                                                                                                                                                                                                        |
|c13    |40     |LVARCHAR          |v                 |v                                                                                                                                                                                                                                                               |
|c15    |4      |SMALLFLOAT        |1.10000000        |zcyMPw 1.10000000                                                                                                                                                                                                                                               |
|c16    |1      |SMALLINT          |1                 |AAE 1                                                                                                                                                                                                                                                           |
|c17    |12     |TEXT              |                  |                                                                                                                                                                                                                                                                |
|c18    |13     |VARCHAR           |v                 |v                                                                                                                                                                                                                                                               |

[lukaseder]

This will be fixed in jOOQ 3.19.0 only, with #15682

[lukaseder]

This issue shows that we're probably not getting DEFAULT values right for all data types and all RDBMS.

[lukaseder]

Will soon create a follow-up issue to collect potential known problems in this area.