Devart Forums

Consider the following TMyQuery with SQL

select * from (select date('2012-02-17') as DateFld) tbl1 left join (select date('2012-02-17') as DateFld, 'X' as StrFld) tbl2
using (DateFld)
where DateFld=:DateParam;

Suppose I make the MasterSource for this query point to a MySQL table where DateParam is of type Date and currently has the value '2012-02-17'. The TMyQuery component ends up sending the following SQL to the server

select * from (select date('2012-02-17') as DateFld) tbl1 left join (select date('2012-02-17') as DateFld, 'X' as StrFld) tbl2
using (DateFld)
where DateFld='2012-02-17 00:00:00';

This returns values ('2012-02-17', NULL) which is clearly wrong.

It's as if Devart adding the Time to the Date value means the join isn't made YET the following join does work

select * from (select date('2012-02-17') as DateFld) tbl1 inner join (select date('2012-02-17') as DateFld, 'X' as StrFld) tbl2
using (DateFld)
where DateFld='2012-02-17 00:00:00';

and returns the values ('2012-02-17', 'X');


MySQL itself is being inconsistent here but Devart adding ' 00:00:00' to pure Date fields isn't helping matters. Both queries return the same values ('2012-02-17', 'X') for
where DateFld='2012-02-17';

Hello,

I cannot reproduce the problem. When parameters are used, MyDAC sends the following query:In order to investigate the problem, please specify the following:
- the exact code you are using;
- the exact version of MyDAC. You can learn it from the About sheet of TMyConnection Editor.

Hi Andrey,

I'm using 5.90.0.60 for rad studio 2009. The above example returns the results described above in my version.

I also mentioned it towards the end of this thread http://forums.mysql.com/read.php?20,511 ... msg-511745 on the MySQL General forum although the reply I got was far from satisfactory.

I cannot reproduce the problem. Please try creating a small sample to demonstrate the problem and send it to andreyz*devart*com .

Apologies Andrey, I have been unable to reproduce it myself using the code I gave you above. I can still reproduce the problem in my programme but the problem queries involve around 8 linked tables with a multitude of calculated fields and are too much work to pass on. The DateFld field in both the query and the source query both return DateFld->DataType as ftDate but the text being passed to the MySQL server definitely shows DateFld as DateTime type (I'm trapping the text using your monitor).

I have found a way round the problem by assigning the parameter directly using ParamByName("DateFld")->AsDate so I'll just leave it at that. Thanks for your time.

If you have time and you reproduce this problem in a small sample, please send it to me for investigation.