FWD » Core Development » Database

Update on the regression testing:

• harness ✅
• large app with fwd tests -- unittests ✅
• large GUI app unittests ✅
• chui regression tests ✅

The change is relatively safe and straightforward. Branch 10181a is ready to be merged.

Working on #11910 where I was trying to perform an "import" of an exported regression test DB (with expanded extents and PG17), the dates were shifted on import. Claude pointed to this task as what was causing it.

The one-day shift is still present, and trunk rev 16010 reproduces the original symptom

Found while validating an export/import round trip of the ChUI regression database (#11910). Every date in the imported database came out one day earlier than the source -- row counts intact, no error reported.

Tracing it leads back to the change made for this issue -- trunk revision 16010, "Changed DateType.setField to use 'UTC' timezone when getting the 'dateValue'", merged 2025-06-27. The short version: the code before rev 16010 is correct in every time zone the JVM knows; the code after it is wrong in 42% of them, and produces exactly the 1920-01-01 -> 1919-12-31 symptom that was originally reported in #7504-31.

What was observed

A .d export of gso_20090909_staging re-imported into a fresh cluster on the same machine:

source:         close_date=2008-10-31   order_date=2008-07-15
round-tripped:  close_date=2008-10-30   order_date=2008-07-14

Not one table and not one column. Every date column checked, minimum and maximum:

Column	Source	Round-tripped
customer_order.order_date	0100-01-10 .. 2011-11-09	0100-01-09 .. 2011-11-08
customer_order.close_date	1992-09-02 .. 2004-05-25	1992-09-01 .. 2004-05-24
job.start_date	1990-10-30 .. 2009-03-11	1990-10-29 .. 2009-03-10

The export is not at fault. The .d record for order 283315 holds 31/10/08, 15/07/08 and 16/08/08, matching the source exactly, and the footer carries dateformat=dmy-1950 which the import reads correctly. The value is damaged going in, not coming out.

Mechanism

One line writes in UTC while everything around it reads and renders in the JVM's default zone.

Write -- DateType.setField():259 stores the millisecond value of midnight in UTC:

data[offset] = new Date(asBDT.dateValue(TimeZone.getTimeZone("UTC")).getTime());

date.dateValue(TimeZone) (date.java:3221) returns neutralMillisToDate(julianDayToMillis(julian), override) -- midnight in the zone passed. So the stored instant is 00:00 UTC.

Both write paths render that instant using the default zone:

Path	Code	Zone used
batch insert	TypeManager.setSqlDateParameter:717 -- stmt.setDate(index, val)	default, no Calendar
bulk COPY	BulkCopyCsvWriter:306 -- sb.append(val), i.e. Date.toString()	default

Both read paths also use the default zone:

Path	Code	Zone used
result set	DateType.readProperty:165 -- rs.getDate(rsOffset)	default, no Calendar
to BDT	DateType.convert:114 -- new date((java.util.Date) sqlVal)	default, via date(Date):767

West of Greenwich, 00:00 UTC is still the previous day locally, so the driver and the CSV writer both emit the previous day. At or east of Greenwich it is the same calendar day and nothing is visible.

Evidence

Rev 16010 changed exactly one expression:

-      data[offset] = new Date(asBDT.dateValue(null).getTime());
+      data[offset] = new Date(asBDT.dateValue(TimeZone.getTimeZone("UTC")).getTime());

dateValue(null) uses TimeZone.getDefault(). Call the two variants A (before) and B (after, current).

Sweeping every time zone the JVM knows, for four dates spanning the range in our data (1920-01-01, 2008-10-31, 1970-01-01, 0100-01-10):

Variant	Combinations tested	Failures
A -- dateValue(null), before	2528	0
B -- dateValue(UTC), current	2528	1061

The first failure the sweep reports is Africa/Bissau 1920-01-01 -> 1919-12-31.

That is the exact symptom this issue was opened to fix. From #7504-note-256783, 2025-05-16:

I imported a .d file containing the value 01/01/1920 for a date column. After import, the stored value in the database was 1919-12-31 (disregarding the formatting), which indicates the date was altered during the import process.

Confirmed against a real import as well, varying only TZ on the import process:

Import ran at	Dates	Records dropped
UTC-5	shifted -1 day	--
UTC-4	shifted -1 day	2
UTC	correct	0
UTC+2	correct	0

Three standalone reproducers are attached -- DateShift.java, Variants.java and Sweep.java. None depends on FWD or on a database; they are a few dozen lines of java.sql.Date and TimeZone and can be run directly.

What I could not establish

I cannot explain what produced the original 1919-12-31 in May 2025. That report predates rev 16010 by six weeks, so the code at the time was variant A -- and variant A does not fail in any of the 2528 combinations I tested. Something else in that environment must have been responsible, and the issue does not record the time zone it ran in.

What the evidence does say is that the change made in response did not remove the symptom. It made it reproducible, from the default configuration, in 42% of the world's time zones.

Greg's guess at the time was "EEST vs GMT timezone issue?", and Lorian's instinct in #7504-33 was right:

If the column is of type date I would incline to say that it should not take into consideration any timezones, and keep its original value.

That is precisely the property the current code lacks: a SQL DATE carries no time zone, but the stored value's calendar day now depends on where the JVM is running.

The dropped rows

The shift also silently discards data. material_edit holds 0200-03-01 in two rows -- long-standing bad data, almost certainly "200" typed for "2009", as the adjacent rows read 2009-03-01. The -1 shift pushes that onto 0200-02-29, which does not exist in the Gregorian calendar, so PostgreSQL rejects the row outright. Under TZ=UTC both rows import cleanly.

Scope

Not specific to ChUI regression testing. Any FWD user running import.sh anywhere west of Greenwich gets every date in the imported database shifted back one day -- silently, with matching row counts and no error.

In our case it was caught only because the resulting database failed 181 regression tests on off-by-one numeric comparisons. A validation based on row counts passes it cleanly, and there is no check inside the database that could notice, because a DATE has no time zone to disagree with. The only thing that catches it is comparing values against the source.

Suggested fix

1. Revert setField to dateValue(null). One expression, and the sweep says it is correct in all 2528 zone/date combinations tested. Given that the symptom it was changed to fix is reproduced by the change itself, this looks like the right immediate move.
2. Or make the whole pipeline explicitly UTC. Keep setField as it is and pass a UTC Calendar at every boundary so nothing consults the default zone: stmt.setDate(index, val, utcCal), rs.getDate(offset, utcCal), new date(d, UTC) in convert, and an explicit UTC format in BulkCopyCsvWriter in place of Date.toString(). This keeps a UTC-normalised internal representation and is zone-independent by construction.

Option 2 is the more principled of the two and would survive someone later adding another code path that forgets the zone. It touches four sites including bulk copy, so it wants review. Option 1 is a one-expression change with exhaustive evidence behind it.

I have made neither change, since this issue belongs to someone else and the original symptom is still unexplained.

Workaround in the meantime

Running the import under TZ=UTC avoids the shift completely: dates match the source exactly and nothing is dropped. The MAJIC regression build now does this, scoped to the import invocation only -- the runtime time zone is deliberately left alone, because the regression container pins its clock for time-sensitive tests such as gso_365 "Clock Out Offenders Report" and gso_382 "Time & Attendance Log report".

One caution for anyone reproducing this: that container derives its time zone from the UTC hour at which it starts (start_regression_testing.sh:62), so it lands either side of UTC depending on when a build is kicked off. Roughly eleven hours out of twenty-four produce a corrupted database and the rest do not, which makes the defect look intermittent and machine-specific when it is neither.