Re: enums

Adding -hackers back to the list...

> -----Original Message-----
> From: Gregory Maxwell [mailto:gmaxwell(at)gmail(dot)com]
> Sent: Thursday, October 27, 2005 5:03 PM
> To: Jim Nasby
> Subject: Re: [HACKERS] enums
>
>
> On 10/27/05, Jim C. Nasby <jnasby(at)pervasive(dot)com> wrote:
> > On Thu, Oct 27, 2005 at 04:54:36PM -0400, Ted Rolle wrote:
> > > This little snippet is great! The only problem I see is
> that the enums must
> > > be consistent across all modules.
> > >
> > > What about loading a variable with a "default" value?
> Then it could be
> > > adjusted to 'play'.
> >
> > Huh? Sorry, but you completely lost me here...
> >
> > On another note, I noticed that the comparison operators seem to be
> > comparing the underlying numeric value used to store the
> enum, which is
> > wrong IMO. Consider:
> >
> > ENUM color '"red","blue","green"'
> > CREATE TABLE t (c color);
> > INSERT INTO t VALUES('blue');
> > INSERT INTO t VALUES('green');
> > INSERT INTO t VALUES('red');
> > SELECT c FROM t ORDER BY c;
> > red
> > blue
> > green
> >
> > That seems counter-intuitive. It's also exposing an implimentation
> > detail (that the enum is stored internally as a number).
>
> You could as equally say that it's ordering it by the order of the
> enum declaration, which seems quite reasonable to me.

I don't really see why that's considered reasonable, especially as a default. I could maybe see an argument for having a means to see this ordering, but IMO anything depending on that is broken. I don't think we should be making any guarantees about how enums are stored in the database (including ordering).

> Now, if you can multiply one enum with another or with an integer,
> that would be a bit odd. But the collation behavior seems quite sane
> to me.
>
> The behavior in mysql is the same:
>
> mysql> create table t (
> -> color enum ('red','blue','green')
> -> );
> mysql> INSERT INTO t VALUES ('blue');
> mysql> INSERT INTO t VALUES ('green');
> mysql> INSERT INTO t VALUES ('red');
> mysql> select color from t order by color;
> +-------+
> | color |
> +-------+
> | red |
> | blue |
> | green |
> +-------+
> 3 rows in set (0.04 sec)
>
> Hopefully we the end implimentation is a bit better than mysql and
> actually cares what you're inserting:
>
> mysql> select color*2 from t;
> +---------+
> | color*2 |
> +---------+
> | 4 |
> | 6 |
> | 2 |
> +---------+
> 3 rows in set (0.00 sec)
> mysql> INSERT INTO t VALUES (1);
> Query OK, 1 row affected (0.00 sec)
> mysql> INSERT INTO t VALUES ('monkey');
> Query OK, 1 row affected, 1 warning (0.00 sec)
> mysql> select color from t;
> +-------+
> | color |
> +-------+
> | blue |
> | green |
> | red |
> | red |
> | |
> +-------+
> 5 rows in set (0.00 sec)

Your examples show why I don't think it's a good idea to use MySQL as a guide for how to do enums.

If we do decide to include the concept of ordering in enums, then it should be fully supported and not just an artifact of our storage mechanism. This means supporting things like being able to re-order the accepted values in an enum. But like I said, I just don't see the use case for doing that.