select on macaddr field type yields incorrect response
Jeff MacDonald (jam@dts.emich.edu) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
select on macaddr field type yields incorrect response
Long Description
I have a table with several hundred records. In this table is a field of type "macaddr". When I do a select on this table for a specific macaddress, I get incorrect results.
The specific query is:
select adapteraddress from users where adapteraddress='00:c0:f0:26:30:4c';
the results I get:
00:c0:f0:2b:30:4c
so, the 4th octet is being corrupted somehow.
I have tried various query strings to see if uppercase or lowercase, or dashes vs. colons between the octets might be causing the problem, but I get the same results every time. The server side is 6.5.3. I have duplicated this problem with the python module from the 7.0.2 distribution as well as with the pgsql utility (both 6.5.3 and 7.0.2)
the application that is using this table is in production, so I need to move carefully if the solution is to upgrade to 7.x.
At the moment the problem is preventing a user from registering their macaddress with our service, which is preventing them from using their internet connection from their residence hall room. I'd like to move as quickly as possible to get this resolved.. somehow.
thanks in advance.
Sample Code
No file was uploaded with this report
pgsql-bugs@postgresql.org writes:
so, the 4th octet is being corrupted somehow.
I have tried various query strings to see if uppercase or lowercase,
or dashes vs. colons between the octets might be causing the problem,
but I get the same results every time. The server side is 6.5.3.
There's a silly little typo in 6.5 that might cause this problem:
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/backend/utils/adt/mac.c,v
retrieving revision 1.13
retrieving revision 1.14
diff -u -r1.13 -r1.14
--- pgsql/src/backend/utils/adt/mac.c 1999/07/17 20:17:57 1.13
+++ pgsql/src/backend/utils/adt/mac.c 1999/12/16 01:30:49 1.14
@@ -1,7 +1,7 @@
/*
* PostgreSQL type definitions for MAC addresses.
*
- * $Id: mac.c,v 1.13 1999/07/17 20:17:57 momjian Exp $
+ * $Id: mac.c,v 1.14 1999/12/16 01:30:49 momjian Exp $
*/@@ -132,7 +132,7 @@
((unsigned long)((addr->a<<16)|(addr->b<<8)|(addr->c)))
#define lobits(addr) \
- ((unsigned long)((addr->c<<16)|(addr->e<<8)|(addr->f)))
+ ((unsigned long)((addr->d<<16)|(addr->e<<8)|(addr->f))) /*
* MAC address reader. Accepts several common notations.
If you don't want to update to 7.0 right now, you could just apply
this source patch instead. Note you will need to drop and recreate
any indexes on MAC columns after fixing this --- the real cause of
your observed result is incorrect ordering of the index due to
buggy behavior of the datatype's comparison routines.
regards, tom lane