sql: support Postgres syntax names for all types

[jordanlewis]

We should consider supporting the Postgres syntax for all of the types whose Postgres OID we support via pgwire and claim to support via pg_catalog.pg_type.

For instance, our integer type is named INT, but we support the int2, int4, and int8 types via their OID in pgwire and in pg_type. We should consider supporting using the type names INT2, INT4, and INT8 as syntax in SQL statements. They would just be aliases for our INT type, the same way their OIDs are, since we don't support storing integers smaller than int8.

[bdarnell]

int4 is already tracked in #12481

[jordanlewis]

There are a few questions here. I have opinions on some.

First, do we care to implement right-sized backing encodings for smaller datatypes, or is backing these datatypes by full-sized ones sufficient? I think that for now the latter is okay.

Second, if we choose to not have different backing encodings for these types, do we care to preserve the user's intention when creating a table, or is it okay to just make them aliases at the parser level? In other words, if a user creates a table with an int4 column, do we need to remember that fact or is it okay to let the column appear for all intents and purposes as an ordinary int column? I think for the purposes of compatibility we do in fact need to preserve the intended types. See #16491 for an example of that - if Hibernate creates a table with int4 and we return an int8 for example, it may end up using the wrong Java type to represent values in that column -which will break user assumptions and programs.

Third, if we are going to preserve the user-intended types (as I think we have to), do we need to have separate type tags (ColumnType.Kind) for these types, or should we introduce some kind of aliasing mechanism at the ColumnType proto level? I don't know the answer to this - either would work okay. The downside to having separate type tags is that it's ugly (in my opinion) to have a lot of repeated, similar values there. The upside is that it might make the code cleaner and provide an easier migration path if we do decide to eventually store these values right-sized.

Fourth, are we going to truncate inputs to smaller datatypes? I think we probably should.

[justinj]

I agree on 1-3, if I understand what you mean for 4 it's also an option to just error out if a value is out of range. It's what Postgres does:

# create table b (a int4);
# insert into b values (100000000000);
ERROR:  integer out of range

I suspect if someone cares to explicitly use int4 (whether "someone" is a user or an ORM) they probably want to be alerted if the values they're inserting don't match that specification.

[jordanlewis]

Oh, yeah - that makes a lot more sense. Forget truncation - let's just error! 👍

[m-schneider]

I agree on 1-3 too and error is a great idea!

If we choose to go this way vs making them aliases, what would be the logic for mathematical operations on them? If int4*int4 can't fit into an int4, do we error or return the value?

[justinj]

Good question, FWIW, Postgres errors:

# select 1000000000::int4 * 10::int4;
ERROR:  integer out of range

But I think either sounds fine to me? Since we'd still error if we try to actually tried to insert it into a column.

[jordanlewis]

Good question. There's another decision we have to make about this project that I didn't mention about: are we going to represent all of these types differently at the Datum level as well?

Currently, we only have one kind of integer datum: DInt. I was assuming that we wouldn't add any new datum types for this project - this would mean that the in-memory representation of integers (or floats, same goes there) of any kind would not change. Validations would be performed at the edges of the system - upon user input, at the point of marshalling into bytes before we store, and upon output to the user as well.

I think that adding new datum types for all of these will be a pretty big pain - I advise against it. The reason that it'll be painful has to do with the way we've implemented comparison and binary operators - check out eval.go for that code. We'd either need to add implementations for all of the different cross-type comparisons and operators we'd need, which is unideal, or totally change how that implementation works.

Instead, I think that ignoring potential smaller-type overflows during math operations is the way to go. It will produce different behavior from Postgres in certain cases, but I think it's an acceptable tradeoff. For example, the following errors in Postgres, but wouldn't error in our implementation:

test=# select (9000::int2 * 9000::int2 / 9000::int2)::int2;
ERROR:  22003: smallint out of range
LOCATION:  int2mul, int.c:845

[m-schneider]

I agree that adding new datums or wrapping DInt into something like OidWrapper might not be worth it for math operations. I was however planning on enforcing the types on Insert and Update because we do have the column descriptors at that stage.

@knz what are your thoughts?

[knz]

I think I am seeing wisdom in TiDB's approach, where all the datums are implemented via the same struct. In that struct there is a SQL type field and then next to this multiple fields of each Go type, with only one that carry the value per SQL type.
The advantages are numerous:

• just one struct, it makes it easy(ier) to add new types later
• we can support precision/width for any type where it makes sense without too much code duplication
• much better allocation (and reuse) story

How would this make you feel? (I suggest you have a brief look at the TiDB source code to see what I mean)

[knz]

(incidentally, this would be walking a path towards a common endpoint with the IR work David was undertaking)