summaryrefslogtreecommitdiff
path: root/test/sql/test_cte.py
Commit message (Collapse)AuthorAgeFilesLines
* Fix recursive CTE to support nestingEric Masseran2021-10-121-19/+141
| | | | | | | | | | | | | | | | | | | | | | | | | | Repaired issue in new :paramref:`_sql.HasCTE.cte.nesting` parameter introduced with :ticket:`4123` where a recursive :class:`_sql.CTE` using :paramref:`_sql.HasCTE.cte.recursive` in typical conjunction with UNION would not compile correctly. Additionally makes some adjustments so that the :class:`_sql.CTE` construct creates a correct cache key. Pull request courtesy Eric Masseran. Fixes: #4123 > This has not been caught by the tests because the nesting recursive queries there did not union against itself, eg there was only the i root clause... - Now tests are real recursive queries - Add tests on aliased nested CTEs (recursive or not) - Adapt the `_restates` attribute to use it as a reference - Add some docs around to explain some variables usage Closes: #7133 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7133 Pull-request-sha: 2633f34f7f5336a4a85bd3f71d07bca33ce27a2c Change-Id: I15512c94e1bc1f52afc619d82057ca647d274e92
* fixes for usage of the null() and similar constantsMike Bayer2021-10-081-2/+2
| | | | | | | | | | | | | | | | | | | | | | | | Adjusted the "column disambiguation" logic that's new in 1.4, where the same expression repeated gets an "extra anonymous" label, so that the logic more aggressively deduplicates those labels when the repeated element is the same Python expression object each time, as occurs in cases like when using "singleton" values like :func:`_sql.null`. This is based on the observation that at least some databases (e.g. MySQL, but not SQLite) will raise an error if the same label is repeated inside of a subquery. Related to :ticket:`7153`, fixed an issue where result column lookups would fail for "adapted" SELECT statements that selected for "constant" value expressions most typically the NULL expression, as would occur in such places as joined eager loading in conjunction with limit/offset. This was overall a regression due to issue :ticket:`6259` which removed all "adaption" for constants like NULL, "true", and "false", but this broke the case where the same adaption logic were used to match the constant to a labeled expression referring to the constant in a subquery. Fixes: #7153 Fixes: #7154 Change-Id: I43823343721b9e70524ea3f5e8f39dd543a3e92b
* Implement nesting CTEEric Masseran2021-09-171-0/+446
| | | | | | | | | | | | | | | | | | Added new parameter :meth:`_sql.HasCte.cte.nesting` to the :class:`_sql.CTE` constructor and :meth:`_sql.HasCTE.cte` method, which flags the CTE as one which should remain nested within an enclosing CTE, rather than being moved to the top level of the outermost SELECT. While in the vast majority of cases there is no difference in SQL functionality, users have identified various edge-cases where true nesting of CTE constructs is desirable. Much thanks to Eric Masseran for lots of work on this intricate feature. Fixes: #4123 Closes: #6709 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/6709 Pull-request-sha: 64ab2f6ea269f2dcf37376a13ea38c48c5226fb6 Change-Id: Ic4dc25ab763af96d96632369e01527d48a654149
* use the stack for insert_from_selectMike Bayer2021-09-161-0/+23
| | | | | | | | | Fixed issue related to new ``add_cte()`` feature where pairing two "INSERT..FROM SELECT" statements simultaneously would lose track of the two independent SELECT statements, leading to the wrong SQL. Fixes: #7036 Change-Id: I90fe47eb203bc5c1ea5810db0edba08250b2b7e6
* Dispatch independent ctes on compound selectEric Masseran2021-08-081-0/+31
| | | | | | | | | | | | | | | Fix issue in :class:`_sql.CTE` where new :meth:`_sql.HasCTE.add_cte` method added in version 1.4.21 / :ticket:`6752` failed to function correctly for "compound select" structures such as :func:`_sql.union`, :func:`_sql.union_all`, :func:`_sql.except`, etc. Pull request courtesy Eric Masseran. Fixes: #6752 Closes: #6849 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/6849 Pull-request-sha: 1c4b4d72b2789cf89ff5043ca964ebdd6c9a6617 Change-Id: I49a16a4fc2af8299502011f3a02d8a2ad93255e3
* Modernize tests - dml_whereclauseGord Thompson2021-07-261-1/+1
| | | | | | | | | Fixed issue where the unit of work would internally use a 2.0-deprecated SQL expression form, emitting a deprecation warning when SQLALCHEMY_WARN_20 were enabled. Fixes: #6812 Change-Id: I0a031e728527a1c3382848b6ddc793939362b128
* Adjust CTE recrusive col list to accommodate dupe col namesMike Bayer2021-07-131-0/+148
| | | | | | | | | | | | | | | | | | | Fixed issue in CTE constructs where a recursive CTE that referred to a SELECT that has duplicate column names, which are typically deduplicated using labeling logic in 1.4, would fail to refer to the deduplicated label name correctly within the WITH clause. As part of this change we are also attempting to remove the behavior of SelectStatementGrouping forcing off the "asfrom" contextual flag, which will have the result of additional labeling being applied to some UNION and similar statements when they are interpreted as subqueries. To maintain compatibility with "grouping", the Grouping/SelectStatementGrouping are now broken out into two separate compiler cases, as the "asfrom" logic appears to be tailored towards table valued SELECTS as column expressions. Fixes: #6710 Change-Id: I8af07a5c670dbe5736cd9f16084ef82f5e4c8642
* implement independent CTEsMike Bayer2021-07-121-6/+146
| | | | | | | | | | | | | | Added new method :meth:`_sql.HasCTE.add_cte` to each of the :func:`_sql.select`, :func:`_sql.insert`, :func:`_sql.update` and :func:`_sql.delete` constructs. This method will add the given :class:`_sql.CTE` as an "independent" CTE of the statement, meaning it renders in the WITH clause above the statement unconditionally even if it is not otherwise referenced in the primary statement. This is a popular use case on the PostgreSQL database where a CTE is used for a DML statement that runs against database rows independently of the primary statement. Fixes: #6752 Change-Id: Ibf635763e40269cbd10f4c17e208850d8e8d0188
* Export deferred columns but not col props; fix CTE labelingMike Bayer2021-06-221-0/+51
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Refined the behavior of ORM subquery rendering with regards to deferred columns and column properties to be more compatible with that of 1.3 while also providing for 1.4's newer features. As a subquery in 1.4 does not make use of loader options, including :func:`_orm.deferred`, a subquery that is against an ORM entity with deferred attributes will now render those deferred attributes that refer directly to mapped table columns, as these are needed in the outer SELECT if that outer SELECT makes use of these columns; however a deferred attribute that refers to a composed SQL expression as we normally do with :func:`_orm.column_property` will not be part of the subquery, as these can be selected explicitly if needed in the subquery. If the entity is being SELECTed from this subquery, the column expression can still render on "the outside" in terms of the derived subquery columns. This produces essentially the same behavior as when working with 1.3. However in this case the fix has to also make sure that the ``.selected_columns`` collection of an ORM-enabled :func:`_sql.select` also follows these rules, which in particular allows recursive CTEs to render correctly in this scenario, which were previously failing to render correctly due to this issue. As part of this change the _exported_columns_iterator() method has been removed and logic simplified to use ._all_selected_columns from any SelectBase object where _exported_columns_iterator() was used before. Additionally sets up UpdateBase to include ReturnsRows in its hierarchy; the literal point of ReturnsRows was to be a common base for UpdateBase and SelectBase so it was kind of weird it wasn't there. Fixes: #6661 Fixed issue in CTE constructs mostly relevant to ORM use cases where a recursive CTE against "anonymous" labels such as those seen in ORM ``column_property()`` mappings would render in the ``WITH RECURSIVE xyz(...)`` section as their raw internal label and not a cleanly anonymized name. Fixes: #6663 Change-Id: I26219d4d8e6c0915b641426e9885540f74fae4d2
* Update black flak8 and zimportsFederico Caselli2021-05-121-2/+0
| | | | Change-Id: I488c9557eda390e4a88319affd4c8813ee274f80
* allow CTE to be direct DML targetMike Bayer2021-05-111-0/+73
| | | | | | | | | | | | | Implemented support for a :class:`_sql.CTE` construct to be used directly as the target of a :func:`_sql.delete` construct, i.e. "WITH ... AS cte DELETE FROM cte". This appears to be a useful feature of SQL Server. The CTE is now generally usable as a DML target table however it's not clear if this syntax is valid beyond the use case of DELETE itself. Fixes: #6464 Change-Id: I3aac6fae2a1abb39bc0ffa87a044f5eb4f90f026
* pass asfrom correctly in compilersMike Bayer2021-04-171-0/+66
| | | | | | | | | | | | Fixed an argument error in the default and PostgreSQL compilers that would interfere with an UPDATE..FROM or DELETE..FROM..USING statement that was then SELECTed from as a CTE. The incorrect pattern was also fixed in the mysql and sybase dialects. MySQL supports CTEs but not "returning". Fixes: #6303 Change-Id: Ic94805611a5ec443749fb6b1fd8a1326b0d83ef7
* Disallow AliasedReturnsRows from executionMike Bayer2021-04-051-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | Executing a :class:`_sql.Subquery` using :meth:`_engine.Connection.execute` is deprecated and will emit a deprecation warning; this use case was an oversight that should have been removed from 1.4. The operation will now execute the underlying :class:`_sql.Select` object directly for backwards compatibility. Similarly, the :class:`_sql.CTE` class is also not appropriate for execution. In 1.3, attempting to execute a CTE would result in an invalid "blank" SQL statement being executed; since this use case was not working it now raises :class:`_exc.ObjectNotExecutableError`. Previously, 1.4 was attempting to execute the CTE as a statement however it was working only erratically. The change also breaks out StatementRole from ReturnsRowsRole, as these roles should not be in the same lineage (some statements don't return rows, the whole class of ReturnsRows that are from clauses are not statements). Consolidate StatementRole and CoerceTextStatementRole as there's no usage difference between these. Simplify some old tests that were trying to make sure that "execution options" didn't transmit from a cte/subquery out to a select; as cte/subuqery() aren't executable in any case the options are removed. Fixes: #6204 Change-Id: I62613b7ab418afdd22c409eae75659e3f52fb65f
* Replace with_labels() and apply_labels() in ORM/CoreGord Thompson2021-01-261-30/+31
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | Replace :meth:`_orm.Query.with_labels` and :meth:`_sql.GenerativeSelect.apply_labels` with explicit getters and setters ``get_label_style`` and ``set_label_style`` to accommodate the three supported label styles: ``LABEL_STYLE_DISAMBIGUATE_ONLY`` (default), ``LABEL_STYLE_TABLENAME_PLUS_COL``, and ``LABEL_STYLE_NONE``. In addition, for Core and "future style" ORM queries, ``LABEL_STYLE_DISAMBIGUATE_ONLY`` is now the default label style. This style differs from the existing "no labels" style in that labeling is applied in the case of column name conflicts; with ``LABEL_STYLE_NONE``, a duplicate column name is not accessible via name in any case. For legacy ORM queries using :class:`_query.Query`, the table-plus-column names labeling style applied by ``LABEL_STYLE_TABLENAME_PLUS_COL`` continues to be used so that existing test suites and logging facilities see no change in behavior by default, however this style of labeling is no longer required for SQLAlchemy queries to function, as result sets are commonly matched to columns using a positional approach since SQLAlchemy 1.0. Within test suites, all use of apply_labels() / use_labels now uses the new methods. New tests added to test/sql/test_deprecations.py nad test/orm/test_deprecations.py to cover just the old apply_labels() method call. Tests in ORM that made explicit use apply_labels()/ etc. where it isn't needed for the ORM to work correctly use default label style now. Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com> Fixes: #4757 Change-Id: I5fdcd2ed4ae8c7fe62f8be2b6d0e8f66409b6a54
* upgrade to black 20.8b1Mike Bayer2020-09-281-5/+9
| | | | | | | It's better, the majority of these changes look more readable to me. also found some docstrings that had formatting / quoting issues. Change-Id: I582a45fde3a5648b2f36bab96bad56881321899b
* Update select usage to use the new 1.4 formatFederico Caselli2020-09-081-151/+121
| | | | | | | | | | | | | | | | This change includes mainly that the bracketed use within select() is moved to positional, and keyword arguments are removed from calls to the select() function. it does not yet fully address other issues such as keyword arguments passed to the table.select(). Additionally, allows False / None to both be considered as "disable" for all of select.correlate(), select.correlate_except(), query.correlate(), which establishes consistency with passing of ``False`` for the legact select(correlate=False) argument. Change-Id: Ie6c6e6abfbd3d75d4c8de504c0cf0159e6999108
* Restore crud flags if visiting_cte is setMike Bayer2020-03-031-0/+35
| | | | | | | | | | Fixed bug where a CTE of an INSERT/UPDATE/DELETE that also uses RETURNING could then not be SELECTed from directly, as the internal state of the compiler would try to treat the outer SELECT as a DELETE statement itself and access nonexistent state. Fixes: #5181 Change-Id: Icba76f2148c8344baa1c04bac4ab6c6d24f23072
* Source base cleanupsMike Bayer2020-01-011-2/+2
| | | | | | | | | | | | | | | in trying to apply 2020 copyright to files, the pre-commit hooks complain about random file issues. - remove old corrections.py utility, this had something to do with repairing refs in the sphinx docs - run pre commit hooks on all files - formatting adjustments to work around code formatting collisions (long import lines that zimports can't rewrite correctly) Change-Id: I260744866f69e902eb93665c7c728ee94d3371a2
* Add CTE prefixesMarat Sharafutdinov2019-12-181-0/+22
| | | | | | | | | | | | | Added support for prefixes to the :class:`.CTE` construct, to allow support for Postgresql 12 "MATERIALIZED" and "NOT MATERIALIZED" phrases. Pull request courtesy Marat Sharafutdinov. Fixes: #5040 Closes: #5043 Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/5043 Pull-request-sha: d1b9059a0b6dae8dc2479ac670999b4af07908e0 Change-Id: I2e9cb5d7f85961ec98ee51965de5b3ec4a97be2f
* Allow duplicate columns in from clauses and selectablesMike Bayer2019-07-111-2/+4
| | | | | | | | | | | | | | | | | | The :func:`.select` construct and related constructs now allow for duplication of column labels and columns themselves in the columns clause, mirroring exactly how column expressions were passed in. This allows the tuples returned by an executed result to match what was SELECTed for in the first place, which is how the ORM :class:`.Query` works, so this establishes better cross-compatibility between the two constructs. Additionally, it allows column-positioning-sensitive structures such as UNIONs (i.e. :class:`.CompoundSelect`) to be more intuitively constructed in those cases where a particular column might appear in more than one place. To support this change, the :class:`.ColumnCollection` has been revised to support duplicate columns as well as to allow integer index access. Fixes: #4753 Change-Id: Ie09a8116f05c367995c1e43623c51e07971d3bf0
* Implement new ClauseElement role and coercion systemMike Bayer2019-05-181-1/+3
| | | | | | | | | | | | | | | | | | | | A major refactoring of all the functions handle all detection of Core argument types as well as perform coercions into a new class hierarchy based on "roles", each of which identify a syntactical location within a SQL statement. In contrast to the ClauseElement hierarchy that identifies "what" each object is syntactically, the SQLRole hierarchy identifies the "where does it go" of each object syntactically. From this we define a consistent type checking and coercion system that establishes well defined behviors. This is a breakout of the patch that is reorganizing select() constructs to no longer be in the FromClause hierarchy. Also includes a rename of as_scalar() into scalar_subquery(); deprecates automatic coercion to scalar_subquery(). Partially-fixes: #4617 Change-Id: I26f1e78898693c6b99ef7ea2f4e7dfd0e8e1a1bd
* Prevent __init__ from being called for Alias, subclassesMike Bayer2019-02-211-0/+27
| | | | | | | | | | | The :class:`.Alias` class and related subclasses :class:`.CTE`, :class:`.Lateral` and :class:`.TableSample` have been reworked so that it is not possible for a user to construct the objects directly. These constructs require that the standalone construction function or selectable-bound method be used to instantiate new objects. Fixes: #4509 Change-Id: I74ae4786cb3ae625dab33b00bfd6bdc4e1219139
* Post black reformattingMike Bayer2019-01-061-13/+13
| | | | | | | | | | | | | Applied on top of a pure run of black -l 79 in I7eda77fed3d8e73df84b3651fd6cfcfe858d4dc9, this set of changes resolves all remaining flake8 conditions for those codes we have enabled in setup.cfg. Included are resolutions for all remaining flake8 issues including shadowed builtins, long lines, import order, unused imports, duplicate imports, and docstring issues. Change-Id: I4f72d3ba1380dd601610ff80b8fb06a2aff8b0fe
* Run black -l 79 against all source filesMike Bayer2019-01-061-479/+571
| | | | | | | | | | | | | | This is a straight reformat run using black as is, with no edits applied at all. The black run will format code consistently, however in some cases that are prevalent in SQLAlchemy code it produces too-long lines. The too-long lines will be resolved in the following commit that will resolve all remaining flake8 issues including shadowed builtins, long lines, import order, unused imports, duplicate imports, and docstring issues. Change-Id: I7eda77fed3d8e73df84b3651fd6cfcfe858d4dc9
* Track if we're rendering within the CTE recursivelyMike Bayer2018-03-141-0/+44
| | | | | | | | | | Fixed a regression that occurred from the previous fix to :ticket:`4204` in version 1.2.5, where a CTE that refers to itself after the :meth:`.CTE.alias` method has been called would not refer to iself correctly. Change-Id: Iaa63d65ad2b90c8693f9953fbb32dbb10c73a037 Fixes: #4204
* Clone _cte_alias instead of assigning "self"Mike Bayer2018-03-051-1/+70
| | | | | | | | | | Fixed bug in :class:.`CTE` construct along the same lines as that of :ticket:`4204` where a :class:`.CTE` that was aliased would not copy itself correctly during a "clone" operation as is frequent within the ORM as well as when using the :meth:`.ClauseElement.params` method. Change-Id: Id68d72dd244dedfc7bd6116c9a5123c51a55ea20 Fixes: #4210
* Check existing CTE for an alias name when rendering FROM clauseMike Bayer2018-03-011-0/+200
| | | | | | | | | | Fixed bug in CTE rendering where a :class:`.CTE` that was also turned into an :class:`.Alias` would not render its "ctename AS aliasname" clause appropriately if there were more than one reference to the CTE in a FROM clause. Change-Id: If8cff27a2f4faa5eceb59aa86398db6edb3b9e72 Fixes: #4204
* Quote cte alias if neededEric Atkin2018-02-221-1/+42
| | | | | | | | | | Fixed bug where CTE expressions would not have their name or alias name quoted when the given name is case sensitive or otherwise requires quoting. Pull request courtesy Eric Atkin. Fixes: #4197 Change-Id: Ib8573e82b9a1ca94b50c7c5d73ee98b79465d689 Pull-request: https://github.com/zzzeek/sqlalchemy/pull/426
* Make all tests to be PEP8 compliantKhairi Hafsham2017-02-071-2/+3
| | | | | | | | tested using pycodestyle version 2.2.0 Fixes: #3885 Change-Id: I5df43adc3aefe318f9eeab72a078247a548ec566 Pull-request: https://github.com/zzzeek/sqlalchemy/pull/343
* Propagate execution_options at compile stageMike Bayer2016-10-051-1/+8
| | | | | | | | Compiler can now set up execution options and additionally will propagate autocommit from embedded CTEs. Change-Id: I19db7b8fe4d84549ea95342e8d2040189fed1bbe Fixes: #3805
* Ensure DML provides named_with_column for CTE(Alias)Mike Bayer2016-07-131-0/+32
| | | | | | | | | | | | Fixed bug in new CTE feature for update/insert/delete whereby an anoymous (e.g. no name passed) :class:`.CTE` construct around the statement would fail. The Alias base class of CTE checks for the "named_with_column" attribute in order to detect if the underlying selectable has a name; UpdateBase now provides this as False. Change-Id: I4b0309db21379a4c0cb93085298c86da3cf840e4 Fixes: #3744
* - CTE functionality has been expanded to support all DML, allowingMike Bayer2016-02-111-2/+150
| | | | | | | INSERT, UPDATE, and DELETE statements to both specify their own WITH clause, as well as for these statements themselves to be CTE expressions when they include a RETURNING clause. fixes #2551
* PEP8 cleanup in /test/sqlEric Streeper2015-03-181-1/+1
|
* - Added support for CTEs under Oracle. This includes some tweaksMike Bayer2014-12-041-0/+30
| | | | | | | to the aliasing syntax, as well as a new CTE feature :meth:`.CTE.suffix_with`, which is useful for adding in special Oracle-specific directives to the CTE. fixes #3220
* - Fixed bug in CTE where ``literal_binds`` compiler argument would notMike Bayer2014-08-021-0/+38
| | | | | | be always be correctly propagated when one CTE referred to another aliased CTE in a statement. Fixes #3154
* - update the flake8 rules againMike Bayer2014-07-181-180/+172
| | | | - apply autopep8 + manual fixes to most of test/sql/
* -Fixed bug in common table expressions whereby positional boundMike Bayer2014-07-141-0/+36
| | | | | | parameters could be expressed in the wrong final order when CTEs were nested in certain ways. fixes #3090
* - reverse order of columns in sample CTEs as this is a UNION and the cols ↵Mike Bayer2014-04-021-5/+5
| | | | | | | | | | need to line up - alter this in the unit tests as well as these queries were just copied from the tests - remove the included_parts.join(parts) from the core CTE doc (also just copied from the test, where we want to make sure joins don't get screwed up with the CTE) as it doesn't contribute to the query itself fixes #3014
* Fixed regression dating back to 0.7.9 whereby the name of a CTE mightMike Bayer2013-08-181-0/+16
| | | | | not be properly quoted if it was referred to in multiple FROM clauses. Also in 0.8.3, 0.7.11. [ticket:2801]
* - Fixed bug in common table expression system where if the CTE wereMike Bayer2013-07-311-0/+29
| | | | | | used only as an ``alias()`` construct, it would not render using the WITH keyword. Also in 0.8.3, 0.7.11. [ticket:2783]
* trying different approaches to test layout. in this one, the testing modulesMike Bayer2012-09-271-2/+2
| | | | | | | become an externally usable package but still remains within the main sqlalchemy parent package. in this system, we use kind of an ugly hack to get the noseplugin imported outside of the "sqlalchemy" package, while still making it available within sqlalchemy for usage by third party libraries.
* - [feature] The before_cursor_execute eventMike Bayer2012-08-231-0/+1
| | | | | | | | | fires off for so-called "_cursor_execute" events, which are usually special-case executions of primary-key bound sequences and default-generation SQL phrases that invoke separately when RETURNING is not used with INSERT. [ticket:2459]
* - [bug] Fixed more un-intuitivenesses in CTEsMike Bayer2012-07-101-20/+159
| | | | | | | | | | | | | | | | which prevented referring to a CTE in a union of itself without it being aliased. CTEs now render uniquely on name, rendering the outermost CTE of a given name only - all other references are rendered just as the name. This even includes other CTE/SELECTs that refer to different versions of the same CTE object, such as a SELECT or a UNION ALL of that SELECT. We are somewhat loosening the usual link between object identity and lexical identity in this case. A true name conflict between two unrelated CTEs now raises an error.
* - move cte tests into their own test/sql/test_cte.pyMike Bayer2012-06-251-0/+213
- rework bindtemplate system of "numbered" params by applying the numbers last, as we now need to generate these out of order in some cases - add positional assertion to assert_compile - add new cte_positional collection to track bindparams generated within cte visits; splice this onto the beginning of self.positiontup at cte render time, [ticket:2521]
View Lorry Controller Status