Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: family cannot be used as an unquoted table name #31589

Closed
bobvawter opened this issue Oct 18, 2018 · 0 comments
Closed

sql: family cannot be used as an unquoted table name #31589

bobvawter opened this issue Oct 18, 2018 · 0 comments
Labels
A-sql-syntax Issues strictly related to the SQL grammar, with no semantic aspect C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. S-3-ux-surprise Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption.

Comments

@bobvawter
Copy link
Contributor 

create table family (
name varchar(255) not null,
primary key (name)
);

Results in

invalid syntax: statement ignored: syntax error at or near "family"

As I read the syntax in https://www.cockroachlabs.com/docs/stable/create-table.html, it looks like the family keyword should only match within the table elements.
@bobvawter bobvawter added C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. S-3-ux-surprise Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption. A-sql-syntax Issues strictly related to the SQL grammar, with no semantic aspect labels Oct 18, 2018
@knz knz changed the title family cannot be used as an unquoted table name sql: family cannot be used as an unquoted table name Oct 23, 2018
@knz knz mentioned this issue Oct 23, 2018
Merged
craig bot pushed a commit that referenced this issue Oct 23, 2018
@knz
Merge #31637 #31725 #31731
b12357b 
31637: pgwire: add telemetry for fetch limits r=knz a=knz

Requested by @awoods187 

The JDBC driver and perhaps others commonly try to use the "fetch
limit" parameter, which is yet unsupported in
CockroachDB (#4035). This patch adds telemetry to gauge demand.

Release note (sql change): attempts by client apps to use the
unsupported "fetch limit" parameter (e.g. via JDBC) will now be
captured in telemetry if statistics reporting is enabled, to gauge
support for this feature.

31725: sql/parser: re-allow FAMILY, MINVALUE, MAXVALUE, NOTHING and INDEX in table names r=knz a=knz

Fixes #31589.

CockroachDB introduced non-standard extensions to its SQL dialect very
early in its history, before concerns of compatibility with existing
PostgreSQL clients became a priority. When these features were added,
new keywords were liberally marked as "reserved", so as to "make the
grammar work", and without noticing / care for the fact that this
change would make existing valid SQL queries/clients encounter new
errors.

An example of this:

1. let's make "column families" a thing

2. the syntax `create table(..., constraint xxx family(a,b,c))` is not
   good enough (although this would not require reserved keywords), we
   really want also `create table (..., family (a,b,c))` to be
   possible.

3. oh, the grammar won't let us because "family" is a possible column
   name? No matter! let's mark "FAMILY" as a reserved name for
   column/function names.

   - No concern for the fact that "family" is a  perfectly valid
	 English name for things that people want to make an attribute of
	 in inventory / classification tables.

   - No concern for the fact that reserved column/function names are
	 also reserved for table names.

4. (much later) Clients complaining about the fact they can't call
   their columns or tables `family` without quoting.

Ditto "INDEX", "MINVALUE", "MAXVALUE", and perhaps others.

Moral of the story: DO NOT MAKE NEW RESERVED KEYWORDS UNLESS YOU'RE
VERY VERY VERY SURE THAT THERE IS NO LEGITIMATE USE FOR THEM IN CLIENT
APPS EVER.

(An example perhaps: the word "NOTHING" was also marked as reserved,
but it's much more unlikely this word will ever be used for something
useful.)

This patch restores the use of FAMILY, INDEX, NOTHING, MINVALUE and
MAXVALUE in table and function names, by introducing an awkward dance
in the grammar of keyword non-terminals and database object names.

They remain reserved as colum names because of the non-standard
CockroachDB extensions.

Release note (sql change): It is now again possible to use the
keywords FAMILY, MINVALUE, MAXVALUE, INDEX or NOTHING as table names,
for compatibility with PostgreSQL.

31731: sql/parser: unreserve INDEX and NOTHING from the RHS of SET statements r=knz a=knz

First commit from #31725.

The SET statement in the pg dialect is special because it
auto-converts identifiers on its RHS to symbolic values or strings. In
particular it is meant to support a diversity of special keywords as
pseudo-values.

This patch ensures that INDEX and NOTHING are accepted on the RHS.

Release note (sql change): the names "index" and "nothing" are again
accepted in the right-hand-side of the assignment in SET statements,
for compatibility with PostgreSQL.


Co-authored-by: Raphael 'kena' Poss <[email protected]>
@craig craig bot closed this as completed in #31725 Oct 23, 2018
@knz knz mentioned this issue Oct 23, 2018
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
A-sql-syntax Issues strictly related to the SQL grammar, with no semantic aspect C-bug Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior. S-3-ux-surprise Issue leaves users wondering whether CRDB is behaving properly. Likely to hurt reputation/adoption.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@bobvawter