Using both mclient and C#/C++ test clients connected via 64bit ODBC I am encountering this type mismatch error for no obvious reason. This is something of a show-stopper for what had proved to be a very interesting experiment!
# MonetDB 5 server v11.19.3 "Oct2014"
# Serving database 'dbfarm', using 4 threads
# Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked
# Found 15.982 GiB available main-memory.
An example query below, weight is a double, all other columns are integer. There are no NULL values, nor any oddities
sql>SELECT count(weight) , sum(weight) , avg(weight) , stddev_pop(weight) FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 3));
Type mismatch
But a family of closely related queries do succeed:
sql>SELECT count(weight) , sum(weight) , avg(weight) , stddev_pop(weight) FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 11));
| L1 | L2 | L3 |>
| 290517 | 290517 | 1 |
1 tuple (200.115ms) !1 column dropped!
Enabling ODBC logging does not reveal a great deal more than this:
dbc-con.vshost 13d8-2ac4 ENTER SQLExecDirectW
HSTMT 0x000000000048F370
WCHAR * 0x000000000256458C [ -3] "SELECT count(weight) as cnt, sum(weight) as total, avg(weight) as average, stddev_pop(weight) as stddev FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 3))\ 0"
SDWORD -3
odbc-con.vshost 13d8-2ac4 EXIT SQLExecDirectW with return code -1 (SQL_ERROR)
HSTMT 0x000000000048F370
WCHAR * 0x000000000256458C [ -3] "SELECT count(weight) as cnt, sum(weight) as total, avg(weight) as average, stddev_pop(weight) as stddev FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 3))\ 0"
SDWORD -3
DIAG [HY000] [MonetDB][ODBC Driver 11.19.3]Type mismatch (0)
odbc-con.vshost 13d8-2ac4 ENTER SQLGetDiagRecW
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000000000048F370
SQLSMALLINT 1
SQLWCHAR * 0x000000001C39D940
SQLINTEGER * 0x000000001C39DB34
SQLWCHAR * 0x000000001B6246C0
SQLSMALLINT 1024
SQLSMALLINT * 0x000000001C39DB30
odbc-con.vshost 13d8-2ac4 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 0x000000000048F370
SQLSMALLINT 1
SQLWCHAR * 0x000000001C39D940 [ 5] "HY000"
SQLINTEGER * 0x000000001C39DB34 (0)
SQLWCHAR * 0x000000001B6246C0 [ 43] "[MonetDB][ODBC Driver 11.19.3]Type mismatch"
SQLSMALLINT 1024
SQLSMALLINT * 0x000000001C39DB30 (43)