This implements `DB_Column.with`, which uses `WITH ... AS` SQL clauses to remove duplicates in the generated SQL.
After a discussion with @radeusgd, we concluded that we will probably want a more complete CTE implementation, so this one is useful for now to deal with big queries (like `round`).
# Important Notes
Still to do in this PR:
- [x] Rename `with` to `let` (or something similar)
- [x] tests
- [x] documentation
- [x] remove `State` hack by moving query generation into a class and using a `Ref` field for scoping
Results on `round_float`:
| --- | SQL length in characters (unprettified) | SQL length in lines (prettified) |
| --- | --- | --- |
| Without CTEs | 13193 | 851 |
| With CTEs | 3644 | 187 |
Compare the SQL:
[without-ctes.sql.txt](https://github.com/user-attachments/files/16629356/without-ctes.sql.txt)
[with-ctes.sql.txt](https://github.com/user-attachments/files/16629357/with-ctes.sql.txt)
Update, with name shortening:
| --- | SQL length in characters (unprettified) | SQL length in lines (prettified) |
| --- | --- | --- |
| Without CTEs | 13193 | 853 |
| With CTEs | 2427 | 176 |
[without-cte.txt](https://github.com/user-attachments/files/16694328/without-cte.txt)
[with-cte.txt](https://github.com/user-attachments/files/16694327/with-cte.txt)
- Closes#10411
- Also a similar technique allowed to add partial support for SQLite as well.
- Only `include_nothing=True` case is still not supported as the technique used for Snowflake did not port well to SQLite and I did not find yet a satisfactory workaround (it is doable, but too ugly to justify it for now IMHO).
Fixes#10609 by rewriting all our upload-related operations to rely on `DDL_Transaction` - an abstraction that handles 'transactionality' of `CREATE TABLE` statements dependent on if a given backend allows DDLs within transactions or not (if not it emulates transactionality by creating the tables outside of transaction and then dropping them on rollback).
- Closes#9486
- All tests are succeeding or marked pending
- Created follow up tickets for things that still need to be addressed, including:
- Fixing upload / table update #10609
- Fixing `Count_Distinct` on Boolean columns #10611
- Running the tests on CI is not part of this PR - to be addressed separately
- Part of #9486
- Fixes `Table.union`, `merge` and `distinct` tests
- Replaces `distinct_on` in `Context` that was actually a Postgres specific addition leaking into the base with a more abstract `Context_Extension` mechanism.
- This allows us to implement the Snowflake-specific `DISTINCT` using `QUALIFY`.
- Related to #9486
- Batching of expression tests
- Fixing arithmetic by simplifying `%` and `/` operations
- Trying to share some more tables, sometimes improving performance sometimes not really
- Adding sorting and other fixes to tests to make them pass: Missing_Values_Spec, Filter_Spec, Map_Spec
- Fixing warnings related to materialization of Decimal->Integer, thus fixing Join_Spec.
- Related to #9486
- Ensures that even though an integer column in Snowflake is represented by `Decimal` type, if the values are small enough, they are materialized as `Integer`.
- If the values are larger, they are still read in as `Decimal`.
- Adds tests for some other `Decimal` edge cases (various precisions and scales), and for `Float`.
- Related to #9486
- Fixes types in literal tables that are used throughout the tests
- Tries to makes testing faster by disabling some edge cases, trying batching some queries, re-using the main connection and trying to re-use tables more
- Implements date/time type mapping and operations for Snowflake
- Updates type mapping to correctly reflect what Snowflake does
- Disables warnings for Integer->Decimal coercion as that's too annoying and implicitly understood in Snowflake
- Allows to select a Decimal column with `..By_Type ..Integer` (only in Snowflake backend) because the Decimal column there is its 'de-facto' Integer column replacement.
- Removes `First` and `Last` from the `Standard.Base` exports.
- Enable auto-scoping for all `Index_Sub_Range` and `Text_Sub_Range`.
- Update all use of those methods to use auto-scoping.
- Part of #9486
- Fixing our tests to not rely on deterministic ordering of created Tables in Database backends
- Before, SQLite and Postgres used to mostly return rows in the order they were inserted in, but Snowflake does not.
- Fixing various parts of Snowflake dialect.
- Part of #9486
- Building on top of initial work by @jdunkerley and finishing it
- Reverted the changes to the Postgres_Dialect from last Snowflake work and split the Snowflake_Dialect into a separate module.
- Moved from `rounding_decimal_places_not_allowed_for_floats` to `supports_float_round_decimal_places` (as too confusing).
- Added Snowflake_Dialect type.
- Extracted `Snowflake_Spec` into separate `Snowflake_Tests`
- It imports the common tests from `Table_Tests`.
- Some initial adaptations to make the snowflake dialect not-crash.
- Adding `Internals_Access` proxy to allow external implementations to access our internal data structures without directly exposing them to users. Users should not use these.
- Adding profiling of SQL to check performance.
