Error getting RecordCount of DISTINCT Query in MySQL

Elias Zurschmiede · Post by **Elias Zurschmiede** » Wed 13 Jul 2011 08:18

Hi

Executing a DISTINCT query having the keyword "AS" in select field list not working with UniDAC MySQL Provider.

The problem is, UniDAC internally creates a SELECT COUNT query to get the recordcount (haveing UniDirectional = true). In a SELECT COUNT DISTINCT query no AS keyword in fieldlist is allowed - so you have to remove this for the count query.

How to reproduce:

Code: Select all

procedure TForm2.Button1Click(Sender: TObject);
begin
  UniQuery2.UniDirectional := true;
  UniQuery2.Options.QueryRecCount := true;
  UniQuery2.SQL.Text := 'SELECT DISTINCT name AS myname, surname AS mysurname FROM addresses';
  UniQuery2.Open;
end;

MySQL Error-Code (btw: depends not on any MySQL version, its the same on all version since 3.x):

#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS myname, surname AS mysurname ) FROM addresses' at line 1.

My workaround in MyServicesUni.pas:
(removing the AS keyword and its following name for distinct count querys)

Code: Select all

function TCustomMyDataSetService.GetRecCount: integer;
  function GetCount(const s: _string): longint;
  var
    UQ: TCustomDADataSet;
    i: integer;
    MonitorClass: TDASQLMonitorClass;
    MessageID: cardinal;
  begin
    FUpdater.CheckUpdateQuery(stCustom);
    UQ := FUpdater.UpdateQuery as TCustomDADataSet;
    UQ.SQL.Text := s;

    UQ.Macros.Assign(FDataSet.Macros);
    for i := 0 to FDataSet.Params.Count - 1 do
      UQ.Params[i].Assign(FDataSet.Params[i]);

    MonitorClass := TDASQLMonitorClass(TDBAccessUtils.SQLMonitorClass(UsedConnection));
    if not TDBAccessUtils.GetLockDebug(FDataSet) and (MonitorClass.HasMonitor or FDataSet.Debug) then
      MonitorClass.SQLExecute(FDataSet, s, UQ.Params, 'Get RecCount', MessageID, True);

    FInGetRecCount := True;
    try
      UQ.Execute;
      Result := UQ.Fields[0].AsInteger;
    finally
      FInGetRecCount := False;
    end;

    if not TDBAccessUtils.GetLockDebug(FDataSet) and (MonitorClass.HasMonitor or FDataSet.Debug) then
      MonitorClass.SQLExecute(FDataSet, s, UQ.Params, 'Get RecCount', MessageID, False);
  end;

  function RemoveAsFromSelectFields(const AFields: String): String; // EZ: impl. whole methode to support count-querys for distinced querys having AS XY in select field list
  var
    i, i2: Integer;
  begin
    Result := AFields;
    i := Pos(' AS ', Result);
    while i > 0 do begin
      i2 := i + 4;
      while (i2  0) and (FDataSet.Params[0].ParamType = ptResult)) then begin // Current SQL does not have RETURN parameter
    s := FDataSet.FinalSQL;
    s := {$IFDEF CLR}Devart.Dac.{$ENDIF}DBAccess._SetOrderBy(s, '', TMyParser);
    Parser := TMyParser.Create(s);
    Parser.OmitBlank := True;
    Parser.OmitComment := True;
    try
      if Parser.ToLexem(lxSELECT)  lcEnd then begin
        SelectPos := Parser.CurrPos;
        Lexem := Parser.GetNextCode;
        HaveDistinct := (Lexem = lxDISTINCT) or (Lexem = lxDISTINCTROW);

        if Parser.ToLexem(lxFROM)  lcEnd then begin
          FromPos := Parser.CurrPos;

          if Parser.ToLexem(lxLIMIT)  lcEnd then begin
            if Parser.ToLexem(7)  lcEnd then // ';'
              DelimiterPos := Parser.CurrPos
            else
              DelimiterPos := MaxInt;
            s := Copy(s, 1, SelectPos) + ' COUNT(*) FROM (SELECT ' + Copy(s, SelectPos + 1, DelimiterPos - 1 - SelectPos) + ') q' + Copy(s, DelimiterPos, MaxInt);
          end
          else
          if HaveDistinct then
            s := Copy(s, 1, SelectPos) + ' COUNT(' + RemoveAsFromSelectFields(Copy(s, SelectPos + 1, FromPos - 4 - SelectPos)) + ')' + Copy(s, FromPos - 4 {length('FROM')}, MaxInt) // EZ: RemoveAsFromSelectFields added, see comment above
          else
            s := Copy(s, 1, SelectPos) + ' COUNT(*)' + Copy(s, FromPos - 4 {length('FROM')}, MaxInt);

          Result := GetCount(s);
        end;
      end;
    finally
      Parser.Free;
    end;
  end
  else
    Result := inherited GetRecCount;
end;

May you can implement a better solution based on your parser, this is only a quick-and-dirty hack to get it work because i'm under time pressure.

My workaround in MyParserUni.pas:
Added this line on end of Unit, else HaveDistinct in TCustomMyDataSetService.GetRecCount gets not initialized because the Parser don't know the DISTINCT keyword.

Code: Select all

MyKeywordLexems.AddObject('DISTINCT', TObject(Integer(lxDISTINCT))); // EZ!

Thanks for fixing this in next release

Regards
Elias Zurschmiede

AndreyZ · Post by **AndreyZ** » Wed 13 Jul 2011 12:14

Hello,

We have reproduced the problem and the investigation of the problem is in progress. As soon as we have any results, we will let you know.

Elias Zurschmiede · Post by **Elias Zurschmiede** » Wed 28 Sep 2011 11:23

Hi

Is this bug fixed in Version 4.0?

Thanks for a short reply
greetings Elias

AndreyZ · Post by **AndreyZ** » Fri 30 Sep 2011 08:32

This problem is not fixed in UniDAC 4.0.1. We will fix this problem in one of the nearest UniDAC builds.

Elias Zurschmiede · Post by **Elias Zurschmiede** » Fri 30 Sep 2011 11:04

ok - thanks for your reply.