Casting text to a timestamp empties a moment the engine reads

[kz930]

What happened?

Type Casting to a timestamp reads text with pd.to_datetime(out1df[col], errors="coerce") in the exported script. pandas parses into nanoseconds, which reach only 1677-09-21 to 2262-04-11, so every moment outside that window becomes an empty cell. The engine reads the same text with DateParserUtils into a java.sql.Timestamp, where those moments are ordinary ones.

Checked on main, with the pandas the product pins (2.2.3):

text	engine	exported script
2500-01-01 00:00:00	2500-01-01 00:00:00.0	empty
1500-06-15 08:30:00	1500-06-15 08:30:00.0	empty
9999-12-31 23:59:59	9999-12-31 23:59:59.0	empty

The coercion is deliberate and should stay: the engine accepts a set of formats no single pandas call states, so text it cannot read is answered with an empty cell rather than by ending the run. What is wrong is that a moment pandas reads perfectly well is answered the same way, because of where the nanosecond window falls. The fix is a parse that keeps coercing what it cannot read while reaching the years the engine reaches. Reading the column at microsecond resolution is how #8480 closed the same gap for Extract Date/Time Fields, but that operator parses strictly, so its one-line form does not carry over here: astype("datetime64[us]") raises on text it cannot read.

A number column has the same window. A LONG is read as epoch milliseconds through _texera_epoch_millis_to_timestamp, which coerces too, so 20000000000000 is empty in the script and 2603-10-11 04:33:20.0 in the engine. That branch carries a second disagreement a range fix alone would expose rather than close: new Timestamp(long) renders through the hybrid Julian/Gregorian calendar, where pandas is proleptic Gregorian, so -20000000000000 is 1336-03-15 to the engine and 1336-03-23 to pandas. Before 1582-10-15 a widened parse would answer with a wrong moment where today it answers with an empty cell, so what that branch should do is worth deciding on its own rather than alongside the text one.

How to reproduce?

Build a workflow of CSV File Scan over a column holding 2500-01-01 00:00:00 as text, then Type Casting on that column to timestamp. Run it and the cell reads 2500-01-01 00:00:00.0. Export the same workflow as Python and run the script, and the cell is empty.

Without the engine, on main at 2ab8ee0:

• AttributeTypeUtils.parseTimestamp("2500-01-01 00:00:00") returns 2500-01-01 00:00:00.0, and the same for the other two rows of the table above.
• pd.to_datetime(pd.Series(["2500-01-01 00:00:00"]), errors="coerce") returns NaT.

Version/Branch

1.4.0-incubating-SNAPSHOT (main)

Commit Hash (Optional)

2ab8ee0

Relevant log output

>>> pd.to_datetime(pd.Series(["2500-01-01 00:00:00", "1500-06-15 08:30:00", "9999-12-31 23:59:59"]), errors="coerce")
0   NaT
1   NaT
2   NaT
dtype: datetime64[ns]