#7210 - F() Query Expressions
Russell Keith-Magee
With [9792], I've committed F() query expressions to trunk. For
details, see the docs:
http://docs.djangoproject.com/en/dev/topics/db/queries/#filters-can-reference-fields-on-the-model
There are two caveats worth knowing about:
1) This patch reveals a bug in the SQLite package that ships with
Ubuntu Intrepid Ibex (SQLite 3.5.9-3). Versions of SQLite 3.5.9 on
other operating systems (including Debian) don't appear to be
affected, and other versions of SQLite on Ubuntu don't appear to be
affected either.
2) F() expressions have not been updated to work with GIS fields.
However, based on some initial discussions with Justin, it should be
possible to make these changes without affecting any public API. If
anyone with access and experience with contrib.GIS cares to help out
on this front, assistance would be gratefully accepted; otherwise,
we'll just have to wait until Justin surfaces from his Bar exams.
Finally, some thank yous:
Sebastian Noack for getting the ball rolling. Sebastian raised the
original ticket and wrote the original patch. Not much of that patch
survived into the final implementation, but his contribution is very
much appreciated. Unfortunately, I forgot about Sebastian's original
involvement when I wrote the commit message - for that, I apologize.
Nicolas Lara for doing the heavy lifting as part of the 2008 Google
Summer of Code. F() expressions were an optional item in his GSoC
proposal, but he made such good progress on aggregates that we got
two-for-one. Nicolas, take a bow - you did some excellent work here.
Alex Gaynor provided some excellent assistance debugging and fixing a
number of problems as we neared completion of the project (including
narrowing down the mutant SQLite problem). Thanks Alex.
Malcolm Tredinnick did his usual stunning job at reviewing code and
picking holes in designs. Thanks Malcolm.
Thanks also to the many people that contributed ideas during the
design phase, and to those who tested the code as it was developing.
Your efforts may not have ended up in the final design or turned into
a bug report, but thanks for taking the time to look at an
experimental feature.
Yours,
Russ Magee %-)
Ian Kelly
<freakb...@gmail.com> wrote:
>
> Hi all,
>
> With [9792], I've committed F() query expressions to trunk. For
> details, see the docs:
Hi Russell,
First, my apologies for not testing this with Oracle sooner. It
appears that nobody else tested it either, because there are some
issues - specifically, Oracle doesn't have the expected %, &, and |
operators.
In order to get this working, it looks like we're going to need to do
some refactoring to push the expression evaluation code into
connection.ops, since currently 1) the operators are hard-coded into
the ExpressionNode class, and 2) the evaluation needs to be flexible
enough to allow function calls in place of operators. Do you have any
suggestions for approaching this task?
The bit-wise or operator presents a third obstacle, since Oracle
annoyingly doesn't even provide a function for that; it's customarily
implemented as a user-defined function. Since we're not currently in
the habit of installing custom functions for Django, the means of
accomplishing that is an open question. As a stopgap measure, we
could implement `x | y` as `x + y - (x & y)`, with all the usual
caveats that double evaluation implies.
Regards,
Ian
Alex Gaynor
I think the best way to approach this might be the same way we do lookup types, just a dict on the ops class. Further, I think that just doing the double evaluation way is probably the best solution in terms of ease of use for users.
Alex
--
"I disapprove of what you say, but I will defend to the death your right to say it." --Voltaire
"The people's good is the highest law."--Cicero
Russell Keith-Magee
>
> On Thu, Jan 29, 2009 at 4:14 AM, Russell Keith-Magee
> <freakb...@gmail.com> wrote:
>>
>> Hi all,
>>
>> With [9792], I've committed F() query expressions to trunk. For
>> details, see the docs:
>
> Hi Russell,
>
> First, my apologies for not testing this with Oracle sooner. It
No problems Ian.
> appears that nobody else tested it either, because there are some
> issues - specifically, Oracle doesn't have the expected %, &, and |
> operators.
Can you clarify what you mean here - is it case of 'doesn't have these
operators in any form', or 'has these operators, but uses different
characters/functions? You answer this below for the bitwise operators,
but what about %? How does Oracle implement mod?
> In order to get this working, it looks like we're going to need to do
> some refactoring to push the expression evaluation code into
> connection.ops, since currently 1) the operators are hard-coded into
> the ExpressionNode class, and 2) the evaluation needs to be flexible
> enough to allow function calls in place of operators. Do you have any
> suggestions for approaching this task?
To a limited extent, we've actually already hit this problem with
Postgres. Postgres doesn't support the % operator with floats, for
example, whereas SQLite and MySQL do. The decision that was made at
the time:
http://groups.google.com/group/django-developers/browse_thread/thread/c8cff7e5e16c692a
was to accept that the backends all have different capabilities, and
while complete backend independence is a laudable goal, it just isn't
practical. We already have some backend-specific incompatibilities
(such as the handling of case sensitivity on some MySQL collations,
and the forward-referencing problem with MySQL)
However, your idea about pushing this evaluation into connection.ops,
and providing the ability to use function calls in addition to simple
infix notation sounds like a good idea to me. A separate thread has
already raised similar questions in the context of operations on date
fields (e.g., add 3 days). Some backends may require function calls to
handle date expressions. Operations on strings are also possible
candidates here.
> The bit-wise or operator presents a third obstacle, since Oracle
> annoyingly doesn't even provide a function for that; it's customarily
> implemented as a user-defined function. Since we're not currently in
> the habit of installing custom functions for Django, the means of
> accomplishing that is an open question. As a stopgap measure, we
> could implement `x | y` as `x + y - (x & y)`, with all the usual
> caveats that double evaluation implies.
If we can't find a simple workaround, I'm happy to put the bitwise
operators into the 'Oracle doesn't support it' bucket, and make a
special case of the tests. Bitwise operators are a bit of an edge case
- making sure the major arithmetic operators work is more important.
Yours,
Russ Magee %-)
Justin Bronn
> However, based on some initial discussions with Justin, it should be
> possible to make these changes without affecting any public API. If
> anyone with access and experience with contrib.GIS cares to help out
> on this front, assistance would be gratefully accepted; otherwise,
> we'll just have to wait until Justin surfaces from his Bar exams.
TypeError is raised when one tries to use F() on a geographic field.
I've got a preliminary patch that fixes F() expressions for GIS
fields:
http://code.djangoproject.com/ticket/10159
Hopefully some people can bang on it and create improvements for the
patch, including maybe a test case or two.
-Justin
Ian Kelly
<freakb...@gmail.com> wrote:
> Can you clarify what you mean here - is it case of 'doesn't have these
> operators in any form', or 'has these operators, but uses different
> characters/functions? You answer this below for the bitwise operators,
> but what about %? How does Oracle implement mod?
Modulo is implemented as `mod(x, y)`. Bit-wise and is implemented as
`bitand(x, y)`. Bit-wise or is, as noted, not built in.
> To a limited extent, we've actually already hit this problem with
> Postgres. Postgres doesn't support the % operator with floats, for
> example, whereas SQLite and MySQL do. The decision that was made at
> the time:
>
> http://groups.google.com/group/django-developers/browse_thread/thread/c8cff7e5e16c692a
>
> was to accept that the backends all have different capabilities, and
> while complete backend independence is a laudable goal, it just isn't
> practical. We already have some backend-specific incompatibilities
> (such as the handling of case sensitivity on some MySQL collations,
> and the forward-referencing problem with MySQL)
>
> However, your idea about pushing this evaluation into connection.ops,
> and providing the ability to use function calls in addition to simple
> infix notation sounds like a good idea to me. A separate thread has
> already raised similar questions in the context of operations on date
> fields (e.g., add 3 days). Some backends may require function calls to
> handle date expressions. Operations on strings are also possible
> candidates here.
This makes sense. I see no reason not to get % and & working, since
there's nothing fundamentally preventing it. Bit-wise or is lower
priority, and while we could emulate it with the formula I mentioned
before, a savvy user could employ the same formula without modifying
Django at all.
Ian
Malcolm Tredinnick
> > 2) F() expressions have not been updated to work with GIS fields.
> > However, based on some initial discussions with Justin, it should be
> > possible to make these changes without affecting any public API. If
> > anyone with access and experience with contrib.GIS cares to help out
> > on this front, assistance would be gratefully accepted; otherwise,
> > we'll just have to wait until Justin surfaces from his Bar exams.
>
> Currently, nothing is broken and all tests pass -- only that a
> TypeError is raised when one tries to use F() on a geographic field.
The main issue I flagged to Russell in some code review notes was
whether the code needed to be able to handle get_placeholder() output,
instead of hard-coding "%s" everywhere for parameters. If there were
particular column comparisons that required a more massaged approach to
getting the data out of parameters in some cases and if we had to feed
the placeholder functions to the Evaluator or something similar.
Maybe we don't -- at least, not right now and not for GIS -- which is
fine, too.
Regards,
Malcolm
Justin Bronn
> whether the code needed to be able to handle get_placeholder() output,
> instead of hard-coding "%s" everywhere for parameters. If there were
> particular column comparisons that required a more massaged approach to
> getting the data out of parameters in some cases and if we had to feed
> the placeholder functions to the Evaluator or something similar.
>
> Maybe we don't -- at least, not right now and not for GIS -- which is
> fine, too.
reference to column was needed, but that was the simple case and I
neglected the situation where one geo column could be in a different
projection than the one referred to in the F expression. Thus,
transformation SQL could actually needed (what `get_placeholder` does,
but on INSERT and UPDATE), and I've since updated to a newer patch and
added some tests. That was a nice break, now back to studying...
-Justin
> Regards,
> Malcolm