Devart's handling of Date params causing error in query

[NoComprende]

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';

[AndreyZ]

Hello,

I cannot reproduce the problem. When parameters are used, MyDAC sends the following query:

Code: Select all

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;

:DateParam(Date,IN)=2012-02-17

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.

[NoComprende]

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.

[AndreyZ]

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

[NoComprende]

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.

[AndreyZ]

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