TClientDataSet与底层TFDQuery记录数不一致问题排查

组件序列 #

TClientDataSet -> TDataSetProvider -> TFDQuery -> TFDConnection

TFDQuery的SQL语句 #

select * from tt_calendar where (tt_type=0)
and coalesce(tt_base_idx,0) = 0

执行代码与现象 #

初始状态所有组件未激活，执行以下代码：

QryCalendarStorage.Open;
cdsCalendarStorage.Open;
ShowMessage('QryCalendarStorage.RecordCount: ' + IntToStr(QryCalendarStorage.RecordCount) + ', CdsCalendarStorage.RecordCount: ' + IntToStr(CdsCalendarStorage.RecordCount));

弹出消息显示TFDQuery记录数为50，TClientDataSet为70，为何出现差异？

后续疑问：若省略QryCalendarStorage.Open，查询组件记录数显示为0

数据库表信息：表中共有122条记录，所有记录的tt_type均为0，tt_base_idx包含NULL及其他不同值。在数据库工具中执行上述SQL，结果与TClientDataSet的70条记录一致。

完整源码（32位Delphi Alexandria 11.3） #

Pas文件 #

unit uRecordcount;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, FireDAC.Stan.Intf, FireDAC.Stan.Option,
  FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,
  FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.Phys.IB,
  FireDAC.Phys.IBDef, FireDAC.VCLUI.Wait, FireDAC.Stan.Param, FireDAC.DatS,
  FireDAC.DApt.Intf, FireDAC.DApt, Vcl.StdCtrls, FireDAC.Comp.DataSet,
  FireDAC.Comp.Client, Data.DB, Datasnap.DBClient, Datasnap.Provider,
  FireDAC.Phys.FB, FireDAC.Phys.FBDef;

type
  TFrmRecordcount = class(TForm)
    dprCalendarStorage: TDataSetProvider;
    cdsCalendarStorage: TClientDataSet;
    FDConnection: TFDConnection;
    QryCalendarStorage: TFDQuery;
    BtnOpen: TButton;
    procedure BtnOpenClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
  private
  public
  end;

var
  FrmRecordcount: TFrmRecordcount;

implementation

{$R *.dfm}

procedure TFrmRecordcount.BtnOpenClick(Sender: TObject);
begin
   QryCalendarStorage.Open;
   cdsCalendarStorage.Open;
   ShowMessage('QryCalendarStorage.RecordCount: ' + IntToStr(QryCalendarStorage.RecordCount) + ', CdsCalendarStorage.RecordCount: ' + IntToStr(CdsCalendarStorage.RecordCount));
end;

procedure TFrmRecordcount.FormCreate(Sender: TObject);
begin
   FDConnection.Params.Database := 'localhost:'+ ExtractFilePath(ParamStr(0)) + 'calendar.gdb';
end;

end.

DFM文件 #

object FrmRecordcount: TFrmRecordcount
  Left = 0
  Top = 0
  Caption = 'Query and ClientdataSet Recordcount'
  ClientHeight = 241
  ClientWidth = 388
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -12
  Font.Name = 'Segoe UI'
  Font.Style = []
  OnCreate = FormCreate
  TextHeight = 15
  object BtnOpen: TButton
    Left = 48
    Top = 192
    Width = 97
    Height = 25
    Caption = 'Open datasets'
    TabOrder = 0
    OnClick = BtnOpenClick
  end
  object dprCalendarStorage: TDataSetProvider
    DataSet = QryCalendarStorage
    Left = 144
    Top = 64
  end
  object cdsCalendarStorage: TClientDataSet
    Aggregates = <>
    FieldDefs = <
      item
        Name = 'TT_CALENDAR_ID'
        Attributes = [faRequired]
        DataType = ftInteger
      end
      item
        Name = 'TT_PARENTID'
        DataType = ftInteger
      end
      item
        Name = 'TT_TYPE'
        DataType = ftInteger
      end
      item
        Name = 'TT_START'
        DataType = ftTimeStamp
      end
      item
        Name = 'TT_FINISH'
        DataType = ftTimeStamp
      end
      item
        Name = 'TT_OPTIONS'
        DataType = ftInteger
      end
      item
        Name = 'TT_CAPTION'
        DataType = ftString
        Size = 255
      end
      item
        Name = 'TT_RECURRENCEINDEX'
        DataType = ftInteger
      end
      item
        Name = 'TT_RECURRENCEINFO'
        DataType = ftBlob
      end
      item
        Name = 'TT_EMP_ID'
        DataType = ftInteger
      end
      item
        Name = 'TT_LOCATION'
        DataType = ftString
        Size = 255
      end
      item
        Name = 'TT_MESSAGE'
        DataType = ftMemo
      end
      item
        Name = 'TT_REMINDERDATE'
        DataType = ftTimeStamp
      end
      item
        Name = 'TT_REMINDERMINUTES'
        DataType = ftInteger
      end
      item
        Name = 'TT_STATE'
        DataType = ftInteger
      end
      item
        Name = 'TT_LABELCOLOR'
        DataType = ftInteger
      end
      item
        Name = 'TT_ACTUALSTART'
        DataType = ftTimeStamp
      end
      item
        Name = 'TT_ACTUALFINISH'
        DataType = ftTimeStamp
      end
      item
        Name = 'TT_ACT_ID'
        DataType = ftInteger
      end
      item
        Name = 'TT_PRJ_ID'
        DataType = ftInteger
      end
      item
        Name = 'TT_CUST_ID'
        DataType = ftInteger
      end
      item
        Name = 'TT_ORG_ID'
        DataType = ftInteger
      end
      item
        Name = 'TT_HOURS'
        DataType = ftSingle
      end
      item
        Name = 'TT_REMAINING'
        DataType = ftSingle
      end
      item
        Name = 'TT_STATUS'
        DataType = ftInteger
      end
      item
        Name = 'TT_PERCENT'
        DataType = ftSingle
      end
      item
        Name = 'TT_SHAREDID'
        DataType = ftInteger
      end
      item
        Name = 'TT_PRIVATE'
        DataType = ftInteger
      end
      item
        Name = 'TT_TASKINDEX'
        DataType = ftInteger
      end
      item
        Name = 'TT_TASKCOMPLETE'
        DataType = ftInteger
      end
      item
        Name = 'TT_TASKSTATUS'
        DataType = ftInteger
      end
      item
        Name = 'TT_GROUPID'
        DataType = ftInteger
      end
      item
        Name = 'TT_PLANTYPE'
        DataType = ftInteger
      end
      item
        Name = 'TT_BASE_IDX'
        DataType = ftInteger
      end
      item
        Name = 'TT_BASE_DESC'
        DataType = ftString
        Size = 50
      end
      item
        Name = 'TT_BASE_SAVEDATE'
        DataType = ftDate
      end
      item
        Name = 'TT_TASKLINKS'
        DataType = ftBlob
      end
      item
        Name = 'TT_START2'
        DataType = ftTimeStamp
      end
      item
        Name = 'TT_FINISH2'
        DataType = ftTimeStamp
      end
      item
        Name = 'TT_BASE_ORIGIN_ID'
        DataType = ftInteger
      end
      item
        Name = 'TT_INFO'
        DataType = ftMemo
      end>
    IndexDefs = <>
    Params = <>
    ProviderName = 'dprCalendarStorage'
    StoreDefs = True
    Left = 144
    Top = 112
  end
  object FDConnection: TFDConnection
    Params.Strings = (
      'User_Name=sysdba'
      'Password=masterkey'
      'DriverID=FB')
    Left = 248
    Top = 32
  end
  object QryCalendarStorage: TFDQuery
    Connection = FDConnection
    SQL.Strings = (
      'select * from tt_calendar where (tt_type=0)'
      'and coalesce(tt_base_idx,0) = 0')
    Left = 248
    Top = 96
  end
end

内容的提问来源于stack exchange，提问作者Jan Doggen