Different index order between MyDAC (on Windows) and MySQL

[zeltron73]

Hello,

Here is the code to reproduce the issue:

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
begin
  Query.SQL.Text := 'SELECT * FROM (' +
    '(SELECT 1 AS Field1, "AAA" AS Field2) UNION ' +
    '(SELECT 2, "AAA-BBB") UNION ' +
    '(SELECT 3, "aaaBBB") UNION ' +
    '(SELECT 4, "aaa BBB")) t ' +
    '&OrderByClause';
  Query.MacroByName('OrderByClause').Value := 'ORDER BY Field2';
  Query.IndexFieldNames := '';
  Query.Open;
  MessageDlg('Ordered by MySQL', mtInformation, [mbOK], 0);

  Query.MacroByName('OrderByClause').Value := '';
  Query.IndexFieldNames := 'Field2 CIS';
  Query.Open;
  MessageDlg('Ordered by MyDAC', mtInformation, [mbOK], 0);
end;

Regards.

[AndreyZ]

Hello,

To compare ansi strings, we use the CompareStringA Windows function with the NORM_IGNORECASE option (to make it case-insensitive). CompareStringA treats the space symbol code as bigger than the '-' symbol code. That's why you receive different results. We cannot influence such CompareStringA behaviour. You can find more detailed description of the CompareStringA function in MSDN.