[SOLVED] Different number of records in TClientDataSet and underlying TFDQuery

Simple component sequence:

TClientDataSet -> TDataSetProvider -> TFDQuery -> TFDConnection

De TFDQuery has SQL:

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

Nothing is active.

I do:

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

This shows 50 and 70 records respectively. Why the difference?
(And follow-up question: if I omit QryCalendarStorage.Open, this reports record count 0 for the query component)

There are 122 records in the table, tt_type=0 for all, tt_base_idx has different values including null

Executing

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

in a DB tool gives the 70 records in the TClientDataset.

Full sources of the 32-bit Delphi Alexandria 11.3 app:

Pas file:

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 file:

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