SQL gives different result set between VS code / ACS / greenscren

[dev-wouter]

hi,

when trying only to use VS-Code i stumbled on something strange.
when looking at 1 date field the result set in VS code shows null, but actually it is *loval date.
even when i have a where 'Is not null' it still gives the *loval fields in the result set.

i think VS code makes the *loval value to a null in the result set but the query does not translate Null to *loval

if needed more info you can always ask.

Ps. working on the latest releases of the plugins/ vs code

[worksofliam]

@ThePrez Any opinion on this?

[FraGag]

I've been able to reproduce this. In fact, in my case, any date outside of the range [1940-01-01, 2039-12-31] shows null.

select cast('0001-01-01' as date) from SYSIBM.SYSDUMMY1; -- null
select cast('1939-12-31' as date) from SYSIBM.SYSDUMMY1; -- null
select cast('1940-01-01' as date) from SYSIBM.SYSDUMMY1; -- 40/01/01
select cast('2039-12-31' as date) from SYSIBM.SYSDUMMY1; -- 39/12/31
select cast('2040-01-01' as date) from SYSIBM.SYSDUMMY1; -- null
select cast('9999-12-31' as date) from SYSIBM.SYSDUMMY1; -- null

The timestamp type is not affected, though.

select cast('0001-01-01' as timestamp) from SYSIBM.SYSDUMMY1; -- 0001-01-01 00:00:00.000000
select cast('1939-12-31' as timestamp) from SYSIBM.SYSDUMMY1; -- 1939-12-31 00:00:00.000000
select cast('1940-01-01' as timestamp) from SYSIBM.SYSDUMMY1; -- 1940-01-01 00:00:00.000000
select cast('2039-12-31' as timestamp) from SYSIBM.SYSDUMMY1; -- 2039-12-31 00:00:00.000000
select cast('2040-01-01' as timestamp) from SYSIBM.SYSDUMMY1; -- 2040-01-01 00:00:00.000000
select cast('9999-12-31' as timestamp) from SYSIBM.SYSDUMMY1; -- 9999-12-31 00:00:00.000000

On the QZDASOINIT job's log, I see messages CPF5035 (Data mapping error on member SYSDUMMY1.) with error code 18 (There is data in a date, time, or timestamp field that is not valid.) and SQL0181 (Value in date, time, or timestamp string not valid.). In particular, SQL0181 has this note:

-- The range for years is from 0001 to 9999. For date formats *MDY, *YMD, *DMY, and *JUL, the year must be in the range 1940 to 2039.

Indeed, if I change the Date format to ISO in the job's Connection Properties (Format tab), then all dates show correctly.

Perhaps ISO should be the default? (Strangely, the Connection Properties says the default Date format is MDY, but I'm actually getting YMD because that's what system value QDATFMT is set to on the system I'm trying this on.)

By the way, ACS also lets us set the date format through the JDBC configuration. If it's set to *MDY, *YMD, *DMY, or *JUL, then there'll be a data mapping error too. However, ACS is able to show red + signs instead of NULL on cells that have data mapping errors.

[mikemoegling]

I have no idea how to add to this but from many years of heavy-duty SQL on Oracle, MySQL, and SQL Server, the returning of "NULL" is more of an industry standard for all data types in a column. Although the ACS example is a date example ACS returns a - on a null result. Comparing it to green on black is not comparable as it is a legacy type of SQL and is not progressing forward.

Showing NULL as a return result is 100% OK. Having used several tools for SQL development like SSMS, Toad, and Oracle Developer, each tool has a set of types that show the result set and nulls. I am enjoying the progress of this project. Thanks for the great work.

Below is from SSMS.

[worksofliam]

@mikemoegling looks like for null values we should not only return NULL, but also a slightly coloured box too.

Thanks for your kind words.

[FraGag]

@mikemoegling The nature of this issue is that vscode-db2i displays null for values that are in fact not NULL, but simply out of range when the date format set for the SQL session is one that displays the year using only 2 digits (which is the default). While I do agree that displaying NULL values in color like other tools would help distinguish it from the string value used to represent it, that wouldn't solve this issue. Out of range dates need to be rendered not as null, but as something else, e.g., ++++++++++ in red text as in ACS.

To summarize, my recommendations are:

1. Display null values with some colour (either text or background).
2. Display data mapping errors as ++++++++++ (or whatever number of plus signs feels appropriate) with some colour (either text or background).
3. Consider changing the default date format to ISO. (This format is consistent with how timestamps are formatted.)

[mikemoegling]

@FraGag Good thoughts. I misunderstood the original post, but your summarization with the clarification of a LOVAL date makes sense. I am a big proponent of having dates in the data state as ISO. Store them in a true state and then use the presentation layer or in the extracted data in the USA format or another format that works for the viewer or application.

In the IBMi world, we work a lot with long-lived data that has been living in all kinds of formats and field types. I am currently working with an entire database full of a hybrid Julian date, which is a real challenge. So having some extended ways to present this to the SQL developer might be a bonus that does not exist in other SQL tools.

I am going to try some tests in VS Code this week if time allows and dig a bit into this.

Mike