6.2.8 - CRGrid bug in GetFilterExpression

[pincopallino]

Hi,
in CRGrid.pas,
function TCRColumn.GetFilterExpression(const RawFilter: string): string;

Code: Select all

  if Assigned(Field) then
    if (Field.DataSet is TCustomDADataSet) then begin
      if not (dgeLocalFilter in TCRDBGrid(Grid).OptionsEx) and
         (Field.FieldName <> TCRFieldDesc(TDBAccessUtils.GetIRecordSet(TCustomDADataSet(Field.DataSet)).Fields[0]).ActualName) then
        FieldName := TCRFieldDesc(TDBAccessUtils.GetIRecordSet(TCustomDADataSet(Field.DataSet)).Fields[0]).ActualName
      else
        FieldName := Field.FieldName;
      if not (dgeLocalFilter in TCRDBGrid(Grid).OptionsEx) and
         (TCRFieldDesc(TDBAccessUtils.GetIRecordSet(TCustomDADataSet(Field.DataSet)).Fields[0]).TableInfo.TableAlias <> '') then
        FieldName := TCRFieldDesc(TDBAccessUtils.GetIRecordSet(TCustomDADataSet(Field.DataSet)).Fields[0]).TableInfo.TableAlias + '.' + FieldName;
    end
    else
      FieldName := Field.FieldName;

This new code, to manage the right name of a field, necessary when the field is from join table, is broken!
The constant 0 can not be right there.

The right thing to do is:

Code: Select all

  xFld : TFieldDescs;

  if not Assigned(Field) then Exit;
  if not (dgeLocalFilter in TCRDBGrid(Grid).OptionsEx) and (Field.DataSet is TCustomDADataSet) then begin
    xFld := TDBAccessUtils.GetIRecordSet(TCustomDADataSet(Field.DataSet)).Fields;
    for i := 0 to xFld.Count -1 do begin
      if (xFld[i].FieldNo = Field.FieldNo) then begin //not in same order!
        if Assigned(TCRFieldDesc(xFld[i]).TableInfo) and (TCRFieldDesc(xFld[i]).TableInfo.TableAlias <> '') then begin
          FieldName := TCRFieldDesc(xFld[i]).TableInfo.TableAlias + '.' + TCRFieldDesc(xFld[i]).ActualName;
        end else begin
          if Field.Origin = '' then
            FieldName := Field.FieldName
          else
            FieldName := Field.Origin
        end;
        Break;
      end;
    end; //for
    if (FieldName = '') then Exit; //Field not found!
  end else
    FieldName := Field.FieldName;

There is also, somewhere, a bug regard the value of Field.Origin (populated if FieldsOrigin in TCustomDADataSet.Options).
Firebird REQUIRE that if a table alias is used then only the alias can be used in the query, not the original table name. Other database are more permissive.
For this the value of Field.Origin is wrong if using aliases (the code above take care of this).


Fire who wrote it and also who tested it!

[AlexP]

Hello,

We have already fixed the problem. The fix will be included in the next version.

[pincopallino]

Hi,
your fix is not enough! With Firebird a problem remains.

Code: Select all

SELECT 
  LIBRI.*,
  EDITORI.NOME AS cEDITORE,
  CLN.NOME AS cCOLLANA,
  CATEGORIE.NOME AS cCATEGORIA,
  L1.NOME AS cLINGUA,
  L2.NOME AS cLINGUA_ORIG,
  COMBINA_AUTORI.CAUTORI,
  SUPPORTI.NOME AS cSUPPORTO,
  FORMATI.NOME AS cFORMATO,
  TIPOAUDIO.NOME AS cAUDIO
FROM
  LIBRI
  LEFT OUTER JOIN EDITORI ON (LIBRI.EDITORE = EDITORI.ID)
  LEFT OUTER JOIN CATEGORIE ON (LIBRI.CATEGORIA = CATEGORIE.ID)
  LEFT OUTER JOIN COLLANE CLN ON (LIBRI.COLLANA = CLN.ID)
  LEFT OUTER JOIN LINGUE L1 ON (LIBRI.LINGUA = L1.ID)
  LEFT OUTER JOIN LINGUE L2 ON (LIBRI.LINGUA_ORIG = L2.ID)
  LEFT OUTER JOIN COMBINA_AUTORI(LIBRI.ID, :Modo, LIBRI.TIPO) ON (1=1)
  LEFT OUTER JOIN SUPPORTI ON (LIBRI.SUPPORTO = SUPPORTI.ID)
  LEFT OUTER JOIN FORMATI ON (LIBRI.FORMATO = FORMATI.ID)
  LEFT OUTER JOIN TIPOAUDIO ON (LIBRI.AUDIO = TIPOAUDIO.ID)
ORDER BY EDITORI.NOME, CLN.NOME, LIBRI.NUMCOLL

With this kind of query I have problem when filtering by ID because your code can't resolve the right table name. With previous (< 6.2. version it worked (but not worked with JOINed fields).

I think that can circumvent this problem enumerating all fields of table LIBRI but this is not ever possible.

My code, in first post, works better!

[AlexP]

The code you provided leads to the same issue.
To resolve the issue, you should explicitly specify the table alias.