Code generator and MetaImpl return CREATE DEFAULT statement instead of just the default expression

[ingvard]

Expected behavior

The Field class should have the correct default value, which is unknown in my case.

Actual behavior

The MSSQL driver implementation for java.sql.DatabaseMetaData#getColumns returns create default my_default_data_type as 'unknown'; for column number 13 instead of the expected string value: unknown. I'm uncertain if this result aligns with the documented column behavior in the JDBC driver documentation:

<LI><B>COLUMN_DEF</B> String {@code =>} default value for the column, which should be interpreted as a string when the value is enclosed in single quotes (may be {@code null}) for default types

However, it appears to be a result of the database engine, causing issues with jOOQ parsing at this point. Consequently, there are numerous logs and an incorrect default value like:

org.jooq.impl.ParserException: Unexpected content after the end of field input: [1:8] create [*]default my_default_data_type as 'unknown'
	at org.jooq.impl.DefaultParseContext.exception(ParserImpl.java:14720)
	at org.jooq.impl.DefaultParseContext.done(ParserImpl.java:14906)
	at org.jooq.impl.DefaultParseContext.lambda$parseField0$8(ParserImpl.java:1001)
	at org.jooq.impl.DefaultParseContext.wrap(ParserImpl.java:14913)
	at org.jooq.impl.DefaultParseContext.parseField0(ParserImpl.java:1001)
	at org.jooq.impl.ParserImpl.parseField(ParserImpl.java:844)
	at org.jooq.impl.ParserImpl.parseField(ParserImpl.java:839)
	at org.jooq.impl.MetaImpl$MetaTable.initColumns(MetaImpl.java:1210)
	at org.jooq.impl.MetaImpl$MetaTable.<init>(MetaImpl.java:799)
	at org.jooq.impl.MetaImpl$MetaSchema.lambda$getTables$2(MetaImpl.java:487)
	at org.jooq.impl.Tools.map(Tools.java:2451)
	at org.jooq.impl.MetaImpl$MetaSchema.getTables(MetaImpl.java:458)
	at org.jooq.impl.FilteredMeta$FilteredSchema.getTables(FilteredMeta.java:335)

I suppose we should extend the field parser for this case.

Steps to reproduce the problem

1. Create schema:

create default my_default_data_type as 'unknown';

create table table_with_default_val (
    problem_column varchar
);

sp_bindefault 'my_default_data_type', 'table_with_default_val.problem_column';

2. Left a brake point in the line:

   jOOQ/jOOQ/src/main/java/org/jooq/impl/MetaImpl.java

   Line 1399 in 50290b4

   .parseField(defaultValue)

3. Call dslContext.meta().schemas.tables.
4. Catch the error in your logs and the breakpoint.

jOOQ Version

jOOQ Professional Edition 3.18.7

Database product and version

mcr.microsoft.com/mssql/server:2017-latest

Java Version

No response

OS Version

No response

JDBC driver name and version (include name if unofficial driver)

mssql-jdbc:12.5.0.jre11-preview (any)

[lukaseder]

Thanks a lot for your report. I can reproduce this.

[lukaseder]

It's what the server returns. Try this query:

select object_definition(default_object_id)
from sys.columns
where name = 'problem_column';

It will reproduce the CREATE DEFAULT ... statement. This affects both the code generator and the runtime meta data. Have you tried reporting this to mssql-jdbc as well? It doesn't look like this is what's expected. In either case, jOOQ should probably be able to handle this as well.

[lukaseder]

Even the INFORMATION_SCHEMA returns this value:

select column_default
from information_schema.columns
where column_name = 'problem_column'

[ingvard]

Even the INFORMATION_SCHEMA returns this value:

select column_default
from information_schema.columns
where column_name = 'problem_column'

Exactly, I suspect this is because this field may have a function as a default provider, such as getdate(). How to handle this case in Jooq correctly is uncertain to me. But the simple case (that I mentioned above) can be parsed.

[lukaseder]

Implementing this now. In order to have a shared implementation between code generator and MetaImpl, I'll implement this directly in the parser (which also throws the exception). There will be an undocumented flag to allow for the create default <identifier> as <constant>; syntax within Parser.parseField(), returning only the constant

[lukaseder]

There's a slight chance users would prefer that no default is reported on such fields as generated DDL from those tables will inline the default. E.g. with:

create default d_15934 as 'd';
create table t_15934 (d varchar(10));
sp_bindefault 'd_15934', 't_15934.d';

And then:

Meta meta =
create().meta()
        .filterCatalogs(c -> c.getName().equals(schema().getCatalog().getName()))
        .filterSchemas(s -> s.getName().equals(schema().getName()))
        .filterTables(x -> x.getName().equals("t_15934"));

String metaDDL = meta.ddl(new DDLExportConfiguration().flags(DDLFlag.TABLE)).toString();
System.out.println(metaDDL);

The result is now this, after the fix:

create table [test].[dbo].[t_15934] (
  [d] varchar(10) default 'd'
);

That's not exactly the same thing as what the original schema mandates. But we don't currently have a way to represent such named defaults in our meta model, so I don't see how this could be fixed without going all in on the feature support (which I don't think is worth prioritising right now):

• Add support for CREATE DEFAULT, DROP DEFAULT and related features #16410

[lukaseder]

Fixed in versions:

• 3.20.0
• 3.19.6 (Code generator and MetaImpl return CREATE DEFAULT statement instead of just the default expression - Merge [#15934] #16411)
• 3.18.13 (Code generator and MetaImpl return CREATE DEFAULT statement instead of just the default expression - Merge [#15934] #16412)
• 3.17.22 (Code generator and MetaImpl return CREATE DEFAULT statement instead of just the default expression - Merge [#15934] #16413)