Thread: [Sqlalchemy-tickets] [sqlalchemy] #1068: render 'SELECT foo() AS x .. ORDER BY x' by default
Brought to you by:
zzzeek
Menu
▾
▴
sqlalchemy-tickets
|
From: sqlalchemy <mi...@zz...> - 2008-05-31 18:02:13
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
-------------------------+--------------------------------------------------
Reporter: jek | Owner: zzzeek
Type: enhancement | Status: new
Priority: medium | Milestone:
Component: sql | Severity: no triage selected yet
Keywords: | Status_field: awaiting triage
-------------------------+--------------------------------------------------
If an expression present in the select list is used elsewhere in the query
(WHERE, ORDER BY, GROUP BY, etc.), render it as the column name (label) by
default. Unlabeled non-column expressions re-used in ORDER BY can be
rendered positionally as integers. Oracle can override and render
expanded expressions.
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python |
|
From: sqlalchemy <mi...@zz...> - 2008-05-31 22:47:06
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
--------------------------------+-------------------------------------------
Reporter: jek | Owner: zzzeek
Type: enhancement | Status: new
Priority: high | Milestone: 0.5.0
Component: sql | Severity: no triage selected yet
Resolution: | Keywords:
Status_field: awaiting triage |
--------------------------------+-------------------------------------------
Changes (by zzzeek):
* priority: medium => high
* milestone: => 0.5.0
Comment:
just to be clear, we're talking about:
{{{
x = func.foo().label('x')
select([x]).order_by(x)
}}}
and not:
{{{
select([func.foo()]).order_by(func.foo())
}}}
?
(do we want `select([func.foo.label('x')]).order_by('x')` to do it ? seems
the most succinct way....since its 0.5 we're fine changing contract a
little bit)
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:1>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python |
|
From: sqlalchemy <mi...@zz...> - 2008-05-31 23:25:02
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
--------------------------------+-------------------------------------------
Reporter: jek | Owner: zzzeek
Type: enhancement | Status: new
Priority: high | Milestone: 0.5.0
Component: sql | Severity: no triage selected yet
Resolution: | Keywords:
Status_field: awaiting triage |
--------------------------------+-------------------------------------------
Old description:
> If an expression present in the select list is used elsewhere in the
> query (WHERE, ORDER BY, GROUP BY, etc.), render it as the column name
> (label) by default. Unlabeled non-column expressions re-used in ORDER BY
> can be rendered positionally as integers. Oracle can override and render
> expanded expressions.
New description:
If an expression present in the select list is used elsewhere in the query
(ORDER BY, GROUP BY, etc.), render it as the column name (label) by
default. Unlabeled non-column expressions re-used in ORDER BY can be
rendered positionally as integers. Oracle can override and render
expanded expressions.
Comment (by jek):
strings seem to work already with the caveat that you need to do your own
quoting on reserved identifiers. or were you suggesting something smarter
that does column inspection and quoting?
i'm thinking maybe like this w/ basic reused references:
(Postgres/Firebird)
{{{
>>> cl = func.char_length('abc').label('cl')
>>> print select([cl]).where(cl > 2).group_by(cl).having(cl >
2).order_by(cl)
SELECT char_length('abc') AS cl
WHERE char_length('abc') > 2
GROUP BY cl
HAVING char_length('abc') > 2
ORDER BY cl
}}}
sqlite (alone?) supports 'cl' in WHERE. mysql and sqlite will both use
'cl' in HAVING, pg & firebird won't.
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:2>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python |
|
From: sqlalchemy <mi...@zz...> - 2008-06-01 01:17:09
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
--------------------------------+-------------------------------------------
Reporter: jek | Owner: zzzeek
Type: enhancement | Status: new
Priority: high | Milestone: 0.5.0
Component: sql | Severity: no triage selected yet
Resolution: | Keywords:
Status_field: awaiting triage |
--------------------------------+-------------------------------------------
Comment (by zzzeek):
OK the "string" use case is probably not going to fly, since we have this
general contract that select().call_something('some string') is going to
just interpret the string as a text() clause. There's just as much chance
that someone says `order_by("CASE when x1>x2 THEN 2 ELSE 0")` as they
would say `order_by('label1')`. It would get out of hand to say that
`where()`, `order_by()`, `column()` etc. are all going to have wildly
different ideas of what a "string" means.
So yeah having `c1 = func.foo().label('c1')` just render as the label in
all cases except columns clause and overriding dialects is not a terribly
big deal. Involves some different semantics regarding the `render_labels`
argument to `visit_label()`, probably including a name change. still
would be good at 0.5.0 to establish the new pattern.
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:3>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python |
|
From: sqlalchemy <mi...@zz...> - 2008-08-03 21:22:15
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
--------------------------------+-------------------------------------------
Reporter: jek | Owner: zzzeek
Type: enhancement | Status: closed
Priority: medium | Milestone: 0.5.xx
Component: sql | Severity: no triage selected yet
Resolution: fixed | Keywords:
Status_field: awaiting triage |
--------------------------------+-------------------------------------------
Changes (by zzzeek):
* priority: high => medium
* status: new => closed
* resolution: => fixed
* milestone: 0.5.0 => 0.5.xx
Comment:
the approach here is described in detail in r4999, and is implemented for
ORDER BY only. Postgres' behavior was used as the guide here and it seems
like `order by <some simple label name>` has special meaning versus the
label name being used in arbitrary expressions or in the WHERE clause.
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:4>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python |
|
From: sqlalchemy <mi...@zz...> - 2008-08-06 14:30:57
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
--------------------------------+-------------------------------------------
Reporter: jek | Owner: zzzeek
Type: enhancement | Status: reopened
Priority: medium | Milestone: 0.5.xx
Component: sql | Severity: no triage selected yet
Resolution: | Keywords:
Status_field: awaiting triage |
--------------------------------+-------------------------------------------
Changes (by jek):
* status: closed => reopened
* resolution: fixed =>
Comment:
Reopening, r4999 seems to be just rendering labels unconditionally in
order by, rather than checking the select list as per the ticket.
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:5>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python |
|
From: sqlalchemy <mi...@zz...> - 2008-08-06 15:17:50
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
--------------------------------+-------------------------------------------
Reporter: jek | Owner: zzzeek
Type: enhancement | Status: reopened
Priority: medium | Milestone: 0.5.xx
Component: sql | Severity: no triage selected yet
Resolution: | Keywords:
Status_field: awaiting triage |
--------------------------------+-------------------------------------------
Comment (by zzzeek):
you need to show me the use case for order_by(somelabel) when "somelabel"
is not present in the select list. This is specifically something that
happens with ORM, correct ?
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:6>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python |
|
From: sqlalchemy <mi...@zz...> - 2008-08-06 15:27:00
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
--------------------------------+-------------------------------------------
Reporter: jek | Owner: zzzeek
Type: enhancement | Status: reopened
Priority: medium | Milestone: 0.5.xx
Component: sql | Severity: no triage selected yet
Resolution: | Keywords:
Status_field: awaiting triage |
--------------------------------+-------------------------------------------
Comment (by jek):
it can happen anywhere where a user makes a bunch of expression objects
and uses them naturally as building blocks, like fullname =
concat(tbl.c.fname, tbl.c.lname).label('fullname'). orm column_properties
also have the same issue.
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:7>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python |
|
From: sqlalchemy <mi...@zz...> - 2008-08-06 15:37:32
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
--------------------------------+-------------------------------------------
Reporter: jek | Owner: zzzeek
Type: enhancement | Status: reopened
Priority: medium | Milestone: 0.5.xx
Component: sql | Severity: no triage selected yet
Resolution: | Keywords:
Status_field: awaiting triage |
--------------------------------+-------------------------------------------
Comment (by zzzeek):
If you're using the SQL construction lib, you *have* to put `fullname`
into the ORDER BY. If you put a `label()` object inside of `ORDER BY`, it
should render the label unconditionally, why should the compiler try to
second guess what you did ? That seems like unnecessary work to be done.
for the ORM scenario it seems like we'd specifically prevent the ORM from
putting labels into ORDER_BY.
If my above points don't fly, then I'm heavily leaning towards rolling
back the feature and closing the ticket at this point, unless someone else
wants to take over and can ensure that no function call overhead is added
to compiler.py. For the change I made it wasn't a big deal. But it's now
inconveniencing me and I really don't need this behavior in the 1st place,
I don't buy the actual use case as very strong either.
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:8>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python |
|
From: sqlalchemy <mi...@zz...> - 2008-08-06 15:48:22
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
--------------------------------+-------------------------------------------
Reporter: jek | Owner: zzzeek
Type: enhancement | Status: reopened
Priority: medium | Milestone: 0.5.xx
Component: sql | Severity: no triage selected yet
Resolution: | Keywords:
Status_field: awaiting triage |
--------------------------------+-------------------------------------------
Comment (by jek):
not letting the rdbms reuse non deterministic computed values in where and
order_by isn't a strong use case?
i think the assertion that branching on 'if label in the select list' is a
untenable burden is pretty weak and makes the api needlessly inhumane.
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:9>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python |
|
From: sqlalchemy <mi...@zz...> - 2008-08-06 15:50:35
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
----------------------------------+-----------------------------------------
Reporter: jek | Owner: zzzeek
Type: enhancement | Status: reopened
Priority: medium | Milestone: 0.5.xx
Component: sql | Severity: no triage selected yet
Resolution: | Keywords:
Status_field: needs a volunteer |
----------------------------------+-----------------------------------------
Changes (by zzzeek):
* status_field: awaiting triage => needs a volunteer
Comment:
I dont have the resources to implement the feature further and its
breaking current code (based on anecdotal evidence). The feature is
removed in r5009. attaching a patch containing the development of code so
far in case someone wants to pick it up.
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:10>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python |
|
From: sqlalchemy <mi...@zz...> - 2008-08-06 15:51:00
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
----------------------------------+-----------------------------------------
Reporter: jek | Owner: anyone
Type: enhancement | Status: new
Priority: medium | Milestone: 0.5.xx
Component: sql | Severity: no triage selected yet
Resolution: | Keywords:
Status_field: needs a volunteer |
----------------------------------+-----------------------------------------
Changes (by zzzeek):
* owner: zzzeek => anyone
* status: reopened => new
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:11>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python |
|
From: sqlalchemy <mi...@zz...> - 2008-08-06 16:02:13
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
----------------------------------+-----------------------------------------
Reporter: jek | Owner: anyone
Type: enhancement | Status: new
Priority: medium | Milestone: 0.5.xx
Component: sql | Severity: no triage selected yet
Resolution: | Keywords:
Status_field: needs a volunteer |
----------------------------------+-----------------------------------------
Comment (by zzzeek):
Replying to [comment:9 jek]:
> not letting the rdbms reuse non deterministic computed values in where
and order_by isn't a strong use case?
its not a use case for Oracle, for example
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:12>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python |
|
From: sqlalchemy <mi...@zz...> - 2008-08-24 20:08:10
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
----------------------------------+-----------------------------------------
Reporter: jek | Owner: anyone
Type: enhancement | Status: new
Priority: medium | Milestone: 0.5.xx
Component: sql | Severity: major - 1-3 hours
Resolution: | Keywords:
Status_field: needs a volunteer |
----------------------------------+-----------------------------------------
Changes (by zzzeek):
* severity: no triage selected yet => major - 1-3 hours
Comment:
just a few thoughts here:
* the work to be done here is to do a check if a label used within ORDER
BY is present in the select() construct being rendered.
* Postgres has a more strict policy regarding labels in ORDER BY - you
can't embed the label into an expression of its own, it has to stand by
itself with an optional ASC or DESC and that's it.
* sqlite and mysql allow the label to be further calculated in an
expression
* I didn't check, but I have a feeling that labels cannot be used in the
WHERE clause with postgres. I'm also fairly certain (again didn't check)
that Oracle never allows labels in WHERE or ORDER BY.
* I'm gathering that MySQL allows the label to be used anywhere.
* The compiler will probably limit its search for the label to the
columns clause of the select() construct that is currently at the top of
the "select stack".
* A lot of effort is needed with testing here (the tests I did are
obviously not enough).
* The whole feature is strictly a more natural and convenient way of
issuing an optimization that is available in any case, by forcing usage of
a label name with `column('name')`.
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:13>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python |
|
From: sqlalchemy <mi...@zz...> - 2009-08-25 01:58:01
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
----------------------------------+-----------------------------------------
Reporter: jek | Owner: anyone
Type: enhancement | Status: new
Priority: medium | Milestone: blue sky
Component: sql | Severity: major - 1-3 hours
Resolution: | Keywords:
Status_field: needs a volunteer |
----------------------------------+-----------------------------------------
Changes (by zzzeek):
* milestone: 0.5.xx => blue sky
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:14>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python |
|
From: sqlalchemy <mi...@zz...> - 2013-05-28 01:45:18
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
------------------------------+-------------------------------
Reporter: jek | Owner: anyone
Type: enhancement | Status: new
Priority: high | Milestone: 0.8.xx
Component: sql | Severity: major - 1-3 hours
Resolution: | Keywords:
Progress State: in progress |
------------------------------+-------------------------------
Changes (by zzzeek):
* priority: medium => high
* milestone: blue sky => 0.8.xx
* status_field: needs a volunteer => in progress
Comment:
the branch at
http://www.sqlalchemy.org/trac/log/?rev=ff399ac75074916045410cedae72489cb60e8b50
has this working. Oracle seems OK with it, at least 10G. The branch
includes a compliance test in the dialect suite.
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:15>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python
|
|
From: sqlalchemy <mi...@zz...> - 2013-06-02 00:14:09
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
------------------------------+-------------------------------
Reporter: jek | Owner: anyone
Type: enhancement | Status: new
Priority: high | Milestone: 0.9.0
Component: sql | Severity: major - 1-3 hours
Resolution: | Keywords:
Progress State: in progress |
------------------------------+-------------------------------
Changes (by zzzeek):
* milestone: 0.8.xx => 0.9.0
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:16>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python
|
|
From: sqlalchemy <mi...@zz...> - 2013-06-03 19:43:21
|
#1068: render 'SELECT foo() AS x .. ORDER BY x' by default
-----------------------------------+-------------------------------
Reporter: jek | Owner: anyone
Type: enhancement | Status: closed
Priority: high | Milestone: 0.9.0
Component: sql | Severity: major - 1-3 hours
Resolution: fixed | Keywords:
Progress State: completed/closed |
-----------------------------------+-------------------------------
Changes (by zzzeek):
* status: new => closed
* resolution: => fixed
* status_field: in progress => completed/closed
Comment:
so far seems to work on all database platforms (even oracle). Will see if
users report issues on older versions of those platforms.
diff:@82c11cc94b41323caeecc9990d1d87ed9fed4b7c:f8325d9dbb52d0da140f3403ce915645ae4a5a9c
--
Ticket URL: <http://www.sqlalchemy.org/trac/ticket/1068#comment:17>
sqlalchemy <http://www.sqlalchemy.org/>
The Database Toolkit for Python
|
×
Want the latest updates on software, tech news, and AI?
Get latest updates about software, tech news, and AI from SourceForge directly in your inbox once a month.
Thanks for helping keep SourceForge clean.
X