IndexDef:       TMYLDBIndexDef;
                   Expression:  TMYLDBExpression); override;
    // add checks to multi-field index scan condition
    procedure ExtendIndexScanCondition(
                   ScanCondition: TMYLDBScanSearchCondition;
                   IndexDef:      TMYLDBIndexDef;
                   Expression:    TMYLDBExpression); override;
  end;


  TMYLDBExprNodeArithmetic = class(TMYLDBExprNode)
   protected
    // SUB operation
    procedure SubData;
    // ADD operation
    procedure AddData;
    // MUL operation
    procedure MulData;
    // DIV operation
    procedure DivData;
    // MYLDB operation
    procedure GetMYLDBValue;
    // ACOS operation
    procedure GetACOSValue;
    // ASIN operation
    procedure GetASINValue;
    // ATAN operation
    procedure GetATANValue;
    // CEIL operation
    procedure GetCEILValue;
    // COS operation
    procedure GetCOSValue;
    // EXP operation
    procedure GetEXPValue;
    // FLOOR operation
    procedure GetFLOORValue;
    // LOG operation
    procedure GetLOGValue;
    // POWER operation
    procedure GetPOWERValue;
    // RAND operation
    procedure GetRANDValue;
    // ROUND operation
    procedure GetROUNDValue;
    // SIGN operation
    procedure GetSIGNValue;
    // SIN operation
    procedure GetSINValue;
    // SQR operation
    procedure GetSQRValue;
    // SQRT operation
    procedure GetSQRTValue;
   public
    // Return Data Value
    function GetDataValue: TMYLDBVariant;  override;
    // get value data type
    function GetDataType: TMYLDBAdvancedFieldType; override;

  end;


  TMYLDBExprNodeAggregated = class(TMYLDBExprNode)
   private
    count: integer;
    distinct: Boolean;
    AvgSum: TMYLDBVariant;
   public
    // Constructors
    constructor Create(Op: TMYLDBDataOperator); overload;
    constructor Create(Op: TMYLDBDataOperator;
                       distinct: boolean;
                       Node: TMYLDBExprNode); overload;
    // Destructor
    destructor Destroy; override;
    // Return Data Value
    function GetDataValue: TMYLDBVariant;  override;
    // return Type of Data
    function GetDataType: TMYLDBAdvancedFieldType; override;
    // return Size of Data (for strings and arrays)
    function GetDataSize: Integer; override;
    // is expression contains aggregated function
    function IsAggregated: Boolean; override;
    function IsAggregatedCountAll: Boolean; override;
    function CanBeAssigned(AO, ParentQueryAO: TMYLDBAO;
                           ParentCursor: TMYLDBCursor; ForHavingClause: Boolean = False): Boolean; override;
    // Init for aggregated functions
    procedure Init; override;
    // Accumulate for group functions
    procedure Accumulate; override;
    procedure SetCountAll(RecordCount: Integer); override;
    // check if agregate expr, const or only grouped fields are used
    function CanBeUsedInGroupBy(GroupByFields: String): Boolean; override;
  end;


  TMYLDBExprNodeCast = class(TMYLDBExprNode)
  private
    FCastType:      TMYLDBAdvancedFieldType;  // Type for convertation
    LCastSizeNode:  TMYLDBExprNode;
   public
    constructor Create(Node:          TMYLDBExprNode;
                       CastType:      TMYLDBAdvancedFieldType;
                       CastSizeNode:  TMYLDBExprNode = nil); overload;
    // Return Data Value
    function GetDataValue: TMYLDBVariant;  override;
    // return Type of Data
    function GetDataType: TMYLDBAdvancedFieldType; override;
    // return Size of Data (for strings and arrays)
    function GetDataSize: Integer; override;
  end;


  TMYLDBExprNodeCase = class(TMYLDBExprNode)
   private
    FWhenExpressions: TList;
    FResultExpressions: TList;
    FElseResultExpression: TMYLDBExprNode;
    FInputExpression: TMYLDBExprNode;
    FCoalesceNodes: TMYLDBExprNodeArray;
    FIsCoalesceExpression: Boolean;
   public
    // Constructors
    procedure CreateInit;
    constructor CreateSimpleCase(InputExpression: TMYLDBExprNode);
    constructor CreateSearchedCase;
    constructor CreateCoalesce(Nodes: TMYLDBExprNodeArray);
    // Destructor
    destructor Destroy; override;

    // Return Data Value
    function GetDataValue: TMYLDBVariant;  override;
    // return Type of Data
    function GetDataType: TMYLDBAdvancedFieldType; override;
    // return Size of Data (for strings and arrays)
    function GetDataSize: Integer; override;

    procedure AddWhenExpression(Node: TMYLDBExprNode);
    procedure AddResultExpression(Node: TMYLDBExprNode);
    procedure SetElseResultExpression(Node: TMYLDBExprNode);
  end;

  TMYLDBExprNodeBlob = class(TMYLDBExprNode)
   private
    procedure MimeToBin;
   public
    function GetDataValue: TMYLDBVariant;  override;
    function GetDataType: TMYLDBAdvancedFieldType; override;
    function GetDataSize: Integer; override;
  end;


  TMYLDBExprNodeStringFunction = class(TMYLDBExprNode)
   protected
    function InitStringValue: Boolean;
   protected
    procedure Concat;
    procedure Upper;
    procedure Lower;
    procedure Trim;
    procedure LTrim;
    procedure RTrim;
    procedure Length;
    procedure Pos;
    procedure Substring;
   public
    // Return Data Value
    function GetDataValue: TMYLDBVariant;  override;
    // return Type of Data
    function GetDataType: TMYLDBAdvancedFieldType; override;
    // return Size of Data (for strings and arrays)
    function GetDataSize: Integer; override;
  end;


  TMYLDBExprNodeSystem = class(TMYLDBExprNode)
   private
    FTableName:   String;
    FColumnName:  String;
    FInMemory:    Boolean;
    FTable:       TDataSet;
    FFieldType:   TMYLDBAdvancedFieldType;
    FRowNumValue: integer;
   private
    procedure LastAutoInc;
    procedure RowNum;
   public
    // creates
    constructor Create(Operator: TMYLDBDataOperator; SessionName, DatabaseName, TableName, ColumnName: String; InMemory: Boolean); overload;
    // Destructor
    destructor Destroy; override;
    // Return Data Value
    function GetDataValue: TMYLDBVariant;  override;
    // return Type of Data
    function GetDataType: TMYLDBAdvancedFieldType; override;
    // return Size of Data (for strings and arrays)
    function GetDataSize: Integer; override;
  end;


  TMYLDBExprNodeDateFunction = class(TMYLDBExprNode)
   private
    DateFormater: TDateFormater;
    ExtractDateTimeField: TMYLDBDateTimeExtractField;

    // ToDate
    procedure ToDate;
    // ToString
    procedure ToString;
    // Extract
    procedure Extract;
   public
    // Constructor
    constructor Create(Op: TMYLDBDataOperator;
                       Node: TMYLDBExprNode;
                       FormatStr: String); overload;
    constructor Create(Op: TMYLDBDataOperator;
                       ExtractField: TMYLDBDateTimeExtractField;
                       ExtractSource: TMYLDBExprNode); overload;

    // Destructor
    destructor Destroy; override;
    // Return Data Value
    function GetDataValue: TMYLDBVariant;  override;
    // return Type of Data
    function GetDataType: TMYLDBAdvancedFieldType; override;
    // return Size of Data (for strings and arrays)
    function GetDataSize: Integer; override;
  end;


  TCheckResultsMethod = function: Boolean of object;
  TMYLDBExprNodeSubquery = class(TMYLDBExprNode)
   private
    FMustHaveOneField: Boolean;
    FMustHaveOneRow: Boolean;
    FValue: TMYLDBVariant;
    FFieldValues: array of TMYLDBVariant;
    LDataset: TDataset;
    LLexer: TMYLDBLexer;
    LAO: TMYLDBAO;
    LCursor: TMYLDBCursor;
    LCursorRecordBuffer: TMYLDBRecordBuffer;
    FSubquery: TObject;// TMYLDBSQLUnion
    FCommandNo: Integer;
    FTokenNo: Integer;
    LValueToCheck: TMYLDBVariant;
    FPriorValueToCheck: TMYLDBVariant;
    FPriorResult: Boolean;
    FPriorResultInitalized: Boolean;
    FCompareOperator: TMYLDBDataOperator;
    FCompareQuantifier: TMYLDBQuantifier;
    FCorrelated: Boolean;

    function CheckGetDataValue: Boolean;
    function CheckGetFieldValues: Boolean;
    function CheckExists: Boolean;
    function CheckIsValueIn: Boolean;
    function CheckQuantifiedCompare: Boolean;
    function RunQueryAndCheckResults(CheckMethod: TCheckResultsMethod): Boolean;
   public
    // Constructor
    constructor Create(MustHaveOneField: Boolean; MustHaveOneRow: Boolean;
                       Dataset: TDataset; Lexer: TMYLDBLexer);
    destructor Destroy; override;
    // process assign cursor
    procedure AssignCursor(Cursor: TMYLDBCursor; ParentQueryAO: TMYLDBAO; ParentCursor: TMYLDBCursor); override;
    // process assign New Cursor Buffer
    procedure AssignCursorBuffer(Buffer: TMYLDBRecordBuffer; ApplyToParentQueryFields: Boolean); override;
    // process assign AO
    procedure AssignAO(AO, ParentQueryAO: TMYLDBAO; ParentCursor: TMYLDBCursor); override;
    function CanBeAssigned(AO, ParentQueryAO: TMYLDBAO; ParentCursor: TMYLDBCursor; ForHavingClause: Boolean = False): Boolean; override;
    // Return Data Value
    function GetDataValue: TMYLDBVariant;  override;
    // calc subquery field values again
    procedure ResetSubqueryFieldValues; override;
    // Return subquery field value
    function GetSubqueryFieldValue(FieldNo: Integer): TMYLDBVariant; override;
    // return Type of Data
    function GetDataType: TMYLDBAdvancedFieldType; override;
    // return Data Size
    function GetDataSize: Integer;  override;
    // EXISTS subquery
    function Exists: Boolean;
    // IN subquery
    function IsValueIn(Value: TMYLDBVariant): Boolean;
    // > ALL subquery
    function QuantifiedCompare(Operator: TMYLDBDataOperator;
                       Quantifier: TMYLDBQuantifier; Value: TMYLDBVariant): Boolean;
  end;




// get operator name
function GetOperatorName(op: TMYLDBDataOperator): String;

// Return FieldType
function GetFieldType(const TypeName: string): TMYLDBAdvancedFieldType;

procedure FillFieldNames(List: TStringList; const Names: String);

// "<" => ">", "=" => "="
function ReverseComparisonOperator(CompOp: TMYLDBDataOperator): TMYLDBDataOperator;

// "<" => ">=", "=" => "<>"
function InvertComparisonOperator(CompOp: TMYLDBDataOperator): TMYLDBDataOperator;

function IsNonConstantExpression(Value: TMYLDBVariant): Boolean;

implementation

uses MYLDBMain, MYLDBCompression, MYLDBSQLProcessor, MYLDBLocalEngine, MYLDBDecUtil,
     Math, MYLDBStrUtils, MYLDBDecimalRounding;

//------------------------------------------------------------------------------
// get operator name
//------------------------------------------------------------------------------
function GetOperatorName(op: TMYLDBDataOperator): String;
begin
  case op of
    doNOT:          Result := 'NOT';
    doAND:          Result := 'AND';
    doOR:           Result := 'OR';
    doEQ:           Result := '=';
    doNE:           Result := '<>';
    doLT:           Result := '<';
    doGT:           Result := '>';
    doLE:           Result := '<=';
    doGE:           Result := '>=';
    doLIKE:         Result := 'LIKE';
    doNOTLIKE:      Result := 'NOT LIKE';
    doISNULL:       Result := 'IS NULL';
    doISNOTNULL:    Result := 'IS NOT NULL';
    doADD:          Result := '+';
    doSUB:          Result := '-';
    doMUL:          Result := '*';
    doDIV:          Result := '/';
    doCONCAT:       Result := '||';
    doUPPER:        Result := 'UPPER';
    doLOWER:        Result := 'LOWER';
    doTRIM:         Result := 'TRIM';
    doLTRIM:        Result := 'LTRIM';
    doRTRIM:        Result := 'RTRIM';
    doSUM:          Result := 'SUM';
    doAVG:          Result := 'AVG';
    doMIN:          Result := 'MIN';