Bug #6870 Subquery Visual Basic ADO
Submitted: 29 Nov 2004 7:54 Modified: 30 May 2013 12:01
Reporter: Ying-Yi Lin Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version:3.51.10 OS:Microsoft Windows (Windows XP Pro.)
Assigned to: CPU Architecture:Any

[29 Nov 2004 7:54] Ying-Yi Lin
Description:
Use MySQL 4.1.7 to query a database. The results are correct in Windows XP Command window. But same database and same script has different results in a Visual Basic program using ADO and ODBC Connector 3.51.10. It seems that the conditions specified after subquery are not executed.

How to repeat:
A) Under Window Command window, the results are correct.

mysql> select * from aostbl where co = (select max(co) from aostbl where dt > '2004-04-30' )
    -> ;
+------------+------+-------+-------+-------+--------+-------+
| dt         | op   | hi    | lo    | co    | vo     | ac    |
+------------+------+-------+-------+-------+--------+-------+
| 2002-05-01 | 30.6 | 31.89 | 30.15 | 31.79 |  49400 | 30.38 |
| 2004-02-20 | 32.2 |  32.2 | 31.75 | 31.79 | 163200 | 31.46 |
| 2004-06-30 | 31.1 | 31.83 |    31 | 31.79 |  47600 | 31.61 |
+------------+------+-------+-------+-------+--------+-------+
3 rows in set (0.01 sec)

mysql> select * from aostbl where co = (select max(co) from aostbl where dt > '2004-04-30' ) and dt > '2004-04-30';
+------------+------+-------+----+-------+-------+-------+
| dt         | op   | hi    | lo | co    | vo    | ac    |
+------------+------+-------+----+-------+-------+-------+
| 2004-06-30 | 31.1 | 31.83 | 31 | 31.79 | 47600 | 31.61 |
+------------+------+-------+----+-------+-------+-------+
1 row in set (0.01 sec)

B) In Visual Basic Program using ADO. The result is not the same as the above result. 
select * from aostbl where co = (select max(co) from aostbl where dt > '2004-04-30' ) and dt > '2004-04-30';

Even the following script in Visual Basic program has incorrect result.
select * from (select * from aostbl where co = (select max(co) from aostbl where dt > '2004-04-30' )) as temp where dt > '2004-4-30';

+------------+------+-------+-------+-------+--------+-------+
| dt         | op   | hi    | lo    | co    | vo     | ac    |
+------------+------+-------+-------+-------+--------+-------+
| 2002-05-01 | 30.6 | 31.89 | 30.15 | 31.79 |  49400 | 30.38 |
+------------+------+-------+-------+-------+--------+-------+
[11 Jan 2005 5:51] Jorge del Conde
Hi!

I was unable to repeat this error.  Can you please send us a test-case that successfully reproduces this behaviour ?

thanks a lot
[11 Jan 2005 6:50] Ying-Yi Lin
Hi,

Please tell me how to send the code and the data?

Thanks.
[11 Jan 2005 7:00] Jorge del Conde
Hi!

Just attatch a zip file containing the test-case in the "Files"  section of this bug report.

Thank!
[12 Jan 2005 10:40] Ying-Yi Lin
Hi,

As I tried to reproduce the errors, I found the results are all correct this time. I don't know exactly what causes this as I have reinstalled MySQL and done modifications to the settings and my program after I sumbitted the problem.

If I find anything next time, I will prepare a text case and write down all the details. 

Thank you very much.
[14 Feb 2005 22:54] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[30 May 2013 12:01] Bogdan Degtyariov
I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.