test/sql/Order/exp.test011.ksh

   1 Statement Executed
   2 Statement Executed: Rows Affected = 1
   3 Statement Executed: Rows Affected = 1
   4 Statement Executed: Rows Affected = 1
   5 Statement Executed: Rows Affected = 1
   6 Statement Executed: Rows Affected = 1
   7 Statement Executed: Rows Affected = 1
   8 Statement Executed: Rows Affected = 1
   9 Statement Executed: Rows Affected = 1
  10 Statement Executed: Rows Affected = 1
  11 Statement Executed: Rows Affected = 1
  12 Statement Executed: Rows Affected = 1
  13 Statement Executed
  14 Statement Executed: Rows Affected = 1
  15 Statement Executed: Rows Affected = 1
  16 Statement Executed: Rows Affected = 1
  17 Statement Executed: Rows Affected = 1
  18 Statement Executed: Rows Affected = 1
  19 Statement Executed: Rows Affected = 1
  20 Statement Executed: Rows Affected = 1
  21 Statement Executed: Rows Affected = 1
  22 Statement Executed: Rows Affected = 1
  23 Statement Executed: Rows Affected = 1
  24 Statement Executed: Rows Affected = 1
  25 Statement Executed
  26 Statement Executed: Rows Affected = 1
  27 Statement Executed: Rows Affected = 1
  28 Statement Executed: Rows Affected = 1
  29 Statement Executed: Rows Affected = 1
  30 Statement Executed: Rows Affected = 1
  31 Statement Executed: Rows Affected = 1
  32 Statement Executed: Rows Affected = 1
  33 Statement Executed: Rows Affected = 1
  34 Statement Executed: Rows Affected = 1
  35 Statement Executed: Rows Affected = 1
  36 Statement Executed: Rows Affected = 1
  37 echo SELECT * FROM t1;
  38 ---------------------------------------------------------
  39         t1.f1   t1.f2   t1.f3   t1.f4
  40 ---------------------------------------------------------
  41         3       AAA     2.500000        2008/7/1
  42         2       AAA     2.500000        2008/7/2
  43         4       GGG     4.500000        2008/7/3
  44         1       DEF     1.500000        2008/7/1
  45         6       CCC     6.500000        2008/7/2
  46         5       HHH     5.500000        2008/7/3
  47         2       AAA     2.500000        2008/7/1
  48         6       CCC     6.500000        2008/7/4
  49         3       ABB     2.600000        2008/7/5
  50         2       BBB     1.600000        2008/7/4
  51         1       DDD     1.600000        2008/7/1
  52
  53 echo SELECT * FROM t2;
  54 ---------------------------------------------------------
  55         t2.f1   t2.f2   t2.f3   t2.f4
  56 ---------------------------------------------------------
  57         1       DEF     1.500000        2008/7/1
  58         9       QQQ     9.500000        2008/7/2
  59         3       MMM     2.500000        2008/7/3
  60         4       GGG     4.600000        2008/7/1
  61         7       PPP     7.500000        2008/7/2
  62         8       NNN     8.500000        2008/7/3
  63         9       QQQ     9.500000        2008/7/1
  64         8       AAA     8.500000        2008/7/4
  65         7       AAA     7.500000        2008/7/5
  66         9       BBB     9.800000        2008/7/4
  67         8       EEE     8.800000        2008/7/1
  68
  69 echo SELECT * FROM t3;
  70 ---------------------------------------------------------
  71         t3.f1   t3.f2   t3.f3   t3.f4
  72 ---------------------------------------------------------
  73         1       DEF     1.500000        2008/7/1
  74         2       AAA     2.500000        2008/7/2
  75         7       PPP     7.500000        2008/7/3
  76         8       NNN     8.500000        2008/7/4
  77         9       QQQ     9.500000        2008/7/1
  78         10      NNN     10.500000       2008/7/2
  79         1       DEF     1.500000        2008/7/3
  80         2       AAA     2.500000        2008/7/4
  81         7       PPP     7.500000        2008/7/1
  82         8       NNN     8.500000        2008/7/2
  83         10      NNN     10.500000       2008/7/4
  84
  85 echo SELECT t1.f1, t2.f1, SUM(t1.f3) FROM t1 INNER JOIN t2 ON t1.f1=t2.f1 AND t1.f1 IN(1,2,4,5,6) AND t2.f1 BETWEEN 2 AND 6 LEFT JOIN t3 ON t2.f1 != 5 WHERE t1.f1 <= t2.f1 OR t2.f2 >=2 AND (t2.f3 LIKE 'A%' OR t2.f3 LIKE 'B%' OR t2.f3 LIKE 'Q%') GROUP BY t1.f1, t2.f1 HAVING SUM(t1.f3) >1 ORDER BY t1.f1;
  86
  87
  88
  89 echo SELECT t1.f1, t2.f1, t3.f1, SUM(t1.f3) FROM t1 INNER JOIN t2 ON t1.f1=t2.f1 AND t1.f1 IN(1,2,4,5,6) OR t2.f1 BETWEEN 2 AND 6 LEFT JOIN t3 ON t2.f1 != 5 WHERE t1.f1 <= t2.f1 OR t2.f2 >=2 OR t2.f3 LIKE 'A%' OR t2.f3 LIKE 'B%' OR t2.f3 LIKE 'Q%' GROUP BY t1.f1, t2.f1, t3.f1 HAVING SUM(t1.f3) >1 ORDER BY t1.f1;
  90
  91
  92
  93 echo SELECT t1.f1, t2.f1, t3.f1, SUM(t1.f3) FROM t1 INNER JOIN t2 ON t1.f1=t2.f1 AND t1.f1 IN(1,2,4,5,6) AND t2.f1 BETWEEN 2 AND 6 LEFT JOIN t3 ON t2.f1 != 5 WHERE t1.f1 <= t2.f1 OR t2.f2 >=2 AND (t2.f3 LIKE 'A%' OR t2.f3 LIKE 'B%' OR t2.f3 LIKE 'Q%') GROUP BY t1.f1, t2.f1, t3.f1 HAVING SUM(t1.f3) >1 ORDER BY t1.f1;
  94
  95 For the above 3 queries retrieved records are more than expected records. Just verify it
  96
  97 Statement Executed
  98 Statement Executed
  99 Statement Executed
 100
 101