Django

Code

Ticket #708 (closed: fixed)

Opened 3 years ago

Last modified 4 months ago

search for meta.IPAddressField with postgresql backend is broken (admin)

Reported by: jhernandez Assigned to: nobody
Milestone: Component: Database wrapper
Version: SVN Keywords: postgres
Cc: kilian.cavalotti@lip6.fr, mattimustang@gmail.com, smileychris+django@gmail.com Triage Stage: Design decision needed
Has patch: 1 Needs documentation: 0
Needs tests: 0 Patch needs improvement: 0

Description

In the admin interface the search for meta.IPAddressField with a postgresql backend is broken due to a missing cast to text from inet fields:

My model is like this: 

class ConnectionType(meta.Model):
    name = meta.CharField(maxlength=20)

    def __repr__(self):
        return self.name

class Connection(meta.Model):
    connection_type = meta.ForeignKey(ConnectionType)
    source_ip = meta.IPAddressField()
    source_port = meta.PositiveIntegerField()
    destination_ip = meta.IPAddressField()
    destination_port = meta.PositiveIntegerField()
    state = meta.CharField(maxlength=20)

    def __repr__(self):
        return "%s:%d - %s:%d (%s)" % (self.source_ip, self.source_port, self.destination_ip, self.destination_port,
                                       self.state)

    class META:
        admin = meta.Admin(
            list_display = ('connection_type', 'source_ip', 'source_port', 'destination_ip', 'destination_port', 'state'),
            list_filter = ['connection_type'],
            search_fields = ['source_ip', 'destination_ip'],
            )

in the admin inteface the search for an IPv4 address fails with the following traceback: 

There's been an error:

Traceback (most recent call last):

  File "/usr/lib/python2.3/site-packages/django/core/handlers/base.py", line 71, in get_response
    response = callback(request, **param_dict)

  File "/usr/lib/python2.3/site-packages/django/contrib/admin/views/decorators.py", line 49, in _checklogin
    return view_func(request, *args, **kwargs)

  File "/usr/lib/python2.3/site-packages/django/contrib/admin/views/main.py", line 180, in change_list
    result_count = p.hits

  File "/usr/lib/python2.3/site-packages/django/core/paginator.py", line 67, in _get_hits
    self._hits = getattr(self.module, self.count_method)(**order_args)

  File "/usr/lib/python2.3/site-packages/django/utils/functional.py", line 3, in _curried
    return args[0](*(args[1:]+moreargs), **dict(kwargs.items() + morekwargs.items()))

  File "/usr/lib/python2.3/site-packages/django/core/meta/__init__.py", line 1144, in function_get_count
    cursor.execute("SELECT COUNT(*)" + sql, params)

  File "/usr/lib/python2.3/site-packages/django/core/db/base.py", line 10, in execute
    result = self.cursor.execute(sql, params)

ProgrammingError: ERROR:  operator does not exist: inet ~~* "unknown"
HINT:  No operator matches the given name and argument type(s). You may need to add explicit type casts.

SELECT COUNT(*) FROM firewall_connections WHERE (firewall_connections.source_ip ILIKE '%10.10.1%' OR firewall_connections.destination_ip ILIKE '%10.10.1%')

In the following thread of the post to the pgsql-bugs mailing list is the complete explanation of this "bug":

http://archives.postgresql.org/pgsql-bugs/2003-11/msg00165.php

on this message shows the proper query that should be used:

http://archives.postgresql.org/pgsql-bugs/2003-11/msg00167.php

this is the main reason:

http://archives.postgresql.org/pgsql-bugs/2003-11/msg00170.php

Attachments

708-1.diff (0.6 kB) - added by mattmcc on 11/17/07 17:04:54.

Change History

11/27/05 14:58:35 changed by adrian

The correct SQL would be this: 

select * from <table> where CAST(<field> as text) ilike <pattern>

In order to fix this, we would need the DB wrapper to have an extra per-field hook that would process values before using them in a LIKE statement. And on top of that, this would have to be database-engine-specific (only Postgres).

06/13/06 18:14:40 changed by adrian

#2149 was a duplicate.

08/29/06 05:42:20 changed by mattimustang@gmail.com

  • cc set to kilian.cavalotti@lip6.fr, mattimustang@gmail.com.
  • component changed from Admin interface to Database wrapper.

A simple fix for this would be to make the IPAddressField a char(15) like every other backend instead of being a special case as it is now of being {inet. In my own app I ended up using a CharField instead of IPAddressField because of this bug.

01/17/07 16:12:17 changed by

  • milestone deleted.

Milestone Version 1.0 deleted

01/17/07 23:04:36 changed by SmileyChris

  • cc changed from kilian.cavalotti@lip6.fr, mattimustang@gmail.com to kilian.cavalotti@lip6.fr, mattimustang@gmail.com, smileychris+django@gmail.com.
  • stage changed from Unreviewed to Accepted.

Matti, your idea sounds good. Want to provide a patch?

01/17/07 23:32:01 changed by Matthew Flanagan <mattimustang@gmail.com>

  • keywords set to postgres.
  • has_patch set to 1.

Chris,

This will be a backwards incompatible change for those users who are using postgres and IPAddressFields. 

Index: django/db/backends/postgresql/creation.py
===================================================================
--- django/db/backends/postgresql/creation.py   (revision 4347)
+++ django/db/backends/postgresql/creation.py   (working copy)
@@ -14,7 +14,7 @@
     'FloatField':        'numeric(%(max_digits)s, %(decimal_places)s)',
     'ImageField':        'varchar(100)',
     'IntegerField':      'integer',
-    'IPAddressField':    'inet',
+    'IPAddressField':    'char(15)',
     'ManyToManyField':   None,
     'NullBooleanField':  'boolean',
     'OneToOneField':     'integer',

01/17/07 23:36:48 changed by SmileyChris

  • stage changed from Accepted to Ready for checkin.

Thanks Matthew.

Core: I'm changing to "ready" for now. If you want more discussion, switch back to "design decision needed"

01/19/07 16:44:01 changed by jacob

  • stage changed from Ready for checkin to Design decision needed.

I'm setting this back to design decision needed since I'm not sure how to fix this exactly. We could:

# Disallow IP fields from searching # Switch to char(15) # Add casting

I'm not sure which is the right choice.

11/17/07 17:04:17 changed by mattmcc

Unless I'm missing something, implementing the field and backend specific cast has become a lot easier than when the ticket was opened. This would surely be better than dropping support for the inet field type.

11/17/07 17:04:54 changed by mattmcc

  • attachment 708-1.diff added.

12/02/07 12:00:39 changed by jacob

  • keywords changed from postgres to postgres qs-rf.
  • version set to SVN.

02/23/08 03:13:33 changed by mtredinnick

  • keywords changed from postgres qs-rf to postgres.

02/23/08 03:15:35 changed by mtredinnick

  • status changed from new to closed.
  • resolution set to fixed.

(In [7151]) Fixed #708 -- Fixed searching within IP fields on PostgreSQL. Based on a patch from Matt McClanahan?.

(follow-up: ↓ 14 ) 02/23/08 03:21:32 changed by mtredinnick

The commit in [7151] isn't the perfect solution, since it does the cast always, but it retains backwards-compatibility and fixes the problem in a reasonable fashion (we don't support field-vs-field comparisons yet). It'll do for now.

(in reply to: ↑ 13 ) 02/26/08 16:14:34 changed by adamcik

As I mentioned on the django-developer list I would recommend using "host(ip)" instead of "CAST(ip AS text)" as the cast will return the ip as an inet address, eg. 10.0.0.1/32, while the host function will return the ip part.

02/26/08 16:19:53 changed by jacob

  • status changed from closed to reopened.
  • resolution deleted.

I've reverted [7151] (in [7160]) while we figure this out more completely; see the django-dev thread for more.

02/26/08 17:12:48 changed by jacob

  • status changed from reopened to closed.
  • resolution set to fixed.

(In [7161]) Re-enable substring lookups for IP address fields in Postgres using HOST() Thanks for the suggestion, Thomas Adamcik. Fixes #708.

Add/Change #708 (search for meta.IPAddressField with postgresql backend is broken (admin))




Change Properties
Action