test/sql/Optimizer/exp.test002.ksh

   1 Statement Executed
   2 Statement Executed
   3 echo Multiple predicates Test Cases - 1,2,3
   4 echo select * from t1 where f1=10 and f3=20;
   5  <TABLE-NODE>
   6  <NAME> t1 </NAME>
   7  <ScanType> TableScan </ScanType>
   8    <PREDICATE>
   9    <LogOp> AND </LogOp>
  10    <PRED-LEFT>
  11      <PREDICATE>
  12      <FieldName1> f1 </FieldName1>
  13      <CompOp> Equals </CompOp>
  14      <OperandPtr> VALUE </OperandPtr>
  15      </PREDICATE>
  16    </PRED-LEFT>
  17    <PRED-RIGHT>
  18      <PREDICATE>
  19      <FieldName1> f3 </FieldName1>
  20      <CompOp> Equals </CompOp>
  21      <OperandPtr> VALUE </OperandPtr>
  22      </PREDICATE>
  23    </PRED-RIGHT>
  24    </PREDICATE>
  25  </TABLE-NODE>
  26 echo select * from t1 where f2>10 and f3=20;
  27  <TABLE-NODE>
  28  <NAME> t1 </NAME>
  29  <ScanType> TableScan </ScanType>
  30    <PREDICATE>
  31    <LogOp> AND </LogOp>
  32    <PRED-LEFT>
  33      <PREDICATE>
  34      <FieldName1> f1 </FieldName1>
  35      <CompOp> GreaterThan </CompOp>
  36      <OperandPtr> VALUE </OperandPtr>
  37      </PREDICATE>
  38    </PRED-LEFT>
  39    <PRED-RIGHT>
  40      <PREDICATE>
  41      <FieldName1> f3 </FieldName1>
  42      <CompOp> Equals </CompOp>
  43      <OperandPtr> VALUE </OperandPtr>
  44      </PREDICATE>
  45    </PRED-RIGHT>
  46    </PREDICATE>
  47  </TABLE-NODE>
  48 echo select * from t1 where f3=20 and f1 >10;
  49  <TABLE-NODE>
  50  <NAME> t1 </NAME>
  51  <ScanType> TableScan </ScanType>
  52    <PREDICATE>
  53    <LogOp> AND </LogOp>
  54    <PRED-LEFT>
  55      <PREDICATE>
  56      <FieldName1> f3 </FieldName1>
  57      <CompOp> Equals </CompOp>
  58      <OperandPtr> VALUE </OperandPtr>
  59      </PREDICATE>
  60    </PRED-LEFT>
  61    <PRED-RIGHT>
  62      <PREDICATE>
  63      <FieldName1> f1 </FieldName1>
  64      <CompOp> GreaterThan </CompOp>
  65      <OperandPtr> VALUE </OperandPtr>
  66      </PREDICATE>
  67    </PRED-RIGHT>
  68    </PREDICATE>
  69  </TABLE-NODE>
  70 echo Disjunctive Queries Test case - 4,5,6,7,8
  71 echo select * from t1 where f1!=10;
  72  <TABLE-NODE>
  73  <NAME> t1 </NAME>
  74  <ScanType> TableScan </ScanType>
  75    <PREDICATE>
  76    <FieldName1> f1 </FieldName1>
  77    <CompOp> NotEquals </CompOp>
  78    <OperandPtr> VALUE </OperandPtr>
  79    </PREDICATE>
  80  </TABLE-NODE>
  81 echo select * from t1 where f2!=10;
  82  <TABLE-NODE>
  83  <NAME> t1 </NAME>
  84  <ScanType> TableScan </ScanType>
  85    <PREDICATE>
  86    <FieldName1> f2 </FieldName1>
  87    <CompOp> NotEquals </CompOp>
  88    <OperandPtr> VALUE </OperandPtr>
  89    </PREDICATE>
  90  </TABLE-NODE>
  91 echo select * from t1 where f1=10 or f3=20;
  92  <TABLE-NODE>
  93  <NAME> t1 </NAME>
  94  <ScanType> TableScan </ScanType>
  95    <PREDICATE>
  96    <LogOp> OR </LogOp>
  97    <PRED-LEFT>
  98      <PREDICATE>
  99      <FieldName1> f1 </FieldName1>
 100      <CompOp> Equals </CompOp>
 101      <OperandPtr> VALUE </OperandPtr>
 102      </PREDICATE>
 103    </PRED-LEFT>
 104    <PRED-RIGHT>
 105      <PREDICATE>
 106      <FieldName1> f3 </FieldName1>
 107      <CompOp> Equals </CompOp>
 108      <OperandPtr> VALUE </OperandPtr>
 109      </PREDICATE>
 110    </PRED-RIGHT>
 111    </PREDICATE>
 112  </TABLE-NODE>
 113 echo select * from t1 where f2>10 or f3=20;
 114  <TABLE-NODE>
 115  <NAME> t1 </NAME>
 116  <ScanType> TableScan </ScanType>
 117    <PREDICATE>
 118    <LogOp> OR </LogOp>
 119    <PRED-LEFT>
 120      <PREDICATE>
 121      <FieldName1> f2 </FieldName1>
 122      <CompOp> GreaterThan </CompOp>
 123      <OperandPtr> VALUE </OperandPtr>
 124      </PREDICATE>
 125    </PRED-LEFT>
 126    <PRED-RIGHT>
 127      <PREDICATE>
 128      <FieldName1> f3 </FieldName1>
 129      <CompOp> Equals </CompOp>
 130      <OperandPtr> VALUE </OperandPtr>
 131      </PREDICATE>
 132    </PRED-RIGHT>
 133    </PREDICATE>
 134  </TABLE-NODE>
 135 echo select * from t1 where f3=20 or f2 >10;
 136  <TABLE-NODE>
 137  <NAME> t1 </NAME>
 138  <ScanType> TableScan </ScanType>
 139    <PREDICATE>
 140    <LogOp> OR </LogOp>
 141    <PRED-LEFT>
 142      <PREDICATE>
 143      <FieldName1> f3 </FieldName1>
 144      <CompOp> Equals </CompOp>
 145      <OperandPtr> VALUE </OperandPtr>
 146      </PREDICATE>
 147    </PRED-LEFT>
 148    <PRED-RIGHT>
 149      <PREDICATE>
 150      <FieldName1> f2 </FieldName1>
 151      <CompOp> GreaterThan </CompOp>
 152      <OperandPtr> VALUE </OperandPtr>
 153      </PREDICATE>
 154    </PRED-RIGHT>
 155    </PREDICATE>
 156  </TABLE-NODE>