Presto: Improve certain types of insert query mismatched column type errors

[electrum]

Some cases that could be improved with specialized errors:

• Insert query has additional columns
• Insert query is missing columns
• Column list lengths are identical but at least one position has mismatched types

The error message should include the column names of the target table and the positions that have a mismatch. If applicable, the error location could be set to the first mismatched column in the insert list.

Example:

presto> insert into src values ('foo', 2);
Query ... failed: Insert query has mismatched column types: Table: (a varchar, *b varchar, c bigint, *d double), Query: (varchar, bigint, bigint, varchar)

[martint]

Any fixes for this should probably go in after the parametric varchar pull request (https://github.com/facebook/presto/pull/4316) is merged, which has some changes to the type validation logic of inserts.

[cosinequanon]

For the last one it would be more helpful if the error message told you which positions had the incorrect types

[electrum]

@cosinequanon good idea, I updated the description

[dbj8]

If possible, a line break in between Table and Query data might make scanning the differences easier on the eye.

for example now we have Table has blah blah blah. Query has blah blah blah.

This sucks when I make really big tables and even only 1 or two mistakes.

Changing it to the following could make it bit easier.
Table has blah blah blah.
Query has blah blah blah.

[electrum]

We should also see how this works from a usability perspective when there are 20+ columns and many errors. We might need to stop printing after N mismatches, use different formats depending on the counts, etc.

[electrum]

For mismatched types, a potentially better option for many columns is to print the name, expected type and actual type rather than printing all columns (and expecting the user to match it up).

[Daniel15]

I hit this recently... It would be good to have a better error message, as this is basically incomprehensible and I had to manually diff the two lists to get an idea of what was broken: