A simple SELECT query with a join

From MonetDB
Jump to navigationJump to search

The Tables[edit]

CREATE TABLE "tpch"."customer" (
        "c_custkey"    INTEGER       NOT NULL,
        "c_name"       VARCHAR(25)   NOT NULL,
        "c_address"    VARCHAR(40)   NOT NULL,
        "c_nationkey"  INTEGER       NOT NULL,
        "c_phone"      VARCHAR(15)   NOT NULL,
        "c_acctbal"    DOUBLE        NOT NULL,
        "c_mktsegment" VARCHAR(10)   NOT NULL,
        "c_comment"    VARCHAR(117)  NOT NULL
);

CREATE TABLE "tpch"."nation" (
        "n_nationkey" INTEGER       NOT NULL,
        "n_name"      VARCHAR(25)   NOT NULL,
        "n_regionkey" INTEGER       NOT NULL,
        "n_comment"   VARCHAR(152)  NOT NULL
);

The Query[edit]

select c_custkey, n_nationkey from customer, nation where c_nationkey = n_nationkey;

The MAL[edit]

| function user.s9_1{autoCommit=true}():void;                                                                                      |
| barrier X_68 := language.dataflow();                                                                                             |
|     X_2 := sql.mvc();                                                                                                            |
|     X_3:bat[:oid,:oid]  := sql.tid(X_2,"tpch","customer");                                                                       |
|     X_6 := sql.bind(X_2,"tpch","customer","c_nationkey",0);                                                                      |
|     (X_9,r1_9) := sql.bind(X_2,"tpch","customer","c_nationkey",2);                                                               |
|     X_12 := sql.bind(X_2,"tpch","customer","c_nationkey",1);                                                                     |
|     X_14 := sql.delta(X_6,X_9,r1_9,X_12);                                                                                        |
|     X_15 := algebra.leftfetchjoin(X_3,X_14);                                                                                     |
|     X_16:bat[:oid,:oid]  := sql.tid(X_2,"tpch","nation");                                                                        |
|     X_18 := sql.bind(X_2,"tpch","nation","n_nationkey",0);                                                                       |
|     (X_20,r1_22) := sql.bind(X_2,"tpch","nation","n_nationkey",2);                                                               |
|     X_22 := sql.bind(X_2,"tpch","nation","n_nationkey",1);                                                                       |
|     X_23 := sql.delta(X_18,X_20,r1_22,X_22);                                                                                     |
|     X_24 := algebra.leftfetchjoin(X_16,X_23);                                                                                    |
|     (X_25,r1_29) := algebra.join(X_15,X_24);                                                                                     |
|     X_27 := sql.bind(X_2,"tpch","customer","c_custkey",0);                                                                       |
|     (X_29,r1_33) := sql.bind(X_2,"tpch","customer","c_custkey",2);                                                               |
|     X_31 := sql.bind(X_2,"tpch","customer","c_custkey",1);                                                                       |
|     X_32 := sql.delta(X_27,X_29,r1_33,X_31);                                                                                     |
|     X_33:bat[:oid,:int]  := algebra.leftfetchjoinPath(X_25,X_3,X_32);                                                            |
|     X_34 := algebra.leftfetchjoin(r1_29,X_24);                                                                                   |
|     language.pass(X_3);                                                                                                          |
|     language.pass(X_24);
| exit X_68;                                                                                                                       |
|     X_35 := sql.resultSet(2,1,X_33);                                                                                             |
|     sql.rsColumn(X_35,"tpch.customer","c_custkey","int",32,0,X_33);                                                              |
|     sql.rsColumn(X_35,"tpch.nation","n_nationkey","int",32,0,X_34);                                                              |
|     X_46 := io.stdout();                                                                                                         |
|     sql.exportResult(X_46,X_35);                                                                                                 |
| end s9_1;                                                                                                                        |
| # querylog.define("explain select c_custkey, n_nationkey from customer, nation where c_nationkey = n_nationkey;","default_pipe")

The Explanation[edit]

Getting the column data[edit]

X_3 holds the OIDs of the undeleted tuples of customer. (sql.tid(...) returns this) X_15 holds the current values of the c_nationkey column X_24 holds the current values of the n_nationkey column

Joining the columns[edit]

algebra.join(X_15, X_24) assigns to X_25 the OIDs of the non-deleted c_nationkey column values which are involved in the left side of the join. It assigns to r1_29 the corresponding values of n_nationkey.

Getting the desired values[edit]

We wish to display the c_custkey and n_nationkey columns corresponding to the joined tuples. We already have n_nationkey in X_24.

To gain the appropriate values of the c_custkey column, we must first fetch the undeleted values and then use X_25, the left side of the join, to get the result. Thsi is done by the algebra.leftfetchjoinPath call.

To X_34 are assigned the n_nationkey values corresponding to the right side of the join.