Code

#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 (1)

21001.diff (589 bytes) - added by timo 10 months ago.

Download all attachments as: .zip

Change History (14)

comment:1 Changed 10 months 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 10 months 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 10 months ago by anonymous

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

comment:4 Changed 10 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 10 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 10 months ago by charettes

  • Keywords oracle added

comment:7 Changed 10 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 10 months ago by anonymous

Previous snippet wrong, correct syntax:

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

Changed 10 months ago by timo

comment:9 Changed 10 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 10 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 10 months ago by akaariai

Yes, adding a test would be good.

comment:12 Changed 10 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 10 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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.