Bug #3305 Subselect in 4.1.1,5.0.0 dont work through ODBC or Ctrl Center
Submitted: 26 Mar 2004 9:04 Modified: 27 Mar 2004 14:17
Reporter: bill stamp Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:4.1.1 OS:Windows (WinNT/XP)
Assigned to: CPU Architecture:Any

[26 Mar 2004 9:04] bill stamp 
Description:
ODBC drivers 3.51 - could not locate compiled 3.6 drivers to try
MySql 4.1.1a, 5.0.0, same results, sub selects return error 1235

Note that the same query, run from the console (mysql.exe) returns a
correct result set

Also, on the mysql news group, there are currently thre posts on this 
same issue.

Thanks for looking at this - we love MySql. But, we need subselect to use it.

How to repeat:
example select string, in either control center or as formatted vi ODBC query:

SELECT s_longname FROM fm60fil WHERE s_libid IN (SELECT s_libid FROM fm60lib)

Error returned (in control center)
[lionheart] ERROR 1235: This version of MySQL doesn't yet
support 'LIMIT & IN/ALL/ANY/SOME subquery'
[27 Mar 2004 14:17] MySQL Verification Team 
I tested MyODBC 3.51 against 4.1.2 server and using the tool ODBCTE32.EXE:

Full Connect(Default)

	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

	Successfully connected to DSN 'myodbc3-test'.
SQLExecDirect:
				In:				hstmt = 0x00991930, 
										szSqlStr = "select name from m where id in(select id from m)", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"name"
"one"
"two"
2 rows fetched from 1 column.
[30 Mar 2004 13:56] bill stamp 
Thanks very much - yes, you are correct, that works.
We are going to put together as small an example as we can - I think the
issue is that we are using MFC 7.1 c++/CRecordset to do the query - oddly, it
works ok on MsSQL and Oracle.
Perhaps if we narrow down the app to stricly connect/query we can get it going
om MySql, then gradually add to it to nderstand why it breaks in pur app.