Opened 11 years ago

Closed 10 years ago

Last modified 10 years ago

#21001 closed Bug (fixed)

Non working SQL generated for Oracle when doing .exclude('')

Reported by: greenRocker Owned by: nobody
Component: Database layer (models, ORM) Version: 1.4
Severity: Normal Keywords: oracle
Cc: Simon Charette Triage Stage: Accepted
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

I have a query to exclude all entries with an empty char field. This works as expected on sqlite and Postgres, but fails on Oracle.
On Oracle the exclude does not exclude a single item.

valid_accounts = get_bank_accounts().exclude(account_number='')

SQL generated for Postgres looks like this (the exclude part only):

AND NOT ("accounts"."account_number" = ))

On Oracle, it looks like this:

AND NOT (("ACCOUNTS"."ACCOUNT_NUMBER" IS NULL
AND "ACCOUNTS"."ACCOUNT_NUMBER"       IS NOT NULL))

So, in order to work on Oracle, I have to resort to:

valid_accounts = get_bank_accounts().filter(account_number__gte=1)

Attachments (2)

21001.diff (589 bytes ) - added by Tim Graham 10 years ago.
oracle_bug_21001_django_1.4.14.diff (1019 bytes ) - added by Pauli Sundberg 10 years ago.
Patch for Django 1.4.14 to fix #21001

Download all attachments as: .zip

Change History (17)

comment:1 by greenRocker, 11 years ago

NOTE: account_number is defined as
account_number = models.CharField(_("Account number"), max_length=200, blank=True)

comment:2 by Anssi Kääriäinen, 11 years ago

Resolution: invalid
Status: newclosed

Oracle treats "" and NULL similarly. So, for Oracle the above query is equivalent to .exclude(account_number__isnull=True), that is account_number IS NOT NULL. This is basically a limitation in Oracle, and I don't see anything Django can do here.

comment:3 by anonymous, 11 years ago

But what would the above code .exclude(account_number__isnull=True) do on Postgres?

comment:4 by anonymous, 11 years ago

akaariai, as this sql is generated by Django, would it not be considered a bug in Django's oracle sql backend?

AND NOT (("ACCOUNTS"."ACCOUNT_NUMBER" IS NULL
AND "ACCOUNTS"."ACCOUNT_NUMBER"       IS NOT NULL))

(The IS NULL and IS NOT NULL, will always give an empty set).

I.e., you would expect that if you have:

get_bank_accounts().exclude(account_number='')

Then the oracle sql backend should return all records that are not NULL. Instead it returns always an empty set.

comment:5 by Anssi Kääriäinen, 11 years ago

Resolution: invalid
Status: closednew

Sorry, I didn't read the query correctly. The query string seems problematic. This might be fixed in later Django versions already.

I can't currently test Oracle, so will leave this unreviewed. Is there any possibility you could test the same query with 1.6 pre-release versions?

comment:6 by Simon Charette, 11 years ago

Keywords: oracle added

comment:7 by anonymous, 11 years ago

In case it may help, the following line seems to work as expected and I'm now using it as a temporary workaround:

valid_accounts = get_bank_accounts().exclude(account_numberexact=)

comment:8 by anonymous, 11 years ago

Previous snippet wrong, correct syntax:

valid_accounts = get_bank_accounts().exclude(account_number__exact='')

by Tim Graham, 10 years ago

Attachment: 21001.diff added

comment:9 by Tim Graham, 10 years ago

Resolution: fixed
Status: newclosed

I confirmed with the attached test that this is fixed in master.

Query generated in 1.4:

SELECT "QUERIES_DUMBCATEGORY"."ID", "QUERIES_NAMEDCATEGORY"."DUMBCATEGORY_PTR_ID", "QUERIES_NAMEDCATEGORY"."NAME" FROM
"QUERIES_NAMEDCATEGORY" INNER JOIN "QUERIES_DUMBCATEGORY" ON ("QUERIES_NAMEDCATEGORY"."DUMBCATEGORY_PTR_ID" = "QUERIES_DUMBCATEGORY"."ID")
WHERE NOT (("QUERIES_NAMEDCATEGORY"."NAME" IS NULL AND "QUERIES_NAMEDCATEGORY"."NAME" IS NOT NULL))

Query generated in master:

SELECT "QUERIES_DUMBCATEGORY"."ID", "QUERIES_NAMEDCATEGORY"."DUMBCATEGORY_PTR_ID", "QUERIES_NAMEDCATEGORY"."NAME" FROM
"QUERIES_NAMEDCATEGORY" INNER JOIN "QUERIES_DUMBCATEGORY" ON ( "QUERIES_NAMEDCATEGORY"."DUMBCATEGORY_PTR_ID" = "QUERIES_DUMBCATEGORY"."ID")
WHERE NOT ("QUERIES_NAMEDCATEGORY"."NAME" IS NULL)

comment:10 by Simon Charette, 10 years ago

Cc: Simon Charette added

@akaariai do you think it might be worth adding the test case to the suite to spot future regressions?

comment:11 by Anssi Kääriäinen, 10 years ago

Yes, adding a test would be good.

comment:12 by Simon Charette, 10 years ago

Has patch: set
Resolution: fixed
Status: closednew
Triage Stage: UnreviewedAccepted

The patch makes sense but I unfortunately don't have an Oracle setup to test the provided patch on 1.4 and master thus I can't mark the patch as RFC.

comment:13 by Simon Charette <charette.s@…>, 10 years ago

Resolution: fixed
Status: newclosed

In 4ec6d281cdc67bee1c7ac272272ae500b600fe49:

Fixed #21001 -- Added a regression test for empty string exclusion on Oracle.

The issue was present in 1.4.x but has been reported to be fixed in master.

Thanks to @timgraham for the patch.

by Pauli Sundberg, 10 years ago

Patch for Django 1.4.14 to fix #21001

comment:14 by Pauli Sundberg, 10 years ago

Hi all.

I encountered the bug on Django 1.4.5-1+deb7u7, that is shipped with Debian 7. I attached what i belive is patch for the issue, i copy pasted it from Django-1.6.6.

I have Oracle backend in use, and can run futher tests if that is needed.

comment:15 by Tim Graham, 10 years ago

Per our supported versions policy, 1.4 is only receiving security fixes so this issue won't be fixed there.

Note: See TracTickets for help on using tickets.
Back to Top