SAP Knowledge Base Article - Public

3337934 - Error: "You have an error in your SQL syntax .. right syntax to use near 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1"" when using freehand SQL from SAC

Symptom

When running a freehand SQL query containing "OPTION SQL_SELECT_LIMIT=DEFAULT" to MySQL database from SAC, the below error message appears:

"Error occurred in the SAP Analytics Cloud agent Service: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1"."

Environment

SAP Analytics Cloud

Reproducing the Issue

1- Login to SAC.
2- Go to connections.
3- Create a new SQL database connection. (MySQL or MariaDB).
4- Check the "Use freehand SQL" checkbox.
5- Go to Modeler.
7- Choose import data from datasource.
8- Choose the" use freehand SQL" option and run a query that contains OPTION SQL_SELECT_LIMIT=DEFAULT.
>>> Notice the error: "Error occurred in the SAP Analytics Cloud agent Service: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=DEFAULT' at line 1""

Cause

This is a known bug in MySQL, please refer to this link for more information: https://bugs.mysql.com/bug.php?id=66659

Resolution

Replace OPTION SQL_SELECT_LIMIT=DEFAULT with LIMIT number_of_rows as such:

SELECT column1, column2, ... FROM table_name LIMIT number_of_rows;

Keywords

Cloud for Analytics, Cloud4Analytics, CloudforAnalytics, SAP AC, Cloud-Analytics, CloudAnalytics, SAPCloudAnalytics, Issue, sapanalyticscloud, sap analytical cloud, sap analytical cloud, limit rows, cannot, execute , KBA , LOD-ANA-AQU , Import Data Connections (Acquiring Data) , Known Error

Product

SAP Analytics Cloud 1.0