fix(spark): parse month, day without leading zeros

[deepyaman]

Following up on #7739 (comment)

Regarding testing each dialect, I ended up testing Ibis backends, which largely supports the earlier conclusion that Spark 3+ (and Databricks) were the issues:

Verdict	Backends
✅ Works	DuckDB, Polars, BigQuery, MySQL, PostgreSQL, Trino, SingleStoreDB, Oracle, RisingWave
❌ Returns NULL silently	Impala, PySpark, Databricks
❌ Returns wrong date	Flink
— Not implemented	ClickHouse, SQLite, DataFusion, MS SQL Server, Druid, Exasol
— Not available	Materialize

Impala and Flink don't have SQLGlot dialect (although Ibis extends the Hive dialect for Impala), and Flink issues specifically run deeper. I can try to spend some time testing the backends Ibis doesn't implement this functionality for separately, if helpful.

Note: I did use Claude specifically to implement the additions in this PR, as well as to test across Ibis backends, but I have reviewed the code/implementation and think it makes sense.

[geooo109]

@deepyaman the solution seems more complicated as expected. We should only change the TIME_MAPPING and the INVERSE_TIME_MAPPING.

We should add in spark's TIME_MAPPING the "MM": "%mstrict", "dd": "%dstrict" + the INVERSE as we already do. Then, for the tests that fail in the case of spark as input we should add in the INVERSE_TIME_MAPPING for the output dialect the corresponding mapping for %mstrict and %dstrict.

[deepyaman]

@deepyaman the solution seems more complicated as expected. We should only change the TIME_MAPPING and the INVERSE_TIME_MAPPING.

We should add in spark's TIME_MAPPING the "MM": "%mstrict", "dd": "%dstrict" + the INVERSE as we already do. Then, for the tests that fail in the case of spark as input we should add in the INVERSE_TIME_MAPPING for the output dialect the corresponding mapping for %mstrict and %dstrict.

I think this is quite similar to what I had initially. The problem is that things break in the format direction:

>>> import sqlglot
>>> sqlglot.transpile("SELECT STRPTIME(x,'%Y-%m-%d')", read="duckdb", write="spark")
["SELECT TO_TIMESTAMP(x, 'yyyy-M-d')"]  # good
>>> sqlglot.transpile("SELECT DATE_FORMAT(x,'yyyy-MM-dd')", read="hive", write="spark")
["SELECT DATE_FORMAT(x, 'yyyy-M-d')"]  # bad; should be 'yyyy-MM-dd'

The added complexity is needed in order to support both parse and format directions; reimplementing the simplification, get 17 test failures (all in the format direction).

FYI what I tried for the "simple" approach is to update sqlglot/dialects/spark.py to remove STRICT_TIME_MAPPING, LENIENT_INVERSE_TIME_MAPPING and just do:

    TIME_MAPPING = {
         **Spark2.TIME_MAPPING,
         "MM": "%mstrict",
         "dd": "%dstrict",
     }

    INVERSE_TIME_MAPPING = {
         "%m": "M",
         "%d": "d",
     }

Not sure if you had something different in mind.

[georgesittas]

>>> sqlglot.transpile("SELECT DATE_FORMAT(x,'yyyy-MM-dd')", read="hive", write="spark")
["SELECT DATE_FORMAT(x, 'yyyy-M-d')"]  # bad; should be 'yyyy-MM-dd'

If Hive follows what Spark does, shouldn't your TIME_MAPPING and INVERSE_TIME_MAPPING changes go to Hive? This looks expected to me if you only change Spark.

[geooo109]

@deepyaman yeah, I thought we have focused on spark only my bad. Hive as I mentioned is strict so we should move there the logic (you can test it also locally to verify like the rest of the dialects). So, the simple map approach I mentioned should work, without any additions.

[deepyaman]

>>> sqlglot.transpile("SELECT DATE_FORMAT(x,'yyyy-MM-dd')", read="hive", write="spark")
["SELECT DATE_FORMAT(x, 'yyyy-M-d')"]  # bad; should be 'yyyy-MM-dd'

If Hive follows what Spark does, shouldn't your TIME_MAPPING and INVERSE_TIME_MAPPING changes go to Hive? This looks expected to me if you only change Spark.

@georgesittas @geooo109 Yes, sorry, I missed that Hive was strict, since I haven't been testing anything directly using Hive (well, I guess Impala). I'm happy to update the mappings there.

Spark2 is still lenient, so I'll need to re-override that, and then back again for Spark (strict by default, unless you specify spark.sql.legacy.timeParserPolicy).

However, this can be illustrated using another dialect (most of the failing tests if take the "simple" fix aren't the mistaken Hive ones). For instance:

>>> import sqlglot
>>> sqlglot.transpile("SELECT STRPTIME(x, '%Y-%m-%d')", read="duckdb", write="spark")
['yyyy-M-d' ]  # good
>>> sqlglot.transpile("SELECT STRFTIME(x, '%Y-%m-%d')", read="duckdb", write="spark")
['yyyy-M-d']  # bad; should be 'yyyy-MM-dd'

[georgesittas]

I'm not sure I understand: why is M bad when going from duckdb to spark, given that the former is lenient? Also, @deepyaman, feel free to join our Slack to iterate on this faster. I think it'll be easier that way.

[deepyaman]

I'm not sure I understand: why is M bad when going from duckdb to spark, given that the former is lenient?

%m for formatting should map to MM, whereas %-m would map to M.

Also, @deepyaman, feel free to join our Slack to iterate on this faster. I think it'll be easier that way.

Will do!

[georgesittas]

Ok, in any case, the mechanism is quite clear: TIME_MAPPING tries to map dialect-specific formats to a canonical representation (think of it as a "format IR") that captures their semantics and then INVERSE_TIME_MAPPING is responsible for mapping that representation into another (or the same) dialect's formats.

By convention, the formats we use for TIME_MAPPING's values are compatible with Python's strftime spec, because these are common and easily understood, but if dialects expose more format variants that aren't covered by said spec, we need to create our own, like %mstrict.

I believe this context should be enough to handle this issue. It's simply a matter of finding the right representation for a given dialect format. If tests break, I believe that means that they're either incorrect or the chosen format is incorrect.

[deepyaman]

Ok, in any case, the mechanism is quite clear: TIME_MAPPING tries to map dialect-specific formats to a canonical representation (think of it as a "format IR") that captures their semantics and then INVERSE_TIME_MAPPING is responsible for mapping that representation into another (or the same) dialect's formats.

By convention, the formats we use for TIME_MAPPING's values are compatible with Python's strftime spec, because these are common and easily understood, but if dialects expose more format variants that aren't covered by said spec, we need to create our own, like %mstrict.

I believe this context should be enough to handle this issue. It's simply a matter of finding the right representation for a given dialect format. If tests break, I believe that means that they're either incorrect or the chosen format is incorrect.

The reason for introducing the STRICT_TIME_MAPPING and LENIENT_INVERSE_TIME_MAPPING is that the existing time mappings (TIME_MAPPING, INVERSE_TIME_MAPPING) are just used for capturing from/to dialect, not parse/format role. The specific code paths:

	read (dialect → canonical)	write (canonical → dialect)
parse role (StrToTime…)	STRICT_TIME_MAPPING	LENIENT_INVERSE_TIME_MAPPING
format role (TimeToStr…)	TIME_MAPPING	INVERSE_TIME_MAPPING

DuckDB %m has to become M when parsing and MM when formatting, so I don't think one mapping covers that. If you mean that the role piece could be solved by adding unique tokens for each role (e.g. %mparselenient, %mparsestrict, %mformatpadded, %mformatunpadded), but it would require adding adding support for that to each dialect.

Let me also follow up on Slack, if it helps clarify any misunderstandings I may have!

[deepyaman]

If you mean that the role piece could be solved by adding unique tokens for each role (e.g. %mparselenient, %mparsestrict, %mformatpadded, %mformatunpadded), but it would require adding adding support for that to each dialect.

Here's a Claude-generated prototype of what that could look like: https://github.com/deepyaman/sqlglot/tree/four-token-prototype

[georgesittas]

Ok, I get it now, thanks for clarifying. I wasn't thinking about the differences between strptime and strftime. This is somewhat annoying because it complicates things.

I need to think more about this. Probably going to circle around to this PR in a few days.