Schema tests and .extra queryset method

[Maximiliano Robaina]

Hi,

Running schema tests I caught an issue, more precisely, in test_add_field_default_transform.

At the end, this test method is doing:

self.assertEqual(Author.objects.extra(where=["thing = 1"]).count(), 2)

The problem here is what in this where clause, the "thing" field must be quoted.

In firebird :

    SELECT * FROM AUTHOR WHERE thing = 1   <-- Here thing (in lowercase) and THING (in uppercase) are equivalent, are the same object

is different of:

   

    SELECT * FROM AUTHOR WHERE "thing" = 1   <--  field is quoted

For a more generic test I think we need to avoid use .extra method or another raw sql statement.

[Russell Keith-Magee]

Hi Maximiliano,

Thanks for letting us know. If you find a problem like this, the best way to report it is to open a ticket in Trac. That way we have a permanent record of the existence of the issue, and we can mark the issue as resolved when a fix is applied to the source tree. It also allows us to flag and triage specific issues so that we know they get addressed before a release.

Yours,

Russ Magee %-)

--
You received this message because you are subscribed to the Google Groups "Django developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/1d03c3b7-8f8e-4794-97bc-73cfcb699d07%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Shai Berger]

Hi Maximiliano,

The issue of case in quoted and un-quoted names in SQL is indeed a sad mess,
bad decisions made in the 1960s that we still need to live with today.

On Wednesday 21 May 2014 15:38:57 Maximiliano Robaina wrote:
>
> self.assertEqual(Author.objects.extra(where=["thing = 1"]).count(), 2)
>
> The problem here is what in this where clause, the "thing" field must be
> quoted.
>
> In firebird :
>
> SELECT * FROM AUTHOR WHERE thing = 1 <-- Here thing (in lowercase)
> and THING (in uppercase) are equivalent, are the same object
>
> is different of:
>
> SELECT * FROM AUTHOR WHERE "thing" = 1 <-- field is quoted
>

Quoting it uncoditionally would break the test on Oracle (which, by default,
turns all the names to uppercase). There is a feature-flag for it...

> For a more generic test I think we need to avoid use .extra method or
> another raw sql statement.

...but I agree -- an "extra" should not be needed at all in this specific test.
The Author model has the added column as a field.

Shai.

[Maximiliano Robaina]

El miércoles, 21 de mayo de 2014 21:06:22 UTC-3, Russell Keith-Magee escribió:

Hi Maximiliano,

Thanks for letting us know. If you find a problem like this, the best way to report it is to open a ticket in Trac. That way we have a permanent record of the existence of the issue, and we can mark the issue as resolved when a fix is applied to the source tree. It also allows us to flag and triage specific issues so that we know they get addressed before a release.

Yours,

Russ Magee %-)

Done! 

https://code.djangoproject.com/ticket/22683#ticket

[Maximiliano Robaina]

El jueves, 22 de mayo de 2014 03:24:12 UTC-3, Shai Berger escribió:

Hi Maximiliano,

The issue of case in quoted and un-quoted names in SQL is indeed a sad mess,
bad decisions made in the 1960s that we still need to live with today.

On Wednesday 21 May 2014 15:38:57 Maximiliano Robaina wrote:
>
> self.assertEqual(Author.objects.extra(where=["thing = 1"]).count(), 2)
>
> The problem here is what in this where clause, the "thing" field must be
> quoted.
>
> In firebird :
>
>     SELECT * FROM AUTHOR WHERE thing = 1   <-- Here thing (in lowercase)
> and THING (in uppercase) are equivalent, are the same object
>
> is different of:
>
>     SELECT * FROM AUTHOR WHERE "thing" = 1   <--  field is quoted
>

Quoting it uncoditionally would break the test on Oracle (which, by default,
turns all the names to uppercase). There is a feature-flag for it...

How this feature flag should works?

I see that Oracle backend is always making an upper in DatabaseOperations.quote_name, therefore, how does Oracle pass test like [1] where it is using field name in lowecase [2] ?

[1] https://github.com/django/django/blob/1.7b4/tests/schema/tests.py#L152

[2] https://github.com/django/django/blob/1.7b4/tests/schema/tests.py#L172

[Shai Berger]

Hi Maximiliano,

Sorry for the delay in responding to this.

On Thursday 22 May 2014 17:06:23 Maximiliano Robaina wrote:
> El jueves, 22 de mayo de 2014 03:24:12 UTC-3, Shai Berger escribió:
> >
> > Quoting it uncoditionally would break the test on Oracle (which, by
> > default, turns all the names to uppercase). There is a feature-flag for
> > it...
>
> How this feature flag should works?

It's called "uppercases_column_names". The way it should work is -- it should
be False for backends that don't change the case of names, and True for Oracle
(currently, it is actually reversed; fixing it is on my to-do, but I haven't
gotten to it yet).

> I see that Oracle backend is always making an upper in
> DatabaseOperations.quote_name, therefore, how does Oracle pass test like
> [1] where it is using field name in lowecase [2] ?
>
> [1] https://github.com/django/django/blob/1.7b4/tests/schema/tests.py#L152
> [2] https://github.com/django/django/blob/1.7b4/tests/schema/tests.py#L172
>

It lowercases the name back in introspection

https://github.com/django/django/blob/1.7b4/django/db/backends/oracle/introspection.py#L62

And yes, this behavior completely breaks things if you try to really use
lowercase in names. I am on record describing the Oracle backend's behavior as
"case-insane", but this cannot be changed without breaking backwards
compatibility.

(FWIW, I believe the SQL standard says that unquoted names should be case-
insensitive, which, in turn, means introspection more-or-less must be broken.
uppercasing unquoted input was standard back in the 1960s -- it was an easy
way to differentiate user input from computer output in a time when the
interface was a one-color, one-font teletype. Regretfully, we now need to deal
with the consequences.)

Shai.