test/sql/Aggregate/test015.ksh

   1 #!/bin/ksh
   2 #Test Case
   3 # CREATE TABLE T1(f1 int,f2 int,f3 int,f4 int,f5 int);
   4 #CREATE INDEX idx1 on T1(f2);
   5 #CREATE INDEX idx2 on T1(f3);
   6 #CREATE INDEX idx3 on T1(f4);
   7 #CREATE INDEX idx4 on T1(f5);
   8 #CREATE TABLE T2(f1 int,f2 int,primary key(f2));
   9 #CREATE TABLE T3(f1 int,f3 int,primary key(f3));
  10 #CREATE TABLE T4(f1 int,f4 int,primary key(f4));
  11 #CREATE TABLE T5(f1 int,f5 int,primary key(f5));
  12 #
  13 #2) Insert 10k records in T1,
  14 #1k records in T2
  15 #1k records in T3
  16 #1k records in T4
  17 #1k records in T5
  18 #
  19 #3) Execute the following join statements.
  20 #select COUNT(*) from T1,T2,T3,T4,T5 where T1.f2=T2.f2 and T1.f3=T3.f3 and T1.f4=T4.f4 and T1.f5=T5.f5;
  21 #select SUM(T1.f1) from T1,T2,T3,T4,T5 where T1.f2=T2.f2 and T1.f3=T3.f3 and T1.f4=T4.f4 and T1.f5=T5.f5;
  22 #select MIN(T1.f1) from T1,T2,T3,T4,T5 where T1.f2=T2.f2 and T1.f3=T3.f3 and T1.f4=T4.f4 and T1.f5=T5.f5;
  23 #select AVG(T1.f1) from T1,T2,T3,T4,T5 where T1.f2=T2.f2 and T1.f3=T3.f3 and T1.f4=T4.f4 and T1.f5=T5.f5;
  24 #select MAX(T1.f1) from T1,T2,T3,T4,T5 where T1.f2=T2.f2 and T1.f3=T3.f3 and T1.f4=T4.f4 and T1.f5=T5.f5;
  25 #
  26
  27 QUITFILE=${PWD}/sql/Aggregate/quit.sql
  28 REL_PATH=.
  29 if [ -s "$QUITFILE" ]
  30 then
  31   REL_PATH=`pwd`/sql/Aggregate
  32 fi
  33
  34 $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/bigtable.sql
  35 if [ $? -ne 0 ]
  36  then
  37    exit 1;
  38 fi
  39 for (( i=1 ; i<=10000 ; i++ ))
  40 do
  41  echo "insert into T1 values ($i,$i,$i,$i,$i);" >>$REL_PATH/T1.sql
  42 done
  43
  44 for (( i=1 ; i<=1000 ; i++ ))
  45 do
  46  echo "insert into T2 values($i,$i);"
  47  echo "insert into T3 values($i,$i);"
  48  echo "insert into T4 values($i,$i);"
  49  echo "insert into T5 values($i,$i);"
  50 done >>$REL_PATH/T11.sql
  51
  52 for (( i=1 ; i<=5 ; i++ ))
  53 do
  54 echo "DROP TABLE T$i;"
  55 done >>$REL_PATH/drop_all_table.sql
  56
  57
  58 $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/T1.sql >/dev/null 2>&1
  59 if [ $? -ne 0 ]
  60   then
  61     $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/drop_all_table.sql
  62     rm -f $REL_PATH/T1.sql $REL_PATH/T11.sql $REL_PATH/drop_all_table.sql
  63     exit 2;
  64  fi
  65
  66 $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/T11.sql >/dev/null 2>&1
  67 if [ $? -ne 0 ]
  68   then
  69     $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/drop_all_table.sql
  70     rm -f $REL_PATH/T1.sql $REL_PATH/T11.sql $REL_PATH/drop_all_table.sql
  71     exit 3;
  72  fi
  73
  74 $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/all_agg.sql
  75 if [ $? -ne 0 ]
  76   then
  77     $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/drop_all_table.sql
  78     rm -f $REL_PATH/T1.sql $REL_PATH/T11.sql $REL_PATH/drop_all_table.sql
  79     exit 4;
  80  fi
  81
  82 $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/drop_all_table.sql
  83 rm -f $REL_PATH/T1.sql $REL_PATH/T11.sql $REL_PATH/drop_all_table.sql
  84 exit 0;
  85