envs::{
  animals:[
    {
      name:"Kumo",
      type:"dog"
    },
    {
      name:"Mochi",
      type:"dog"
    },
    {
      name:"Lilikoi",
      type:"unicorn"
    }
  ],
  animal_types:[
    {
      id:"dog",
      is_magic:false
    },
    {
      id:"cat",
      is_magic:false
    },
    {
      id:"unicorn",
      is_magic:true
    }
  ],
}

joins::[
  {
    name:"selectCrossProduct",
    statement:"SELECT * FROM animals, animal_types",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:$bag::[
        {
          name:"Kumo",
          type:"dog",
          id:"dog",
          is_magic:false
        },
        {
          name:"Kumo",
          type:"dog",
          id:"cat",
          is_magic:false
        },
        {
          name:"Kumo",
          type:"dog",
          id:"unicorn",
          is_magic:true
        },
        {
          name:"Mochi",
          type:"dog",
          id:"dog",
          is_magic:false
        },
        {
          name:"Mochi",
          type:"dog",
          id:"cat",
          is_magic:false
        },
        {
          name:"Mochi",
          type:"dog",
          id:"unicorn",
          is_magic:true
        },
        {
          name:"Lilikoi",
          type:"unicorn",
          id:"dog",
          is_magic:false
        },
        {
          name:"Lilikoi",
          type:"unicorn",
          id:"cat",
          is_magic:false
        },
        {
          name:"Lilikoi",
          type:"unicorn",
          id:"unicorn",
          is_magic:true
        }
      ]
    }
  }
]

'join-with-condition'::[
  envs::{
    table1:[
      {
        id:1,
        val:a
      },
      {
        id:2,
        val:b
      },
      {
        id:3,
        val:c
      }
    ],
    table1_null_id:[
      {
        id:1,
        val:a
      },
      {
        id:null,
        val:b
      },
      {
        id:3,
        val:c
      }
    ],
    table1_null_row:[
      null
    ],
    table1_null:null
  },
  {
    name:"join on column - all column values non-null",
    statement:"SELECT t1.id AS id, t1.val AS val1, t2.val AS val2 FROM table1 AS t1 JOIN table1 AS t2 ON t1.id = t2.id",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          id:1,
          val1:a,
          val2:a
        },
        {
          id:2,
          val1:b,
          val2:b
        },
        {
          id:3,
          val1:c,
          val2:c
        }
      ]
    }
  },
  {
    name:"join on column - some column values are null",
    statement:"SELECT t1.id AS id, t1.val AS val1, t2.val AS val2 FROM table1 AS t1 JOIN table1_null_id AS t2 ON t1.id = t2.id",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          id:1,
          val1:a,
          val2:a
        },
        {
          id:3,
          val1:c,
          val2:c
        }
      ]
    }
  },
  {
    name:"join on column - 1 table contains 1 row with the value null",
    statement:"SELECT t1.id AS id, t1.val AS val1, t2.val AS val2 FROM table1 AS t1 JOIN table1_null_row AS t2 ON t1.id = t2.id",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
      ]
    }
  },
  {
    name:"join on column - ON conidition = true ",
    statement:"SELECT t1.id AS id, t1.val AS val1, t2.val AS val2 FROM table1 AS t1 JOIN table1 AS t2 ON true",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          id:1,
          val1:a,
          val2:a
        },
        {
          id:1,
          val1:a,
          val2:b
        },
        {
          id:1,
          val1:a,
          val2:c
        },
        {
          id:2,
          val1:b,
          val2:a
        },
        {
          id:2,
          val1:b,
          val2:b
        },
        {
          id:2,
          val1:b,
          val2:c
        },
        {
          id:3,
          val1:c,
          val2:a
        },
        {
          id:3,
          val1:c,
          val2:b
        },
        {
          id:3,
          val1:c,
          val2:c
        }
      ]
    }
  },
  {
    name:"join on column - ON condition = false",
    statement:"SELECT t1.id AS id, t1.val AS val1, t2.val AS val2 FROM table1 AS t1 JOIN table1 AS t2 ON false",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
      ]
    }
  }
]

join::[
  envs::{
    t3:[
      {
        name:"bb",
        n:13
      },
      {
        name:"cc",
        n:23
      },
      {
        name:"dd",
        n:33
      }
    ],
    j1_tbl:[
      {
        i:1,
        j:4,
        t:"one"
      },
      {
        i:2,
        j:3,
        t:"two"
      },
      {
        i:3,
        j:2,
        t:"three"
      },
      {
        i:4,
        j:1,
        t:"four"
      },
      {
        i:5,
        j:0,
        t:"five"
      },
      {
        i:6,
        j:6,
        t:"six"
      },
      {
        i:7,
        j:7,
        t:"seven"
      },
      {
        i:8,
        j:8,
        t:"eight"
      }
    ],
    j2_tbl:[
      {
        i:1,
        k:-1
      },
      {
        i:2,
        k:2
      },
      {
        i:3,
        k:-3
      },
      {
        i:2,
        k:4
      },
      {
        i:5,
        k:-5
      },
      {
        i:5,
        k:-5
      }
    ],
    t1:[
      {
        name:"bb",
        n:11
      }
    ],
    t2:[
      {
        name:"bb",
        n:12
      },
      {
        name:"cc",
        n:22
      },
      {
        name:"ee",
        n:42
      }
    ]
  },
  {
    name:"PG_JOIN_01",
    statement:"SELECT * FROM j1_tbl JOIN j2_tbl ON (j1_tbl.i = j2_tbl.i)",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          i:1,
          j:4,
          t:"one",
          i:1,
          k:-1
        },
        {
          i:2,
          j:3,
          t:"two",
          i:2,
          k:2
        },
        {
          i:2,
          j:3,
          t:"two",
          i:2,
          k:4
        },
        {
          i:3,
          j:2,
          t:"three",
          i:3,
          k:-3
        },
        {
          i:5,
          j:0,
          t:"five",
          i:5,
          k:-5
        },
        {
          i:5,
          j:0,
          t:"five",
          i:5,
          k:-5
        }
      ]
    }
  },
  {
    name:"PG_JOIN_02",
    statement:"SELECT * FROM j1_tbl JOIN j2_tbl ON (j1_tbl.i = j2_tbl.k)",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          i:2,
          j:3,
          t:"two",
          i:2,
          k:2
        },
        {
          i:4,
          j:1,
          t:"four",
          i:2,
          k:4
        }
      ]
    }
  },
  {
    name:"PG_JOIN_03",
    statement:"SELECT * FROM j1_tbl JOIN j2_tbl ON (j1_tbl.i <= j2_tbl.k)",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          i:1,
          j:4,
          t:"one",
          i:2,
          k:2
        },
        {
          i:1,
          j:4,
          t:"one",
          i:2,
          k:4
        },
        {
          i:2,
          j:3,
          t:"two",
          i:2,
          k:2
        },
        {
          i:2,
          j:3,
          t:"two",
          i:2,
          k:4
        },
        {
          i:3,
          j:2,
          t:"three",
          i:2,
          k:4
        },
        {
          i:4,
          j:1,
          t:"four",
          i:2,
          k:4
        }
      ]
    }
  },
  {
    name:"PG_JOIN_04",
    statement:"SELECT * FROM j1_tbl LEFT JOIN j2_tbl ON j1_tbl.i = j2_tbl.i WHERE (j2_tbl.k = 1)",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
      ]
    }
  },
  {
    name:"PG_JOIN_05",
    statement:"SELECT * FROM j1_tbl LEFT JOIN j2_tbl ON j1_tbl.i = j2_tbl.i WHERE (j2_tbl.i = 1)",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          i:1,
          j:4,
          t:"one",
          i:1,
          k:-1
        }
      ]
    }
  },
  {
    name:"PG_JOIN_06",
    statement:"SELECT * FROM (SELECT * FROM t2) as s2 INNER JOIN (SELECT * FROM t3) s3 ON s2.name = s3.name",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          name:"bb",
          n:12,
          name:"bb",
          n:13
        },
        {
          name:"cc",
          n:22,
          name:"cc",
          n:23
        }
      ]
    }
  },
  {
    name:"PG_JOIN_07",
    statement:"SELECT * FROM (SELECT * FROM t2) as s2 LEFT JOIN (SELECT * FROM t3) s3 ON s2.name = s3.name",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          name:"bb",
          n:12,
          name:"bb",
          n:13
        },
        {
          name:"cc",
          n:22,
          name:"cc",
          n:23
        },
        {
          name:"ee",
          n:42,
          _3:null
        }
      ]
    }
  },
  {
    name:"PG_JOIN_08",
    statement:"SELECT * FROM (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 INNER JOIN (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3 ON s2.name = s3.name",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          name:"bb",
          s2_n:12,
          s2_2:2,
          name:"bb",
          s3_n:13,
          s3_2:3
        },
        {
          name:"cc",
          s2_n:22,
          s2_2:2,
          name:"cc",
          s3_n:23,
          s3_2:3
        }
      ]
    }
  },
  {
    name:"PG_JOIN_09",
    statement:"SELECT * FROM (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 LEFT JOIN (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3 ON s2.name = s3.name",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          name:"bb",
          s2_n:12,
          s2_2:2,
          name:"bb",
          s3_n:13,
          s3_2:3
        },
        {
          name:"cc",
          s2_n:22,
          s2_2:2,
          name:"cc",
          s3_n:23,
          s3_2:3
        },
        {
          name:"ee",
          s2_n:42,
          s2_2:2,
          _4:null
        }
      ]
    }
  },
  {
    name:"PG_JOIN_10",
    statement:"SELECT * FROM (SELECT name, n as s1_n, 1 as s1_1 FROM t1) as s1 INNER JOIN (SELECT name, n as s2_n, 2 as s2_2 FROM t2) as s2 ON s1.name = s2.name INNER JOIN (SELECT name, n as s3_n, 3 as s3_2 FROM t3) s3 ON s2.name = s3.name",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          name:"bb",
          s1_n:11,
          s1_1:1,
          name:"bb",
          s2_n:12,
          s2_2:2,
          name:"bb",
          s3_n:13,
          s3_2:3
        }
      ]
    }
  }
]