Loosing AutoInc Value when Using "Order By" on a complex querie

[Guest]

Hello,

I finally found "my bug".
When using complex queries and "Order by" keyword, the AutoInc key isn't retreive. I have test this behaviour on MySQL 4.0.x and MySQL 4.1.x

Just test this simple query (You must have an AutoInc field on the 1st table)

Select
Table1.*,
Table2.Name,
Table3.Rate
From
Table1
INNER JOIN Table2 on (Table1.Id = Table2 .Id)
LEFT OUTER JOIN Table3 ON (Table2.Id = Table3 .Id)
Order by
Table1.Id

When inserting a new record, and after posting , I can't see the new created ID number. I have to refresh the dataset to get it (not useful)

When I remove the Order by clause, I can see the new ID after posting.

Is this a MySQL issue , or an component limitation ?
Is there a solution ?

Thank you and keep going on with your wonderfull product

[Ikar]

It is a restriction of MySQL. To avoid it use instead JOIN WHERE.

[Guest]

Humm. I have more than 100 queries using the join keyword.
I can't afford to rewrite and re-test all of them.

When calling the refreshrecord method, I can get the auto inc field !. So, i'm using it in the after insert statement (it is not really "clean" but it must works )

[Ikar]

You are right, it is not the best solution. But on using JOIN values of autoinc-fields aren't retrieved from the server.