secondo/Algebras/OptAux/OptAuxAlgebra.test

#create test database
delete database optauxtest;
create database optauxtest;

# setup
# open database 
open database optauxtest;

# create two relations
(create tenRows : 
   (rel
      (tuple(
         (ID int)
         (String string)
         (FKeyTenRows int)
         (Real real)
       ))
   )
);

(create thousandRows : 
   (rel
      (tuple(
         (ID int)
         (String string)
         (FKeyTenRows int)
         (Real real)
       ))
   )
);

# create sample lines of relation tenRows
(update tenRows := 
   (
      (rel
         (tuple(
            (ID int)
            (String string)
            (FKeyTenRows int)
            (Real real)
          ))
      )
      (
         (0 "level 0" 0 0)
         (1 "level 1" 0 0.1)
         (2 "level 1" 0 0.1)
         (3 "level 2" 1 0.2)
         (4 "level 2" 2 0.2)
         (5 "level 2" 2 0.2)
         (6 "level 3" 3 0.3)
         (7 "level 3" 3 0.3)
         (8 "level 3" 4 0.3)
         (9 "level 3" 5 0.3)
      )
   )
);

# create sample lines of relation thousandRows
let tmp = 
   tenRows feed {C} 
   tenRows feed {A} 
      product 
      tenRows feed {B} 
         product 
         extend [ 
            ID: (.ID_B*100)+(.ID_A*10)+(.ID_C), 
            String: .String_C, 
            FKeyTenRows: .FKeyTenRows_C, 
            Real: .Real_C ] 
         project [ ID, String, FKeyTenRows, Real] 
         consume;
(update thousandRows := tmp);

# testcase simple
# yields \
   ( \
      (rel (tuple( (Atom int) (Count int) )) ) \
      ( \
         (1 999) \
	 (0 1) \
      ) \
   )
query thousandRows feed predcounts [ 
   P01: .ID>0 
] consume;

# testcase divide2and4
# yields \
   ( \
      (rel (tuple( (Atom int) (Count int) )) ) \
      ( \
         (3 250) \
         (2 0) \
         (1 250) \
	 (0 500) \
      ) \
   )
query thousandRows feed predcounts [ 
   P01: (.ID mod 2) = 0, 
   P02: (.ID mod 4) = 0 
] consume;

# testcase divide2and3and6
# yields \
   ( \
      (rel (tuple( (Atom int) (Count int) )) ) \
      ( \
         (7 167) \
         (6 0) \
         (5 0) \
	 (4 0) \
         (3 0) \
         (2 167) \
         (1 333) \
	 (0 333) \
      ) \
   )
query thousandRows feed predcounts [ 
   P01: (.ID mod 2) = 0, 
   P02: (.ID mod 3) = 0 , 
   P03: (.ID mod 6) = 0
] consume;

# testcase singleHits
# yields \
   ( \
      (rel (tuple( (Atom int) (Count int) )) ) \
      ( \
         (512  1) \
         (256  1) \
         (128  1) \
         (64   1) \
         (32   1) \
         (16   1) \
         (8    1) \
         (4    1) \
         (2    1) \
         (1    1) \
         (0    990) \
      ) \
   )
query thousandRows feed predcounts [ 
   P01: .ID=1, 
   P02: .ID=2, 
   P03: .ID=3, 
   P04: .ID=4, 
   P05: .ID=5, 
   P06: .ID=6, 
   P07: .ID=7, 
   P08: .ID=8, 
   P09: .ID=9, 
   P10: .ID=10 
] filter [ .Count > 0 ] consume;

# testcase singleHits20Predicates
# yields \
   ( \
      (rel (tuple( (Atom int) (Count int) )) ) \
      ( \
         (524288  1) \
         (262144  1) \
         (131072  1) \
         (65536   1) \
         (32768   1) \
         (16384   1) \
         (8192    1) \
         (4096    1) \
         (2048    1) \
         (1024    1) \
         (512     1) \
         (256     1) \
         (128     1) \
         (64      1) \
         (32      1) \
         (16      1) \
         (8       1) \
         (4       1) \
         (2       1) \
         (1       1) \
         (0       980) \
      ) \
   )
query thousandRows feed predcounts [ 
   P01: .ID=1, 
   P02: .ID=2, 
   P03: .ID=3, 
   P04: .ID=4, 
   P05: .ID=5, 
   P06: .ID=6, 
   P07: .ID=7, 
   P08: .ID=8, 
   P09: .ID=9, 
   P10: .ID=10,
   P11: .ID=11, 
   P12: .ID=12, 
   P13: .ID=13, 
   P14: .ID=14, 
   P15: .ID=15, 
   P16: .ID=16, 
   P17: .ID=17, 
   P18: .ID=18, 
   P19: .ID=19, 
   P20: .ID=20
] filter [ .Count > 0 ] consume;

# testcase maxCountOfPredicates32Bit - is still possible, if 8GB of memory are available
# yields success
query tenRows feed predcounts [ 
   P01: .ID=1, 
   P02: .ID=2, 
   P03: .ID=3, 
   P04: .ID=4, 
   P05: .ID=5, 
   P06: .ID=6, 
   P07: .ID=7, 
   P08: .ID=8, 
   P09: .ID=9, 
   P10: .ID=10,
   P11: .ID=11, 
   P12: .ID=12, 
   P13: .ID=13, 
   P14: .ID=14, 
   P15: .ID=15, 
   P16: .ID=16, 
   P17: .ID=17, 
   P18: .ID=18, 
   P19: .ID=19, 
   P20: .ID=20,
   P21: .ID=21, 
   P22: .ID=22, 
   P23: .ID=23, 
   P24: .ID=24, 
   P25: .ID=25, 
   P26: .ID=26, 
   P27: .ID=27, 
   P28: .ID=28, 
   P29: .ID=29, 
   P30: .ID=30,
   P31: .ID=31
] filter [ .Count > 0 ] consume;


# testcase tooManyPredicates32Bit
# yields error
query tenRows feed predcounts [ 
   P01: .ID=1, 
   P02: .ID=2, 
   P03: .ID=3, 
   P04: .ID=4, 
   P05: .ID=5, 
   P06: .ID=6, 
   P07: .ID=7, 
   P08: .ID=8, 
   P09: .ID=9, 
   P10: .ID=10,
   P11: .ID=11, 
   P12: .ID=12, 
   P13: .ID=13, 
   P14: .ID=14, 
   P15: .ID=15, 
   P16: .ID=16, 
   P17: .ID=17, 
   P18: .ID=18, 
   P19: .ID=19, 
   P20: .ID=20,
   P21: .ID=21, 
   P22: .ID=22, 
   P23: .ID=23, 
   P24: .ID=24, 
   P25: .ID=25, 
   P26: .ID=26, 
   P27: .ID=27, 
   P28: .ID=28, 
   P29: .ID=29, 
   P30: .ID=30,
   P31: .ID=31,
   P32: .ID=32
] filter [ .Count > 0 ] consume;

# testcase tooManyRows - needs too much time
# yields error
#query 
#   thousandRows feed {A} 
#   thousandRows feed {B} 
#      product 
#      thousandRows feed {C} 
#         product
#         tenRows feed {D}
#            product
#            predcounts [ P01: .ID_D>=0 ]
#            consume;

# testcase invalidColumnNames
# yields error
query tenRows feed tenRows feed product predcounts [ .ID > 2 ] consume;

# teardown
# delete database
close database;