[Django] #27257: JSONB regex and iregex
Django
----------------------------------------------+----------------------------
Reporter: Nick Stefan | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: 1.9
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 1 | UI/UX: 0
----------------------------------------------+----------------------------
Given a model with a jsonb field, 'content', and it has a key called
'altText'.
{{{
queryset.filter(content__altText__iregex="bob")
}}}
creates this SQL
{{{
"content" -> 'altText'::text ~* 'bob'
}}}
Which creates a type error
{{{
ERROR: operator does not exist: jsonb ~* unknown at character 2792
postgres | HINT: No operator matches the given name and argument
type(s). You might need to add explicit type casts.
}}}
SQL actually needed is
{{{
content ->> altText ~* 'bob'
}}}
So I had to use:
{{{
queryset.extra(where=['content ->> %s ~* %s'], params=['altText', 'bob'])
}}}
Is this something to be filed under QuerySet.extra or is this more of a
bug just with JSONField ?
--
Ticket URL: <https://code.djangoproject.com/ticket/27257>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
----------------------------------+------------------------------------
Reporter: Nick Stefan | Owner: nobody
Component: contrib.postgres | Version: 1.9
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+------------------------------------
Changes (by Tim Graham):
* needs_better_patch: => 0
* component: Database layer (models, ORM) => contrib.postgres
* needs_tests: => 0
* easy: 1 => 0
* needs_docs: => 0
* type: Uncategorized => Bug
* stage: Unreviewed => Accepted
Comment:
Is the discussion from #26511 relevant here? I'd at least call it a bug
that a lookup is available that crashes.
--
Ticket URL: <https://code.djangoproject.com/ticket/27257#comment:1>
Django
----------------------------------+------------------------------------
Component: contrib.postgres | Version: 1.9
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------+------------------------------------
Comment (by Nick Stefan):
Replying to [comment:1 Tim Graham]:
> Is the discussion from #26511 relevant here? I'd at least call it a bug
that a lookup is available that crashes.
Yes! That other discussion is relevant. Especially this comment:
https://code.djangoproject.com/ticket/26511#comment:10
The error I got was distinct, but the needed solution is similar.
--
Ticket URL: <https://code.djangoproject.com/ticket/27257#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: Nick Stefan | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* keywords: QuerySet.extra =>
* owner: nobody => Simon Charette
* version: 1.9 => master
* status: new => assigned
Comment:
The operator precedence crash was fixed by
2eb7d6e6d41480f21305fc6abe2f1a443491ddec but we should really be using the
`->>` for text lookups as `::text` will use the text representation of the
accessed key.
--
Ticket URL: <https://code.djangoproject.com/ticket/27257#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: Nick Stefan | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):
* has_patch: 0 => 1
Comment:
[https://github.com/django/django/pull/7279 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/27257#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: Nick Stefan | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: contrib.postgres | Version: master
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/27257#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: Nick Stefan | Owner: Simon
| Charette
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"cecef94275118dc49a1b0d89d3ca9734e2ec9776" cecef94]:
{{{
#!CommitTicketReference repository=""
revision="cecef94275118dc49a1b0d89d3ca9734e2ec9776"
Fixed #27257 -- Fixed builtin text lookups on JSONField keys.
Thanks Nick Stefan for the report and Tim for the review.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27257#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: Nick Stefan | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Simon Charette <charette.s@…>):
In [changeset:"d976760260c2d8371c1535a7bdeba9a2e0568a34" d9767602]:
{{{
#!CommitTicketReference repository=""
revision="d976760260c2d8371c1535a7bdeba9a2e0568a34"
Fixed #27693, Refs #27257 -- Fixed iexact lookup on JSONField keys.
Thanks Harris Lapiroff for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27257#comment:7>
Django
-------------------------------------+-------------------------------------
Reporter: Nick Stefan | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Alym):
Problem repeats in django 3
--
Ticket URL: <https://code.djangoproject.com/ticket/27257#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Nick Stefan | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: contrib.postgres | Version: master
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by felixxm):
Alym, please don't edit old comments. If you want to report any regression
in Django 3+, create a new ticket.
--
Ticket URL: <https://code.djangoproject.com/ticket/27257#comment:9>