fix up argument escaping in .startswith() and .endswith()

[jek]

now that ESCAPE support is in, the expr.startswith and .endswith operators can be adjusted to escape their argument. (i believe we had said the ultimate intention for these operators was to be no-brainer sugar methods mimicking the Python equivs, i.e. no wildcards.)

the twist is that the escaping will probably need to be SQL-side, so we can continue to emit sql like 'expr LIKE <stuff> + '%', where stuff can be a literal bind string or column or expression, etc.

previous SA versions haven't applied the escaping, so this is a behavior change. milestone TBD.

[Mike Bayer]

startswith() and endswith() have an escape argument which render ESCAPE on the SQL side so I believe this has been in for some time now.

[Marcin Kasperski]

I found this ticket while checking whether somebody already reported that .startswith("a_b_c") finds "a/b/c/d". So let me note that:

a) escape argument of startswith seems completely undocumented ( various parts of doc refer to http://docs.sqlalchemy.org/en/rel_0_8/core/expression_api.html#sqlalchemy.sql.operators.ColumnOperators.startswith which shows what it shows )

b) as I understand, I must write sth like qry = qry.where(tab.c.column.startswith("a#_b#_c", escape="#")) (at least this is the syntax which works for postgresql) what is not the most pleasant way

c) in general I feel that .startswith should perform some kind of escaping internally, if I wanted % and _ handling, I would use like

[Mike Bayer]

escape argument of startswith seems completely undocumented

fine

(at least this is the syntax which works for postgresql) what is not the most pleasant way

the "_" and "%" characters in a LIKE are special. They need to be escaped. The default escape character is a backslash, so there's no need to set a special escape character unless you actually need to search for backslashes. (see http://www.postgresql.org/docs/8.3/static/functions-matching.html)

in general I feel that .startswith should perform some kind of escaping internally, if I wanted % and _ handling,

startswith/endswith would hide the fact that LIKE is in use and auto-escape _ and %, could be useful, though we could never make this a default at this point. feel free to open a separate enhancement for that.

[Marcin Kasperski]

I got used to the fact that backslashes are risky as they are config-parameter dependant (see caution below http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS ). Not sure whether/how this applies to bound params (and sqlalchemy generated SQL in general).

OK, I will open another ticket.

[Marcin Kasperski]

See #2694