Re: Doing better at HINTing an appropriate column within errorMissingColumn()

On Mon, Nov 17, 2014 at 3:04 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> postgres=# select qty from orderlines ;
> ERROR: 42703: column "qty" does not exist
> LINE 1: select qty from orderlines ;
> ^
> HINT: Perhaps you meant to reference the column "orderlines"."quantity".
> """

I don't buy this example, because it would give you the same hint if
you told it you wanted to access a column called ant, or uay, or tit.
And that's clearly ridiculous. The reason why quantity looks like a
reasonable suggestion for qty is because it's a conventional
abbreviation, but an extremely high percentage of comparable cases
won't be.

>> + /*
>> + * Charge extra (for inexact matches only) when an alias was
>> + * specified that differs from what might have been used to
>> + * correctly qualify this RTE's closest column
>> + */
>> + if (wrongalias)
>> + rtestate.distance += 3;
>>
>> I don't understand what situation this is catering to. Can you
>> explain? It seems to account for a good deal of complexity.
>
> Two cases:
>
> 1. Distinguishing between the case where there was an exact match to a
> column that isn't visible (i.e. the existing reason for
> errorMissingColumn() to call here), and the case where there is a
> visible column, but our alias was the wrong one. I guess that could
> live in errorMissingColumn(), but overall it's more convenient to do
> it here, so that errorMissingColumn() handles things almost uniformly
> and doesn't really have to care.
>
> 2. For non-exact (fuzzy) matches, it seems more useful to give one
> match rather than two when the user gave an alias that matches one
> particular RTE. Consider this:
>
> """
> postgres=# select ordersid from orders o join orderlines ol on
> o.orderid = ol.orderid;
> ERROR: 42703: column "ordersid" does not exist
> LINE 1: select ordersid from orders o join orderlines ol on o.orderi...
> ^
> HINT: Perhaps you meant to reference the column "o"."orderid" or the
> column "ol"."orderid".
> LOCATION: errorMissingColumn, parse_relation.c:3166
>
> postgres=# select ol.ordersid from orders o join orderlines ol on
> o.orderid = ol.orderid;
> ERROR: 42703: column ol.ordersid does not exist
> LINE 1: select ol.ordersid from orders o join orderlines ol on o.ord...
> ^
> HINT: Perhaps you meant to reference the column "ol"."orderid".
> LOCATION: errorMissingColumn, parse_relation.c:3147
> """

I guess I'm confused at a broader level. If the alias is wrong, why
are we considering names in this RTE *at all*?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company