- A deep improvement to the recently added :meth:`.TextClause.columns`

method, and its interaction with result-row processing, now allows
the columns passed to the method to be positionally matched with the
result columns in the statement, rather than matching on name alone.
The advantage to this includes that when linking a textual SQL statement
to an ORM or Core table model, no system of labeling or de-duping of
common column names needs to occur, which also means there's no need
to worry about how label names match to ORM columns and so-forth.  In
addition, the :class:`.ResultProxy` has been further enhanced to
map column and string keys to a row with greater precision in some
cases.  fixes #3501
- reorganize the initialization of ResultMetaData for readability
and complexity; use the name "cursor_description", define the
task of "merging" cursor_description with compiled column information
as its own function, and also define "name extraction" as a separate task.
- fully change the name we use in the "ambiguous column" error to be the
actual name that was ambiguous, modify the C ext also

5 files changed, 333 insertions, 58 deletions

diff --git a/doc/build/changelog/changelog_11.rst b/doc/build/changelog/changelog_11.rst
index 975badc36..81637a0b4 100644
--- a/doc/build/changelog/changelog_11.rst
+++ b/doc/build/changelog/changelog_11.rst
@@ -22,6 +22,28 @@
     :version: 1.1.0b1
 
     .. change::
+        :tags: feature, sql
+        :tickets: 3501
+
+        A deep improvement to the recently added :meth:`.TextClause.columns`
+        method, and its interaction with result-row processing, now allows
+        the columns passed to the method to be positionally matched with the
+        result columns in the statement, rather than matching on name alone.
+        The advantage to this includes that when linking a textual SQL statement
+        to an ORM or Core table model, no system of labeling or de-duping of
+        common column names needs to occur, which also means there's no need
+        to worry about how label names match to ORM columns and so-forth.  In
+        addition, the :class:`.ResultProxy` has been further enhanced to
+        map column and string keys to a row with greater precision in some
+        cases.
+
+        .. seealso::
+
+            :ref:`change_3501` - feature overview
+
+            :ref:`behavior_change_3501` - backwards compatibility remarks
+
+    .. change::
         :tags: feature, engine
         :tickets: 2685
 
diff --git a/doc/build/changelog/migration_11.rst b/doc/build/changelog/migration_11.rst
index b87e7207b..ed2ae075f 100644
--- a/doc/build/changelog/migration_11.rst
+++ b/doc/build/changelog/migration_11.rst
@@ -445,6 +445,120 @@ will not have much impact on the behavior of the column during an INSERT.
 
 :ticket:`3216`
 
+.. _change_3501:
+
+ResultSet column matching enhancements; positional column setup for textual SQL
+-------------------------------------------------------------------------------
+
+A series of improvements were made to the :class:`.ResultProxy` system
+in the 1.0 series as part of :ticket:`918`, which reorganizes the internals
+to match cursor-bound result columns with table/ORM metadata positionally,
+rather than by matching names, for compiled SQL constructs that contain full
+information about the result rows to be returned.   This allows a dramatic savings
+on Python overhead as well as much greater accuracy in linking ORM and Core
+SQL expressions to result rows.  In 1.1, this reorganization has been taken
+further internally, and also has been made available to pure-text SQL
+constructs via the use of the recently added :meth:`.TextClause.columns` method.
+
+TextAsFrom.columns() now works positionally
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+The :meth:`.TextClause.columns` method, added in 0.9, accepts column-based arguments
+positionally; in 1.1, when all columns are passed positionally, the correlation
+of these columns to the ultimate result set is also performed positionally.
+The key advantage here is that textual SQL can now be linked to an ORM-
+level result set without the need to deal with ambiguous or duplicate column
+names, or with having to match labeling schemes to ORM-level labeling schemes.  All
+that's needed now is the same ordering of columns within the textual SQL
+and the column arguments passed to :meth:`.TextClause.columns`::
+
+
+    from sqlalchemy import text
+    stmt = text("SELECT users.id, addresses.id, users.id, "
+         "users.name, addresses.email_address AS email "
+         "FROM users JOIN addresses ON users.id=addresses.user_id "
+         "WHERE users.id = 1").columns(
+            User.id,
+            Address.id,
+            Address.user_id,
+            User.name,
+            Address.email_address
+         )
+
+    query = session.query(User).from_statement(text).\
+        options(contains_eager(User.addresses))
+    result = query.all()
+
+Above, the textual SQL contains the column "id" three times, which would
+normally be ambiguous.  Using the new feature, we can apply the mapped
+columns from the ``User`` and ``Address`` class directly, even linking
+the ``Address.user_id`` column to the ``users.id`` column in textual SQL
+for fun, and the :class:`.Query` object will receive rows that are correctly
+targetable as needed, including for an eager load.
+
+This change is **backwards incompatible** with code that passes the columns
+to the method with a different ordering than is present in the textual statement.
+It is hoped that this impact will be low due to the fact that this
+method has always been documented illustrating the columns being passed in the same order as that of the
+textual SQL statement, as would seem intuitive, even though the internals
+weren't checking for this.  The method itself was only added as of 0.9 in
+any case and may not yet have widespread use.  Notes on exactly how to handle
+this behavioral change for applications using it are at :ref:`behavior_change_3501`.
+
+.. seealso::
+
+    :ref:`sqlexpression_text_columns` - in the Core tutorial
+
+    :ref:`behavior_change_3501` - backwards compatibility remarks
+
+Positional matching is trusted over name-based matching for Core/ORM SQL constructs
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+Another aspect of this change is that the rules for matching columns have also been modified
+to rely upon "positional" matching more fully for compiled SQL constructs
+as well.   Given a statement like the following::
+
+    ua = users.alias('ua')
+    stmt = select([users.c.user_id, ua.c.user_id])
+
+The above statement will compile to::
+
+    SELECT users.user_id, ua.user_id FROM users, users AS ua
+
+In 1.0, the above statement when executed would be matched to its original
+compiled construct using positional matching, however because the statement
+contains the ``'user_id'`` label duplicated, the "ambiguous column" rule
+would still get involved and prevent the columns from being fetched from a row.
+As of 1.1, the "ambiguous column" rule does not affect an exact match from
+a column construct to the SQL column, which is what the ORM uses to
+fetch columns::
+
+    result = conn.execute(stmt)
+    row = result.first()
+
+    # these both match positionally, so no error
+    user_id = row[users.c.user_id]
+    ua_id = row[ua.c.user_id]
+
+    # this still raises, however
+    user_id = row['user_id']
+
+Much less likely to get an "ambiguous column" error message
+^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
+
+As part of this change, the wording of the error message ``Ambiguous column
+name '<name>' in result set! try 'use_labels' option on select statement.``
+has been dialed back; as this message should now be extremely rare when using
+the ORM or Core compiled SQL constructs, it merely states
+``Ambiguous column name '<name>' in result set column descriptions``, and
+only when a result column is retrieved using the string name that is actually
+ambiguous, e.g. ``row['user_id']`` in the above example.  It also now refers
+to the actual ambiguous name from the rendered SQL statement itself,
+rather than indicating the key or name that was local to the construct being
+used for the fetch.
+
+:ticket:`3501`
+
 .. _change_2528:
 
 A UNION or similar of SELECTs with LIMIT/OFFSET/ORDER BY now parenthesizes the embedded selects
@@ -896,6 +1010,54 @@ Key Behavioral Changes - ORM
 Key Behavioral Changes - Core
 =============================
 
+.. _behavior_change_3501:
+
+TextClause.columns() will match columns positionally, not by name, when passed positionally
+-------------------------------------------------------------------------------------------
+
+The new behavior of the :meth:`.TextClause.columns` method, which itself
+was recently added as of the 0.9 series, is that when
+columns are passed positionally without any additional keyword arguments,
+they are linked to the ultimate result set
+columns positionally, and no longer on name.   It is hoped that the impact
+of this change will be low due to the fact that the method has always been documented
+illustrating the columns being passed in the same order as that of the
+textual SQL statement, as would seem intuitive, even though the internals
+weren't checking for this.
+
+An application that is using this method by passing :class:`.Column` objects
+to it positionally must ensure that the position of those :class:`.Column`
+objects matches the position in which these columns are stated in the
+textual SQL.
+
+E.g., code like the following::
+
+    stmt = text("SELECT id, name, description FROM table")
+
+    # no longer matches by name
+    stmt = stmt.columns(my_table.c.name, my_table.c.description, my_table.c.id)
+
+Would no longer work as expected; the order of the columns given is now
+significant::
+
+    # correct version
+    stmt = stmt.columns(my_table.c.id, my_table.c.name, my_table.c.description)
+
+Possibly more likely, a statement that worked like this::
+
+    stmt = text("SELECT * FROM table")
+    stmt = stmt.columns(my_table.c.id, my_table.c.name, my_table.c.description)
+
+is now slightly risky, as the "*" specification will generally deliver columns
+in the order in which they are present in the table itself.  If the structure
+of the table changes due to schema changes, this ordering may no longer be the same.
+Therefore when using :meth:`.TextClause.columns`, it's advised to list out
+the desired columns explicitly in the textual SQL, though it's no longer
+necessary to worry about the names themselves in the textual SQL.
+
+.. seealso::
+
+    :ref:`change_3501`
 
 Dialect Improvements and Changes - Postgresql
 =============================================
diff --git a/doc/build/core/selectable.rst b/doc/build/core/selectable.rst
index 03ebeb4ab..e73ce7b64 100644
--- a/doc/build/core/selectable.rst
+++ b/doc/build/core/selectable.rst
@@ -86,3 +86,4 @@ elements are themselves :class:`.ColumnElement` subclasses).
 
 .. autoclass:: TextAsFrom
    :members:
+   :inherited-members:
diff --git a/doc/build/core/tutorial.rst b/doc/build/core/tutorial.rst
index 06fc44ce8..04262ac5e 100644
--- a/doc/build/core/tutorial.rst
+++ b/doc/build/core/tutorial.rst
@@ -791,35 +791,127 @@ Above, we can see that bound parameters are specified in
 :func:`~.expression.text` using the named colon format; this format is
 consistent regardless of database backend.  To send values in for the
 parameters, we passed them into the :meth:`~.Connection.execute` method
-as additional arguments.  Depending on how we are working, we can also
-send values to be associated directly with the :func:`~.expression.text`
-construct using the :meth:`~.TextClause.bindparams` method; if we are
-using datatypes that need special handling as they are received in Python,
-or we'd like to compose our :func:`~.expression.text` object into a larger
-expression, we may also wish to use the :meth:`~.TextClause.columns` method
-in order to specify column return types and names:
+as additional arguments.
+
+Specifying Bound Parameter Behaviors
+------------------------------------------
+
+The :func:`~.expression.text` construct supports pre-established bound values
+using the :meth:`.TextClause.bindparams` method::
+
+    stmt = text("SELECT * FROM users WHERE users.name BETWEEN :x AND :y")
+    stmt = stmt.bindparams(x="m", y="z")
+
+The parameters can also be explicitly typed::
+
+    stmt = stmt.bindparams(bindparam("x", String), bindparam("y", String))
+    result = conn.execute(stmt, {"x": "m", "y": "z"})
+
+Typing for bound parameters is necessary when the type requires Python-side
+or special SQL-side processing provided by the datatype.
+
+.. seealso::
+
+    :meth:`.TextClause.bindparams` - full method description
+
+.. _sqlexpression_text_columns:
+
+Specifying Result-Column Behaviors
+----------------------------------------------
+
+We may also specify information about the result columns using the
+:meth:`.TextClause.columns` method; this method can be used to specify
+the return types, based on name::
+
+    stmt = stmt.columns(id=Integer, name=String)
+
+or it can be passed full column expressions positionally, either typed
+or untyped.  In this case it's a good idea to list out the columns
+explicitly within our textual SQL, since the correlation of our column
+expressions to the SQL will be done positionally::
+
+    stmt = text("SELECT id, name FROM users")
+    stmt = stmt.columns(users.c.id, users.c.name)
+
+When we call the :meth:`.TextClause.columns` method, we get back a
+:class:`.TextAsFrom` object that supports the full suite of
+:attr:`.TextAsFrom.c` and other "selectable" operations::
+
+    j = stmt.join(addresses, stmt.c.id == addresses.c.user_id)
+
+    new_stmt = select([stmt.c.id, addresses.c.id]).\
+        select_from(j).where(stmt.c.name == 'x')
+
+The positional form of :meth:`.TextClause.columns` is particularly useful
+when relating textual SQL to existing Core or ORM models, because we can use
+column expressions directly without worrying about name conflicts or other issues with the
+result column names in the textual SQL:
 
 .. sourcecode:: pycon+sql
 
-    >>> s = text(
-    ...     "SELECT users.fullname || ', ' || addresses.email_address AS title "
-    ...         "FROM users, addresses "
-    ...         "WHERE users.id = addresses.user_id "
-    ...         "AND users.name BETWEEN :x AND :y "
-    ...         "AND (addresses.email_address LIKE :e1 "
-    ...             "OR addresses.email_address LIKE :e2)")
-    >>> s = s.columns(title=String)
-    >>> s = s.bindparams(x='m', y='z', e1='%@aol.com', e2='%@msn.com')
-    >>> conn.execute(s).fetchall()
-    SELECT users.fullname || ', ' || addresses.email_address AS title
-    FROM users, addresses
-    WHERE users.id = addresses.user_id AND users.name BETWEEN ? AND ? AND
-    (addresses.email_address LIKE ? OR addresses.email_address LIKE ?)
-    ('m', 'z', '%@aol.com', '%@msn.com')
-    {stop}[(u'Wendy Williams, wendy@aol.com',)]
+    >>> stmt = text("SELECT users.id, addresses.id, users.id, "
+    ...     "users.name, addresses.email_address AS email "
+    ...     "FROM users JOIN addresses ON users.id=addresses.user_id "
+    ...     "WHERE users.id = 1").columns(
+    ...        users.c.id,
+    ...        addresses.c.id,
+    ...        addresses.c.user_id,
+    ...        users.c.name,
+    ...        addresses.c.email_address
+    ...     )
+    {sql}>>> result = conn.execute(stmt)
+    SELECT users.id, addresses.id, users.id, users.name,
+        addresses.email_address AS email
+    FROM users JOIN addresses ON users.id=addresses.user_id WHERE users.id = 1
+    ()
+    {stop}
+
+Above, there's three columns in the result that are named "id", but since
+we've associated these with column expressions positionally, the names aren't an issue
+when the result-columns are fetched using the actual column object as a key.
+Fetching the ``email_address`` column would be::
+
+    >>> row = result.fetchone()
+    >>> row[addresses.c.email_address]
+    'jack@yahoo.com'
+
+If on the other hand we used a string column key, the usual rules of name-
+based matching still apply, and we'd get an ambiguous column error for
+the ``id`` value::
+
+    >>> row["id"]
+    Traceback (most recent call last):
+    ...
+    InvalidRequestError: Ambiguous column name 'id' in result set column descriptions
+
+It's important to note that while accessing columns from a result set using
+:class:`.Column` objects may seem unusual, it is in fact the only system
+used by the ORM, which occurs transparently beneath the facade of the
+:class:`~.orm.query.Query` object; in this way, the :meth:`.TextClause.columns` method
+is typically very applicable to textual statements to be used in an ORM
+context.   The example at :ref:`orm_tutorial_literal_sql` illustrates
+a simple usage.
+
+.. versionadded:: 1.1
+
+    The :meth:`.TextClause.columns` method now accepts column expressions
+    which will be matched positionally to a plain text SQL result set,
+    eliminating the need for column names to match or even be unique in the
+    SQL statement when matching table metadata or ORM models to textual SQL.
+
+.. seealso::
 
+    :meth:`.TextClause.columns` - full method description
 
-:func:`~.expression.text` can also be used freely within a
+    :ref:`orm_tutorial_literal_sql` - integrating ORM-level queries with
+    :func:`.text`
+
+
+Using text() fragments inside bigger statements
+-----------------------------------------------
+
+:func:`~.expression.text` can also be used to produce fragments of SQL
+that can be freely within a
 :func:`~.expression.select` object, which accepts :func:`~.expression.text`
 objects as an argument for most of its builder functions.
 Below, we combine the usage of :func:`~.expression.text` within a
@@ -850,30 +942,13 @@ need to refer to any pre-established :class:`.Table` metadata:
     ('%@aol.com', '%@msn.com')
     {stop}[(u'Wendy Williams, wendy@aol.com',)]
 
-.. topic:: Why not use strings everywhere?
-
-    When we use literal strings, the Core can't adapt our SQL to work
-    on different database backends.  Above, our expression won't work
-    with MySQL since MySQL doesn't have the ``||`` construct.
-    If we only use :func:`.text` to specify columns, our :func:`.select`
-    construct will have an empty ``.c`` collection
-    that we'd normally use to create subqueries.
-    We also lose typing information about result columns and bound parameters,
-    which is often needed to correctly translate data values between
-    Python and the database.  Overall, the more :func:`.text` we use,
-    the less flexibility and ability for manipulation/transformation
-    the statement will have.
-
-.. seealso::
-
-    :ref:`orm_tutorial_literal_sql` - integrating ORM-level queries with
-    :func:`.text`
-
 .. versionchanged:: 1.0.0
    The :func:`.select` construct emits warnings when string SQL
    fragments are coerced to :func:`.text`, and :func:`.text` should
    be used explicitly.  See :ref:`migration_2992` for background.
 
+
+
 .. _sqlexpression_literal_column:
 
 Using More Specific Text with :func:`.table`, :func:`.literal_column`, and :func:`.column`
diff --git a/doc/build/orm/tutorial.rst b/doc/build/orm/tutorial.rst
index 53f161003..6e98dfc9c 100644
--- a/doc/build/orm/tutorial.rst
+++ b/doc/build/orm/tutorial.rst
@@ -965,10 +965,12 @@ method:
     (224, 'fred')
     {stop}<User(name='fred', fullname='Fred Flinstone', password='blah')>
 
-To use an entirely string-based statement, using
-:meth:`~sqlalchemy.orm.query.Query.from_statement()`; just ensure that the
-columns clause of the statement contains the column names normally used by the
-mapper (below illustrated using an asterisk):
+To use an entirely string-based statement, a :func:`.text` construct
+representing a complete statement can be passed to
+:meth:`~sqlalchemy.orm.query.Query.from_statement()`.  Without additional
+specifiers, the columns in the string SQL are matched to the model columns
+based on name, such as below where we use just an asterisk to represent
+loading all columns:
 
 .. sourcecode:: python+sql
 
@@ -979,19 +981,37 @@ mapper (below illustrated using an asterisk):
     ('ed',)
     {stop}[<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>]
 
-Or alternatively, specify how the columns map to the :func:`.text` construct
-explicitly using the :meth:`.TextClause.columns` method:
+Matching columns on name works for simple cases but can become unwieldy when
+dealing with complex statements that contain duplicate column names or when
+using anonymized ORM constructs that don't easily match to specific names.
+Additionally, there is typing behavior present in our mapped columns that
+we might find necessary when handling result rows.  For these cases,
+the :func:`~.expression.text` construct allows us to link its textual SQL
+to Core or ORM-mapped column expressions positionally; we can achieve this
+by passing column expressions as positional arguments to the
+:meth:`.TextClause.columns` method:
 
 .. sourcecode:: python+sql
 
-    >>> stmt = text("SELECT name, id FROM users where name=:name")
-    >>> stmt = stmt.columns(User.name, User.id)
+    >>> stmt = text("SELECT name, id, fullname, password "
+    ...             "FROM users where name=:name")
+    >>> stmt = stmt.columns(User.name, User.id, User.fullname, User.password)
     {sql}>>> session.query(User).from_statement(stmt).params(name='ed').all()
-    SELECT name, id FROM users where name=?
+    SELECT name, id, fullname, password FROM users where name=?
     ('ed',)
     {stop}[<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>]
 
-We can choose columns to return individually as well, as in any other case:
+.. versionadded:: 1.1
+
+    The :meth:`.TextClause.columns` method now accepts column expressions
+    which will be matched positionally to a plain text SQL result set,
+    eliminating the need for column names to match or even be unique in the
+    SQL statement.
+
+When selecting from a :func:`~.expression.text` construct, the :class:`.Query`
+may still specify what columns and entities are to be returned; instead of
+``query(User)`` we can also ask for the columns individually, as in
+any other case:
 
 .. sourcecode:: python+sql
 
@@ -1008,11 +1028,6 @@ We can choose columns to return individually as well, as in any other case:
     :ref:`sqlexpression_text` - The :func:`.text` construct explained
     from the perspective of Core-only queries.
 
-.. versionchanged:: 1.0.0
-   The :class:`.Query` construct emits warnings when string SQL
-   fragments are coerced to :func:`.text`, and :func:`.text` should
-   be used explicitly.  See :ref:`migration_2992` for background.
-
 Counting
 --------