remove usage of text() in conjunction with select() from SQL expression tutorial in favor of plain strings.

[Lele Gaifax]

I was trying to get a particular select working, knowing how I would have written by hand:

  SELECT t1.some_id, (SELECT COALESCE(t2.integer_field, 0)  || 'sep' || COALESCE(t2.string_field, '')
                      FROM t2
                      WHERE t2.fk_id = t1.pk_id)
  FROM t1

I didn't get it right at first, because t2.integer_field is an integer and I need to specify the type_=VARCHAR on the SA func.coalesce() function, otherwise built an expression of the kind COALESCE(f1,0) + 'sep' || COALESCE(f2, ''), treating the first operand as an integer.

Getting there with a few experiments, I once tried using text() to build the subselect, and then using .as_scalar() on it: I got the error InvalidRequestError: Scalar select can only be created from a Select object that has exactly one column expression. that is clearly wrong, since effectively the subselect yields a single column and single row result.

The following test triggers the case:

Index: test/sql/select.py
===================================================================
--- test/sql/select.py  (revisione 5902)
+++ test/sql/select.py  (copia locale)
@@ -734,6 +734,12 @@
             "SELECT col1, col2 FROM tablename"
         )

+        self.assert_compile(
+            select([                   select([text("users.user_name || '-' || users.password")](addresses.c.street,
+),
+                           addresses.c.user_id==users.c.user_id).as_scalar().label('udetail')]),
+            "SELECT addresses.street, (SELECT users.user_name || '-' || users.password FROM users WHERE addresses.user_id=users.user_id) AS udetail FROM addresses")
+
     def test_binds_in_text(self):
         self.assert_compile(
             text("select * from foo where lala=:bar and hoho=:whee", bindparams=[4), bindparam('whee', 7)](bindparam('bar',)),

that gives:

======================================================================
ERROR: test_text (__main__.SelectTest)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "test/sql/select.py", line 740, in test_text
    addresses.c.user_id==users.c.user_id).as_scalar().label('udetail')]),
  File "/home/lele/wip/sqla/svn-trunk/lib/sqlalchemy/sql/expression.py", line 3012, in as_scalar
    return _ScalarSelect(self)
  File "/home/lele/wip/sqla/svn-trunk/lib/sqlalchemy/sql/expression.py", line 3115, in __init__
    raise exc.InvalidRequestError("Scalar select can only be created "
InvalidRequestError: Scalar select can only be created from a Select object that has exactly one column expression.

----------------------------------------------------------------------

For the record, I finally got it right using SA constructors, something like:

select([0, type_=meta.VARCHAR) +
        ' (' +
        func.coalesce(t2.str_field, 'none', type_=meta.VARCHAR) +
        ')'](func.coalesce(t2.integer_field,), ...).as_scalar().label('ComposedDescription')

[Mike Bayer]

a text() expression may have multiple columns. its legal to say:

select([y, z")](text("x,))

although this is kind of a silly usage, the select() construct really wants literal_columns() in the columns clause. the intent though is that you dont really have to know what it wants. when mixing strings into select(), you should use just a plain string, and the select() will interpret it appropriately:

 self.assert_compile(
            select([                       addresses.c.street,
                        select(["users.user_name || '-' || users.password"](
),
                            addresses.c.user_id==users.c.user_id).as_scalar().label('udetail')]

                    ),
                    "SELECT addresses.street, (SELECT users.user_name || '-' || users.password FROM users WHERE addresses.user_id = users.user_id) AS udetail FROM addresses")

i see the documentation is encouraging the usage of text() for this specific example, and should be changed. im surprised it still uses text(), which really is a construct that should only be used by itself and not embedded anywhere (even though the select() might embed its own generated text() constructs).

http://www.sqlalchemy.org/docs/05/sqlexpression.html#using-text

[Mike Bayer]

54e6dedc5fd2daa4baf6fe3c0b9ca0bf146e5d33 74102ffc296d3a472a655db4e53584a24b8004a4

[Mike Bayer]

Removing milestone: 0.5.xx (automated comment)