Opened 6 years ago

Closed 6 years ago

Last modified 6 years ago

#29155 closed Bug (fixed)

Using contains lookup with Substr causes modification of second parameter of Substr

Reported by: norac89 Owned by: Mariusz Felisiak
Component: Database layer (models, ORM) Version: 2.0
Severity: Normal Keywords:
Cc: Mariusz Felisiak Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Tim Graham)

My model:

class User(models.Model):
    field1 = models.CharField(max_length=120)
    field2 = models.CharField(max_length=120)

Tried querying the model to get User instances in which field2 start with the three first characters of field1.
Tried the following:

User.objects.filter(field2__startswith=Substr(F('field1'), 1, 3))


I m getting the following error

psycopg2.DataError: invalid input syntax for integer: "1%"
LINE 1: ...(REPLACE(REPLACE((SUBSTRING("test_user"."field1", '1%', 3)),...

The second parameter of the substr function seems to be replaced with '1%'.

Attachments (1)

29155-test.diff​ (1.1 KB ) - added by Tim Graham 6 years ago.

Download all attachments as: .zip

Change History (8)

comment:1 by Tim Graham, 6 years ago

Description: modified (diff)
Summary: Using contains field lookup with Substr database function cause modification of second parameter of Substr → Using contains lookup with Substr causes modification of second parameter of Substr
Triage Stage: Unreviewed → Accepted

I'm attaching a test for Django's test suite that fails (on PostgreSQL but not SQLite) as of ba37ee9ef882deb8e917f1cae0c586a0a275e731.

by Tim Graham, 6 years ago

Attachment: 29155-test.diff​ added

comment:2 by Mariusz Felisiak, 6 years ago

Cc: Mariusz Felisiak added

comment:3 by Mariusz Felisiak, 6 years ago

Owner: changed from nobody to Mariusz Felisiak
Status: new → assigned

comment:4 by Mariusz Felisiak, 6 years ago

Has patch: set

​PR

comment:5 by Tim Graham, 6 years ago

Triage Stage: Accepted → Ready for checkin

comment:6 by GitHub <noreply@…>, 6 years ago

Resolution: → fixed
Status: assigned → closed

In feb683c4:

Fixed #29155 -- Fixed crash when database functions are used with pattern lookups.

Thanks Tim Graham and Sergey Fedoseev for reviews.

comment:7 by Tim Graham <timograham@…>, 6 years ago

In ba3078c:

Refs #29155 -- Fixed LookupTests.test_pattern_lookups_with_substr() crash on Oracle.

Test introduced in feb683c4c2c5ecfb61e4cb490c3e357450c0c0e8 revealed
unexpected behavior on Oracle that allows concatenating NULL with string.

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