Avoid incorrect quotation marks in queries

Background

Certain user behaviors might result in a query that appears to be syntactically correct, but will produce an error. Copying and pasting queries from rich text formats might replace the required  " (the ASCII character "quotation mark" (U+0022)) with “ (Left double quotation mark (U+201C)) or  ” (Right double quotation mark (U+201D)).

Additionally, single quotes ' and double quotes " have different syntactical usage expectations. Only " can be used when referencing usernames and database objects. Similarly, string values used in SQL queries must be quoted with single quotes '. 

Avoidance

Whenever possible, avoid using rich text mediums for transferring queries in HeavySQL or Immerse SQL Editor. Attach queries as plain text files when transferring via email, for example.

Troubleshooting

If you experience an unexpected syntax error message, check for use of incorrect double quotation marks. To do this programmatically, you can use the file linux program.

Save the query to a file naedquery.sql and then from the command line:

• $ file query.sqlshould returnquery.sql: ASCII text
• If your query has incorrect quotes the program will return query.sql: Unicode text, UTF-8 text
• This test assumes your query does not otherwise contain non-ASCII UTF-8 characters. However, UTF-8 characters, when appropriately placed inside quoted strings, are supported by HeavyDB.