Parameter substitution for MySQL TIME value bug

[NoComprende]

If I put the following code in a TMyQuery

select * from MySQLTable where TimeValue > :ParamTimeValue;

and ParamTimeValue is taken from a MasterSource table where the value is 14:10:00 then the final text that is sent to the MySQL server is

select * from MySQLTable where TimeValue > '14:10:0';

The missing '0' in the substitution means the result contains records where the value of TimeValue was 14:10:00 because
'14:10:00' > '14:10:0'

[NoComprende]

It's also missing out the trailing zero in the minute field as well if the minute field is zero e.g. the substitution for 17:00:00 is '17:0:0'.

[Dimon]

I could not reproduce the problem.
The point is that MySQL compares Time fields as time, but not as string. Therefore '14:10:00' = '14:10:0'.

[NoComprende]

I could not reproduce the problem.
The point is that MySQL compares Time fields as time, but not as string. Therefore '14:10:00' = '14:10:0'.

Dimon, I simplified the code in the above example. It may be because I am using the following code

where (MyDate,MyTime,MyString)>(:MyDate,:MyTime,:MyString)

in my query rather than writing out the long winded version of that comparison. Try to reproduce it using

select * from MySQLTable where (TimeValue,StringValue) > (:TimeValue,:StringValue);

[Dimon]

Plase try to compose a small sample to demonstrate the problem and send it to dmitryg*devart*com.

[NoComprende]

In the MySQL query browser

select time('12:50:00')=time('12:50:0');

returns 1

and

select time('12:50:00')='12:50:00';

returns 1

but

select time('12:50:00')='12:50:0';

returns 0.


While this error may be down to MySQL it would be easily solved if MyDAC substituted the full number of digits.

[Dimon]

Ok. We will fix this problem. This fix will be included in the next MyDAC build.

[NoComprende]

Thanks Dimon.