v2.0-beta.20180319: Regression in star queries

[charsleysa]

BUG REPORT

• What did you do?
  Executed the following query

select
    "myblueprint"."study_industry".*
from "myblueprint"."study_industry"

• What did you expect to see?
  All rows with all columns from the table.

• What did you see instead?
  The following error [42703] ERROR: no data source named "myblueprint.study_industry"

This worked fine in v1.1.6 but is now broken in v2.0-beta.20180319

[jordanlewis]

Hi @charsleysa,

For better Postgres compatibility, we've changed CockroachDB's name resolution to understand the concept of database schemas. This means that there's now another level in the name hierarchy, between database and table: the schema.

The default schema in CockroachDB (and the only one with user data, since we don't yet support custom schemas) is called public, like it is in Postgres. We insert public between the database and table in data sources, if one isn't specified, but it seems we don't do the same for render expressions.

To work around this, you may insert public between the database name and table name in your render expression:

select "myblueprint"."public"."study_industry".* from ...

We didn't quite realize that anyone was using fully qualified names in render expressions, otherwise we would have caught this. Why do you need to qualify the table by the database name, though? If you're connected to the database myblueprint, you do not need to qualify your table names with the database name.

cc @knz

[charsleysa]

Hi @jordanlewis,

Thanks for getting back quickly.

I am aware some of those changes were made but I was using the "myblueprint" part as the schema qualifier as a SQL generation library I was using kept placing "public" into queries.

I think I may have missed some information when initially creating the issue, the following query works in v1.1.6 AND the v2.0-beta:

select
    "myblueprint"."study_industry"."id",
    "myblueprint"."study_industry"."title"
from "myblueprint"."study_industry"

[jordanlewis]

Oh, I see. That seems like a bug. Here's a simple reproduction:

root@:26257/> use test;
SET

Time: 313.263µs

root@:26257/test> create table a (a int primary key);
CREATE TABLE

Time: 5.299591ms

root@:26257/test> select test.a.a from a;
+---+
| a |
+---+
+---+
(0 rows)

Time: 4.133979ms

root@:26257/test> select test.a.* from a;
pq: no data source named "test.a"

[knz]

I'll look into it when I am back.

[knz]

(technical information ahead - not relevant to understand the issue, but necessary to fix):

The proximate cause is that FindColumnInSource / FindSourceProvidingColumn assume that the given prefix (if any) must match one of the names provided by the source(s). Meanwhile, the source name undergoes qualification before its name is made available to column name resolution, so from the perspective of ColumnResolver there are only fully qualified sources with "public" in them.

To address this properly requires adding to FindColumnInSource / FindSourceProvidingColumn the same crdb 1.x compatibility fallback that is also present in table name resolution. However, achieving this change probably requires extra care (need to investigate what are the subtleties associated with dbs called "public") and what happens when both "db.public.blah" and "public.public.blah" are in scope for a given column resolution.

cc @rytaft - Becca, maybe this doesn't change anything for you, but you'll want to watch the story as it unfolds. The ambiguity problem will need attention + docs when we extend the fix to this issue to work with correlated naming resolution.