Hello community,
here is the log from the commit of package python-SQLAlchemy.14848 for
openSUSE:Leap:15.1:Update checked in at 2020-11-05 12:27:20
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Comparing /work/SRC/openSUSE:Leap:15.1:Update/python-SQLAlchemy.14848 (Old)
and
/work/SRC/openSUSE:Leap:15.1:Update/.python-SQLAlchemy.14848.new.11331 (New)
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Package is "python-SQLAlchemy.14848"
Thu Nov 5 12:27:20 2020 rev:1 rq:845406 version:1.2.14
Changes:
--------
New Changes file:
--- /dev/null 2020-10-22 01:51:33.322291705 +0200
+++
/work/SRC/openSUSE:Leap:15.1:Update/.python-SQLAlchemy.14848.new.11331/python-SQLAlchemy.changes
2020-11-05 12:27:21.474136057 +0100
@@ -0,0 +1,1782 @@
+-------------------------------------------------------------------
+Thu Oct 15 12:35:15 UTC 2020 - Johannes Grassler <[email protected]>
+
+- Add 0002-maintain-compiled_params-replacement_expressions.patch
+ (bsc#1176953)
+ * Maintain compiled_params / replacement_expressions within
+ expanding IN
+
+-------------------------------------------------------------------
+Tue Aug 20 11:25:21 UTC 2019 - Ralf Haferkamp <[email protected]>
+
+- Add 0001-Illustrate-fix-for-4481-in-terms-of-a-1.2-patch.patch
+ (bsc#1124593, CVE-2019-7164, CVE-2019-7548)
+
+-------------------------------------------------------------------
+Mon Nov 12 06:16:33 UTC 2018 - Arun Persaud <[email protected]>
+
+- update to version 1.2.14:
+ * orm
+ + [orm] [bug] Fixed bug in Session.bulk_update_mappings() where
+ alternate mapped attribute names would result in the primary key
+ column of the UPDATE statement being included in the SET clause,
+ as well as the WHERE clause; while usually harmless, for SQL
+ Server this can raise an error due to the IDENTITY column. This
+ is a continuation of the same bug that was fixed in #3849, where
+ testing was insufficient to catch this additional flaw.
+ References: #4357
+ + [orm] [bug] Fixed a minor performance issue which could in some
+ cases add unnecessary overhead to result fetching, involving the
+ use of ORM columns and entities that include those same columns
+ at the same time within a query. The issue has to do with hash /
+ eq overhead when referring to the column in different ways.
+ References: #4347
+ * mysql
+ + [mysql] [bug] Fixed regression caused by #4344 released in
+ 1.2.13, where the fix for MySQL 8.0’s case sensitivity problem
+ with referenced column names when reflecting foreign key
+ referents is worked around using the information_schema.columns
+ view. The workaround was failing on OSX /
+ lower_case_table_names=2 which produces non-matching casing for
+ the information_schema.columns vs. that of SHOW CREATE TABLE, so
+ in case-insensitive SQL modes case-insensitive matching is now
+ used. References: #4361
+
+-------------------------------------------------------------------
+Thu Nov 1 22:48:27 UTC 2018 - Arun Persaud <[email protected]>
+
+- update to version 1.2.13:
+ * orm
+ + [orm] [bug] Fixed bug where “dynamic” loader needs to explicitly
+ set the “secondary” table in the FROM clause of the query, to
+ suit the case where the secondary is a join object that is
+ otherwise not pulled into the query from its columns alone.
+ References: #4349
+ * orm declarative
+ + [bug] [declarative] [orm] Fixed regression caused by #4326 in
+ version 1.2.12 where using declared_attr with a mixin in
+ conjunction with orm.synonym() would fail to map the synonym
+ properly to an inherited subclass. References: #4350
+ + [bug] [declarative] [orm] The column conflict resolution
+ technique discussed at Resolving Column Conflicts is now
+ functional for a Column that is also a primary key
+ column. Previously, a check for primary key columns declared on
+ a single-inheritance subclass would occur before the column copy
+ were allowed to pass. References: #4352
+ * sql
+ + [sql] [feature] Refactored SQLCompiler to expose a
+ SQLCompiler.group_by_clause() method similar to the
+ SQLCompiler.order_by_clause() and SQLCompiler.limit_clause()
+ methods, which can be overridden by dialects to customize how
+ GROUP BY renders. Pull request courtesy Samuel Chou.
+ + [sql] [bug] Fixed bug where the Enum.create_constraint flag on
+ the Enum datatype would not be propagated to copies of the type,
+ which affects use cases such as declarative mixins and abstract
+ bases. References: #4341
+ * postgresql
+ + [postgresql] [bug] Added support for the aggregate_order_by
+ function to receive multiple ORDER BY elements, previously only
+ a single element was accepted. References: #4337
+ * mysql
+ + [mysql] [bug] Added word function to the list of reserved words
+ for MySQL, which is now a keyword in MySQL 8.0 References: #4348
+ + [mysql] [bug] Added a workaround for a MySQL bug #88718
+ introduced in the 8.0 series, where the reflection of a foreign
+ key constraint is not reporting the correct case sensitivity for
+ the referred column, leading to errors during use of the
+ reflected constraint such as when using the automap
+ extension. The workaround emits an additional query to the
+ information_schema tables in order to retrieve the correct case
+ sensitive name. References: #4344
+ * misc
+ + [misc] [bug] Fixed issue where part of the utility language
+ helper internals was passing the wrong kind of argument to the
+ Python __import__ builtin as the list of modules to be
+ imported. The issue produced no symptoms within the core library
+ but could cause issues with external applications that redefine
+ the __import__ builtin or otherwise instrument it. Pull request
+ courtesy Joe Urciuoli.
+ + [misc] [bug] [py3k] Fixed additional warnings generated by
+ Python 3.7 due to changes in the organization of the Python
+ collections and collections.abc packages. Previous collections
+ warnings were fixed in version 1.2.11. Pull request courtesy
+ xtreak. References: #4339
+ + [bug] [ext] Added missing .index() method to list-based
+ association collections in the association proxy extension.
+
+-------------------------------------------------------------------
+Sat Sep 22 05:28:52 UTC 2018 - Arun Persaud <[email protected]>
+
+- update to version 1.2.12:
+ * orm
+ + [orm] [bug] Added a check within the weakref cleanup for the
+ InstanceState object to check for the presence of the dict
+ builtin, in an effort to reduce error messages generated when
+ these cleanups occur during interpreter shutdown. Pull request
+ courtesy Romuald Brunet.
+ + [orm] [bug] Fixed bug where use of Lateral construct in
+ conjunction with Query.join() as well as
+ Query.select_entity_from() would not apply clause adaption to
+ the right side of the join. “lateral” introduces the use case of
+ the right side of a join being correlatable. Previously,
+ adaptation of this clause wasn’t considered. Note that in 1.2
+ only, a selectable introduced by Query.subquery() is still not
+ adapted due to #4304; the selectable needs to be produced by the
+ select() function to be the right side of the “lateral” join.
+ References: #4334
+ + [orm] [bug] Fixed 1.2 regression caused by #3472 where the
+ handling of an “updated_at” style column within the context of a
+ post-update operation would also occur for a row that is to be
+ deleted following the update, meaning both that a column with a
+ Python-side value generator would show the now-deleted value
+ that was emitted for the UPDATE before the DELETE (which was not
+ the previous behavor), as well as that a SQL- emitted value
+ generator would have the attribute expired, meaning the previous
+ value would be unreachable due to the row having been deleted
+ and the object detached from the session.The “postfetch” logic
+ that was added as part of #3472 is now skipped entirely for an
+ object that ultimately is to be deleted. References: #4327
+ * orm declarative
+ + [bug] [declarative] [orm] Fixed bug where the declarative scan
+ for attributes would receive the expression proxy delivered by a
+ hybrid attribute at the class level, and not the hybrid
+ attribute itself, when receiving the descriptor via the
+ @declared_attr callable on a subclass of an already-mapped
+ class. This would lead to an attribute that did not report
+ itself as a hybrid when viewed within
+ Mapper.all_orm_descriptors. References: #4326
+ * postgresql
+ + [postgresql] [bug] Fixed bug in PostgreSQL dialect where
+ compiler keyword arguments such as literal_binds=True were not
+ being propagated to a DISTINCT ON expression. References: #4325
+ + [postgresql] [bug] Fixed the postgresql.array_agg() function,
+ which is a slightly altered version of the usual
+ functions.array_agg() function, to also accept an incoming
+ “type” argument without forcing an ARRAY around it, essentially
+ the same thing that was fixed for the generic function in 1.1 in
+ #4107. References: #4324
+ + [postgresql] [bug] Fixed bug in PostgreSQL ENUM reflection where
+ a case-sensitive, quoted name would be reported by the query
+ including quotes, which would not match a target column during
+ table reflection as the quotes needed to be stripped off.
+ References: #4323
+ * oracle
+ + [oracle] [bug] Fixed issue for cx_Oracle 7.0 where the behavior
+ of Oracle param.getvalue() now returns a list, rather than a
+ single scalar value, breaking autoincrement logic throughout the
+ Core and ORM. The dml_ret_array_val compatibility flag is used
+ for cx_Oracle 6.3 and 6.4 to establish compatible behavior with
+ 7.0 and forward, for cx_Oracle 6.2.1 and prior a version number
+ check falls back to the old logic. References: #4335
+ * misc
+ + [bug] [ext] Fixed issue where BakedQuery did not include the
+ specific query class used by the Session as part of the cache
+ key, leading to incompatibilities when using custom query
+ classes, in particular the ShardedQuery which has some different
+ argument signatures. References: #4328
+
+-------------------------------------------------------------------
+Sat Aug 25 18:12:02 UTC 2018 - [email protected]
+
+- update to version 1.2.11:
+ * orm declarative
+ + [bug] [declarative] [orm] Fixed issue in previously untested use
+ case, allowing a declarative mapped class to inherit from a
+ classically-mapped class outside of the declarative base,
+ including that it accommodates for unmapped intermediate
+ classes. An unmapped intermediate class may specify
+ __abstract__, which is now interpreted correctly, or the
+ intermediate class can remain unmarked, and the classically
+ mapped base class will be detected within the hierarchy
+ regardless. In order to anticipate existing scenarios which may
+ be mixing in classical mappings into existing declarative
+ hierarchies, an error is now raised if multiple mapped bases are
+ detected for a given class. References: #4321
+ * sql
+ + [sql] [bug] Fixed issue that is closely related to #3639 where
+ an expression rendered in a boolean context on a non-native
++++ 1585 more lines (skipped)
++++ between /dev/null
++++ and
/work/SRC/openSUSE:Leap:15.1:Update/.python-SQLAlchemy.14848.new.11331/python-SQLAlchemy.changes
New:
----
0001-Illustrate-fix-for-4481-in-terms-of-a-1.2-patch.patch
0002-maintain-compiled_params-replacement_expressions.patch
SQLAlchemy-1.2.14.tar.gz
python-SQLAlchemy.changes
python-SQLAlchemy.spec
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Other differences:
------------------
++++++ python-SQLAlchemy.spec ++++++
#
# spec file for package python-SQLAlchemy
#
# Copyright (c) 2018 SUSE LINUX GmbH, Nuernberg, Germany.
#
# All modifications and additions to the file contributed by third parties
# remain the property of their copyright owners, unless otherwise agreed
# upon. The license for this file, and modifications and additions to the
# file, is the same license as for the pristine package itself (unless the
# license for the pristine package is not an Open Source License, in which
# case the license is the MIT License). An "Open Source License" is a
# license that conforms to the Open Source Definition (Version 1.9)
# published by the Open Source Initiative.
# Please submit bugfixes or comments via https://bugs.opensuse.org/
#
%{?!python_module:%define python_module() python-%{**} python3-%{**}}
%define oldpython python
%bcond_without tests
Name: python-SQLAlchemy
Version: 1.2.14
Release: 0
Summary: Database Abstraction Library
License: MIT
Group: Development/Languages/Python
URL: http://www.sqlalchemy.org
Source:
https://files.pythonhosted.org/packages/source/S/SQLAlchemy/SQLAlchemy-%{version}.tar.gz
Patch1: 0001-Illustrate-fix-for-4481-in-terms-of-a-1.2-patch.patch
# PATCH-FIX-UPSTREAM
0002-maintain-compiled_params-replacement_expressions.patch bsc#1176953
Patch2: 0002-maintain-compiled_params-replacement_expressions.patch
BuildRequires: %{python_module devel}
# Test requirements:
BuildRequires: %{python_module mock}
BuildRequires: %{python_module pytest-xdist}
BuildRequires: %{python_module pytest}
BuildRequires: %{python_module setuptools}
BuildRequires: fdupes
BuildRequires: python-rpm-macros
#BuildRequires: python-pysqlite
%ifpython2
Obsoletes: %{oldpython}-sqlalchemy < %{version}
Provides: %{oldpython}-sqlalchemy = %{version}
%endif
%ifpython3
Provides: python3-sqlalchemy = %{version}
Obsoletes: python3-sqlalchemy < %{version}
%endif
%python_subpackages
%description
SQLAlchemy is an Object Relational Mappper (ORM) that provides a flexible,
high-level interface to SQL databases. Database and domain concepts are
decoupled, allowing both sides maximum flexibility and power. SQLAlchemy
provides a powerful mapping layer that can work as automatically or as manually
as you choose, determining relationships based on foreign keys or letting you
define the join conditions explicitly, to bridge the gap between database and
domain.
%package -n %{name}-doc
Summary: Documentation for python-SQLAlchemy
Group: Documentation/Other
Provides: python-SQLAlchemy-doc = %{version}
Provides: python2-SQLAlchemy-doc = %{version}
Provides: python3-SQLAlchemy-doc = %{version}
BuildArch: noarch
%description -n %{name}-doc
This package contains HTML documentation, including tutorials and API
reference for python-SQLAlchemy.
%prep
%setup -q -n SQLAlchemy-%{version}
%patch1 -p1
%patch2 -p1
rm -rf doc/build # Remove unnecessary scripts for building documentation
sed -i 's/\r$//' examples/dynamic_dict/dynamic_dict.py
%build
export CFLAGS="%{optflags} -fno-strict-aliasing"
%python_build
%install
%python_install
%python_expand %fdupes %{buildroot}%{$python_sitearch}
%if %{with tests}
%check
%python_expand py.test-%{$python_bin_suffix}
%endif
%files %{python_files}
%license LICENSE
%doc CHANGES README.rst README.dialects.rst README.unittests.rst
%{python_sitearch}/sqlalchemy/
%{python_sitearch}/SQLAlchemy-%{version}-py%{py_ver}.egg-info
%files -n %{name}-doc
%doc doc/
%doc examples/
%changelog
++++++ 0001-Illustrate-fix-for-4481-in-terms-of-a-1.2-patch.patch ++++++
>From 6d2d5eb63fcfba97efa86fc765bdf840b106e553 Mon Sep 17 00:00:00 2001
From: Mike Bayer <[email protected]>
Date: Mon, 8 Apr 2019 22:07:35 -0400
Subject: [PATCH] Illustrate fix for #4481 in terms of a 1.2 patch
Release 1.2 has decided (so far) not to backport 1.3's fix for #4481 as it is
backwards-incompatible with code that relied upon the feature of automatic text
coercion in SQL statements. However, for the specific case of order_by() and
group_by(), we present a patch that backports the specific change in compiler
to have 1.3's behavior for order_by/group_by specifically. This is much more
targeted than the 0.9 version of the patch as it takes advantage 1.0's
architecture which runs all order_by() / group_by() through a label lookup that
only warns if the label can't be matched.
For an example of an application that was actually impacted by 1.3's change
and how they had to change it, see:
https://github.com/ctxis/CAPE/commit/be0482294f5eb30026fe97a967ee5a768d032278
Basically, in the uncommon case an application is actually using the text
coercion feature which was generally little-known, within the order_by()
and group_by() an error is now raised instead of a warning; the application
must instead ensure the SQL fragment is passed within a text() construct.
The above application has also been seeing a warning about this since 1.0
which apparently remained unattended.
The patch includes adjustments to the tests that were testing for the
warning to now test that an exception is raised. Any distro that wants
to patch the specific CVE issue resolved in #4481 to SQLAlchemy 1.0, 1.1
or 1.2 can use this patch.
Change-Id: I3363b21428f1ad8797394b63197375a2e56a0bd7
References: #4481
---
lib/sqlalchemy/sql/compiler.py | 10 ++--
lib/sqlalchemy/sql/elements.py | 11 ++++
test/orm/test_eager_relations.py | 17 ++----
test/orm/test_query.py | 99 +++++++++++++-------------------
test/sql/test_text.py | 56 +++++++-----------
5 files changed, 81 insertions(+), 112 deletions(-)
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index e27a736ef..467e89371 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -637,12 +637,10 @@ class SQLCompiler(Compiled):
else:
col = with_cols[element.element]
except KeyError:
- # treat it like text()
- util.warn_limited(
- "Can't resolve label reference %r; converting to text()",
- util.ellipses_string(element.element))
- return self.process(
- element._text_clause
+ elements._no_text_coercion(
+ element.element,
+ exc.CompileError,
+ "Can't resolve label reference for ORDER BY / GROUP BY.",
)
else:
kwargs['render_label_as_label'] = col
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 7b827d130..03dda6376 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -4306,6 +4306,17 @@ def _literal_as_text(element, warn=False):
)
+def _no_text_coercion(element, exc_cls=exc.ArgumentError, extra=None):
+ raise exc_cls(
+ "%(extra)sTextual SQL expression %(expr)r should be "
+ "explicitly declared as text(%(expr)r)"
+ % {
+ "expr": util.ellipses_string(element),
+ "extra": "%s " % extra if extra else "",
+ }
+ )
+
+
def _no_literals(element):
if hasattr(element, '__clause_element__'):
return element.__clause_element__()
diff --git a/test/orm/test_eager_relations.py b/test/orm/test_eager_relations.py
index 3c669d90d..704e11313 100644
--- a/test/orm/test_eager_relations.py
+++ b/test/orm/test_eager_relations.py
@@ -14,7 +14,7 @@ from sqlalchemy.orm import mapper, relationship,
create_session, \
from sqlalchemy.sql import operators
from sqlalchemy.testing import assert_raises, assert_raises_message
from sqlalchemy.testing.assertsql import CompiledSQL
-from sqlalchemy.testing import fixtures, expect_warnings
+from sqlalchemy.testing import fixtures
from test.orm import _fixtures
from sqlalchemy.util import OrderedDict as odict
import datetime
@@ -246,16 +246,11 @@ class EagerTest(_fixtures.FixtureTest,
testing.AssertsCompiledSQL):
q = sess.query(User).options(joinedload("addresses")).\
order_by("email_address")
- with expect_warnings("Can't resolve label reference 'email_address'"):
- self.assert_compile(
- q,
- "SELECT users.id AS users_id, users.name AS users_name, "
- "addresses_1.id AS addresses_1_id, addresses_1.user_id AS "
- "addresses_1_user_id, addresses_1.email_address AS "
- "addresses_1_email_address FROM users LEFT OUTER JOIN "
- "addresses AS addresses_1 ON users.id = addresses_1.user_id "
- "ORDER BY email_address"
- )
+ assert_raises_message(
+ sa.exc.CompileError,
+ "Can't resolve label reference for ORDER BY / GROUP BY.",
+ q.all,
+ )
def test_deferred_fk_col(self):
users, Dingaling, User, dingalings, Address, addresses = (
diff --git a/test/orm/test_query.py b/test/orm/test_query.py
index 880497501..4f4935890 100644
--- a/test/orm/test_query.py
+++ b/test/orm/test_query.py
@@ -15,7 +15,7 @@ import sqlalchemy as sa
from sqlalchemy.testing.assertions import (
eq_, assert_raises, assert_raises_message, expect_warnings,
eq_ignore_whitespace)
-from sqlalchemy.testing import fixtures, AssertsCompiledSQL, assert_warnings
+from sqlalchemy.testing import fixtures, AssertsCompiledSQL
from test.orm import _fixtures
from sqlalchemy.orm.util import join, with_parent
import contextlib
@@ -1885,18 +1885,11 @@ class ColumnPropertyTest(_fixtures.FixtureTest,
AssertsCompiledSQL):
ua = aliased(User)
q = s.query(ua).order_by("email_ad")
- def go():
- self.assert_compile(
- q,
- "SELECT (SELECT max(addresses.email_address) AS max_1 "
- "FROM addresses WHERE addresses.user_id = users_1.id) "
- "AS anon_1, users_1.id AS users_1_id, "
- "users_1.name AS users_1_name FROM users AS users_1 "
- "ORDER BY email_ad"
- )
- assert_warnings(
- go,
- ["Can't resolve label reference 'email_ad'"], regex=True)
+ assert_raises_message(
+ sa.exc.CompileError,
+ "Can't resolve label reference for ORDER BY / GROUP BY",
+ q.with_labels().statement.compile,
+ )
def test_order_by_column_labeled_prop_attr_aliased_one(self):
User = self.classes.User
@@ -3498,43 +3491,33 @@ class TextTest(QueryTest, AssertsCompiledSQL):
# the queries here are again "invalid" from a SQL perspective, as the
# "name" field isn't matched up to anything.
#
- with expect_warnings("Can't resolve label reference 'name';"):
- self.assert_compile(
- s.query(User).options(joinedload("addresses")).
- order_by(desc("name")).limit(1),
- "SELECT anon_1.users_id AS anon_1_users_id, "
- "anon_1.users_name AS anon_1_users_name, "
- "addresses_1.id AS addresses_1_id, "
- "addresses_1.user_id AS addresses_1_user_id, "
- "addresses_1.email_address AS addresses_1_email_address "
- "FROM (SELECT users.id AS users_id, users.name AS users_name "
- "FROM users ORDER BY users.name "
- "DESC LIMIT :param_1) AS anon_1 "
- "LEFT OUTER JOIN addresses AS addresses_1 "
- "ON anon_1.users_id = addresses_1.user_id "
- "ORDER BY name DESC, addresses_1.id"
- )
+ q = (
+ s.query(User)
+ .options(joinedload("addresses"))
+ .order_by(desc("name"))
+ .limit(1)
+ )
+ assert_raises_message(
+ sa_exc.CompileError,
+ "Can't resolve label reference for ORDER BY / GROUP BY.",
+ q.with_labels().statement.compile,
+ )
def test_order_by_w_eager_two(self):
User = self.classes.User
s = create_session()
- with expect_warnings("Can't resolve label reference 'name';"):
- self.assert_compile(
- s.query(User).options(joinedload("addresses")).
- order_by("name").limit(1),
- "SELECT anon_1.users_id AS anon_1_users_id, "
- "anon_1.users_name AS anon_1_users_name, "
- "addresses_1.id AS addresses_1_id, "
- "addresses_1.user_id AS addresses_1_user_id, "
- "addresses_1.email_address AS addresses_1_email_address "
- "FROM (SELECT users.id AS users_id, users.name AS users_name "
- "FROM users ORDER BY users.name "
- "LIMIT :param_1) AS anon_1 "
- "LEFT OUTER JOIN addresses AS addresses_1 "
- "ON anon_1.users_id = addresses_1.user_id "
- "ORDER BY name, addresses_1.id"
- )
+ q = (
+ s.query(User)
+ .options(joinedload("addresses"))
+ .order_by("name")
+ .limit(1)
+ )
+ assert_raises_message(
+ sa_exc.CompileError,
+ "Can't resolve label reference for ORDER BY / GROUP BY.",
+ q.with_labels().statement.compile,
+ )
def test_order_by_w_eager_three(self):
User = self.classes.User
@@ -3604,20 +3587,18 @@ class TextTest(QueryTest, AssertsCompiledSQL):
q = sess.query(User, Address.email_address.label('email_address'))
- result = q.join('addresses').options(joinedload(User.orders)).\
- order_by(
- "email_address desc").limit(1).offset(0)
- with expect_warnings(
- "Can't resolve label reference 'email_address desc'"):
- eq_(
- [
- (User(
- id=7,
- orders=[Order(id=1), Order(id=3), Order(id=5)],
- addresses=[Address(id=1)]
- ), '[email protected]')
- ],
- result.all())
+ result = (
+ q.join("addresses")
+ .options(joinedload(User.orders))
+ .order_by("email_address desc")
+ .limit(1)
+ .offset(0)
+ )
+ assert_raises_message(
+ sa_exc.CompileError,
+ "Can't resolve label reference for ORDER BY / GROUP BY",
+ result.all,
+ )
class TextWarningTest(QueryTest, AssertsCompiledSQL):
diff --git a/test/sql/test_text.py b/test/sql/test_text.py
index c31c22853..519b300ff 100644
--- a/test/sql/test_text.py
+++ b/test/sql/test_text.py
@@ -1,7 +1,7 @@
"""Test the TextClause and related constructs."""
from sqlalchemy.testing import fixtures, AssertsCompiledSQL, eq_, \
- assert_raises_message, expect_warnings, assert_warnings
+ assert_raises_message, expect_warnings
from sqlalchemy import text, select, Integer, String, Float, \
bindparam, and_, func, literal_column, exc, MetaData, Table, Column,\
asc, func, desc, union, literal
@@ -580,18 +580,14 @@ class TextWarningsTest(fixtures.TestBase,
AssertsCompiledSQL):
class OrderByLabelResolutionTest(fixtures.TestBase, AssertsCompiledSQL):
__dialect__ = 'default'
- def _test_warning(self, stmt, offending_clause, expected):
- with expect_warnings(
- "Can't resolve label reference %r;" % offending_clause):
- self.assert_compile(
- stmt,
- expected
- )
+ def _test_exception(self, stmt, offending_clause):
assert_raises_message(
- exc.SAWarning,
- "Can't resolve label reference %r; converting to text" %
- offending_clause,
- stmt.compile
+ exc.CompileError,
+ r"Can't resolve label reference for ORDER BY / GROUP BY. "
+ "Textual SQL "
+ "expression %r should be explicitly "
+ r"declared as text\(%r\)" % (offending_clause, offending_clause),
+ stmt.compile,
)
def test_order_by_label(self):
@@ -641,11 +637,8 @@ class OrderByLabelResolutionTest(fixtures.TestBase,
AssertsCompiledSQL):
)
def test_unresolvable_warning_order_by(self):
- stmt = select([table1.c.myid]).order_by('foobar')
- self._test_warning(
- stmt, "foobar",
- "SELECT mytable.myid FROM mytable ORDER BY foobar"
- )
+ stmt = select([table1.c.myid]).order_by("foobar")
+ self._test_exception(stmt, "foobar")
def test_group_by_label(self):
stmt = select([table1.c.myid.label('foo')]).group_by('foo')
@@ -662,11 +655,8 @@ class OrderByLabelResolutionTest(fixtures.TestBase,
AssertsCompiledSQL):
)
def test_unresolvable_warning_group_by(self):
- stmt = select([table1.c.myid]).group_by('foobar')
- self._test_warning(
- stmt, "foobar",
- "SELECT mytable.myid FROM mytable GROUP BY foobar"
- )
+ stmt = select([table1.c.myid]).group_by("foobar")
+ self._test_exception(stmt, "foobar")
def test_asc(self):
stmt = select([table1.c.myid]).order_by(asc('name'), 'description')
@@ -764,20 +754,14 @@ class OrderByLabelResolutionTest(fixtures.TestBase,
AssertsCompiledSQL):
s1 = select([adapter.columns[expr] for expr in exprs]).\
apply_labels().order_by("myid", "t1name", "x")
- def go():
- # the labels here are anonymized, so label naming
- # can't catch these.
- self.assert_compile(
- s1,
- "SELECT mytable_1.myid AS mytable_1_myid, "
- "mytable_1.name AS name_1, foo(:foo_2) AS foo_1 "
- "FROM mytable AS mytable_1 ORDER BY mytable_1.myid, t1name, x"
- )
-
- assert_warnings(
- go,
- ["Can't resolve label reference 't1name'",
- "Can't resolve label reference 'x'"], regex=True)
+ assert_raises_message(
+ exc.CompileError,
+ r"Can't resolve label reference for ORDER BY / GROUP BY. "
+ "Textual SQL "
+ "expression 't1name' should be explicitly "
+ r"declared as text\('t1name'\)",
+ s1.compile,
+ )
def test_columnadapter_non_anonymized(self):
"""test issue #3148
--
2.22.0
++++++ 0002-maintain-compiled_params-replacement_expressions.patch ++++++
>From e4ec7d2b22546587d7a692041c6c70d23f6de3ac Mon Sep 17 00:00:00 2001
From: Mike Bayer <[email protected]>
Date: Fri, 21 Dec 2018 17:35:12 -0500
Subject: [PATCH] Maintain compiled_params / replacement_expressions within
expanding IN
URL: https://github.com/sqlalchemy/sqlalchemy/issues/4394
Fixed issue in "expanding IN" feature where using the same bound parameter
name more than once in a query would lead to a KeyError within the process
of rewriting the parameters in the query.
Fixes: #4394
Change-Id: Ibcadce9fefbcb060266d9447c2044ee6efeccf5a
(cherry picked from commit c495769751e8b19d54fb92388ced587b5d13b85d)
---
doc/build/changelog/unreleased_12/4394.rst | 7 ++
lib/sqlalchemy/engine/default.py | 75 +++++++++++++---------
test/sql/test_query.py | 37 ++++++++++-
3 files changed, 87 insertions(+), 32 deletions(-)
create mode 100644 doc/build/changelog/unreleased_12/4394.rst
diff --git a/doc/build/changelog/unreleased_12/4394.rst
b/doc/build/changelog/unreleased_12/4394.rst
new file mode 100644
index 0000000000..faa3547ad0
--- /dev/null
+++ b/doc/build/changelog/unreleased_12/4394.rst
@@ -0,0 +1,7 @@
+.. change::
+ :tag: bug, sql
+ :tickets: 4394
+
+ Fixed issue in "expanding IN" feature where using the same bound parameter
+ name more than once in a query would lead to a KeyError within the process
+ of rewriting the parameters in the query.
diff --git a/lib/sqlalchemy/engine/default.py b/lib/sqlalchemy/engine/default.py
index 915812a4f2..e63c5eafca 100644
--- a/lib/sqlalchemy/engine/default.py
+++ b/lib/sqlalchemy/engine/default.py
@@ -726,45 +726,57 @@ class DefaultExecutionContext(interfaces
positiontup = None
replacement_expressions = {}
+ to_update_sets = {}
+
for name in (
self.compiled.positiontup if compiled.positional
else self.compiled.binds
):
parameter = self.compiled.binds[name]
if parameter.expanding:
- values = compiled_params.pop(name)
- if not values:
- raise exc.InvalidRequestError(
- "'expanding' parameters can't be used with an "
- "empty list"
- )
- elif isinstance(values[0], (tuple, list)):
- to_update = [
- ("%s_%s_%s" % (name, i, j), value)
- for i, tuple_element in enumerate(values, 1)
- for j, value in enumerate(tuple_element, 1)
- ]
- replacement_expressions[name] = ", ".join(
- "(%s)" % ", ".join(
- self.compiled.bindtemplate % {
- "name":
- to_update[i * len(tuple_element) + j][0]
- }
- for j, value in enumerate(tuple_element)
+ if name in replacement_expressions:
+ to_update = to_update_sets[name]
+ else:
+ # we are removing the parameter from compiled_params
+ # because it is a list value, which is not expected by
+ # TypeEngine objects that would otherwise be asked to
+ # process it. the single name is being replaced with
+ # individual numbered parameters for each value in the
+ # param.
+ values = compiled_params.pop(name)
+
+ if not values:
+ raise exc.InvalidRequestError(
+ "'expanding' parameters with an empty list not "
+ "supported until SQLAlchemy 1.3."
)
- for i, tuple_element in enumerate(values)
+ elif isinstance(values[0], (tuple, list)):
+ to_update = to_update_sets[name] = [
+ ("%s_%s_%s" % (name, i, j), value)
+ for i, tuple_element in enumerate(values, 1)
+ for j, value in enumerate(tuple_element, 1)
+ ]
+ replacement_expressions[name] = ", ".join(
+ "(%s)" % ", ".join(
+ self.compiled.bindtemplate % {
+ "name":
+ to_update[i * len(tuple_element) + j][0]
+ }
+ for j, value in enumerate(tuple_element)
+ )
+ for i, tuple_element in enumerate(values)
- )
- else:
- to_update = [
- ("%s_%s" % (name, i), value)
- for i, value in enumerate(values, 1)
- ]
- replacement_expressions[name] = ", ".join(
- self.compiled.bindtemplate % {
- "name": key}
- for key, value in to_update
- )
+ )
+ else:
+ to_update = to_update_sets[name] = [
+ ("%s_%s" % (name, i), value)
+ for i, value in enumerate(values, 1)
+ ]
+ replacement_expressions[name] = ", ".join(
+ self.compiled.bindtemplate % {
+ "name": key}
+ for key, value in to_update
+ )
compiled_params.update(to_update)
processors.update(
(key, processors[name])
@@ -778,7 +790,7 @@ class DefaultExecutionContext(interfaces
positiontup.append(name)
def process_expanding(m):
- return replacement_expressions.pop(m.group(1))
+ return replacement_expressions[m.group(1)]
self.statement = re.sub(
r"\[EXPANDING_(\S+)\]",
diff --git a/test/sql/test_query.py b/test/sql/test_query.py
index 3e629fb261..d649da202a 100644
--- a/test/sql/test_query.py
+++ b/test/sql/test_query.py
@@ -457,7 +457,7 @@ def test_expanding_in(self):
assert_raises_message(
exc.StatementError,
- "'expanding' parameters can't be used with an empty list",
+ "'expanding' parameters with an empty list not supported",
conn.execute,
stmt, {"uname": []}
)
@@ -531,6 +531,41 @@ def test_expanding_in_multiple(self):
[(8, 'fred'), (9, 'ed')]
)
+ def test_expanding_in_repeated(self):
+ testing.db.execute(
+ users.insert(),
+ [
+ dict(user_id=7, user_name='jack'),
+ dict(user_id=8, user_name='fred'),
+ dict(user_id=9, user_name='ed')
+ ]
+ )
+
+ with testing.db.connect() as conn:
+ stmt = select([users]).where(
+ users.c.user_name.in_(
+ bindparam('uname', expanding=True)
+ ) | users.c.user_name.in_(bindparam('uname2', expanding=True))
+ ).where(users.c.user_id == 8)
+ stmt = stmt.union(
+ select([users]).where(
+ users.c.user_name.in_(
+ bindparam('uname', expanding=True)
+ ) | users.c.user_name.in_(
+ bindparam('uname2', expanding=True))
+ ).where(users.c.user_id == 9)
+ ).order_by(stmt.c.user_id)
+
+ eq_(
+ conn.execute(
+ stmt,
+ {
+ "uname": ['jack', 'fred'],
+ "uname2": ['ed'], "userid": [8, 9]}
+ ).fetchall(),
+ [(8, 'fred'), (9, 'ed')]
+ )
+
@testing.requires.tuple_in
def test_expanding_in_composite(self):
testing.db.execute(
