- add some tutorial about this

5 files changed, 188 insertions, 66 deletions

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..053229b8e 100644
--- a/doc/build/core/tutorial.rst
+++ b/doc/build/core/tutorial.rst
@@ -791,35 +791,125 @@ 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
+
+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 +940,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
 --------
 
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 58e8d78ec..f18a9cdcd 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -1486,9 +1486,17 @@ class TextClause(Executable, ClauseElement):
                         mytable.join(stmt, mytable.c.name == stmt.c.name)
                     ).where(stmt.c.id > 5)
 
-        Above, we used untyped :func:`.column` elements.  These can also have
-        types specified, which will impact how the column behaves in
-        expressions as well as determining result set behavior::
+        Above, we pass a series of :func:`.column` elements to the
+        :meth:`.TextClause.columns` method positionally.  These :func:`.column`
+        elements now become first class elements upon the :attr:`.TextAsFrom.c`
+        column collection, just like any other selectable.
+
+        The column expressions we pass to :meth:`.TextClause.columns` may
+        also be typed; when we do so, these :class:`.TypeEngine` objects become
+        the effective return type of the column, so that SQLAlchemy's
+        result-set-processing systems may be used on the return values.
+        This is often needed for types such as date or boolean types, as well
+        as for unicode processing on some dialect configurations::
 
             stmt = text("SELECT id, name, timestamp FROM some_table")
             stmt = stmt.columns(
@@ -1500,9 +1508,8 @@ class TextClause(Executable, ClauseElement):
             for id, name, timestamp in connection.execute(stmt):
                 print(id, name, timestamp)
 
-        Keyword arguments allow just the names and types of columns to be
-        specified, where the :func:`.column` elements will be generated
-        automatically::
+        As a shortcut to the above syntax, keyword arguments referring to
+        types alone may be used, if only type conversion is needed::
 
             stmt = text("SELECT id, name, timestamp FROM some_table")
             stmt = stmt.columns(
@@ -1514,6 +1521,33 @@ class TextClause(Executable, ClauseElement):
             for id, name, timestamp in connection.execute(stmt):
                 print(id, name, timestamp)
 
+        The positional form of :meth:`.TextClause.columns` also provides
+        the unique feature of **positional column targeting**, which is
+        particularly useful when using the ORM with complex textual queries.
+        If we specify the columns from our model to :meth:`.TextClause.columns`,
+        the result set will match to those columns positionally, meaning the
+        name or origin of the column in the textual SQL doesn't matter::
+
+            stmt = text("SELECT users.id, addresses.id, users.name, )
+
+            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(stmt).options(
+                contains_eager(User.addresses))
+
+        .. versionadded:: 1.1 the :meth:`.TextClause.columns` method now
+           offers positional column targeting in the result set when
+           the column expressions are passed purely positionally.
+
         The :meth:`.TextClause.columns` method provides a direct
         route to calling :meth:`.FromClause.alias` as well as
         :meth:`.SelectBase.cte` against a textual SELECT statement::
@@ -1527,6 +1561,7 @@ class TextClause(Executable, ClauseElement):
            :meth:`.TextClause.columns` method.  This method supersedes the
            ``typemap`` argument to :func:`.text`.
 
+
         """
 
         positional_input_cols = [
diff --git a/test/sql/test_resultset.py b/test/sql/test_resultset.py
index 319d0af5a..952e0433a 100644
--- a/test/sql/test_resultset.py
+++ b/test/sql/test_resultset.py
@@ -1294,5 +1294,3 @@ class PositionalTextTest(fixtures.TablesTest):
             "Could not locate column in row for column 'text1.a'",
             lambda: row[text1.c.a]
         )
-
-