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"
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"