Using back-tics for special characters in queries

SQL++
1

Using Couchbase server 7.1.5, I am creating indexes for a collection. I create them in the Couchbase UI as well as with the cbq command line tool. In the query, I use snake_case naming convention. I see that any naming with a hyphen needs to be surrounded by back tics.

An example from the docs:

SELECT a.country FROM default:`travel-sample`.inventory.airline a WHERE a.name = "Excel Airways";

An example of my queries:

CREATE INDEX index_anomalies_date ON room.ecmp.anomalies(date);

  1. Is there a document that shows which special characters must be surrounded with back tics?

  2. When I create my queries with and without back tics, I get the same results in the UI and with cbq. The indexes get created properly and can be inspected with:

SELECT * FROM system:indexes;

"results": [
    {
        "indexes": {
            "bucket_id": "room",
            "datastore_id": "http://127.0.0.1:8091",
            "id": "92063fb23e7ad6da",
            "index_key": [
                "`date`"
            ],
            "keyspace_id": "anomalies",
            "name": "index_anomalies_date",
            "namespace_id": "default",
            "scope_id": "ecmp",
            "state": "online",
            "using": "gsi"
        }
    }
]

Is this intended behavior?

  1. Iā€™m not explicitly declaring a namespace such as ā€œdefaultā€. If I use the default namespace in the query or omit it, I get the same results as seen by the results output above.
CREATE INDEX index_anomalies_date ON room.ecmp.anomalies(date);

CREATE INDEX index_anomalies_date ON default:room.ecmp.anomalies(date);

Is this intended behavior?

2

Good questions.

  1. I didnā€™t find a list of characters that must be escaped. Itā€™s safe to escape every bucket name and n1ql identifier. Also when an identifier is the same as a reserved word, it must be escaped.

Hereā€™s the definition of bucket names: Create a Bucket | Couchbase Docs

Hereā€™s the definition of identifiers : Identifiers | Couchbase Docs

Hereā€™s the n1ql language reference SQL++ Language Reference | Couchbase Docs

  1. The back-tics are not part of the name, so the resulting indexes will be the same. The back-tics are for parsing n1ql statements (or query-contexts for n1ql statements of namespace:bucketname.scope.collection)

  2. the namespace is kind of a ā€˜for future useā€™ thing. Itā€™s always ā€˜defaultā€™.

3

Thank you for your quick response. In the Identifiers | Couchbase Docs I see in the EBNF that the underscore is supported:

unescaped-identifier ::= [a-zA-Z_] ( [0-9a-zA-Z_$] )*

ā€œUnescaped identifiers cannot support the full range of identifiers allowed in a JSON document, but do support the most common ones with a simpler syntax.ā€

I was just wondering if the lack of back-tics would cause a parsing issue but you clarify that in your answer to question 2.

Thank you.

4

@eric_balawejder ,

namespace is optional (at present all couchbase buckets use default namespace). In memory keyspaces use system as namepsace

FYI: JSON schema less travel-sample without escape can be fields and - is minus arithmetic operations.
ex: Also valid

{"a-b":1, "a+b":9, "c.d":10, "c d":20, .....}

Also reserve words used as fields must be escaped

Reserve words, keyspace (i.e. bucket, scope, collection, aliases ) where identifier expected may give syntax error.
Some cases if conflict with fields and expression operators -,+,ā€¦ may interpret wrongly.

SELECT a-b AS a1 
FROM  [ {"a-b":1, "a+b":9, "c.d":10, "c d":20, "a":30, "b":20 }] AS d;

NOTE: Identifiers, fields are case sensitive.

1 Like