MySQL Bugs: #6870: Subquery Visual Basic ADO

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:	Windows (Windows XP Pro.)
Assigned to:		CPU Architecture:	Any

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

Hi!

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

thanks a lot

Hi,

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

Thanks.

Hi!

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

Thank!

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.

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

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.