Opened 2 years ago

Closed 22 months ago

Last modified 11 months 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: charettes 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 timo 22 months ago.
oracle_bug_21001_django_1.4.14.diff (1019 bytes) - added by susundberg 11 months ago.
Patch for Django 1.4.14 to fix #21001

Download all attachments as: .zip

Change History (17)

comment:1 Changed 2 years ago by greenRocker

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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

comment:2 Changed 2 years ago by akaariai

  • Resolution set to invalid
  • Status changed from new to closed

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 Changed 2 years ago by anonymous

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

comment:4 Changed 23 months ago by anonymous

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 Changed 23 months ago by akaariai

  • Resolution invalid deleted
  • Status changed from closed to new

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 Changed 23 months ago by charettes

  • Keywords oracle added

comment:7 Changed 23 months ago by anonymous

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 Changed 23 months ago by anonymous

Previous snippet wrong, correct syntax:

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

Changed 22 months ago by timo

comment:9 Changed 22 months ago by timo

  • Resolution set to fixed
  • Status changed from new to closed

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 Changed 22 months ago by charettes

  • Cc charettes added

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

comment:11 Changed 22 months ago by akaariai

Yes, adding a test would be good.

comment:12 Changed 22 months ago by charettes

  • Has patch set
  • Resolution fixed deleted
  • Status changed from closed to new
  • Triage Stage changed from Unreviewed to Accepted

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 Changed 22 months ago by Simon Charette <charette.s@…>

  • Resolution set to fixed
  • Status changed from new to closed

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.

Changed 11 months ago by susundberg

Patch for Django 1.4.14 to fix #21001

comment:14 Changed 11 months ago by susundberg

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 Changed 11 months ago by timgraham

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