sql: error in create ... interleave due to type aliases

[danhhz]

From gitter: https://gitter.im/cockroachdb/cockroach?at=5ad7c30f5d7286b43a42e80a

+--------------+--------------------------------------------------------------------------+
|    Table     |                               CreateTable                                |
+--------------+--------------------------------------------------------------------------+
| marketplaces | CREATE TABLE marketplaces (                                              |
|              |                                                                          |
|              |     id INTEGER NOT NULL,                                                 |
|              |                                                                          |
|              |     marketplace STRING NOT NULL,                                         |
|              |                                                                          |
|              |     status INTEGER NOT NULL DEFAULT 0:::INT,                             |
|              |                                                                          |
|              |     CONSTRAINT "primary" PRIMARY KEY (id ASC),                           |
|              |                                                                          |
|              |     UNIQUE INDEX uq_marketplace (marketplace ASC),                       |
|              |                                                                          |
|              |     CONSTRAINT fk_status FOREIGN KEY (status) REFERENCES                 |
|              | marketplace_status (enum),                                               |
|              |                                                                          |
|              |     INDEX marketplaces_auto_index_fk_status (status ASC),                |
|              |                                                                          |
|              |     FAMILY "primary" (id, marketplace, status)                           |
|              |                                                                          |
|              | )                                                                        |
+--------------+--------------------------------------------------------------------------+
(1 row)

Time: 16.267986ms

root@c1.vultr.zuberglobal.com:26257/zuber> create table sellers ( marketplace int, id int, seller_marketplace_id string, primary key (marketplace, id) ) interleave in parent marketplaces (marketplace);
pq: declared interleaved columns (marketplace) must match type and sort direction of the parent's primary index (id)
root@c1.vultr.zuberglobal.com:26257/zuber> ```

This is unexpected because they do match.

[danhhz]

Aha, the issue here is that one table uses INTEGER and the other uses INT. I don't know much about our type aliases, but I think this should work and the code in question should be ignoring the VisibleType field of ColumnDescriptor.

[knz]

thanks for figuring the cause out. This seems closely related to #24686 .

[knz]

This has been fixed in #28690.

Will be available in 2.1.

[bithavoc]

was this included in 2.1? I'm running 2.1 stable and I'm still getting it:

Main table was created using INT, the child table is trying to use INT and I'm getting the mismatch error.

In order to fix it I had to a SHOW CREATE TABLE over the parent table to find out it really created it with INT8 data type, as soon as I used INT8 in the child table prefix key column then it worked.

[knz]

You are right, this is actually not entirely fixed yet. Thanks for noticing.

[knz]

@bobvawter you don't need to look at this issue specifically, but I'd like to take the opportunity to underline we need to entirely eradicate the current coltype called "INT" (and only keep the fixed-size types), because it would become ambiguous with the resolution rules we discussed last week.

[knz]

@bobvawter I think your recent PR has fixed this by doing the INT->INT8 substitution upfront. Can you verify and close this issue?

[knz]

This issue has indeed been fixed.