SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE expression_tree (
  expression_id bigint,
  node_id bigint PRIMARY KEY,
  value varchar(12),
  side char(1),
  parent_node_id bigint
);
INSERT expression_tree
  SELECT * FROM (VALUES
    (1, 1, '-', NULL, NULL), -- "-" at top
    (1, 2, '7', 'L', 1), 
    (1, 3, '*', 'R', 1), 
    (1, 4, '2', 'L', 3), 
    (1, 5, '3', 'R', 3)
    )AS tmp(expression_id, node_id, value, side, parent_node_id)
5 rows affected

SET STATISTICS XML ON;
CREATE TABLE #tree (
  node_id bigint PRIMARY KEY,
  value bigint,
  op varchar(2),
  side char(1),
  parent_node_id bigint,
  INDEX IX (parent_node_id) INCLUDE (side, value),
  CHECK (parent_node_id <> node_id)
);

INSERT #tree
SELECT
  et.node_id,
  CASE WHEN et.value NOT LIKE '%[^0-9]%' THEN et.value END,
  CASE WHEN et.value LIKE '%[^0-9]%' THEN et.value END,
  et.side,
  et.parent_node_id
FROM expression_tree et
WHERE et.expression_id = 1;

DECLARE @final bigint, @parent bigint;

WHILE 1=1
BEGIN
    WITH child AS (
        SELECT
          child.parent_node_id,
          MIN(CASE WHEN child.side = 'L' THEN child.value END) AS L,
          MIN(CASE WHEN child.side = 'R' THEN child.value END) AS R
        FROM #tree child
        WHERE child.parent_node_id IS NOT NULL
        GROUP BY
          child.parent_node_id
        HAVING COUNT(CASE WHEN child.value IS NULL THEN 1 END) = 0
    )
    UPDATE t
    SET @final = v.final,
        value = v.final,
        @parent = t.parent_node_id
    FROM #tree t
    JOIN child c ON c.parent_node_id = t.node_id
    CROSS APPLY (
        SELECT CASE t.op
              WHEN '+' THEN L + R
              WHEN '-' THEN ISNULL(L, 0) - R
              WHEN '*' THEN L * R
              WHEN '/' THEN L / R
              WHEN '%' THEN L % R
          END
    ) v(final)
    WHERE t.value IS NULL;
    
    IF @@ROWCOUNT = 0 OR @parent IS NULL
        BREAK;
END;

SELECT @final;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="INSERT #tree&#xa;SELECT&#xa;  et.node_id,&#xa;  CASE WHEN et.value NOT LIKE &apos;%[^0-9]%&apos; THEN et.value END,&#xa;  CASE WHEN et.value LIKE &apos;%[^0-9]%&apos; THEN et.value END,&#xa;  et.side,&#xa;  et.parent_node_id&#xa;FROM expression_tree et&#xa;WHERE et.expression_id = 1" StatementId="1" StatementCompId="2" StatementType="INSERT" StatementSqlHandle="0x0900F8C90F53FC2D189F46DDA78F182235CD0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" BatchSqlHandle="0x0200000095C5D9003BC0928BDC74F67468D69A5C54A9B2FE0000000000000000000000000000000000000000" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0232998" StatementEstRows="5" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x8DE935188D3CD898" QueryPlanHash="0x0E67E62A68730EB6" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="0" CompileCPU="0" CompileMemory="248"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2415768"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2024-03-27T22:13:32.31" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000001_35BCFE0A]" Table="[expression_tree]" Schema="[dbo]" Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Assert" LogicalOp="Assert" EstimateRows="5" EstimateIO="0" EstimateCPU="9e-07" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0232998" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Assert StartupExpression="0"><RelOp NodeId="1" PhysicalOp="Clustered Index Insert" LogicalOp="Insert" EstimateRows="5" EstimateIO="0.02" EstimateCPU="1e-05" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0232989" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[#tree]" Column="node_id"></ColumnReference><ColumnReference Table="[#tree]" Column="parent_node_id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="11" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[tempdb]" Schema="[dbo]" Table="[#tree]" Index="[PK__#tree_____5F19EF166F9A724F]" Storage="RowStore"></Object><Object Database="[tempdb]" Schema="[dbo]" Table="[#tree]" Index="[IX]" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[#tree].[node_id] = [fiddle_0618f5330bcf43a0b205258d05d851bb].[dbo].[expression_tree].[node_id] as [et].[node_id],[#tree].[value] = [Expr1003],[#tree].[op] = [Expr1004],[#tree].[side] = [fiddle_0618f5330bcf43a0b205258d05d851bb].[dbo].[expression_tree].[side] as [et].[side],[#tree].[parent_node_id] = [fiddle_0618f5330bcf43a0b205258d05d851bb].[dbo].[expression_tree].[parent_node_id] as [et].[parent_node_id]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Table="[#tree]" Column="node_id"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="node_id"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#tree]" Column="value"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#tree]" Column="op"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#tree]" Column="side"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="side"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[#tree]" Column="parent_node_id"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="parent_node_id"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="5" EstimateIO="0" EstimateCPU="5e-07" AvgRowSize="37" EstimatedTotalSubtreeCost="0.0032889" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="node_id"></ColumnReference><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="side"></ColumnReference><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="parent_node_id"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(bigint,CASE WHEN NOT [fiddle_0618f5330bcf43a0b205258d05d851bb].[dbo].[expression_tree].[value] as [et].[value] like &apos;%[^0-9]%&apos; THEN [fiddle_0618f5330bcf43a0b205258d05d851bb].[dbo].[expression_tree].[value] as [et].[value] ELSE NULL END,0)"><Convert DataType="bigint" Style="0" Implicit="1"><ScalarOperator><IF><Condition><ScalarOperator><Logical Operation="NOT"><ScalarOperator><Intrinsic FunctionName="like"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="value"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;%[^0-9]%&apos;"></Const></ScalarOperator></Intrinsic></ScalarOperator></Logical></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="value"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Convert></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(2),CASE WHEN [fiddle_0618f5330bcf43a0b205258d05d851bb].[dbo].[expression_tree].[value] as [et].[value] like &apos;%[^0-9]%&apos; THEN [fiddle_0618f5330bcf43a0b205258d05d851bb].[dbo].[expression_tree].[value] as [et].[value] ELSE NULL END,0)"><Convert DataType="varchar" Length="2" Style="0" Implicit="1"><ScalarOperator><IF><Condition><ScalarOperator><Intrinsic FunctionName="like"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="value"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;%[^0-9]%&apos;"></Const></ScalarOperator></Intrinsic></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="value"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="5" EstimatedRowsRead="5" EstimateIO="0.003125" EstimateCPU="0.0001625" AvgRowSize="42" EstimatedTotalSubtreeCost="0.0032875" TableCardinality="5" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="node_id"></ColumnReference><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="value"></ColumnReference><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="side"></ColumnReference><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="parent_node_id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="5" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="5" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="node_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="value"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="side"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="parent_node_id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Index="[PK__expressi__5F19EF168FDA3785]" Alias="[et]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_0618f5330bcf43a0b205258d05d851bb].[dbo].[expression_tree].[expression_id] as [et].[expression_id]=(1)"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0618f5330bcf43a0b205258d05d851bb]" Schema="[dbo]" Table="[expression_tree]" Alias="[et]" Column="expression_id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></ComputeScalar></RelOp></Update></RelOp><Predicate><ScalarOperator ScalarString="CASE WHEN [#tree].[parent_node_id]=[#tree].[node_id] THEN (0) ELSE NULL END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="[#tree]" Column="parent_node_id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Table="[#tree]" Column="node_id"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Predicate></Assert></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH child AS (&#xa;        SELECT&#xa;          child.parent_node_id,&#xa;          MIN(CASE WHEN child.side = &apos;L&apos; THEN child.value END) AS L,&#xa;          MIN(CASE WHEN child.side = &apos;R&apos; THEN child.value END) AS R&#xa;        FROM #tree child&#xa;        WHERE child.parent_node_id IS NOT NULL&#xa;        GROUP BY&#xa;          child.parent_node_id&#xa;        HAVING COUNT(CASE WHEN child.value IS NULL THEN 1 END) = 0&#xa;    )&#xa;    UPDATE t&#xa;    SET @final = v.final,&#xa;        value = v.final,&#xa;        @parent = t.parent_node_id&#xa;    FROM #tree t&#xa;    JOIN child c ON c.parent_node_id = t.node_id&#xa;    CROSS APPLY (&#xa;        SELECT CASE t.op&#xa;              WHEN &apos;+&apos; THEN L + R&#xa;              WHEN &apos;-&apos; THEN ISNULL(L, 0) - R&#xa;              WHEN &apos;*&apos; THEN L * R&#xa;              WHEN &apos;/&apos; THEN L / R&#xa;              WHEN &apos;%&apos; THEN L % R&#xa;          END&#xa;    ) v(final)&#xa;    WHERE t.value IS NULL" StatementId="2" StatementCompId="4" StatementType="UPDATE" StatementSqlHandle="0x09006833A2B2E4F550F42802C359D4C721C60000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" BatchSqlHandle="0x0200000095C5D9003BC0928BDC74F67468D69A5C54A9B2FE0000000000000000000000000000000000000000" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0398072" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xFC1E1149EA2054C7" QueryPlanHash="0x0427530FD4AF52E4" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="80" CompileTime="15" CompileCPU="15" CompileMemory="784"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2415768"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2024-03-27T22:13:32.32" ModificationCount="0" SamplingPercent="100" Statistics="[IX]" Table="[#tree_______________________________________________________________________________________________________________000000005CB6]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2024-03-27T22:13:32.32" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_BBC7654C]" Table="[#tree_______________________________________________________________________________________________________________000000005CB6]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2024-03-27T22:13:32.32" ModificationCount="0" SamplingPercent="100" Statistics="[PK__#tree_____5F19EF166F9A724F]" Table="[#tree_______________________________________________________________________________________________________________000000005CB6]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="1" PhysicalOp="Clustered Index Update" LogicalOp="Update" EstimateRows="1" EstimateIO="0.02" EstimateCPU="2e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0398072" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[tempdb]" Schema="[dbo]" Table="[#tree]" Index="[PK__#tree_____5F19EF166F9A724F]" Alias="[t]" Storage="RowStore"></Object><Object Database="[tempdb]" Schema="[dbo]" Table="[#tree]" Index="[IX]" Alias="[t]" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[@final] = [Expr1006],[@parent] = [Expr1007],#tree.[value] as [t].[value] = [Expr1005]"><ScalarExpressionList><ScalarOperator><Assign><ScalarOperator><Identifier><ColumnReference Column="@final"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator></Assign></ScalarOperator><ScalarOperator><Assign><ScalarOperator><Identifier><ColumnReference Column="@parent"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator></Assign></ScalarOperator><ScalarOperator><MultipleAssign><Assign><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0.013125" EstimateCPU="0.00010086" AvgRowSize="43" EstimatedTotalSubtreeCost="0.0198052" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1013"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1013"></ColumnReference><ScalarOperator ScalarString="[Expr1013]"><Identifier><ColumnReference Column="Expr1013"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="0.013125" EstimateCPU="0.00010086" AvgRowSize="43" EstimatedTotalSubtreeCost="0.0198052" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1013"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="5" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Spool><RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="43" EstimatedTotalSubtreeCost="0.00657932" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1013"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1013"></ColumnReference><ScalarOperator ScalarString="CASE WHEN [Expr1009] THEN (0) ELSE (1) END"><IF><Condition><ScalarOperator><Identifier><ColumnReference Column="Expr1009"></ColumnReference></Identifier></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="40" EstimatedTotalSubtreeCost="0.00657922" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1009"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1009"></ColumnReference><ScalarOperator ScalarString="CASE WHEN #tree.[value] as [t].[value] = [Expr1005] THEN (1) ELSE (0) END"><IF><Condition><ScalarOperator><Compare CompareOp="BINARY IS"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="47" EstimatedTotalSubtreeCost="0.00657912" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="[@final] = CASE WHEN #tree.[op] as [t].[op]=&apos;+&apos; THEN [Expr1003]+[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;-&apos; THEN isnull([Expr1003],(0))-[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;*&apos; THEN [Expr1003]*[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;/&apos; THEN [Expr1003]/[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;%&apos; THEN [Expr1003]%[Expr1004] ELSE NULL END END END END END"><Assign><ScalarOperator><Identifier><ColumnReference Column="@final"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;+&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;-&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="SUB"><ScalarOperator><Intrinsic FunctionName="isnull"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;*&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="MULT"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;/&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="DIV"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;%&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="MOD"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Assign></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="[@parent] = #tree.[parent_node_id] as [t].[parent_node_id]"><Assign><ScalarOperator><Identifier><ColumnReference Column="@parent"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="parent_node_id"></ColumnReference></Identifier></ScalarOperator></Assign></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="60" EstimatedTotalSubtreeCost="0.00657902" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="parent_node_id"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="CASE WHEN #tree.[op] as [t].[op]=&apos;+&apos; THEN [Expr1003]+[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;-&apos; THEN isnull([Expr1003],(0))-[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;*&apos; THEN [Expr1003]*[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;/&apos; THEN [Expr1003]/[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;%&apos; THEN [Expr1003]%[Expr1004] ELSE NULL END END END END END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;+&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;-&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="SUB"><ScalarOperator><Intrinsic FunctionName="isnull"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;*&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="MULT"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;/&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="DIV"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;%&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="MOD"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="52" EstimatedTotalSubtreeCost="0.00657892" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="parent_node_id"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference></OuterReferences><RelOp NodeId="9" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="9.6e-07" AvgRowSize="31" EstimatedTotalSubtreeCost="0.00329116" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="10" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="2" EstimateIO="0" EstimateCPU="0" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0032902" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1018],0)"><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1018"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="11" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="2" EstimateIO="0" EstimateCPU="3.4e-06" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0032902" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1018"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1018"></ColumnReference><ScalarOperator ScalarString="COUNT([Expr1010])"><Aggregate Distinct="0" AggType="COUNT_BIG"><ScalarOperator><Identifier><ColumnReference Column="Expr1010"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="MIN([Expr1011])"><Aggregate Distinct="0" AggType="MIN"><ScalarOperator><Identifier><ColumnReference Column="Expr1011"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="MIN([Expr1012])"><Aggregate Distinct="0" AggType="MIN"><ScalarOperator><Identifier><ColumnReference Column="Expr1012"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference></GroupBy><RelOp NodeId="12" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="4" EstimateIO="0" EstimateCPU="4e-07" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0032868" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference><ColumnReference Column="Expr1010"></ColumnReference><ColumnReference Column="Expr1011"></ColumnReference><ColumnReference Column="Expr1012"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1010"></ColumnReference><ScalarOperator ScalarString="CASE WHEN #tree.[value] as [child].[value] IS NULL THEN (1) ELSE NULL END"><IF><Condition><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[child]" Column="value"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1011"></ColumnReference><ScalarOperator ScalarString="CASE WHEN #tree.[side] as [child].[side]=&apos;L&apos; THEN #tree.[value] as [child].[value] ELSE NULL END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[child]" Column="side"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;L&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[child]" Column="value"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1012"></ColumnReference><ScalarOperator ScalarString="CASE WHEN #tree.[side] as [child].[side]=&apos;R&apos; THEN #tree.[value] as [child].[value] ELSE NULL END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[child]" Column="side"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;R&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[child]" Column="value"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="13" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="4" EstimatedRowsRead="4" EstimateIO="0.003125" EstimateCPU="0.0001614" AvgRowSize="24" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="5" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[child]" Column="value"></ColumnReference><ColumnReference Table="#tree" Alias="[child]" Column="side"></ColumnReference><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="#tree" Alias="[child]" Column="value"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#tree" Alias="[child]" Column="side"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#tree]" Index="[IX]" Alias="[child]" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><IsNotNull><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference></IsNotNull></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp><Predicate><ScalarOperator ScalarString="[Expr1002]=(0)"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp><RelOp NodeId="14" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="36" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="5" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="parent_node_id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#tree" Alias="[t]" Column="parent_node_id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#tree]" Index="[PK__#tree_____5F19EF166F9A724F]" Alias="[t]" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="#tree.[parent_node_id] as [child].[parent_node_id]"><Identifier><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="#tree.[value] as [t].[value] IS NULL"><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></Spool></RelOp></ComputeScalar></RelOp></Update></RelOp><ParameterList><ColumnReference Column="@parent" ParameterDataType="bigint" ParameterRuntimeValue="(1)"></ColumnReference><ColumnReference Column="@final" ParameterDataType="bigint" ParameterRuntimeValue="(6)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


Warning: Null value is eliminated by an aggregate or other SET operation.




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH child AS (&#xa;        SELECT&#xa;          child.parent_node_id,&#xa;          MIN(CASE WHEN child.side = &apos;L&apos; THEN child.value END) AS L,&#xa;          MIN(CASE WHEN child.side = &apos;R&apos; THEN child.value END) AS R&#xa;        FROM #tree child&#xa;        WHERE child.parent_node_id IS NOT NULL&#xa;        GROUP BY&#xa;          child.parent_node_id&#xa;        HAVING COUNT(CASE WHEN child.value IS NULL THEN 1 END) = 0&#xa;    )&#xa;    UPDATE t&#xa;    SET @final = v.final,&#xa;        value = v.final,&#xa;        @parent = t.parent_node_id&#xa;    FROM #tree t&#xa;    JOIN child c ON c.parent_node_id = t.node_id&#xa;    CROSS APPLY (&#xa;        SELECT CASE t.op&#xa;              WHEN &apos;+&apos; THEN L + R&#xa;              WHEN &apos;-&apos; THEN ISNULL(L, 0) - R&#xa;              WHEN &apos;*&apos; THEN L * R&#xa;              WHEN &apos;/&apos; THEN L / R&#xa;              WHEN &apos;%&apos; THEN L % R&#xa;          END&#xa;    ) v(final)&#xa;    WHERE t.value IS NULL" StatementId="3" StatementCompId="4" StatementType="UPDATE" StatementSqlHandle="0x09006833A2B2E4F550F42802C359D4C721C60000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="2" ParentObjectId="0" BatchSqlHandle="0x0200000095C5D9003BC0928BDC74F67468D69A5C54A9B2FE0000000000000000000000000000000000000000" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0398072" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xFC1E1149EA2054C7" QueryPlanHash="0x0427530FD4AF52E4" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="80" CompileTime="15" CompileCPU="15" CompileMemory="784"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2415768"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2024-03-27T22:13:32.32" ModificationCount="0" SamplingPercent="100" Statistics="[IX]" Table="[#tree_______________________________________________________________________________________________________________000000005CB6]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2024-03-27T22:13:32.32" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_BBC7654C]" Table="[#tree_______________________________________________________________________________________________________________000000005CB6]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2024-03-27T22:13:32.32" ModificationCount="0" SamplingPercent="100" Statistics="[PK__#tree_____5F19EF166F9A724F]" Table="[#tree_______________________________________________________________________________________________________________000000005CB6]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="1" PhysicalOp="Clustered Index Update" LogicalOp="Update" EstimateRows="1" EstimateIO="0.02" EstimateCPU="2e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0398072" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[tempdb]" Schema="[dbo]" Table="[#tree]" Index="[PK__#tree_____5F19EF166F9A724F]" Alias="[t]" Storage="RowStore"></Object><Object Database="[tempdb]" Schema="[dbo]" Table="[#tree]" Index="[IX]" Alias="[t]" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[@final] = [Expr1006],[@parent] = [Expr1007],#tree.[value] as [t].[value] = [Expr1005]"><ScalarExpressionList><ScalarOperator><Assign><ScalarOperator><Identifier><ColumnReference Column="@final"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator></Assign></ScalarOperator><ScalarOperator><Assign><ScalarOperator><Identifier><ColumnReference Column="@parent"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1007"></ColumnReference></Identifier></ScalarOperator></Assign></ScalarOperator><ScalarOperator><MultipleAssign><Assign><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0.013125" EstimateCPU="0.00010086" AvgRowSize="43" EstimatedTotalSubtreeCost="0.0198052" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1013"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1013"></ColumnReference><ScalarOperator ScalarString="[Expr1013]"><Identifier><ColumnReference Column="Expr1013"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="0.013125" EstimateCPU="0.00010086" AvgRowSize="43" EstimatedTotalSubtreeCost="0.0198052" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1013"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="5" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Spool><RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="43" EstimatedTotalSubtreeCost="0.00657932" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1013"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1013"></ColumnReference><ScalarOperator ScalarString="CASE WHEN [Expr1009] THEN (0) ELSE (1) END"><IF><Condition><ScalarOperator><Identifier><ColumnReference Column="Expr1009"></ColumnReference></Identifier></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="40" EstimatedTotalSubtreeCost="0.00657922" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1009"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1009"></ColumnReference><ScalarOperator ScalarString="CASE WHEN #tree.[value] as [t].[value] = [Expr1005] THEN (1) ELSE (0) END"><IF><Condition><ScalarOperator><Compare CompareOp="BINARY IS"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="47" EstimatedTotalSubtreeCost="0.00657912" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="[@final] = CASE WHEN #tree.[op] as [t].[op]=&apos;+&apos; THEN [Expr1003]+[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;-&apos; THEN isnull([Expr1003],(0))-[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;*&apos; THEN [Expr1003]*[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;/&apos; THEN [Expr1003]/[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;%&apos; THEN [Expr1003]%[Expr1004] ELSE NULL END END END END END"><Assign><ScalarOperator><Identifier><ColumnReference Column="@final"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;+&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;-&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="SUB"><ScalarOperator><Intrinsic FunctionName="isnull"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;*&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="MULT"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;/&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="DIV"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;%&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="MOD"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Assign></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="[@parent] = #tree.[parent_node_id] as [t].[parent_node_id]"><Assign><ScalarOperator><Identifier><ColumnReference Column="@parent"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="parent_node_id"></ColumnReference></Identifier></ScalarOperator></Assign></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="60" EstimatedTotalSubtreeCost="0.00657902" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="parent_node_id"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="CASE WHEN #tree.[op] as [t].[op]=&apos;+&apos; THEN [Expr1003]+[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;-&apos; THEN isnull([Expr1003],(0))-[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;*&apos; THEN [Expr1003]*[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;/&apos; THEN [Expr1003]/[Expr1004] ELSE CASE WHEN #tree.[op] as [t].[op]=&apos;%&apos; THEN [Expr1003]%[Expr1004] ELSE NULL END END END END END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;+&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;-&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="SUB"><ScalarOperator><Intrinsic FunctionName="isnull"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;*&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="MULT"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;/&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="DIV"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;%&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Arithmetic Operation="MOD"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="52" EstimatedTotalSubtreeCost="0.00657892" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="parent_node_id"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference></OuterReferences><RelOp NodeId="9" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="9.6e-07" AvgRowSize="31" EstimatedTotalSubtreeCost="0.00329116" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="10" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="2" EstimateIO="0" EstimateCPU="0" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0032902" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1018],0)"><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1018"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="11" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="2" EstimateIO="0" EstimateCPU="3.4e-06" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0032902" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1018"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1018"></ColumnReference><ScalarOperator ScalarString="COUNT([Expr1010])"><Aggregate Distinct="0" AggType="COUNT_BIG"><ScalarOperator><Identifier><ColumnReference Column="Expr1010"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="MIN([Expr1011])"><Aggregate Distinct="0" AggType="MIN"><ScalarOperator><Identifier><ColumnReference Column="Expr1011"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="MIN([Expr1012])"><Aggregate Distinct="0" AggType="MIN"><ScalarOperator><Identifier><ColumnReference Column="Expr1012"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference></GroupBy><RelOp NodeId="12" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="4" EstimateIO="0" EstimateCPU="4e-07" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0032868" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference><ColumnReference Column="Expr1010"></ColumnReference><ColumnReference Column="Expr1011"></ColumnReference><ColumnReference Column="Expr1012"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1010"></ColumnReference><ScalarOperator ScalarString="CASE WHEN #tree.[value] as [child].[value] IS NULL THEN (1) ELSE NULL END"><IF><Condition><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[child]" Column="value"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1011"></ColumnReference><ScalarOperator ScalarString="CASE WHEN #tree.[side] as [child].[side]=&apos;L&apos; THEN #tree.[value] as [child].[value] ELSE NULL END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[child]" Column="side"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;L&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[child]" Column="value"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1012"></ColumnReference><ScalarOperator ScalarString="CASE WHEN #tree.[side] as [child].[side]=&apos;R&apos; THEN #tree.[value] as [child].[value] ELSE NULL END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[child]" Column="side"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;R&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[child]" Column="value"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="13" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="4" EstimatedRowsRead="4" EstimateIO="0.003125" EstimateCPU="0.0001614" AvgRowSize="24" EstimatedTotalSubtreeCost="0.0032864" TableCardinality="5" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[child]" Column="value"></ColumnReference><ColumnReference Table="#tree" Alias="[child]" Column="side"></ColumnReference><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="4" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="#tree" Alias="[child]" Column="value"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#tree" Alias="[child]" Column="side"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#tree]" Index="[IX]" Alias="[child]" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><IsNotNull><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference></IsNotNull></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp><Predicate><ScalarOperator ScalarString="[Expr1002]=(0)"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp><RelOp NodeId="14" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="36" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="5" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference><ColumnReference Table="#tree" Alias="[t]" Column="parent_node_id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="2" ActualEndOfScans="1" ActualExecutions="2" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#tree" Alias="[t]" Column="op"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#tree" Alias="[t]" Column="parent_node_id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#tree]" Index="[PK__#tree_____5F19EF166F9A724F]" Alias="[t]" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Table="#tree" Alias="[t]" Column="node_id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="#tree.[parent_node_id] as [child].[parent_node_id]"><Identifier><ColumnReference Table="#tree" Alias="[child]" Column="parent_node_id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="#tree.[value] as [t].[value] IS NULL"><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Table="#tree" Alias="[t]" Column="value"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></Spool></RelOp></ComputeScalar></RelOp></Update></RelOp><ParameterList><ColumnReference Column="@parent" ParameterDataType="bigint" ParameterRuntimeValue="NULL"></ColumnReference><ColumnReference Column="@final" ParameterDataType="bigint" ParameterRuntimeValue="(1)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


Warning: Null value is eliminated by an aggregate or other SET operation.




(No column name)


1