Bug #83153 SQLNumResultCols() causes the driver to return one segment in SQLPrimaryKey
Submitted: 26 Sep 2016 15:06 Modified: 27 Sep 2016 11:38
Reporter: Dror Harari Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:5.3 OS:Any
Assigned to: CPU Architecture:Any
Tags: SQLNumResultCols, SQLPrimaryKey

[26 Sep 2016 15:06] Dror Harari
Description:
A sequence of SQLAllocHandle,SQLPrepare,SQLNumResultCols(),SQLFreeHandle() results in a later SQLPrimaryKey call to return just one row (first segment) rather than all rows.

The cause: calling SQLNumResultCols() directly after SQLPrepare() causes the driver to emit "set @@sql_select_limit=1" command which is not reset back using "set @@sql_select_limit=DEFAULT". This happens at the connection level so later on, an unrelated SQLPrimary key which does not reset the limit ends up getting back just one row.

Capturing the network exchange shows the issue:
5.6.28-76.1-56-log._os.Win64._client_name.libmysql._pid.2296._thread.4964	_platform.x86_64._client_version.5.7.11
SET NAMES latin1
SET character_set_results = NULL
SET SQL_AUTO_IS_NULL = 0
set @@sql_select_limit=1
SELECT * FROM nirdb.lineitem WHERE 1=0
def.nirdb.lineitem.lineitem
L_ORDERKEY
L_PARTKEY	
L_SUPPKEYL_LINENUMBER
L_QUANTITY
L_EXTENDEDPRICE
L_DISCOUNT
L_TAX
L_RETURNFLAG
L_LINESTATUS
L_SHIPDATE
L_COMMITDATE.L_COMMITDATE
L_RECEIPTDATE
L_SHIPINSTRUCT
L_SHIPMODE
L_COMMENT
SHOW KEYS FROM `nirdb`.`lineitem`
...
lineitem.0.PRIMARY.1 L_ORDERKEY BTREE
^--- Here there should be another line for L_PARTKEY

A workaround is to issue SQLExecute() on the prepared statement (something the driver should have done but did not). The bug is problematic because its impact is in entirely different and unexpected place.

This bug exists in older versions from 5.1 and up (at least)

How to repeat:
/* This program demonstrates a bug with SQLNumResultCols(). It uses the following
 * table definion (assumed to exist):

 CREATE TABLE `lineitem` ( 
`L_ORDERKEY` decimal(10,0) NOT NULL, 
`L_PARTKEY` decimal(10,0) NOT NULL, 
`L_SUPPKEY` decimal(10,0) NOT NULL, 
`L_LINENUMBER` decimal(10,0) NOT NULL, 
`L_COMMENT` varchar(44) NOT NULL, 
PRIMARY KEY (`L_ORDERKEY`,`L_PARTKEY`) 
)

 */
#include <windows.h>
#include <sql.h>
#include <sqlext.h>

#include <stdio.h>

#define CATALOG					"nirdb"
#define SCHEMA					"nirdb"
#define TABLE					"lineitem"
#define QUERY_TEXT              "SELECT * FROM nirdb.lineitem WHERE 1=0"
#define CONNECT_STR             "DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=10.0.56.60;port=3306;UID=root;PWD=;DB=;"
//#define CONNECT_STR             "DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=10.0.56.60;port=3306;UID=root;PWD=;DB=;initstmt=SET time_zone='+00:00';Option=74448896;"

static void ReportError(const SQLCHAR *pszContext, SQLHANDLE henv, SQLHANDLE hdbc, SQLHANDLE hstmt)
{
    SQLCHAR		szState[25];
    SQLCHAR		szMsg[SQL_MAX_MESSAGE_LENGTH+1];
    SQLSMALLINT	cbMsg;
    SQLINTEGER	iNativeError;
    SQLRETURN	rc;

    printf("ODBC Error in %s\n", pszContext);
    while (1)
    {
        rc = SQLError(henv, hdbc, hstmt, szState, &iNativeError, szMsg,
			          SQL_MAX_MESSAGE_LENGTH - 1, &cbMsg);

        if (rc == SQL_ERROR || rc == SQL_INVALID_HANDLE) {
	        printf("SQLError failed!\n");
	        return;
        }

        if (rc != SQL_NO_DATA_FOUND) {
	        printf("SQLSTATE = %s\n", szState);
	        printf("NATIVE ERROR = %d\n", iNativeError);
	        szMsg[cbMsg] = '\0';
	        printf("MSG = %s\n\n", szMsg);
	        continue;
        }
        else
	        break;
    }
}

int main(int argc, char *argv[])
{
    SQLHANDLE    hDbc = NULL;
    SQLHANDLE    hEnv = NULL;
    SQLHANDLE    hStmt = NULL;
    SQLRETURN    rc;
    SQLCHAR		 szCatalog[64] = CATALOG;
    SQLCHAR		 szSchema[64] = SCHEMA;
    SQLCHAR		 szTable[64] = TABLE;
    int			 iSegs = 0;

    /* Allocate environment and connection handles: */
    rc = SQLAllocHandle(SQL_HANDLE_ENV, NULL, &hEnv);
    if (rc != SQL_SUCCESS) {
        printf("SQLAllocEnv has failed. RC=%d\n", rc);	
        exit(1);
    }

    {
        int iVersion = SQL_OV_ODBC3;
        rc = SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)iVersion, sizeof(iVersion));
        if (rc != SQL_SUCCESS) {
            ReportError("SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION,SQL_OV_ODBC3,...)", hEnv, NULL, NULL);
            exit(1);
        }
    }

    rc = SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDbc);
    if (rc != SQL_SUCCESS) {
        ReportError("SQLAllocHandle(SQL_HANDLE_DBC,...)", hEnv, NULL, NULL);
        exit(1);
    }

    {
        SQLCHAR *pszConnectString = (SQLCHAR *)CONNECT_STR;
        SQLCHAR szConnectStringOut[256];

		printf("Connecting\n");
        rc = SQLDriverConnect(hDbc, NULL, pszConnectString, SQL_NTS, szConnectStringOut, sizeof(szConnectStringOut), NULL, SQL_DRIVER_NOPROMPT);
        if (rc != SQL_SUCCESS) {
            ReportError("SQLDriverConnect(hDbc,...)", hEnv, hDbc, NULL);
            exit(1);
        }
    }

    rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
    if (rc != SQL_SUCCESS) {
        ReportError("SQLAllocHandle(SQL_HANDLE_STMT,...)", hEnv, hDbc, NULL);
        exit(1);
    }

    rc = SQLPrepare(hStmt, (SQLCHAR *)QUERY_TEXT, SQL_NTS);
    if (rc != SQL_SUCCESS) {
        ReportError("SQLPrepare(hStmt,...)", hEnv, hDbc, hStmt);
        exit(1);
    }

    // Workaround for the issue at the end - change 0 to 1 ...
    if (0) {
        rc = SQLExecute(hStmt);
        if (rc != SQL_SUCCESS) {
            ReportError("SQLExecute()", hEnv, hDbc, hStmt);
            exit(1);
        }
    }

    {
        SQLSMALLINT cCols, col;
        rc = SQLNumResultCols(hStmt, &cCols);
        if (rc != SQL_SUCCESS) {
            ReportError("SQLNumResultCols(hStmt,...)", hEnv, hDbc, hStmt);
            exit(1);
        }

        printf("Number of results columns is %d\n", cCols);
    }

    rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
    if (rc != SQL_SUCCESS) {
        ReportError("SQLFreeHandle(hStmt)", hEnv, hDbc, hStmt);
        exit(1);
    }

    rc = SQLAllocHandle(SQL_HANDLE_STMT, hDbc, &hStmt);
    if (rc != SQL_SUCCESS) {
        ReportError("SQLAllocHandle(SQL_HANDLE_DBC,...)", hEnv, NULL, NULL);
        exit(1);
    }

    rc = SQLPrimaryKeys(hStmt, szCatalog, SQL_NTS, szSchema, SQL_NTS, szTable, SQL_NTS);
    if (rc != SQL_SUCCESS) {
        ReportError("SQLPrimaryKeys()", hEnv, hDbc, hStmt);
        exit(1);
    }

    rc = SQLFetch(hStmt);
    while (rc == SQL_SUCCESS) {
        iSegs++;
        rc = SQLFetch(hStmt);
    }

    if (iSegs == 1) {
        printf("BUG - Number of segments found in the primary key is %d, expected 2\n", iSegs);
    } else if (iSegs == 2) {
        printf("OK - Number of segments found in the primary key is 2 as expected\n");
    }

    rc = SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
    if (rc != SQL_SUCCESS) {
        ReportError("SQLFreeHandle(hStmt)", hEnv, hDbc, hStmt);
        exit(1);
    }

    rc = SQLDisconnect(hDbc);
    if (rc != SQL_SUCCESS) {
        ReportError("SQLDisconnect(hStmt)", hEnv, hDbc, NULL);
        exit(1);
    }

    rc = SQLFreeHandle(SQL_HANDLE_DBC, hDbc);
    if (rc != SQL_SUCCESS) {
        ReportError("SQLFreeHandle(hStmt)", hEnv, hDbc, NULL);
        exit(1);
    }

    rc = SQLFreeHandle(SQL_HANDLE_ENV, hEnv);
    if (rc != SQL_SUCCESS) {
        ReportError("SQLFreeHandle(hStmt)", hEnv, NULL, NULL);
        exit(1);
    }
}

Suggested fix:
The SQLPrimaryKey should issue "set @@sql_select_limit=DEFAULT" if the connection is currently under "set @@sql_select_limit=1"
[27 Sep 2016 11:38] Chiranjeevi Battula
Hello Dror Harari,

Thank you for the bug report.
Verified based on internal discussion with dev's.

Thanks,
Chiranjeevi.