test/sql/Optimizer/test017.ksh

   1 #!/bin/ksh
   2 # explain plan for is null and is not null operator and composite hash index on mixed datatypes
   3 #create table t1(f1 int, f2 int, f3 varchar(20),f4 date);
   4 #create index idx on t1(f3,f4,f2) hash;
   5 #explain plan select * from t1 where f2 is null;
   6 #explain plan select * from t1 where f2 is not null;
   7 #explain plan select * from t1 where f2 is null and f3 is not null;
   8 #explain plan select * from t1 where f1 is null or f2 is not null or f3 is null or f4 is not null;
   9 #explain plan select * from t1 where f1 is null and f2 is not null and f3 is null and f4 is not null;
  10 #
  11 QUITFILE=${PWD}/sql/Optimizer/drop.sql
  12 REL_PATH=.
  13 if [ -s "$QUITFILE" ]
  14 then
  15 REL_PATH=`pwd`/sql/Optimizer
  16 fi
  17
  18 echo "drop table t1;" >${REL_PATH}/dropt1.sql
  19
  20 $CSQL_INSTALL_ROOT/bin/csql -s ${REL_PATH}/dropt1.sql >/dev/null 2>&1
  21
  22 if [ "$VALGRIND" = "true" ]
  23 then
  24     valgrind --tool=memcheck --leak-check=full $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/explainplan_isnull_isnotnull.sql
  25 else
  26     $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/explainplan_isnull_isnotnull.sql
  27     if [ $? -ne 0 ]
  28     then
  29       rm -f ${REL_PATH}/dropt1.sql
  30       exit 1;
  31     fi
  32 fi
  33
  34 $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/dropt1.sql
  35 rm -f ${REL_PATH}/dropt1.sql
  36 exit 0;
  37