Devart Forums

I'm not sure if subqueries have something to do with it. Anyway, the following (admittedly useless) query should illustrate the problem.

select *,
(select @x:=sum(r.Price) from Orders r where r.ID=o.ID) as X,
@x+0 as Y
from Orders o;

In the above Y equals 1/10th of what its' value should be. In some of the queries I've been running (all of which return the correct values using the MySQL command line client) some of the user variables are returning values such as 4.9032567e-309 instead of an integer value.

Try downloading last version of MyDAC (4.30.1.14). If problem persists then
send us complete small sample to demonstrate it, including script to create and fill table.
Also supply us following information
- Exact version of Delphi, C++ Builder or Kylix
- Exact version of MySQL server and MySQL client. You can see it in Info sheet of TMyConnection Editor
- What the result is if you set FieldsAsString option to True

Apologies, the above example works correctly and I have been unable to duplicate the problem outwith my own extensive data. I will try to come up with a slimmed down version of that.

In the meantime, I noticed the following,

1) I'm viewing the data in a Master/Detail form. When the Master query first opens the correct data shows for the Detail query. Thereafter, moving the Master record and triggering a new Detail result set yields the incorrect data (including if you move back to the opening/first Master record).

2) Setting the FieldsAsString option to true does result in the correct values being shown for every Detail result set but these are in "elongated" form (e.g. 0.000000000000000 instead of 0) for all bar the Detail result set when the form first opens.

PS Using
MySQL server version: 5.0.20a-nt
MySQL client version: Direct
Borland C++ Builder Version 6.0 (Build 10.166)
Windows XP (Build 2600: Service Pack 2)

1) We have tested this issue using queries like in your first post, but no problem was found. Try to determine what data and what query cause this problem.

2) If FieldsAsString option is set to True, MyDAC doesn't convert values received from server into internal format. All values is presented as strings. So, you should see the same result running this query using standard MySQL client.

The following should illustrate the problem

create table if not exists
Customer(CustID tinyint, Name char(20), primary key (CustID));
insert into Customer values
(1, "John"),
(2, "Jack");

create table if not exists
Orders(OrderID tinyint, CustID tinyint, ProductID tinyint, Price decimal(6,2), primary key (OrderID));
insert into Orders Values
(1, 1, 1, 10),
(2, 1, 2, 20),
(3, 2, 3, 30),
(4, 2, 4, 40);

Now in c++ builder create a master query

select * from Customer;

and a detail query

select *,
(0,0)=(select @x:=Count(*), @y:=sum(Price) from Orders r where r.CustID=o.CustID) as Dummy,
@x+0 as X,
@y+0 as Y
from Orders o
where o.CustID=:CustID;

You will have to use a TDBGrid and TDataSource component for each query and set the MasterSource property for the detail query to point to the TDataSource for the master. Note - it will appear to work correctly if you set the 2 queries to active in the IDE, the errors won't show until you compile and run the actual project.

The above should show the X column as incorrect and the Y column containing 1/10th of the value that it should.

P.S. Removing the '+0' to get '@x as X, @y as Y' results in even weirder behaviour with fields not showing their values.

Thank you for example. We have reproduced the problem with it quite easy. Result of the first executing of the big query is different from second one, third one, etc. So, first time server says that X is float field, second, third, etc times X becomes Int64. MyDAC describes fields only first time after changing SQL property. So, MyDAC tries to assign Int64 value to FloatField. Invalid field type casting causes data loss. You can make a request to MySQL AB about this issue.
Wrong value for Y column is generates because for the first query server returns "30", while for the second one, third one, etc it returns "30.000000000000000000000000000000" (This can be reproduced with standard MySQL client). Error happens when MyDAC converts such values. We have fixed this problem of MyDAC. This fix will be included in the next MyDAC build.

Setting MyQuery.FieldDefs.Updated to False before opening MyQuery helps to avoid this problem.

Thanks Antaeus. I can't say I was able to reproduce the error within MySQL client but, with respect to the rest of your post I did notice this morning that you could get the query to work properly by forcing the field type to be unambiguous

e.g.

select *,
(0,0)=(select @x:=Count(*), @y:=sum(Price) from Orders r where r.CustID=o.CustID) as Dummy,
@x div 1 as X,
round(@y,2) as Y
from Orders o
where o.CustID=:CustID;

produces the correct result.

> I can't say I was able to reproduce the error within MySQL client
I mean if you start MySQL client, connect to the server and run following query two times You will get for the first time: for the second time:

Thanks Antaeus. I was using the MySQL query browser which seemed to be returning the correct result. I will post something on the MySQL forum about this.