cancel
Showing results for 
Search instead for 
Did you mean: 
Community Platform Discussions
Connect with fellow community members to discuss general topics related to the Databricks platform, industry trends, and best practices. Share experiences, ask questions, and foster collaboration within the community.
cancel
Showing results for 
Search instead for 
Did you mean: 

ALIAS Not accepted 42601

malhm
New Contributor II

I am unable to run the following query generated from my backend at databricks side

Query: 
SELECT "A".`cut` AS "Cut" , "A".`color` AS "Color" , "A".`carat` AS "Carat" , "A".`clarity` AS "Clarity" FROM databricksconnect.default.diamonds "A"  
Error logs: 
Caused by: com.pega.pegarules.pub.database.DatabaseException: Unable to query the database: code: 500051 SQLState: 42601 Message: [Databricks][JDBCDriver](500051) ERROR processing query/statement. Error Code: 0, SQL state: 42601, Query: SELECT "A"***, Error message from Server: org.apache.hive.service.cli.HiveSQLException: Error running query: [PARSE_SYNTAX_ERROR] org.apache.spark.sql.catalyst.parser.ParseException:   
[PARSE_SYNTAX_ERROR] Syntax error at or near '"Cut"'. SQLSTATE: 42601 (line 1, pos 20)  

4 REPLIES 4

arekmust
New Contributor III

Hi there!

aliases must be strings. If your column alias contains space, special character, or is a number, then grab it within a backticks. See example below:

 

select 
  order_id,                     -- No Alias - Works
  order_id as ORDER_ID,         -- String - Works
  -- order_id as "ORDER_ID",    -- Double quotes - Syntax Error
  -- order_id as 'orderid',     -- Single quotes - Syntax Error
  -- order_id as _@+$,          -- Specia chars - Syntax Error
  order_id as `_@+$`            -- Backticks - Works
from my_view

 

Output :

arek_must_0-1737968328773.png

Table alias should have been used without quotes as well 🙂

Cheers!

malhm
New Contributor II

Is there any way or workaround to it so that it accepts double quotes for aliases, as sql and postgres does allow the same?

arekmust
New Contributor III

None that I'm aware of. You could handle this on the fly if there' s a possibility

filipniziol
Contributor III

Hi @malhm ,

Double quotes are not supported in column alias. In Databricks SQL/Spark SQL one uses backticks instead of double quotes like in PostgreSQL.

Check the docs:
https://spark.apache.org/docs/3.5.1/sql-ref-identifier.html


 

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group