Re: Indexes not used in 7.1RC4: Bug?

Thomas Lockhart wrote:

> The parser does not know that your int4 constant "0" can be represented
> as an int2. Try
>
> SELECT * FROM access_log WHERE method_num = int2 '0';
>
> (note the type coersion on the constant; there are other ways of
> specifying the same thing).

Surely this is something that should be fixed. An int2 column ought to behave
exactly like an int4 with a CHECK() constraint forcing the value to be in
range.

In object oriented terms:

a smallint isA integer
a integer isA bigint

Likewise:

a integer isA smallint if it falls in -32768..32767
a bigint isA integer if it falls in -2147483648..2147483647

Similar promotion rules should apply for all other numeric types. Any floating
point value without a fractional part should be treated exactly like a big
integer.

The issues here are closely related to the 7.1 changes in INHERITS semantics.
If any operator treats a smaller precision (more highly constrained) type in a
materially different way than a compatible higher precision type, it is
fundamentally broken for exactly the same reason that we expect a query on a
super-class would be if if did not return all matching instances of every sub
class.

If a function is overloaded with multiple compatible scalar data types, the
database should be free to call any matching implementation after performing
an arbitrary number of *lossless* compatible type conversions.

i.e. if you have f(smallint), f(integer), and f(double) the actual function
called by f(0) should be undefined. The distinction between smallint '0',
integer '0', and double '0' is meaningless and should be explicitly ignored.

This is a little extreme, but I do not think it makes a lot of sense to
maintain semantic differences between different representations of the same
number. (Oracle certainly doesn't)

Any comments?

- Mark Butler