DocumentDB Java SQL API：带空格的集合查询语法合规性咨询

Great question! Let's break this down clearly for you:

Is using double quotes for space-containing identifiers SQL-compliant? #

Absolutely! Wrapping identifiers (like collection/table names or column names with spaces) in double quotes is part of the ANSI SQL standard. This is the correct way to signal to the SQL parser that you’re referring to a database object name, not a string literal or reserved keyword.

Your example query is fully valid per SQL standards:

select * from "collection with space" where "collection with space".field1 = <value1> AND "collection with space".field2 = <value2>

Why don't single quotes work? #

Single quotes serve a completely different purpose in SQL—they’re used to enclose string literals (actual text values like 'New York' or '2024-05-20'). When you wrap a collection name in single quotes, the SQL engine treats it as a plain string, not a reference to your database collection. That’s why this approach fails: the engine looks for a collection named the string itself, not your actual space-containing collection.

Notes for your SQL Java API 1.15 #

As long as your API adheres to ANSI SQL standards (which most mainstream SQL Java APIs do), your double-quoted syntax should work seamlessly. Some databases have dialect-specific rules (like MySQL using backticks ` by default), but if your setup follows standard SQL, your current approach is the right one.

内容的提问来源于stack exchange，提问作者Rajeev Singh