Repository navigation
fix(oracle): read DATE and TIMESTAMP as the stored wall clock, not a local Date - #1225
Conversation
…local Date oracledb builds both types as a Date in the Node process's zone, and every row path serialized it as ISO UTC, so DATE '2026-09-01' read as 2026-08-31T21:00:00.000Z under TZ=Europe/Istanbul. The row fetch type handler now converts both through the Date's local getters, the inverse of the driver's makeDate(useLocal), and the SQL INSERT export writes that text back through TO_DATE / TO_TIMESTAMP for a column declared DATE or TIMESTAMP[(n)]. TIMESTAMP WITH [LOCAL] TIME ZONE stay instants. Adds tests/live/oracle-zoneless-values.ts, which compares the provider's read with the server's own TO_CHAR under UTC, Europe/Istanbul and America/Los_Angeles and replays the export into a copy table. Closes libredb#1131
Codecov Report✅ All modified and coverable lines are covered by tests. 📢 Thoughts on this report? Let us know! |
|
Thanks, this is ready and I will merge it shortly. With main's oracle.ts and result-export.ts put back, 11 of your new tests fail, which matches your count. Breaking the converter in a dozen small ways, such as UTC getters, no year padding, a lost BC sign, an untrimmed fraction, or queryInTransaction left on the LOB handler, turns at least one case red each time. Both files still pass on top of current main, which has moved since you branched. Your Thin-mode finding on the issue is what made this approach work. One thing I will add on top: oracleTextLiteral puts the cell text into TO_DATE without quoting, so the ^ and $ of ORACLE_ZONELESS_TEXT are what keep the export safe. They hold today, but every test stays green with either one removed. I will pin them with a case that has text after a valid timestamp and one with text before it. |
…#1131) The provider's DATE and TIMESTAMP text goes into TO_DATE / TO_TIMESTAMP unquoted, so ORACLE_ZONELESS_TEXT has to match the whole cell. Removing either ^ or $ kept every test green; a wall clock with text before or after it now has to stay an escaped quoted literal.
Description
The Oracle provider now returns
DATEandTIMESTAMP(without time zone) as the engine's own wall clock,2026-09-01 00:00:00/2026-09-01 10:30:00.345, whatever time zone the Studio server process runs in. The SQL INSERT export writes that text back throughTO_DATE/TO_TIMESTAMP.oracledb (Thin and Thick) builds both types as
new Date(y, m - 1, d, h, mi, s, ms)in the Node process's zone (makeDate(useLocal)inlib/util.js), and every row path serialized it withtoISOString. A converter in the per-call fetch type handler ofquery()andqueryInTransaction()reads theDateback through its local getters, which is the exact inverse. As found in the issue, fetching asSTRINGdoes not work in Thin mode, and moving the sessionTIME_ZONEwould changeCURRENT_DATE/LOCALTIMESTAMPin users' own queries.TIMESTAMP WITH TIME ZONEandWITH LOCAL TIME ZONEare untouched, andreadDefinition()keeps the LOB-only handler.Library surface: in-process consumers now receive strings instead of
Dateobjects for these two Oracle types, the same as #1134 did for PostgreSQL.Where to look
The behaviour change is about 110 lines in three files:
rowFetchTypeHandler/formatZonelessDateinoracle.ts,oracleTextLiteral/oracleDateColumninresult-export.ts, and two type additions indb-drivers.d.ts. The rest is tests (≈460 lines, including the live script) and docs (≈110 lines).Live measurement
tests/live/oracle-zoneless-values.ts, 2026-10-02,gvenzl/oracle-free:slim(Oracle AI Database 26ai Free Release 23.26.3.0.0), oracledb 6.10.0, Bun 1.4.2. Every value read through the provider is compared with the server's ownTO_CHAR:TZDATEbeforeTIMESTAMPbeforeDATEnowTIMESTAMPnowUTC2026-09-01T00:00:00.000Z2026-09-01T10:30:00.000Z2026-09-01 00:00:002026-09-01 10:30:00Europe/Istanbul2026-08-31T21:00:00.000Z2026-09-01T07:30:00.000Z2026-09-01 00:00:002026-09-01 10:30:00America/Los_Angeles2026-09-01T07:00:00.000Z2026-09-01T17:30:00.000Z2026-09-01 00:00:002026-09-01 10:30:00TO_CHARin all three zones:10:11:12on aDATE,.345and.5fractions,NULL, and the BC date-0044-03-15 00:00:00. Before the change, that BC date read as-000044-03-14T22:04:08.000Zunder Istanbul.TIMESTAMP WITH TIME ZONE(the control) reads2026-09-01T07:30:00.000Zin every zone, before and after.POST /api/db/queryhands it on. Before:VALUES (1, '2026-08-31T21:00:00.000Z', '2026-09-01T07:30:00.000Z'), refused on replay withORA-01861: literal does not match format string. Now:TO_DATE('2026-09-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'),TO_TIMESTAMP('2026-09-01 10:30:00.345', 'YYYY-MM-DD HH24:MI:SS.FF')andSYYYYfor the BC row. All four rows replay to values the server compares as equal.DATEunder Istanbul is still the shifted2026-08-31T21:00:00.000Z. If the driver changes that, the guard fails instead of silently testing nothing.One correction to the issue text: in my measurement the old in-process export (rows still holding the driver's
Date) replayed correctly, becauseoracleDateLiteralalready used the local getters. The broken path was HTTP, where it was refused rather than writing the wrong day. The docs say this.Type of Change
Related Issue
Closes #1131
Changes Made
src/lib/db/providers/sql/oracle.ts:formatZonelessDateandrowFetchTypeHandler(DATE/TIMESTAMP converter, then the existing LOB mapping), used byquery()andqueryInTransaction().src/lib/export/result-export.ts: for a column declaredDATEorTIMESTAMP[(n)], text in exactly the provider's form becomesTO_DATE/TO_TIMESTAMPwith a mask matching that text (SYYYYonly for a BC year,.FFonly when there is a fraction). AVARCHAR2with the same characters, or any other text, stays quoted. TheDatepath is unchanged for hosts that build their own rows.src/types/db-drivers.d.ts:DB_TYPE_DATE,DB_TYPE_TIMESTAMP, and the{ converter }form ofFetchType.tests/integration/db/oracle-provider.test.ts: 9 tests. The same row is read underUTC,Europe/IstanbulandAmerica/Los_Angelesthrough a mockexecute()that applies the handler as the driver does. They cover the fraction, padding, BC year,NULL, declared types, the SQL INSERT export of the read,queryInTransaction(), and the two zoned types as unconverted controls.tests/unit/lib/export/result-export.test.ts: 6 tests for the text literals and the cases that must stay quoted.tests/live/oracle-zoneless-values.ts: the opt-in live guard above, modelled onmssql-zoneless-values.ts.docs/providers/oracle.md: §5.3 value table and a new subsection with the measurements, the §5.5 export table, §12 coverage and §12.4 live script, and the three driver-side losses in §14.Testing
11 of the 15 new tests fail on
mainand pass with the change; the other four are the controls (zoned types, declared column types, aVARCHAR2and malformed text staying quoted).oracle-provider.test.ts228 pass andresult-export.test.ts113 pass through the runner.format,lint(0 errors, no new warnings),typecheck,knip, the four drift guards,build,build:libandattwall pass.In the full
bun run teston Windows, the only failures are the node:sqlite driver test,launcher-utilsandpack-standalone-tarball. They fail the same way onmainon Windows.Test Environment
mainat 4b6aae8)gvenzl/oracle-free:slim), oracledb 6.10.0Checklist
bun run test:coverageandbun run coverage:check)src/lib/db/providers/, I updated the matchingdocs/providers/documentation andtests/integration/db/tests in the same PR (provider triad)Additional Notes
About the coverage box: on Windows,
test:coveragegoes red on the Windows-only failures above and so skips the merge. Merging the 694 per-file reports by hand and runningscripts/check-coverage.mjson the result gives 71725/71748 lines. The 23 uncovered lines aresrc/lib/db/providers/sql/sqlite.ts(the failing node:sqlite test) andsrc/instrumentation.ts32-36 (a POSIX-only test).oracle.tsandresult-export.tsare fully covered. On CI's Linux runner the gate passes:check-coverage: OK — 71748/71748 lines (100.00%)in the Unit & Integration Tests job.The separate
TIMESTAMP WITH TIME ZONEexport-over-HTTP finding is filed as #1224.