Re: Autoincrement value not returned when using join command

[Krunoslav Farkas]

Perhaps I didn't explain everything clearly. I have try to provide minimum required info necessary to reproduce issue.

I'm using Delphi with Devart MyDAC components to access data. To create example you have to create tables like described in previous post. In data module create TMyQuery object with SQL commands like described:

select `t1`.*, `t2`.NAME NAME_T2, `t3`.NAME NAME_T3
from `t1`
left outer join `t2` on `t2`.CODE = `t1`.CODE_T2
left outer join `t3` on `t3`.CODE = `t1`.CODE_T3;

Even creating form with Delphi wizard should suffice to notice issue. Form that show data fields in upper parts and DBGrid to show data in table in lower part.

Whenever you enter new data in table and post it, all data is visible in table except autoincrement ID value. Next, after using refresh button on DBNavigator or in code cursor that marks row and show data in upper part of form is placed on first table in row, instead to remain on same place.

It is extremely important in business applications, if user need to navigate trough table and change some data, UI would show always wrong data on screen after posting. User are not always aware of it and it is high chance that user can enter data in wrong row.

Another example is if you use some data process trough while loop where you need to calculate some data and wrote it in table, using it in scenario like this:

ExampleQuery.first;
while not ExampleQuery.EOF do
begin
// do some calculations
ExampleQuery.Edit;
ExampleQuery.FieldByName('SOMEDATA1').asstring:= 'DesiredValue';
ExampleQuery.Post;
ExampleQuery.Refresh;
ExampleQuery.Next;
end;

In that case application would "freeze" as it will never reach the end of loop due to bug that would always return position in table in first row.

Same issue happens with Delphi 5, Delphi 2009, Delphi XE2 and Delphi 10. I have thought that issue is somewhere in MyDAC components, but Devart team investigated it and wrote me this response:

"When investigating we found that this behavior is related to MySQL functionality
specificity for server versions 5.6.x and higher (and some 5.5.x) and we cannot
affect it. For MySQL server of version 5.6.x and higher (and some 5.5.x), the
information about autoincrement field is not passed for a query with JOIN and MyDAC cannot get the autoincrement field value."