topic Re: ALIAS Not accepted 42601 in Get Started Discussions

ALIAS Not accepted 42601

malhm — Mon, 27 Jan 2025 05:02:35 GMT

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)

Re: ALIAS Not accepted 42601

arekmust — Mon, 27 Jan 2025 09:03:12 GMT

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 :

Table alias should have been used without quotes as well 🙂

Cheers!

Re: ALIAS Not accepted 42601

malhm — Mon, 27 Jan 2025 09:18:22 GMT

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

Re: ALIAS Not accepted 42601

arekmust — Mon, 27 Jan 2025 09:33:52 GMT

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

Re: ALIAS Not accepted 42601

filipniziol — Mon, 27 Jan 2025 10:03:25 GMT

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