Pulling data from DB into memory
Hello!
This Malcolm's reply in a nearby thread:
> Unless you're
> using really huge querysets, the memory usage is not going to kill you.
> Pulling back the huge number of results already uses a bunch of memory
> and that's a property of the db wrapper.
... has reminded me that this behavior was bothering me for some time.
In fact pulling everything into a local memory on .execute('select') is
not an inherent property of a db wrapper. MySQLdb and psycopg2 can do
server-side cursors and I'm pretty sure Oracle's driver is as able.
There even was a discussion about this feature [1] where Malcolm has
raised some practical questions. I believe this feature is very useful
in some cases where one wants to iterate over a queryset and count
something or dump its content into another storage. So is there an
interest in implementing this behavior to be controllable from queryset
interface?
[1]:
http://groups.google.com/group/django-developers/browse_frm/thread/f7b78b399c478cf
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en
-~--~~~~--~~--~--~---
Re: Model-validation: call for discussions
The last unsolved model-validation design issue is the error message
protocol (my work on fields is waiting on it). Let me present the
approach that looks sane to me.
The old Field.default_error_messages dict and corresponding
logic is no longer required (and removed in my branch) as default
error messages live in core/validators now.
In field construction, the custom error logic is plain:
CharFied.__init__(self, ..., error_messages={}):
...
self.error_messages = error_messages
And it is used as follows:
class Form(...):
...
def full_clean(self):
...
for name, field in self.fields.items():
try:
...
# clean() calls to_python() and validate(),
# raising ValidationErrors
value = field.clean(value)
...
except ValidationError, e:
e.custom_messages = field.error_messages
self._errors[name] = self.error_class(e.messages)
The e.custom_messages = field.error_messages binds custom { code:
message } overrides to the ValidationError.
e.messages is a property that returns the list of error messages.
In reality, ValidationError contains only a single error message. The
list is required for aggregating several errors from the validator
collection associated with a field. However, as of now, the
ValidationError class tries to be both a single value and a
collection, resulting in quite a mess.
This is a classic case of the Composite pattern (http://
en.wikipedia.org/wiki/Composite_pattern ), so I propose that
* ValidationErrors remain single values
* core/validators gets an additional ValidationErrorComposite class
that derives from ValidationError and has the same interface
It will be used as follows (example for form field, model field
similar):
class Field(...):
...
def clean(self, value, all_values={}):
if value is not None:
value = self.to_python(value)
self.validate(value, all_values)
return value
def validate(self, value, all_values={}, form_instance=None):
value_is_empty = value in validators.EMPTY_VALUES
if self.required and value_is_empty:
raise ValidationError(_("This field is required."),
validators.REQUIRED)
error_collection = ValidationErrorComposite()
for validator in self.validators:
if value_is_empty and not getattr(validator, 'always_test',
False):
continue
try:
validator(value, all_values)
except ValidationError, e:
error_collection.add(e)
if error_collection:
raise error_collection
User code can remain oblivious of the composite, catching
ValidationErrors as before and using them as specified.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en
-~--~~~~--~~--~--~---
Re: Model-validation: call for discussions
On Feb 18, 2:28 pm, mrts wrote:
> def validate(self, value, all_values={}, form_instance=None):
That should have been
def validate(self, value, all_values={}):
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en
-~--~~~~--~~--~--~---
Revised 1.1 release plan
Hi folks -- By now I think it's obvious to everyone that we're behind on the 1.1 release. We (the core devs) have talked it over, and we're going to revise the plan for 1.1. In a nutshell: * Model validation will be dropped for 1.1 (it'll ship in 1.2 instead). * All other "must-haves" will be moved to "maybes." * The schedule slips a month: Django 1.1 will now be targeted for the week of April 13. Full details follow. Model validation The work on model validation simply isn't yet at a point where we feel comfortable merging it into trunk, and the work remaining to be done isn't trivial. There's a number of pervasive problems causing major backwards-incompatibilities which need to shake out, and after that it needs a *lot* of testing. I can't stress enough how grateful we are to the folks who put so much work into this -- Honza and mrts especially. The failure here is by no means on their part; the failure is ours for not providing more guidance. I'm the right person to blame for this feature missing 1.1: I should have found more time to devote to this work. We'll keep developing on this in parallel with the 1.1 release -- I'll be working to get a merge of the work done by Honza, mrts, Malcolm, and me into an SVN branch where it can get more eyes -- and we should be in good shape to get this in shortly after 1.1. I've already talked to a few folks who want to focus on this at the PyCon sprints next month. It's looking like model validation and 1.1 will be like aggregation and 1.0: a feature nearly done that just needs another release cycle for full maturity. Again, many thanks to those who've worked on this, and I'm sorry we couldn't make your work pay off for Django 1.1. Other features -- All other features on the to-do list are going to be considered "maybe" features. If they're done by the beta release and feature freeze (see below) they'll make it into 1.1; otherwise they won't. In retrospect we bit off more than we could chew here, and most of these aren't really make-or-break features anyway. I think with some focused work over the next few weeks we should be in good shape to get most of these done, though; many of them already have a patch in pretty good shape already. New schedule We've reorganized the schedule to give more time, and also to take advantage of the PyCon sprint days (3/29 - 4/2). We'll release the beta just before PyCon, sprint on fixing as many bugs as possible during PyCon, and push out the release candidate just after PyCon. Dates: * Alpha: 2/23 -- ship what we've got now plus a couple of small fixes to be checked in this week. * Beta: 3/20 -- feature freeze. * Release candidate: 4/2 -- total freeze and string freeze for translators. * Final release: 4/13, or ten days after the last RC if we need to do another. Let's do it! Jacob --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to django-developers@googlegroups.com To unsubscribe from this group, send email to django-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-developers?hl=en -~--~~~~--~~--~--~---
Re: Model-validation: call for discussions
Hi,
see inline text.
On Wed, Feb 18, 2009 at 1:28 PM, mrts wrote:
>
> The last unsolved model-validation design issue is the error message
> protocol (my work on fields is waiting on it). Let me present the
> approach that looks sane to me.
>
> The old Field.default_error_messages dict and corresponding
> logic is no longer required (and removed in my branch) as default
> error messages live in core/validators now.
What if I want to use the same error_messages on multiple instances of
a given field?
Now I would subclass the FormField and override it's default_messages,
with your approach it would require overriding __init__. I am not
saying that that is a bad thing, just that it still has a reason.
Besides not all validation will be done inside validators, some will
remain on the Field classes.
>
> In field construction, the custom error logic is plain:
>
> CharFied.__init__(self, ..., error_messages={}):
> ...
> self.error_messages = error_messages
>
> And it is used as follows:
>
> class Form(...):
> ...
> def full_clean(self):
> ...
> for name, field in self.fields.items():
> try:
> ...
> # clean() calls to_python() and validate(),
> # raising ValidationErrors
> value = field.clean(value)
> ...
> except ValidationError, e:
> e.custom_messages = field.error_messages
> self._errors[name] = self.error_class(e.messages)
>
> The e.custom_messages = field.error_messages binds custom { code:
> message } overrides to the ValidationError.
> e.messages is a property that returns the list of error messages.
>
> In reality, ValidationError contains only a single error message. The
> list is required for aggregating several errors from the validator
> collection associated with a field. However, as of now, the
> ValidationError class tries to be both a single value and a
> collection, resulting in quite a mess.
>
> This is a classic case of the Composite pattern (http://
> en.wikipedia.org/wiki/Composite_pattern ), so I propose that
> * ValidationErrors remain single values
> * core/validators gets an additional ValidationErrorComposite class
> that derives from ValidationError and has the same interface
What if then validators want to raise multiple messages? Of FormFields
for that matter?
for example:
"Must be greated than 1000."
"If field Y is supplied, X must be greater than Y."
"I don't like your shoes!"
Would they then raise ValidationErrorComposite instead? So you just
pushed the check to a different layer. I agree that it makes more
sense in it's new location, but the distinction still has to be made.
And how would ValidationErrorComposite.add(exception) work then? It
would have to check the exception type to know whether to append or
extend it's message list.
I agree that handling of different datat types (string, list or dict)
in ValidationError isn't pretty, but it IMHO greatly simplifies it's
usage and handling.
>
> It will be used as follows (example for form field, model field
> similar):
>
> class Field(...):
> ...
> def clean(self, value, all_values={}):
> if value is not None:
> value = self.to_python(value)
> self.validate(value, all_values)
> return value
>
> def validate(self, value, all_values={}, form_instance=None):
unrelated to custom messages - you cannot pass all_values to field for
validation, because at the time field validation is called, not all
fields have been cleaned. That's why validator handling is done in
(form/model).clean.
> value_is_empty = value in validators.EMPTY_VALUES
> if self.required and value_is_empty:
> raise ValidationError(_("This field is required."),
> validators.REQUIRED)
>
> error_collection = ValidationErrorComposite()
>
> for validator in self.validators:
> if value_is_empty and not getattr(validator, 'always_test',
> False):
> continue
> try:
> validator(value, all_values)
> except ValidationError, e:
> error_collection.add(e)
>
> if error_collection:
> raise error_collection
>
> User code can remain oblivious of the composite, catching
> ValidationErrors as before and using them as specified.
> >
>
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en
-~--~~~~--~~--~--~---
Re: Revised 1.1 release plan
> I've already talked to a few folks who want to focus > on this at the PyCon sprints next month. It's looking like model validation > and > 1.1 will be like aggregation and 1.0: a feature nearly done that just needs > another release cycle for full maturity. It's almost becomming a tradition for me to work on model-validation on python conference sprints (first version was done at PyCON last year, second on europython). I will most likely be at PyCON again, ready to code away :). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to django-developers@googlegroups.com To unsubscribe from this group, send email to django-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-developers?hl=en -~--~~~~--~~--~--~---
Re: Model-validation: call for discussions
On Feb 18, 8:03 pm, Honza Král wrote:
> Hi,
> see inline text.
>
> On Wed, Feb 18, 2009 at 1:28 PM, mrts wrote:
>
> > The last unsolved model-validation design issue is the error message
> > protocol (my work on fields is waiting on it). Let me present the
> > approach that looks sane to me.
>
> > The old Field.default_error_messages dict and corresponding
> > logic is no longer required (and removed in my branch) as default
> > error messages live in core/validators now.
>
> What if I want to use the same error_messages on multiple instances of
> a given field?
>
> Now I would subclass the FormField and override it's default_messages,
> with your approach it would require overriding __init__. I am not
> saying that that is a bad thing, just that it still has a reason.
Yup, design decision needed:
is the need for class-wide overrides of error messages common enough
to justify leaving it as-is (considering that this can still be done
via __init__)?
> Besides not all validation will be done inside validators, some will
> remain on the Field classes.
As of now, I've managed to weed out all validation from fields into
validators. The result is not entirely stable yet and it brings in
minor backwards-compatibility issues, so it remains to be seen if this
is entirely viable.
> > In field construction, the custom error logic is plain:
>
> > CharFied.__init__(self, ..., error_messages={}):
> > ...
> > self.error_messages = error_messages
>
> > And it is used as follows:
>
> > class Form(...):
> > ...
> > def full_clean(self):
> > ...
> > for name, field in self.fields.items():
> > try:
> > ...
> > # clean() calls to_python() and validate(),
> > # raising ValidationErrors
> > value = field.clean(value)
> > ...
> > except ValidationError, e:
> > e.custom_messages = field.error_messages
> > self._errors[name] = self.error_class(e.messages)
>
> > The e.custom_messages = field.error_messages binds custom { code:
> > message } overrides to the ValidationError.
> > e.messages is a property that returns the list of error messages.
>
> > In reality, ValidationError contains only a single error message. The
> > list is required for aggregating several errors from the validator
> > collection associated with a field. However, as of now, the
> > ValidationError class tries to be both a single value and a
> > collection, resulting in quite a mess.
>
> > This is a classic case of the Composite pattern (http://
> > en.wikipedia.org/wiki/Composite_pattern ), so I propose that
> > * ValidationErrors remain single values
> > * core/validators gets an additional ValidationErrorComposite class
> > that derives from ValidationError and has the same interface
>
> What if then validators want to raise multiple messages? Of FormFields
> for that matter?
> for example:
>
> "Must be greated than 1000."
> "If field Y is supplied, X must be greater than Y."
> "I don't like your shoes!"
In core/validators there are none that need to raise multiple error
messages.
> Would they then raise ValidationErrorComposite instead? So you just
> pushed the check to a different layer. I agree that it makes more
> sense in it's new location, but the distinction still has to be made.
Yes, if that need arises, they can throw a ValidationErrorComposite.
> And how would ValidationErrorComposite.add(exception) work then? It
> would have to check the exception type to know whether to append or
> extend it's message list.
I see no problem there.
def add(e):
if isinstance(e, ValidationErrorComposite):
add the collection of ValidationErrors to self.errors
else:
add the single e to self.errors
> I agree that handling of different datat types (string, list or dict)
> in ValidationError isn't pretty, but it IMHO greatly simplifies it's
> usage and handling.
Let's just leave it to core devs to decide.
> > It will be used as follows (example for form field, model field
> > similar):
>
> > class Field(...):
> > ...
> > def clean(self, value, all_values={}):
> > if value is not None:
> > value = self.to_python(value)
> > self.validate(value, all_values)
> > return value
>
> > def validate(self, value, all_values={}, form_instance=None):
>
> unrelated to custom messages - you cannot pass all_values to field for
> validation, because at the time field validation is called, not all
> fields have been cleaned. That's why validator handling is done in
> (form/model).clean.
Ah, sorry, there was just an unintentional omission in my example. It
should have obviously been as follows:
class Form(...):
...
def full_clean(self):
...
for name, field in self.fields.items():
try:
...
# self.cleaned_data was missing
value = field.clean(value, self.cleaned_data)
...
Re: Revised 1.1 release plan
On Wed, Feb 18, 2009 at 1:09 PM, Honza Král wrote: > > > I've already talked to a few folks who want to focus > > on this at the PyCon sprints next month. It's looking like model > validation and > > 1.1 will be like aggregation and 1.0: a feature nearly done that just > needs > > another release cycle for full maturity. > > It's almost becomming a tradition for me to work on model-validation > on python conference sprints (first version was done at PyCON last > year, second on europython). I will most likely be at PyCON again, > ready to code away :). > > > > Thanks for the update Jacob, I'd like to add, since I didn't get much feedback on it last time I posted to the mailing list, that Admin-0(or 1I forget how it's indexed) bulk edit on changelist, is more or less done as far as I can tell and needs some review now: http://github.com/alex/django/commits/admin-bulk-edits . 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 --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to django-developers@googlegroups.com To unsubscribe from this group, send email to django-developers+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/django-developers?hl=en -~--~~~~--~~--~--~---
Re: Pulling data from DB into memory
On Wed, 2009-02-18 at 11:20 +0300, Ivan Sagalaev wrote:
> Hello!
>
> This Malcolm's reply in a nearby thread:
>
> > Unless you're
> > using really huge querysets, the memory usage is not going to kill you.
> > Pulling back the huge number of results already uses a bunch of memory
> > and that's a property of the db wrapper.
>
> ... has reminded me that this behavior was bothering me for some time.
> In fact pulling everything into a local memory on .execute('select') is
> not an inherent property of a db wrapper. MySQLdb and psycopg2 can do
> server-side cursors and I'm pretty sure Oracle's driver is as able.
>
> There even was a discussion about this feature [1] where Malcolm has
> raised some practical questions.
So, once again, it was Jeremy stirring the pot and it's you keeping the
issue alive a couple of years later. Clearly I have to create a whole
new category of troublemaker in my little book. The current severity
levels don't go high enough. :-)
I guess you should be thanked for having a good memory, though. But
don't think that's getting you back in the good books.
> I believe this feature is very useful
> in some cases where one wants to iterate over a queryset and count
> something or dump its content into another storage. So is there an
> interest in implementing this behavior to be controllable from queryset
> interface?
>
> [1]:
> http://groups.google.com/group/django-developers/browse_frm/thread/f7b78b399c478cf
I think the comments and requests for information Younger Me made in
that thread are still valid. These days I would also throw in checking
how concurrent cursor behaviour on the same connection works. We already
know that SQLite cannot handle creating a new result set / cursor when
an existing cursor is only partially read and I wonder if there are
similar issues like that (again, it's related to lifecycle and resource
management of the server-side data).
As I wrote that previous paragraph, I also remembered [2], mentioning
unbuffered cursors in MySQLdb (at least in the future, as of a year ago)
and the need for one cursor at a time in that case. I haven't
investigated that any deeper than re-reading the post just now, but it's
something I would want to understand before making decisions about what
an API looks like here.
I suspect it is probably possible to make this work. How much it
complicates Django internals and userspace usage is something to
understand first, though.
[2] http://mysql-python.blogspot.com/2008/03/i-am-not-dead.html
Regards,
Malcolm
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en
-~--~~~~--~~--~--~---
Re: Model-validation: call for discussions
On Wed, 2009-02-18 at 04:28 -0800, mrts wrote:
> The last unsolved model-validation design issue is the error message
> protocol (my work on fields is waiting on it).
Well, it's the the last issue, but it's certainly a stumbling block.
I've been spending quite a lot of time lately trying to merge model
validation into trunk and, as you'll see in the 1.1. announcement, it's
not really there yet. There are a number of backwards incompatibilities
that have been introduced along the way and some of the API is fairly
hard to use.
> Let me present the
> approach that looks sane to me.
>
> The old Field.default_error_messages dict and corresponding
> logic is no longer required (and removed in my branch) as default
> error messages live in core/validators now.
It *is* still required. Firstly, it's a very useful way of specifying
holistic error messages. Secondly, removing it would be backwards
incompatible in a major way (many custom fields would no longer work
properly, for example).
[...]
> In reality, ValidationError contains only a single error message. The
> list is required for aggregating several errors from the validator
> collection associated with a field.
> However, as of now, the
> ValidationError class tries to be both a single value and a
> collection, resulting in quite a mess.
Definitely. I've managed to simplify it somewhat, but it requires some
conventions. Part of the problems come from trying to make model field
validation and form field validation so alike in an area where it isn't
necessarily so. It turns out to be easier to have
exceptions.ValidationError be more dictionary-like by default, although
I'm still playing with the API a bit there.
There are two possibilities here. One is that if a default error message
exists for the field, we only display that when a validation error
occurs. Otherwise we aggregate the individual validation error messages.
The other approach is to always aggregate but we have the convention
that the default (field-wide) error message is descriptive, in the sense
that it describes what is expected ("must be an integer between 1 and
101"), whilst the individual validator error messages are more
proscriptive about the actual problem that has been found ("is not an
integer"). The latter always state a problem, not a requirement, so they
read well when aggregated with the description.
>
> This is a classic case of the Composite pattern (http://
> en.wikipedia.org/wiki/Composite_pattern ), so I propose that
> * ValidationErrors remain single values
> * core/validators gets an additional ValidationErrorComposite class
> that derives from ValidationError and has the same interface
That's pretty good analysis. I've done the same thing in the merged code
I've been doing, except slightly differently.
Django.core.exceptions.ValidationError is the composite collection of
error messages.
Django.forms.utils.ValidationError, which you guys had nuked, remains
the way to make a single HTML presentation of form field errors (that
had to go back anyway for backwards-compatibility reasons, since
otherwise every single custom field cleaning method would potentially
break, at a minimum). There's a constructor for forms.ValidationError
that takes an exceptions.ValidationError to do the conversion.
Presenting model field validation errors isn't something that we have a
default for, since they aren't tied to HTML output or anything like
that. If presenting them as HTML output, the forms.ValidationError class
could be used (that's how ModelForms would do it), but, for other cases,
pulling out the errors on an as-needs required
When I get out from under some work stuff this week, I'm going to commit
what I've been merging (it a merge of yours and Honza's git branches and
a lot of editing and review changes) into a subversion branch so that
more people can test it. I'm then happy to pull in and review changes
from git branches if you guys want to work on it there, but we need to
have this done in a more general way. We've all dropped the ball a bit
-- I thought the problems that were creeping in were going to simple to
fix up in review; I was wrong. It's the broken-windows problem, where
one little variation from "great" leads to another and another, without
realising it.
Regards,
Malcolm
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en
-~--~~~~--~~--~--~---
Re: Pulling data from DB into memory
Malcolm Tredinnick wrote:
> So, once again, it was Jeremy stirring the pot and it's you keeping the
> issue alive a couple of years later.
Well, the issue wasn't resolved. What could I do? :-)
> I think the comments and requests for information Younger Me made in
> that thread are still valid. These days I would also throw in checking
> how concurrent cursor behaviour on the same connection works. We already
> know that SQLite cannot handle creating a new result set / cursor when
> an existing cursor is only partially read and I wonder if there are
> similar issues like that (again, it's related to lifecycle and resource
> management of the server-side data).
I think we can solve the whole issue of different concurrency levels
within one connection among backends just by having more than one
connection. I.e. make a new connection for each queryset requested with
a server-side flag.
API may be as simple as:
MyModel.objects.all().server_side()
which sould trigger a flag in a queryset instance that is then passed
onto backends cursor() method. This will require changing all backends
to hold a list of opened connections but I thinks it's not too
complicated. Luckily there's no public API in Django for accessing
connection objects directly. There's db._connection but it has '_'
before it for a reason :-)
Another option is to reuse an old proposal of Simon Willison on .using()
method for querysets that switches it between several configured
databases. Then we can invent a new setting system for multiple DBs like:
DATABASE = {
'default': ...,
'huge_archive': {
'name': ...,
'user_server_side_cursor': True,
}
}
And then a queryset can `.using('huge_archive')` to use server-side
cursors. But I like this thing less because it's more complex and it
confuses into one method slightly different things.
> As I wrote that previous paragraph, I also remembered [2], mentioning
> unbuffered cursors in MySQLdb (at least in the future, as of a year ago)
> and the need for one cursor at a time in that case.
I believe Andy was talking about MySQLdb > 1.2 but we're still on 1.2 so
it's not something I'd consider here.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en
-~--~~~~--~~--~--~---
Re: #10290: grouping with extra selects produces invalid SQL
On Tue, 2009-02-17 at 18:15 -0700, Ian Kelly wrote:
> On Tue, Feb 17, 2009 at 5:54 PM, Malcolm Tredinnick
> wrote:
> >
> > Hi Ian,
> >
> > On Tue, 2009-02-17 at 16:05 -0700, Ian Kelly wrote:
> > [...]
> >> The solution that I'm proposing is to use the extra select expression
> >> itself in the group by, rather than the alias. This passes the tests
> >> across all four included backends, and seems to work in general as
> >> long as the expression is not a scalar subquery (in which case I think
> >> a general solution would require advanced inspection of the
> >> expression). But it bothers me a bit because it just feels fragile.
> >
> > I'm pretty tired today and under a pile of past-urgent things to do, so
> > excuse the request for training wheels on this one... can you give a
> > short SQL example of what you're talking about here (before vs. after).
>
> No problem. Take the queryset
> `Author.objects.extra(select={'sqrt_age':
> 'sqrt(age)'}).values('sqrt_age').annotate(Count('book'))`
>
> Current SQL: SELECT (sqrt(age)) AS "SQRT_AGE",
> COUNT("AGGREGATION_REGRESS_BOOK_AC81D"."BOOK_ID") AS "BOOK__COUNT"
> FROM "AGGREGATION_REGRESS_AUTHOR" LEFT OUTER JOIN
> "AGGREGATION_REGRESS_BOOK_AC81D" ON ("AGGREGATION_REGRESS_AUTHOR"."ID"
> = "AGGREGATION_REGRESS_BOOK_AC81D"."AUTHOR_ID") GROUP BY "SQRT_AGE"
>
> Proposed SQL: SELECT (sqrt(age)) AS "SQRT_AGE",
> COUNT("AGGREGATION_REGRESS_BOOK_AC81D"."BOOK_ID") AS "BOOK__COUNT"
> FROM "AGGREGATION_REGRESS_AUTHOR" LEFT OUTER JOIN
> "AGGREGATION_REGRESS_BOOK_AC81D" ON ("AGGREGATION_REGRESS_AUTHOR"."ID"
> = "AGGREGATION_REGRESS_BOOK_AC81D"."AUTHOR_ID") GROUP BY (sqrt(age))
Ah, ok, I understand what you're talking about now. I don't see anything
horribly bad about this change.
To be SQL spec-compliant, we would have to do this. In addition to
Oracle, SQL Server can't handle aliases in GROUP BY. PostgreSQL can only
do it sometimes (see [1], which it turns out I'd bookmarked a couple of
years ago) and acknowledge it's a bit fragile to use aliases.
I'm not particularly worried about Alex's concern of backend optimisers
that for some reason support aliases, but can't do common sub-expression
optimisation in exactly the same case. They're just broken, if they
exist at all. So lacking concrete evidence, I'd choose not to be scared
by that right now (and keep the internals as simple as possible).
Any particular grounds for doing something spec-compliant feeling
fragile to you, Ian? Having let this bounce around for 24 hours, I can't
think of a strong reason not to do it.
[1] http://archives.postgresql.org/pgsql-sql/2004-02/msg00143.php
Regards,
Malcolm
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
"Django developers" group.
To post to this group, send email to django-developers@googlegroups.com
To unsubscribe from this group, send email to
django-developers+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/django-developers?hl=en
-~--~~~~--~~--~--~---
