single vs double quotes in WHERE clause returning different results

user_8999037 0
Viewed 1160

It seemed that Athena was including CSV column headers in my query results. I recreated the tables with the DDL included below using TBLPROPERTIES ("skip.header.line.count"="1") to remove the headers.

I'm running the following queries to validate that the CREATE TABLE DDL worked. The only difference between the queries below is the use of single vs double quotes in the WHERE clause. The issue is that I'm getting different result when running them.

Query 1:

SELECT
  file_name
FROM table
WHERE file_name = "file_name"

The query above returns the actual data (see sample table below), rather than only rows where the file_name field is "file_name".

+-------+--------------------+
| Row # |     file_name      |
+-------+--------------------+
|     1 |                    |
|     2 | 1586786323.8194735 |
|     3 |                    |
|     4 | 1586858857.3117666 |
|     5 | 1586858857.3117666 |
|     6 | 1586858857.3117666 |
|   ... |                    |
+-------+--------------------+

Query 2:

SELECT
  file_name
FROM table
WHERE file_name = 'file_name'

The query above returns no results, as expected if the CSV column headers are not being included in the results.

I'm quite confused by the first query returning any results at all. I've scoured the AWS documentation at this point and doesn't seem I did anything wrong with the DDL and SQL should not care whether I use single vs. double quotes. What am I missing here?

DDL:

CREATE EXTERNAL TABLE `table` (
     `file_name` string,
     `ticker` string,
     ...
     )
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ( 
  'escapeChar'='\\', 
  'separatorChar'=',')
LOCATION
  's3://{bucket_name}/{folder}/'
TBLPROPERTIES (
  "skip.header.line.count"="1")
sql
2 Answers

Single quotes are the SQL standard for delimiting strings.

Double quotes are used for escaping delimiters. So "file_name" refers to the column of that name. Some databases also accept double quotes for strings. That is just confusing. Don't do that.

In your original tags, for instance, Hive uses backticks to escape identifiers and double quotes for strings. Presto uses double quotes (which is the standard) to delimit identifiers.

user_11440350

Just to expand on Gordon's answer a little. Your first query:

SELECT
  file_name
FROM table
WHERE file_name = "file_name"

In this case, the double quotes are causing the query engine to treat "file_name" as a column identifier, not a value, so that query is functionally the same as:

SELECT
  file_name
FROM table
WHERE file_name = file_name

Obviously (when written that way) the condition is always true, so the full table is returned.

user_57905840
Related