Study Q6

[wukefe]

MonetDB MAL details (An Example from TPC-H Q6)

Code snippet

SQL code

  select
    sum(l_extendedprice * l_discount) as revenue
  from
    lineitem
  where
    l_shipdate >= date '1994-01-01'
    and l_shipdate < date '1994-01-01' + interval '1' year
    and l_discount between .06 - 0.01 and .06 + 0.01
    and l_quantity < 24; 

MAL code (for WHERE)

...
|     X_137:bat[:lng] := sql.bind(X_3,"sys","lineitem","l_discount",0:int,0:int,8:int);   |
...
|     C_114:bat[:oid] := sql.tid(X_3,"sys","lineitem",0:int,8:int);                       |
|     X_48:date := mtime.addmonths("1994-01-01":date,12:int);                             |
|     C_171 := algebra.subselect(X_147,C_114,"1994-01-01":date,X_48,true,false,false);    |
|     C_179 := algebra.subselect(X_137,C_171,5:lng,7:lng,true,true,false);                |
|     C_187 := algebra.thetasubselect(X_129,C_179,2400:lng,"<");                          |

Details

• mtime.addmonths: returns a new date by adding months
• algebra.subselect: selects items by a boolean mask ()
• algebra.thetasubselect: see source code

Remaining questions

1. Why floating point literals in the query multiply by 100 to convert to a long int type? Any performance concerns?
   • The column discount is treated as a long int column.
2. Why does the MAL code have to repeat 8 times? (Hint: 8 threads?)
   • I made some efforts to figure out the meaning of each parameter in sql.bind
   • I searched on the MonetDB GitHub repo, but failed to identify where the source code was