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

[greenRocker]

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)

[greenRocker]

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

[Anssi Kääriäinen]

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.

[anonymous]

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

[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.

[Anssi Kääriäinen]

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?

[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=)

[anonymous]

Previous snippet wrong, correct syntax:

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

[Tim Graham]

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)

[Simon Charette]

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

[Anssi Kääriäinen]

Yes, adding a test would be good.

[Simon Charette]

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.

[Simon Charette <charette.s@…>]

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.

[Pauli Sundberg]

Patch for Django 1.4.14 to fix #21001

[Pauli Sundberg]

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.

[Tim Graham]

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