Why this, well working, query against Impala throws error via ODBC driver?

Question

I have the query below which runs perfectly in Hue but fails via ODBC from C#. The ODBC driver says something about unknown parameters, but I could not figure out what is that. In the logs the ^ points to the question mark following the LIMIT keyword. I had to modify the query in the logs to hide details.

Based on the logs, it seems the query reaches Impala and Impala throws error, not the driver. If it is true, then why the same query works from Hue? (the same means I copy-paste selectCommand.CommandText into Hue and replace the question marks to the same numbers as are used in the query and execute.

On the other hand, the same application has queries where ODBC driver puts together the queries using question mark format and works like charm.

I run out of ideas what can be done. Any help would be appreciated.

Query:

SELECT
  -- list of fields comma separated 
FROM tableName 
ORDER BY oneOfTheFields 
LIMIT ? 
OFFSET ?

C# code:

public async Task<List<ClusterMetadataIEdhEntity>> GetAllOffSetted(int limit, int offset)
{
    if (limit == 0)
    {
        throw new ArgumentNullException();
    }

    List<ClusterMetadataIEdhEntity> result = new List<ClusterMetadataIEdhEntity>();

    string select = $"SELECT " +
                    $"{someFieldName}, " +
                    $"{anotherFieldName} " +
                    $"FROM {tableName} " +
                    $"ORDER BY {colId} " +
                    $"LIMIT ? " +
                    $"OFFSET ?";

    using (OdbcCommand selectCommand = new OdbcCommand(select, odbcConnection))
    {
        selectCommand.Parameters.Add("@limit", OdbcType.Int).Value = limit;
        selectCommand.Parameters.Add("@offset", OdbcType.Int).Value = offset;

        Console.WriteLine(selectCommand.CommandText);
        DbDataReader reader = await selectCommand.ExecuteReaderAsync();
    }

ODBC Driver log:

Dec 28 13:55:49.179 TRACE 775 Connection::SQLGetFunctions: +++++ enter +++++
Dec 28 13:55:49.179 TRACE 775 ConnectionState::SQLGetFunctions: +++++ enter +++++
Dec 28 13:55:49.217 TRACE 775 Connection::SQLAllocHandle: +++++ enter +++++
Dec 28 13:55:49.217 TRACE 775 ConnectionState4::SQLAllocHandle: +++++ enter +++++
Dec 28 13:55:49.217 TRACE 775 ConnectionState::SQLAllocHandle: +++++ enter +++++
Dec 28 13:55:49.217 TRACE 775 ImpalaConnection::CreateStatement: +++++ enter +++++
Dec 28 13:55:49.217 TRACE 775 ImpalaStatement::ImpalaStatement: +++++ enter +++++
Dec 28 13:55:49.217 TRACE 775 Statement::SQLGetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.217 INFO  775 Statement::SQLGetStmtAttrW: Attribute: SQL_ATTR_APP_ROW_DESC (10010)
Dec 28 13:55:49.217 TRACE 775 StatementStateAllocated::SQLGetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.217 TRACE 775 StatementState::SQLGetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.218 TRACE 775 Statement::SQLGetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.218 INFO  775 Statement::SQLGetStmtAttrW: Attribute: SQL_ATTR_APP_PARAM_DESC (10011)
Dec 28 13:55:49.218 TRACE 775 StatementStateAllocated::SQLGetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.218 TRACE 775 StatementState::SQLGetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.218 TRACE 775 Statement::SQLGetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.218 INFO  775 Statement::SQLGetStmtAttrW: Attribute: SQL_ATTR_IMP_ROW_DESC (10012)
Dec 28 13:55:49.218 TRACE 775 StatementStateAllocated::SQLGetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.218 TRACE 775 StatementState::SQLGetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.218 TRACE 775 Statement::SQLGetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.218 INFO  775 Statement::SQLGetStmtAttrW: Attribute: SQL_ATTR_IMP_PARAM_DESC (10013)
Dec 28 13:55:49.218 TRACE 775 StatementStateAllocated::SQLGetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.218 TRACE 775 StatementState::SQLGetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.219 TRACE 775 Statement::SQLSetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.219 INFO  775 Statement::SQLSetStmtAttrW: Attribute: SQL_ATTR_QUERY_TIMEOUT (0)
Dec 28 13:55:49.219 TRACE 775 StatementState::SQLSetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.219 TRACE 775 StatementAttributes::SetAttribute: +++++ enter +++++
Dec 28 13:55:49.222 TRACE 775 Connection::SQLGetInfoW: +++++ enter +++++
Dec 28 13:55:49.222 INFO  775 Connection::SQLGetInfoW: InfoType: SQL_DRIVER_ODBC_VER (77)
Dec 28 13:55:49.222 TRACE 775 Statement::SQLSetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.222 INFO  775 Statement::SQLSetStmtAttrW: Attribute: Unknown Attribute (1228)
Dec 28 13:55:49.222 TRACE 775 StatementState::SQLSetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.222 TRACE 775 StatementAttributes::SetAttribute: +++++ enter +++++
Dec 28 13:55:49.222 INFO  775 StatementAttributes::SetAttribute: Invalid attribute: 1228
Dec 28 13:55:49.223 ERROR 775 Statement::SQLSetStmtAttrW: [Cloudera][ODBC] (10210) Attribute identifier invalid or not supported: 1228
Dec 28 13:55:49.226 TRACE 775 Statement::SQLSetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.226 INFO  775 Statement::SQLSetStmtAttrW: Attribute: Unknown Attribute (1227)
Dec 28 13:55:49.226 TRACE 775 StatementState::SQLSetStmtAttrW: +++++ enter +++++
Dec 28 13:55:49.226 TRACE 775 StatementAttributes::SetAttribute: +++++ enter +++++
Dec 28 13:55:49.226 INFO  775 StatementAttributes::SetAttribute: Invalid attribute: 1227
Dec 28 13:55:49.226 ERROR 775 Statement::SQLSetStmtAttrW: [Cloudera][ODBC] (10210) Attribute identifier invalid or not supported: 1227
Dec 28 13:55:49.246 TRACE 775 Statement::SQLBindParameter: +++++ enter +++++
Dec 28 13:55:49.246 TRACE 775 StatementState::SQLBindParameter: +++++ enter +++++
Dec 28 13:55:49.246 TRACE 775 Statement::SQLBindParameter: +++++ enter +++++
Dec 28 13:55:49.246 TRACE 775 StatementState::SQLBindParameter: +++++ enter +++++
Dec 28 13:55:49.247 TRACE 775 Connection::SQLExecDirectW: +++++ enter +++++
Dec 28 13:55:49.247 TRACE 775 ConnectionState5::SQLExecDirectW: +++++ enter +++++
Dec 28 13:55:49.247 TRACE 775 ConnectionState::SQLExecDirectW: +++++ enter +++++
Dec 28 13:55:49.247 TRACE 775 Statement::SQLExectDirectW: +++++ enter +++++
Dec 28 13:55:49.247 TRACE 775 StatementStateAllocated::SQLExecDirectW: +++++ enter +++++
Dec 28 13:55:49.247 INFO  775 StatementState::InternalPrepare: Preparing query: SELECT fields FROM table ORDER BY id LIMIT ? OFFSET ?
Dec 28 13:55:49.247 TRACE 775 ImpalaStatement::CreateDataEngine: +++++ enter +++++
Dec 28 13:55:49.248 TRACE 775 ImpalaDataEngine::ImpalaDataEngine: +++++ enter +++++
Dec 28 13:55:49.248 TRACE 775 ImpalaDataEngine::Prepare: +++++ enter +++++
Dec 28 13:55:49.251 INFO  775 ImpalaDataEngine::Prepare: Trying to parse query: SELECT fields FROM table ORDER BY id LIMIT ? OFFSET ?
Dec 28 13:55:49.254 INFO  775 ImpalaDataEngine::Prepare: [Cloudera][SQLEngine] (31480) syntax error near 'SELECT fields FROM table ORDER BY id LIMIT ? OFFSET<<< ??? >>> ?'.
Dec 28 13:55:49.254 TRACE 775 ImpalaClient::IsNullCellInfosRequired: +++++ enter +++++
Dec 28 13:55:49.254 TRACE 775 ImpalaResultSet::ImpalaResultSet: +++++ enter +++++
Dec 28 13:55:49.254 TRACE 775 ImpalaNativeQueryExecutor::Execute: +++++ enter +++++
Dec 28 13:55:49.254 TRACE 775 ImpalaClient::ExecuteStatement: +++++ enter +++++
Dec 28 13:55:49.254 TRACE 775 ImpalaClient::ExecuteStatementInternal: +++++ enter +++++
Dec 28 13:55:49.254 DEBUG 775 ImpalaTCLIServiceThreadSafeClient::ExecuteStatement: TExecuteStatementReq
    sessionHandle.sessionId.guid = be2667f4506840daaba657ad40304f53
    statement = SELECT fields FROM table ORDER BY id LIMIT ? OFFSET ?
    runAsync = true
Dec 28 13:55:49.254 TRACE 775 ImpalaTCLIServiceThreadSafeClient::ExecuteStatement: +++++ enter +++++
Dec 28 13:55:49.474 TRACE 775 ImpalaTCLIServiceThreadSafeClient::ExecuteStatement: +++++ leave +++++
Dec 28 13:55:49.475 ERROR 775 QueryExecutor::Execute: [Cloudera][ImpalaODBC] (110) Error while executing a query in Impala: [HY000] : AnalysisException: Syntax error in line 1:
... ORDER BY id LIMIT ? OFFSET ?
                             ^
Encountered: Unexpected character
Expected: CASE, CAST, DEFAULT, EXISTS, FALSE, IF, INTERVAL, LEFT, NOT, NULL, REPLACE, RIGHT, TRUNCATE, TRUE, IDENTIFIER

`$"LIMIT ? "` should be `$"LIMIT @limit "`, and same for `$"OFFSET ?"` (should be `$"OFFSET @offset"`), right? — Rafalon, Dec 28 '18 at 13:38
The error is the same. Majority of the examples uses question marks in the query, that is the reason I use them. — AndrasCsanyi, Dec 28 '18 at 13:42
This looks similar to errors I see using oledb with excel when the command type isn't set to text. The command has three types : cmd.CommandType : TableDirect, Text, StoredProcedure — jdweng, Dec 28 '18 at 15:40

Why this, well working, query against Impala throws error via ODBC driver?

0 Answers0