Re: [HACKERS] indexes and floats

> Your explanation implies that here, the parser is converting to
>          select x from f8 where int4(x) = 500;
> which is wrong for the same accuracy-loss reasons you cite later.
> (And if that isn't what it's doing, what then?)

No, that isn't what it would do. The parser does know about a heirarchy
of built-in data types, and would not downgrade a float8 to an int4.
I've sent some e-mail about the new parser features, and have written
them up in doc/src/sgml/typeconv.sgml (but haven't generated a new html
version yet).

> I think it would be a good idea if someone actually dug into this
> and verified what's going on.  I have found some other cases that
> lead me to think there's more to this than we understand just yet.

I'm pretty sure that there is another thing happening, which is getting
in the way of using indices with your example using

  where lowercase(col) = lowercase('const')

> That's a good point.  Still, it would be nice if the system had some
> reasonable amount of smarts about the "primitive" types that the
> parser has constant syntax for.  In particular I think an automatic
> coercion of an int constant to float where needed would be a
> reasonable thing to expect.  That's not happening now, see my example
> above.

I agree with your points, but it already does exactly what you would
want. I don't see an example above illustrating this problem.

> > So, is there a problem to fix, or just documentation to write?
>
> This one is most certainly a bug:
>
> play=> select x from f4 where x = 500.0 :: float4;
> ERROR:  parser_typecast: cannot cast this expression to type 'float4'

Might be easy. I'll look at it... Oh, the current workaround is to
specify it as

  where x = '500.0'::float4;

or

  where x = float4(500.0);

> Beyond that, if I can force the right thing to happen by casting
> the constant to the type of the field, then I can live with it.
> I have seen a number of cases where the system wouldn't use an index
> even with a cast, however, so I'm not a happy camper yet.

Yeah, that was Vadim's example and he sez we need to add a bit of code
to get things working the way you'd expect.

                    - Tom