[
https://issues.apache.org/jira/browse/CASSANDRA-2027?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13002790#comment-13002790
]
Eric Evans commented on CASSANDRA-2027:
---------------------------------------
I just fired up a test instance of Postgres to try to get an idea of how some
of this looked in practice.
Creating a table with column _named_ "10", with int typed values. Using a
numeric literal here does not work (i.e. it must be quoted).
{quote}
test=# CREATE TABLE oneint(10 int);
ERROR: syntax error at or near "10"
LINE 1: CREATE TABLE oneint(10 int);
test=# CREATE TABLE oneint("10" int);
CREATE TABLE
{quote}
Inserting an integer value of 10 into the column named 10. Quoting the value
does not work, nor does using a numeric literal for the column name.
{quote}
test=# INSERT INTO oneint ("10") VALUES ("10");
ERROR: column "10" does not exist
LINE 1: INSERT INTO oneint ("10") VALUES ("10");
test=# INSERT INTO oneint (10) VALUES (10);
ERROR: syntax error at or near "10"
LINE 1: INSERT INTO oneint (10) VALUES (10);
test=# INSERT INTO oneint ("10") VALUES (10);
INSERT 0 1
{quote}
Strangely enough though, single quoting the value does work, while single
quoting the column name does not.
{quote}
test=# INSERT INTO oneint VALUES ('10');
INSERT 0 1
test=# INSERT INTO oneint ('10') VALUES (10);
ERROR: syntax error at or near "'10'"
LINE 1: INSERT INTO oneint ('10') VALUES (10);
{quote}
But, anything goes for column names in a projection.
{quote}
test=# SELECT "10" FROM oneint;
10
---
10
(1 rows)
test=# SELECT '10' FROM oneint;
?column?
---
10
(1 rows)
test=# SELECT 10 FROM oneint;
?column?
---
10
(1 rows)
{quote}
There may be a method to all of that madness, but it escapes me, and it seems
even more convoluted trying to map this to CQL in light of the fact that column
names here are string identifiers and values are typed, and both column names
and values are typed in Cassandra.
It may also be worth mentioning that I repeated this experiment for MySQL and
SQLite (what was handy) with results that were entirely inconsistent with both
Postgres and each other.
So like I said, I'm trying to be convinced, but I keep imagining (1) trying to
explain to someone the rules governing syntax and semantics and then (2)
attempting to answer the question "but why is it like that?". So far it seems
the answer to both is SQL, (i.e. "it works the same way as SQL", and "it's that
way because SQL is that way). That strikes me as a bad answer to both. It's a
bad answer to the How because it's somewhere between a non-answer and not true
(depending on whether you're referring to one of the specs, or
implementations). It's a bad answer to Why because of the impedance mismatch,
because we're far from being SQL compliant otherwise, and because mimicking
something broken is broken (for some value of broken, I guess).
> term definitions
> ----------------
>
> Key: CASSANDRA-2027
> URL: https://issues.apache.org/jira/browse/CASSANDRA-2027
> Project: Cassandra
> Issue Type: Sub-task
> Components: API
> Affects Versions: 0.8
> Reporter: Eric Evans
> Assignee: Eric Evans
> Priority: Minor
> Labels: cql
> Fix For: 0.8
>
> Attachments: v1-0001-CASSANDRA-2027-utf8-and-integer-term-types.txt,
> v1-0002-column-name-validation.txt,
> v1-0003-system-tests-for-integer-and-utf8-term-types.txt,
> v1-0004-uuid-term-definitions.txt,
> v1-0005-missed-doc-update-for-utf8-term-type.txt
>
> Original Estimate: 0h
> Remaining Estimate: 0h
>
> h3. String
> Anything between double-quotes. Node-side this is just converted to bytes,
> so it could really be used to represent *any* type so long as it is
> appropriately encoded.
> Examples:
> {code:style=SQL}
> SELECT "name" FROM cf;
> UPDATE cf SET "name" = "value" WHERE KEY = "key";
> {code}
> h3. UTF-8
> A double-quoted string literal that is prefixed with a "u" to indicated that
> it should be encoded to bytes using the utf-8 charset node-side.
> Examples:
> {code:style=SQL}
> SELECT u"name" FROM cf;
> UPDATE cf SET u"name" = u"value" WHERE KEY = "key";
> {code}
> h3. Integer
> An undecorated numeric literal, converted to a 4-byte int node-side.
> Examples:
> {code:style=SQL}
> SELECT 10..100 FROM cf WHERE KEY = "key";
> UPDATE cf SET 1000 = "thousand", 100 = "hundred" WHERE KEY = "key";
> {code}
> h3. Long
> A numeric literal suffixed with an "L", converted to an 8-byte long node-side.
> Examples:
> {code:style=SQL}
> SELECT 10L..100L FROM cf WHERE KEY = "key";
> UPDATE cf SET 1000L = "thousand", 100L = "hundred" WHERE KEY = "key";
> {code}
> h3. UUID
> A string-formatted UUID supplied as an "argument" to a ctor/function formated
> string ({{uuid(<uuid string>)}}). Node-side this is converted back to the
> corresponding UUID.
> Examples:
> {code:style=SQL}
> SELECT uuid(5f989e95-ae07-4425-b84a-6876ba106c66) FROM cf WHERE KEY = "key";
> UPDATE cf SET uuid(5621b93d-d3a2-4d22-8a59-bdb93202b6cb) = "username" WHERE
> KEY = "key";
> {code}
> h3. TimeUUID (UUID Type 1)
> A string-formatted time-based UUID (type 1) supplied as an "argument" to a
> ctor/function formated string ({{timeuuid(<uuid string>)}}). Node-side this
> is converted back to the corresponding UUID. In addition to a
> string-formatted UUID, it should also be possible to supply dates in a
> variety of formats which will result in a new UUID being created node-side.
> Examples:
> {code:style=SQL}
> SELECT timeuuid(2011-01-01)..timeuuid(2010-01-21) FROM cf WHERE KEY = "key";
> UPDATE cf SET timeuuid(now) = 1000L WHERE KEY = "key";
> {code}
--
This message is automatically generated by JIRA.
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
