summaryrefslogtreecommitdiff
path: root/lib/sqlalchemy/sql/compiler.py
Commit message (Collapse)AuthorAgeFilesLines
* happy new year 2023Mike Bayer2023-01-031-1/+1
| | | | Change-Id: I14db8e9c69a832b0f5dae8036db3c0a70bb49edd
* implement literal_binds with expanding + bind_expressionMike Bayer2022-12-151-9/+60
| | | | | | | | | | | | Fixed bug where SQL compilation would fail (assertion fail in 2.0, NoneType error in 1.4) when using an expression whose type included :meth:`_types.TypeEngine.bind_expression`, in the context of an "expanding" (i.e. "IN") parameter in conjunction with the ``literal_binds`` compiler parameter. Fixes: #8989 Change-Id: Ic9fd27b46381b488117295ea5a492d8fc158e39f (cherry picked from commit 8c6de3c2c43ab372cbbe76464b4c5be3b6457252)
* Fix positional compiling bugsFederico Caselli2022-12-011-20/+89
| | | | | | | | | | | | Fixed a series of issues regarding positionally rendered bound parameters, such as those used for SQLite, asyncpg, MySQL and others. Some compiled forms would not maintain the order of parameters correctly, such as the PostgreSQL ``regexp_replace()`` function as well as within the "nesting" feature of the :class:`.CTE` construct first introduced in :ticket:`4123`. Fixes: #8827 Change-Id: I9813ed7c358cc5c1e26725c48df546b209a442cb (cherry picked from commit 0f2baae6bf72353f785bad394684f2d6fa53e0ef)
* add informative exception context for literal renderMike Bayer2022-11-141-4/+25
| | | | | | | | | | | An informative re-raise is now thrown in the case where any "literal bindparam" render operation fails, indicating the value itself and the datatype in use, to assist in debugging when literal params are being rendered in a statement. Fixes: #8800 Change-Id: Id658f8b03359312353ddbb0c7563026239579f7b (cherry picked from commit c7baf6e0aa624c9378c3bc3c4923d1e188d62dc9)
* establish consistency for RETURNING column labelsMike Bayer2022-11-111-3/+7
| | | | | | | | | | | | | | | | | | | | | | For the PostgreSQL and SQL Server dialects only, adjusted the compiler so that when rendering column expressions in the RETURNING clause, the "non anon" label that's used in SELECT statements is suggested for SQL expression elements that generate a label; the primary example is a SQL function that may be emitting as part of the column's type, where the label name should match the column's name by default. This restores a not-well defined behavior that had changed in version 1.4.21 due to :ticket:`6718`, :ticket:`6710`. The Oracle dialect has a different RETURNING implementation and was not affected by this issue. Version 2.0 features an across the board change for its widely expanded support of RETURNING on other backends. Fixed issue in the Oracle dialect where an INSERT statement that used ``insert(some_table).values(...).returning(some_table)`` against a full :class:`.Table` object at once would fail to execute, raising an exception. Fixes: #8770 Change-Id: I2ab078a214a778ffe1720dbd864ae4c105a0691d (cherry picked from commit c8a7b67181d31634355150fc0379ec0e780ff728)
* break out text() from TextualSelect for col matchingMike Bayer2022-09-191-1/+15
| | | | | | | | | | | Fixed issue where mixing "*" with additional explicitly-named column expressions within the columns clause of a :func:`_sql.select` construct would cause result-column targeting to sometimes consider the label name or other non-repeated names to be an ambiguous target. Fixes: #8536 Change-Id: I3c845eaf571033e54c9208762344f67f4351ac3a (cherry picked from commit 78327d98be9236c61f950526470f29b184dabba6)
* deep compare CTEs before considering them conflictingMike Bayer2022-08-051-4/+13
| | | | | | | | | | | | Fixed issue where referencing a CTE multiple times in conjunction with a polymorphic SELECT could result in multiple "clones" of the same CTE being constructed, which would then trigger these two CTEs as duplicates. To resolve, the two CTEs are deep-compared when this occurs to ensure that they are equivalent, then are treated as equivalent. Fixes: #8357 Change-Id: I1f634a9cf7a6c4256912aac1a00506aecea3b0e2 (cherry picked from commit 85fa363c846f4ed287565c43c32e2cca29470e25)
* use concat() directly for contains, startswith, endswithMike Bayer2022-07-171-6/+6
| | | | | | | | | | | | | | | | Adjusted the SQL compilation for string containment functions ``.contains()``, ``.startswith()``, ``.endswith()`` to force the use of the string concatenation operator, rather than relying upon the overload of the addition operator, so that non-standard use of these operators with for example bytestrings still produces string concatenation operators. To accommodate this, needed to add a new _rconcat operator function, which is private, as well as a fallback in concat_op() that works similarly to Python builtin ops. Fixes: #8253 Change-Id: I2b7f56492f765742d88cb2a7834ded6a2892bd7e (cherry picked from commit 85a88df13ab8d217331cf98392544a888b4d7df3)
* generalize sql server check for id col to accommodate ORM casesMike Bayer2022-07-051-0/+18
| | | | | | | | | | | | | | | Fixed issues that prevented the new usage patterns for using DML with ORM objects presented at :ref:`orm_dml_returning_objects` from working correctly with the SQL Server pyodbc dialect. Here we add a step to look in compile_state._dict_values more thoroughly for the keys we need to determine "identity insert" or not, and also add a new compiler variable dml_compile_state so that we can skip the ORM's compile_state if present. Fixes: #8210 Change-Id: Idbd76bb3eb075c647dc6c1cb78f7315c821e15f7 (cherry picked from commit 5806428800d2f1ac775156f90497a2fc3a644f35)
* restore parameter escaping for public methodsMike Bayer2022-06-091-9/+28
| | | | | | | | | | | | | | | Adjusted the fix made for :ticket:`8056` which adjusted the escaping of bound parameter names with special characters such that the escaped names were translated after the SQL compilation step, which broke a published recipe on the FAQ illustrating how to merge parameter names into the string output of a compiled SQL string. The change restores the escaped names that come from ``compiled.params`` and adds a conditional parameter to :meth:`.SQLCompiler.construct_params` named ``escape_names`` that defaults to ``True``, restoring the old behavior by default. Fixes: #8113 Change-Id: I9cbedb1080bc06d51f287fd2cbf26aaab1c74653 (cherry picked from commit 105cd180856309cf5abf24f59b782a1bcd8210d6)
* move bindparam quote application from compiler to defaultMike Bayer2022-05-291-36/+17
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | in 296c84313ab29bf9599634f3 for #5653 we generalized Oracle's parameter escaping feature into the compiler, so that it could also work for PostgreSQL. The compiler used quoted names within parameter dictionaries, which then led to the complexity that all functions which interpreted keys from the compiled_params dict had to also quote the param names to use the dictionary. This extra complexity was not added to the ORM peristence.py however, which led to the versioning id feature being broken as well as other areas where persistence.py relies on naming schemes present in context.compiled_params. It also was not added to the "processors" lookup which led to #8053, that added this escaping to that part of the compiler. To both solve the whole problem as well as simplify the compiler quite a bit, move the actual application of the escaped names to be as late as possible, when default.py builds the final list of parameters. This is more similar to how it worked previously where OracleExecutionContext would be late-applying these escaped names. This re-establishes context.compiled_params as deterministically named regardless of dialect in use and moves out the complexity of the quoted param names to be only at the cursor.execute stage. Fixed bug, likely a regression from 1.3, where usage of column names that require bound parameter escaping, more concretely when using Oracle with column names that require quoting such as those that start with an underscore, or in less common cases with some PostgreSQL drivers when using column names that contain percent signs, would cause the ORM versioning feature to not work correctly if the versioning column itself had such a name, as the ORM assumes certain bound parameter naming conventions that were being interfered with via the quotes. This issue is related to :ticket:`8053` and essentially revises the approach towards fixing this, revising the original issue :ticket:`5653` that created the initial implementation for generalized bound-parameter name quoting. Fixes: #8056 Change-Id: I57b064e8f0d070e328b65789c30076f6a0ca0fef (cherry picked from commit a48b597d0cafa1dd7fc46be99eb808fd4cb0a347)
* apply bindparam escape name to processors dictionaryMike Bayer2022-05-251-1/+9
| | | | | | | | | | | | | | Fixed SQL compiler issue where the "bind processing" function for a bound parameter would not be correctly applied to a bound value if the bound parameter's name were "escaped". Concretely, this applies, among other cases, to Oracle when a :class:`.Column` has a name that itself requires quoting, such that the quoting-required name is then used for the bound parameters generated within DML statements, and the datatype in use requires bind processing, such as the :class:`.Enum` datatype. Fixes: #8053 Change-Id: I39d060a87e240b4ebcfccaa9c535e971b7255d99 (cherry picked from commit 4d58ca05e83048e999059a8c2c2e67cb77abf976)
* raise for same param name in expanding + non expandingMike Bayer2022-05-151-0/+9
| | | | | | | | | | | | | | An informative error is raised if two individual :class:`.BindParameter` objects share the same name, yet one is used within an "expanding" context (typically an IN expression) and the other is not; mixing the same name in these two different styles of usage is not supported and typically the ``expanding=True`` parameter should be set on the parameters that are to receive list values outside of IN expressions (where ``expanding`` is set by default). Fixes: #8018 Change-Id: Ie707f29680eea16b9e421af93560ac1958e11a54 (cherry picked from commit f9fccdeeb6749d10aeec458f1a549906d58ddad8)
* allow executemany values for ON CONFLICT DO NOTHINGMike Bayer2022-03-311-8/+1
| | | | | | | | | | | | | | Scaled back a fix made for :ticket:`6581` where "executemany values" mode for psycopg2 were disabled for all "ON CONFLICT" styles of INSERT, to not apply to the "ON CONFLICT DO NOTHING" clause, which does not include any parameters and is safe for "executemany values" mode. "ON CONFLICT DO UPDATE" is still blocked from "executemany values" as there may be additional parameters in the DO UPDATE clause that cannot be batched (which is the original issue fixed by :ticket:`6581`). Fixes: #7880 Change-Id: Id3e23a0c6699333409a50148fa8923cb8e564bdc (cherry picked from commit cc8c5835a92b0035530b541c81b0c714b570b095)
* Add option to disable from linting for table valued functionMike Bayer2022-03-231-0/+2
| | | | | | | | | | | | | | Added new parameter :paramref:`.FunctionElement.table_valued.joins_implicitly`, for the :meth:`.FunctionElement.table_valued` construct. This parameter indicates that the given table-valued function implicitly joins to the table it refers towards, essentially disabling the "from linting" feature, i.e. the "cartesian product" warning, from taking effect due to the presence of this parameter. May be used for functions such as ``func.json_each()``. Fixes: #7845 Change-Id: I80edcb74efbd4417172132c0db4d9c756fdd5eae (cherry picked from commit 04dcc5c704dbf0b22705523e263e512c24936175)
* pop the stack that we pushedMike Bayer2022-03-081-0/+2
| | | | | | | | | | | | Fixed regression caused by :ticket:`7760` where the new capabilities of :class:`.TextualSelect` were not fully implemented within the compiler properly, leading to issues with composed INSERT constructs such as "INSERT FROM SELECT" and "INSERT...ON CONFLICT" when combined with CTE and textual statements. Fixes: #7798 Change-Id: Ia2ce92507e574dd36fd26dd38ec9dd2713584467 (cherry picked from commit c36965ab211183764357456fff1640418586ed97)
* support add_cte() for TextualSelectMike Bayer2022-02-231-1/+17
| | | | | | | | | | | | Fixed issue where the :meth:`.HasCTE.add_cte` method as called upon a :class:`.TextualSelect` instance was not being accommodated by the SQL compiler. The fix additionally adds more "SELECT"-like compiler behavior to :class:`.TextualSelect` including that DML CTEs such as UPDATE and INSERT may be accommodated. Fixes: #7760 Change-Id: Id97062d882e9b2a81b8e31c2bfaa9cfc5f77d5c1 (cherry picked from commit bef67e58121704a9836e1e5ec2d361cd2086036c)
* Accommodate escaped_bind_names for defaults/insert paramsMike Bayer2022-02-081-4/+26
| | | | | | | | | | | Fixed issue in Oracle dialect where using a column name that requires quoting when written as a bound parameter, such as ``"_id"``, would not correctly track a Python generated default value due to the bound-parameter rewriting missing this value, causing an Oracle error to be raised. Fixes: #7676 Change-Id: I5a54426d24f2f9b336e3597d5595fb3e031aad97 (cherry picked from commit c2aa6374f3965c28aa2d56cbddf6dab3e1de18a2)
* happy new year 2022Mike Bayer2022-01-061-1/+1
| | | | Change-Id: Ic38dbc640aa0fe8a784a5b5e57c45a41eb0ea01b
* Merge "set within_columns_clause=False for all sub-elements of select()" ↵mike bayer2021-11-091-0/+3
|\ | | | | | | into rel_1_4
| * set within_columns_clause=False for all sub-elements of select()Mike Bayer2021-11-091-0/+3
| | | | | | | | | | | | | | | | | | | | | | | | Fixed issue where using the feature of using a string label for ordering or grouping described at :ref:`tutorial_order_by_label` would fail to function correctly if used on a :class:`.CTE` construct, when the CTE were embedded inside of an enclosing :class:`_sql.Select` statement that itself was set up as a scalar subquery. Fixes: #7269 Change-Id: Ied6048a1c9a622374a418230c8cfedafa8d3f87e (cherry picked from commit 89661c1a218b7117c1835698dbb81836e72015ae)
* | change the POSTCOMPILE/ SCHEMA symbols to not conflict w mssql quotingMike Bayer2021-11-091-6/+6
|/ | | | | | | | | | | | | | | | | | | Adjusted the compiler's generation of "post compile" symbols including those used for "expanding IN" as well as for the "schema translate map" to not be based directly on plain bracketed strings with underscores, as this conflicts directly with SQL Server's quoting format of also using brackets, which produces false matches when the compiler replaces "post compile" and "schema translate" symbols. The issue created easy to reproduce examples both with the :meth:`.Inspector.get_schema_names` method when used in conjunction with the :paramref:`_engine.Connection.execution_options.schema_translate_map` feature, as well in the unlikely case that a symbol overlapping with the internal name "POSTCOMPILE" would be used with a feature like "expanding in". Fixes: #7300 Change-Id: I6255c850b140522a4aba95085216d0bca18ce230 (cherry picked from commit b919a0a85afd5066f9188b20ef06ee1b4af884a9)
* use tuple expansion if type._is_tuple, test for Sequence if no typeMike Bayer2021-11-051-6/+15
| | | | | | | | | | | | | | | | | | | | Fixed regression where the row objects returned for ORM queries, which are now the normal :class:`_sql.Row` objects, would not be interpreted by the :meth:`_sql.ColumnOperators.in_` operator as tuple values to be broken out into individual bound parameters, and would instead pass them as single values to the driver leading to failures. The change to the "expanding IN" system now accommodates for the expression already being of type :class:`.TupleType` and treats values accordingly if so. In the uncommon case of using "tuple-in" with an untyped statement such as a textual statement with no typing information, a tuple value is detected for values that implement ``collections.abc.Sequence``, but that are not ``str`` or ``bytes``, as always when testing for ``Sequence``. Added :class:`.TupleType` to the top level ``sqlalchemy`` import namespace. Fixes: #7292 Change-Id: I8286387e3b3c3752b3bd4ae3560d4f31172acc22 (cherry picked from commit 0c44a1e77cfde0f841a4a64140314c6b833efdab)
* Revise "literal parameters" FAQ sectionMike Bayer2021-11-011-1/+23
| | | | | | | | | | | based on feedback in #7271, the emphasis on TypeDecorator as a solution to this problem is not very practical. illustrate a series of quick recipes that are useful for debugging purposes to print out a repr() or simple stringify of a parameter without the need to construct custom dialects or types. Change-Id: I788ce1b5ea01d88dd0a22d03d06f35aabff5e5c8 (cherry picked from commit b2a28c556f10ee31605c978173f0cce62175ad61)
* support bind expressions w/ expanding IN; apply to psycopg2Mike Bayer2021-10-151-30/+76
| | | | | | | | | | | | | | | | | | | | | | | Fixed issue where "expanding IN" would fail to function correctly with datatypes that use the :meth:`_types.TypeEngine.bind_expression` method, where the method would need to be applied to each element of the IN expression rather than the overall IN expression itself. Fixed issue where IN expressions against a series of array elements, as can be done with PostgreSQL, would fail to function correctly due to multiple issues within the "expanding IN" feature of SQLAlchemy Core that was standardized in version 1.4. The psycopg2 dialect now makes use of the :meth:`_types.TypeEngine.bind_expression` method with :class:`_types.ARRAY` to portably apply the correct casts to elements. The asyncpg dialect was not affected by this issue as it applies bind-level casts at the driver level rather than at the compiler level. as part of this commit the "bind translate" feature has been simplified and also applies to the names in the POSTCOMPILE tag to accommodate for brackets. Fixes: #7177 Change-Id: I08c703adb0a9bd6f5aeee5de3ff6f03cccdccdc5
* Fix recursive CTE to support nestingEric Masseran2021-10-121-34/+58
| | | | | | | | | | | | | | | | | | | | | | | | | | 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
* Implement nesting CTEEric Masseran2021-09-171-24/+105
| | | | | | | | | | | | | | | | | | 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-1/+4
| | | | | | | | | 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
* Fix various lib / test / examples typos (#7017)Kevin Kirsche2021-09-111-1/+1
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | * fix: lib/sqlalchemy/sql/lambdas.py * fix: lib/sqlalchemy/sql/compiler.py * fix: lib/sqlalchemy/sql/selectable.py * fix: lib/sqlalchemy/orm/relationships.py * fix: lib/sqlalchemy/dialects/mssql/base.py * fix: lib/sql/test_compiler.py * fix: lib/sqlalchemy/testing/requirements.py * fix: lib/sqlalchemy/orm/path_registry.py * fix: lib/sqlalchemy/dialects/postgresql/psycopg2.py * fix: lib/sqlalchemy/cextension/immutabledict.c * fix: lib/sqlalchemy/cextension/resultproxy.c * fix: ./lib/sqlalchemy/dialects/oracle/cx_oracle.py * fix: examples/versioned_rows/versioned_rows_w_versionid.py * fix: examples/elementtree/optimized_al.py * fix: test/orm/test_attribute.py * fix: test/sql/test_compare.py * fix: test/sql/test_type_expression.py * fix: capitalization in test/dialect/mysql/test_compiler.py * fix: typos in test/dialect/postgresql/test_reflection.py * fix: typo in tox.ini comment * fix: typo in /lib/sqlalchemy/orm/decl_api.py * fix: typo in test/orm/test_update_delete.py * fix: self-induced typo * fix: typo in test/orm/test_query.py * fix: typos in test/dialect/mssql/test_types.py * fix: typo in test/sql/test_types.py
* fix linter JOIN logic; fix PostgreSQL ARRAY op comparisonMike Bayer2021-08-151-1/+5
| | | | | | | | | | | | | | | | | | | Adjusted the "from linter" warning feature to accommodate for a chain of joins more than one level deep where the ON clauses don't explicitly match up the targets, such as an expression such as "ON TRUE". This mode of use is intended to cancel the cartesian product warning simply by the fact that there's a JOIN from "a to b", which was not working for the case where the chain of joins had more than one element. this incurs a bit more compiler overhead that comes out in profiling but is not extensive. Added the "is_comparison" flag to the PostgreSQL "overlaps", "contained_by", "contains" operators, so that they work in relevant ORM contexts as well as in conjunction with the "from linter" feature. Fixes: #6886 Change-Id: I078dc3fe6d4f7871ffe4ebac3e71e62f3f213d12
* Merge "Dispatch independent ctes on compound select"mike bayer2021-08-081-0/+6
|\
| * Dispatch independent ctes on compound selectEric Masseran2021-08-081-0/+6
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 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
* | dont qualify literal_binds with literal_executeMike Bayer2021-08-071-1/+1
|/ | | | | | | | | | | | | | | this appears to be unnecessary and prevents end-user literal_binds case from working. Fixed issue where the ``literal_binds`` compiler flag, as used externally to render bound parameters inline, would fail to work when used with a certain class of parameters known as "literal_execute", which covers things like LIMIT and OFFSET values for dialects where the drivers don't allow a bound parameter, such as SQL Server's "TOP" clause. The issue locally seemed to affect only the MSSQL dialect. Fixes: #6863 Change-Id: Ia74cff5b0107b129a11b9b965883552b2962e449
* accommodate for cloned bindparams w/ maintain_keyMike Bayer2021-07-291-2/+8
| | | | | | | | | | | Fixed issue where a bound parameter object that was "cloned" would cause a name conflict in the compiler, if more than one clone of this parameter were used at the same time in a single statement. This could occur in particular with things like ORM single table inheritance queries that indicated the same "discriminator" value multiple times in one query. Fixes: #6824 Change-Id: Iba7a786fc5a2341ff7d07fc666d24ed790ad4fe8
* Ensure alias traversal block works when adapt_from_selectables presentMike Bayer2021-07-141-1/+0
| | | | | | | | | | | | Fixed regression which appeared in version 1.4.3 due to :ticket:`6060` where rules that limit ORM adaptation of derived selectables interfered with other ORM-adaptation based cases, in this case when applying adaptations for a :func:`_orm.with_polymorphic` against a mapping which uses a :func:`_orm.column_property` which in turn makes use of a scalar select that includes a :func:`_orm.aliased` object of the mapped table. Fixes: #6762 Change-Id: Ice3dc34b97d12b59f044bdc0c5faaefcc4015227
* Merge "Adjust CTE recrusive col list to accommodate dupe col names"mike bayer2021-07-131-6/+35
|\
| * Adjust CTE recrusive col list to accommodate dupe col namesMike Bayer2021-07-131-6/+35
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 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
* | Merge "Extract format_constraint truncation rules to ON CONFLICT"mike bayer2021-07-131-13/+34
|\ \ | |/ |/|
| * Extract format_constraint truncation rules to ON CONFLICTMike Bayer2021-07-131-13/+34
| | | | | | | | | | | | | | | | | | | | | | Fixed issue where a too-long constraint name rendered as part of the "ON CONFLICT ON CONSTRAINT" element of the :class:`_postgresql.Insert` construct due to naming convention generation would not correctly truncate the name in the same way that it normally renders within a CREATE TABLE statement, thus producing a non-matching and too-long constraint name. Fixes: #6755 Change-Id: Ib27014a5ecbc9cd5861a396f8bb49fbc60bf49fe
* | Merge "labeling refactor"mike bayer2021-07-131-48/+128
|\ \
| * | labeling refactorMike Bayer2021-07-121-48/+128
| |/ | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | To service #6718 and #6710, the system by which columns are given labels in a SELECT statement as well as the system that gives them keys in a .c or .selected_columns collection have been refactored to provide a single source of truth for both, in constrast to the previous approach that included similar logic repeated in slightly different ways. Main ideas: 1. ColumnElement attributes ._label, ._anon_label, ._key_label are renamed to include the letters "tq", meaning "table-qualified" - these labels are only used when rendering a SELECT that has LABEL_STYLE_TABLENAME_PLUS_COL for its label style; as this label style is primarily legacy, the "tq" names should be isolated so that in a 2.0 style application these aren't being used at all 2. The means by which the "labels" and "proxy keys" for the elements of a SELECT has been centralized to a single source of truth; previously, the three of _generate_columns_plus_names, _generate_fromclause_column_proxies, and _column_naming_convention all had duplicated rules between them, as well as that there were a little bit of labeling rules in compiler._label_select_column as well; by this we mean that the various "anon_label" "anon_key" methods on ColumnElement were called by all four of these methods, where there were many cases where it was necessary that one method comes up with the same answer as another of the methods. This has all been centralized into _generate_columns_plus_names for all the names except the "proxy key", which is generated by _column_naming_convention. 3. compiler._label_select_column has been rewritten to both not make any naming decisions nor any "proxy key" decisions, only whether to label or not to label; the _generate_columns_plus_names method gives it the information, where the proxy keys come from _column_naming_convention; previously, these proxy keys were matched based on restatement of similar (but not really the same) logic in two places. The heuristics of "whether to label or not to label" are also reorganized to be much easier to read and understand. 4. a new method compiler._label_returning_column is added for dialects to use in their "generate returning columns" methods. A github search reveals a small number of third party dialects also doing this using the prior _label_select_column method so we try to make sure _label_select_column continues to work the exact same way for that specific use case; for the "SELECT" use case it now needs 5. After some attempts to do it different ways, for the case where _proxy_key is giving us some kind of anon label, we are hard changing it to "_no_label" right now, as there's not currently a way to fully match anonymized labels from stmt.c or stmt.selected_columns to what will be in the result map. The idea of "_no_label" is to encourage the user to use label('name') for columns they want to be able to target by string name that don't have a natural name. Change-Id: I7a92a66f3a7e459ccf32587ac0a3c306650daf11
* | Merge "implement independent CTEs"mike bayer2021-07-121-2/+17
|\ \
| * | implement independent CTEsMike Bayer2021-07-121-2/+17
| |/ | | | | | | | | | | | | | | | | | | | | | | | | | | 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
* | repair schema_translate_map for schema type use casesMike Bayer2021-07-111-0/+1
|/ | | | | | | | | | | | | Fixed issue where the PostgreSQL ``ENUM`` datatype as embedded in the ``ARRAY`` datatype would fail to emit correctly in create/drop when the ``schema_translate_map`` feature were also in use. Additionally repairs a related issue where the same ``schema_translate_map`` feature would not work for the ``ENUM`` datatype in combination with a ``CAST``, that's also intrinsic to how the ``ARRAY(ENUM)`` combination works on the PostgreSQL dialect. Fixes: #6739 Change-Id: I44b1ad4db4af3acbf639aa422c46c22dd3b0d3a6
* Replace all http:// links to https://Federico Caselli2021-07-041-1/+1
| | | | | | Also replace http://pypi.python.org/pypi with https://pypi.org/project Change-Id: I84b5005c39969a82140706472989f2a30b0c7685
* Ensure compiler uses quote_schema hook for translates rendersMike Bayer2021-06-301-1/+1
| | | | | | | | | Fixed regression where the special dotted-schema name handling for the SQL Server dialect would not function correctly if the dotted schema name were used within the ``schema_translate_map`` feature. Fixes: #6697 Change-Id: Idb610755cbf8122e71223d5dd0a17fcb61b1b98d
* consider "*" col as textual orderedMike Bayer2021-06-231-1/+1
| | | | | | | | | Fixed old issue where a :func:`_sql.select()` made against the token "*", which then yielded exactly one column, would fail to correctly organize the ``cursor.description`` column name into the keys of the result object. Fixes: #6665 Change-Id: Ie8c00f62998972ad4a19a750d2642d00fde006f6
* Export deferred columns but not col props; fix CTE labelingMike Bayer2021-06-221-3/+12
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | 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
* don't enable "fast insert executemany" for ON CONFLICT etcMike Bayer2021-06-041-1/+8
| | | | | | | | | | | | | Fixed issue where using the PostgreSQL "INSERT..ON CONFLICT" structure would fail to work with the psycopg2 driver if it were used in an "executemany" context along with bound parameters in the "SET" clause, due to the implicit use of the psycopg2 fast execution helpers which are not appropriate for this style of INSERT statement. Additional checks to exclude this kind of statement from that particular extension have been added. Fixes: #6581 Change-Id: I3d6169e7e188dc087d1d1bfba9a42162db183265
* set bindparam.expanding in coercion againMike Bayer2021-05-101-20/+2
| | | | | | | | | | | | | | | | | Adjusted the logic added as part of :ticket:`6397` in 1.4.12 so that internal mutation of the :class:`.BindParameter` object occurs within the clause construction phase as it did before, rather than in the compilation phase. In the latter case, the mutation still produced side effects against the incoming construct and additionally could potentially interfere with other internal mutation routines. In order to solve the issue of the correct operator being present on the BindParameter.expand_op, we necessarily have to expand the BinaryExpression._negate() routine to flip the operator on the BindParameter also. Fixes: #6460 Change-Id: I1e53a9aeee4de4fc11af51d7593431532731561b
View Lorry Controller Status