envs::{
  data:[
    1,
    1,
    1,
    2
  ],
  numbers:[
    1,
    2.0,
    3e0,
    4,
    5.
  ],
}

sql_max::[
  {
    name:"max select{agg:'MAX(d)',result:2}",
    statement:"SELECT MAX(d) AS a FROM data d",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:2
        }
      ]
    }
  },
  {
    name:"max select{agg:'MAX(ALL d)',result:2}",
    statement:"SELECT MAX(ALL d) AS a FROM data d",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:2
        }
      ]
    }
  },
  {
    name:"max select{agg:'MAX(DISTINCT d)',result:2}",
    statement:"SELECT MAX(DISTINCT d) AS a FROM data d",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:2
        }
      ]
    }
  },
  {
    name:"max group by{agg:'MAX(t.b)',expectedF1:2,expectedF2:4}",
    statement:"SELECT t.a, MAX(t.b) as c FROM << {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 2}, {'a': 'f2', 'b': 2}, {'a': 'f2', 'b': 4} >> AS t GROUP BY t.a",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:"f1",
          c:2
        },
        {
          a:"f2",
          c:4
        }
      ]
    }
  },
  {
    name:"max group by{agg:'MAX(ALL t.b)',expectedF1:2,expectedF2:4}",
    statement:"SELECT t.a, MAX(ALL t.b) as c FROM << {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 2}, {'a': 'f2', 'b': 2}, {'a': 'f2', 'b': 4} >> AS t GROUP BY t.a",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:"f1",
          c:2
        },
        {
          a:"f2",
          c:4
        }
      ]
    }
  },
  {
    name:"max group by{agg:'MAX(DISTINCT t.b)',expectedF1:2,expectedF2:4}",
    statement:"SELECT t.a, MAX(DISTINCT t.b) as c FROM << {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 2}, {'a': 'f2', 'b': 2}, {'a': 'f2', 'b': 4} >> AS t GROUP BY t.a",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:"f1",
          c:2
        },
        {
          a:"f2",
          c:4
        }
      ]
    }
  }
]

sql_avg::[
  {
    name:"avg select{agg:'AVG(d)',result:1.25}",
    statement:"SELECT AVG(d) AS a FROM data d",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:1.25
        }
      ]
    }
  },
  {
    name:"avg select{agg:'AVG(ALL d)',result:1.25}",
    statement:"SELECT AVG(ALL d) AS a FROM data d",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:1.25
        }
      ]
    }
  },
  {
    name:"avg select{agg:'AVG(DISTINCT d)',result:1.5}",
    statement:"SELECT AVG(DISTINCT d) AS a FROM data d",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:1.5
        }
      ]
    }
  },
  {
    name:"avg group by{agg:'AVG(t.b)',expectedF1:1.25,expectedF2:3.}",
    statement:"SELECT t.a, AVG(t.b) as c FROM << {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 2}, {'a': 'f2', 'b': 2}, {'a': 'f2', 'b': 4} >> AS t GROUP BY t.a",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:"f1",
          c:1.25
        },
        {
          a:"f2",
          c:3.
        }
      ]
    }
  },
  {
    name:"avg group by{agg:'AVG(ALL t.b)',expectedF1:1.25,expectedF2:3.}",
    statement:"SELECT t.a, AVG(ALL t.b) as c FROM << {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 2}, {'a': 'f2', 'b': 2}, {'a': 'f2', 'b': 4} >> AS t GROUP BY t.a",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:"f1",
          c:1.25
        },
        {
          a:"f2",
          c:3.
        }
      ]
    }
  },
  {
    name:"avg group by{agg:'AVG(DISTINCT t.b)',expectedF1:1.5,expectedF2:3.}",
    statement:"SELECT t.a, AVG(DISTINCT t.b) as c FROM << {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 2}, {'a': 'f2', 'b': 2}, {'a': 'f2', 'b': 4} >> AS t GROUP BY t.a",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:"f1",
          c:1.5
        },
        {
          a:"f2",
          c:3.
        }
      ]
    }
  }
]

sql_count::[
  {
    name:"count select{agg:'COUNT(d)',result:4}",
    statement:"SELECT COUNT(d) AS a FROM data d",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:4
        }
      ]
    }
  },
  {
    name:"count select{agg:'COUNT(ALL d)',result:4}",
    statement:"SELECT COUNT(ALL d) AS a FROM data d",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:4
        }
      ]
    }
  },
  {
    name:"count select{agg:'COUNT(DISTINCT d)',result:2}",
    statement:"SELECT COUNT(DISTINCT d) AS a FROM data d",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:2
        }
      ]
    }
  },
  {
    name:"count group by{agg:'COUNT(t.b)',expectedF1:4,expectedF2:2}",
    statement:"SELECT t.a, COUNT(t.b) as c FROM << {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 2}, {'a': 'f2', 'b': 2}, {'a': 'f2', 'b': 4} >> AS t GROUP BY t.a",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:"f1",
          c:4
        },
        {
          a:"f2",
          c:2
        }
      ]
    }
  },
  {
    name:"count group by{agg:'COUNT(ALL t.b)',expectedF1:4,expectedF2:2}",
    statement:"SELECT t.a, COUNT(ALL t.b) as c FROM << {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 2}, {'a': 'f2', 'b': 2}, {'a': 'f2', 'b': 4} >> AS t GROUP BY t.a",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:"f1",
          c:4
        },
        {
          a:"f2",
          c:2
        }
      ]
    }
  },
  {
    name:"count group by{agg:'COUNT(DISTINCT t.b)',expectedF1:2,expectedF2:2}",
    statement:"SELECT t.a, COUNT(DISTINCT t.b) as c FROM << {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 2}, {'a': 'f2', 'b': 2}, {'a': 'f2', 'b': 4} >> AS t GROUP BY t.a",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:"f1",
          c:2
        },
        {
          a:"f2",
          c:2
        }
      ]
    }
  },
  {
    name:"selectListCountStar",
    statement:"SELECT COUNT(*) AS c FROM <<numbers, numbers>> AS v",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:$bag::[
        {
          c:2
        }
      ]
    }
  },
  {
    name:"selectListCountVariable",
    statement:"SELECT COUNT(v) AS c FROM <<numbers, numbers>> AS v",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:$bag::[
        {
          c:2
        }
      ]
    }
  },
  {
    name:"aggregateInSubqueryOfSelect",
    statement:"SELECT foo.cnt FROM (SELECT COUNT(*) AS cnt FROM [1, 2, 3]) AS foo",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:$bag::[
        {
          cnt:3
        }
      ]
    }
  },
  {
    name:"aggregateInSubqueryOfSelectValue",
    statement:"SELECT VALUE foo.cnt FROM (SELECT COUNT(*) AS cnt FROM [1, 2, 3]) AS foo",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:$bag::[
        3
      ]
    }
  },
  {
    name:"aggregateWithAliasingInSubqueryOfSelectValue",
    statement:"SELECT VALUE foo.cnt FROM (SELECT COUNT(baz.bar) AS cnt FROM << { 'bar': 1 }, { 'bar': 2 } >> AS baz) AS foo",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:$bag::[
        2
      ]
    }
  }
]

sql_sum::[
  {
    name:"sum select{agg:'SUM(d)',result:5}",
    statement:"SELECT SUM(d) AS a FROM data d",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:5
        }
      ]
    }
  },
  {
    name:"sum select{agg:'SUM(ALL d)',result:5}",
    statement:"SELECT SUM(ALL d) AS a FROM data d",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:5
        }
      ]
    }
  },
  {
    name:"sum select{agg:'SUM(DISTINCT d)',result:3}",
    statement:"SELECT SUM(DISTINCT d) AS a FROM data d",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:3
        }
      ]
    }
  },
  {
    name:"sum group by{agg:'SUM(t.b)',expectedF1:5,expectedF2:6}",
    statement:"SELECT t.a, SUM(t.b) as c FROM << {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 2}, {'a': 'f2', 'b': 2}, {'a': 'f2', 'b': 4} >> AS t GROUP BY t.a",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:"f1",
          c:5
        },
        {
          a:"f2",
          c:6
        }
      ]
    }
  },
  {
    name:"sum group by{agg:'SUM(ALL t.b)',expectedF1:5,expectedF2:6}",
    statement:"SELECT t.a, SUM(ALL t.b) as c FROM << {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 2}, {'a': 'f2', 'b': 2}, {'a': 'f2', 'b': 4} >> AS t GROUP BY t.a",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:"f1",
          c:5
        },
        {
          a:"f2",
          c:6
        }
      ]
    }
  },
  {
    name:"sum group by{agg:'SUM(DISTINCT t.b)',expectedF1:3,expectedF2:6}",
    statement:"SELECT t.a, SUM(DISTINCT t.b) as c FROM << {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 2}, {'a': 'f2', 'b': 2}, {'a': 'f2', 'b': 4} >> AS t GROUP BY t.a",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:"f1",
          c:3
        },
        {
          a:"f2",
          c:6
        }
      ]
    }
  }
]

sql_min::[
  {
    name:"min select{agg:'MIN(d)',result:1}",
    statement:"SELECT MIN(d) AS a FROM data d",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:1
        }
      ]
    }
  },
  {
    name:"min select{agg:'MIN(ALL d)',result:1}",
    statement:"SELECT MIN(ALL d) AS a FROM data d",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:1
        }
      ]
    }
  },
  {
    name:"min select{agg:'MIN(DISTINCT d)',result:1}",
    statement:"SELECT MIN(DISTINCT d) AS a FROM data d",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:1
        }
      ]
    }
  },
  {
    name:"min group by{agg:'MIN(t.b)',expectedF1:1,expectedF2:2}",
    statement:"SELECT t.a, MIN(t.b) as c FROM << {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 2}, {'a': 'f2', 'b': 2}, {'a': 'f2', 'b': 4} >> AS t GROUP BY t.a",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:"f1",
          c:1
        },
        {
          a:"f2",
          c:2
        }
      ]
    }
  },
  {
    name:"min group by{agg:'MIN(ALL t.b)',expectedF1:1,expectedF2:2}",
    statement:"SELECT t.a, MIN(ALL t.b) as c FROM << {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 2}, {'a': 'f2', 'b': 2}, {'a': 'f2', 'b': 4} >> AS t GROUP BY t.a",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:"f1",
          c:1
        },
        {
          a:"f2",
          c:2
        }
      ]
    }
  },
  {
    name:"min group by{agg:'MIN(DISTINCT t.b)',expectedF1:1,expectedF2:2}",
    statement:"SELECT t.a, MIN(DISTINCT t.b) as c FROM << {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 1}, {'a': 'f1', 'b': 2}, {'a': 'f2', 'b': 2}, {'a': 'f2', 'b': 4} >> AS t GROUP BY t.a",
    assert:{
      result:EvaluationSuccess,
      evalMode:[
        EvalModeCoerce,
        EvalModeError
      ],
      output:$bag::[
        {
          a:"f1",
          c:1
        },
        {
          a:"f2",
          c:2
        }
      ]
    }
  }
]

sql_any::[
  {
    name:"ANY with GROUP BY",
    statement:'''
              SELECT x.a, ANY(x.b < 15) AS e
              FROM << {'a': 1, 'b': 10}, {'a': 1, 'b': 17}, {'a': 2, 'b': 20}, {'a': 3} >> AS x
              GROUP BY x.a
              ''',
    assert:{
      result:EvaluationSuccess,
      evalMode:[EvalModeCoerce, EvalModeError],
      output:$bag::[
        {
          a: 1,
          e: true
        },
        {
          a: 2,
          e: false
        },
        {
          a: 3,
          e: null
        },
      ]
    }
  },
  {
    name:"ANY DISTINCT with GROUP BY",
    statement:'''
              SELECT x.a, ANY(DISTINCT x.b < 15) AS e
              FROM [ {'a': 1, 'b': 10}, {'a': 1, 'b': 17}, {'a': 2, 'b': 20}, {'a': 3} ] AS x
              GROUP BY x.a
              ''',
    assert:{
      result:EvaluationSuccess,
      evalMode:[EvalModeCoerce, EvalModeError],
      output:$bag::[
        {
          a: 1,
          e: true
        },
        {
          a: 2,
          e: false
        },
        {
          a: 3,
          e: null
        },
      ]
    }
  }
]

sql_some::[
  {
    name:"SOME with GROUP BY",
    statement:'''
              SELECT x.a, SOME(x.b < 15) AS e
              FROM << {'a': 1, 'b': 10}, {'a': 1, 'b': 17}, {'a': 2, 'b': 20}, {'a': 3} >> AS x
              GROUP BY x.a
              ''',
    assert:{
      result:EvaluationSuccess,
      evalMode:[EvalModeCoerce, EvalModeError],
      output:$bag::[
        {
          a: 1,
          e: true
        },
        {
          a: 2,
          e: false
        },
        {
          a: 3,
          e: null
        },
      ]
    }
  },
  {
    name:"SOME DISTINCT with GROUP BY",
    statement:'''
              SELECT x.a, SOME(DISTINCT x.b < 15) AS e
              FROM [ {'a': 1, 'b': 10}, {'a': 1, 'b': 17}, {'a': 2, 'b': 20}, {'a': 3} ] AS x
              GROUP BY x.a
              ''',
    assert:{
      result:EvaluationSuccess,
      evalMode:[EvalModeCoerce, EvalModeError],
      output:$bag::[
        {
          a: 1,
          e: true
        },
        {
          a: 2,
          e: false
        },
        {
          a: 3,
          e: null
        },
      ]
    }
  }
]

sql_every::[
  {
    name:"EVERY with GROUP BY",
    statement:'''
              SELECT x.a, EVERY(x.b < 15) AS e
              FROM << {'a': 1, 'b': 10}, {'a': 1, 'b': 11}, {'a': 2, 'b': 20}, {'a': 3} >> AS x
              GROUP BY x.a
              ''',
    assert:{
      result:EvaluationSuccess,
      evalMode:[EvalModeCoerce, EvalModeError],
      output:$bag::[
        {
          a: 1,
          e: true
        },
        {
          a: 2,
          e: false
        },
        {
          a: 3,
          e: null
        },
      ]
    }
  },
  {
    name:"EVERY DISTINCT with GROUP BY",
    statement:'''
              SELECT x.a, EVERY(DISTINCT x.b < 15) AS e
              FROM [ {'a': 1, 'b': 10}, {'a': 1, 'b': 11}, {'a': 2, 'b': 20}, {'a': 3} ] AS x
              GROUP BY x.a
              ''',
    assert:{
      result:EvaluationSuccess,
      evalMode:[EvalModeCoerce, EvalModeError],
      output:$bag::[
        {
          a: 1,
          e: true
        },
        {
          a: 2,
          e: false
        },
        {
          a: 3,
          e: null
        },
      ]
    }
  }
]

multiple_sql_aggregates::[
  {
    name:"selectListMultipleAggregates",
    statement:"SELECT COUNT(*) AS c, AVG(v * 2) + SUM(v + v) AS result FROM numbers AS v",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:$bag::[
        {
          c:5,
          result:36.0
        }
      ]
    }
  },
  {
    name:"selectListMultipleAggregatesNestedQuery",
    statement:"SELECT VALUE (SELECT MAX(v2 * v2) + MIN(v2 * 2) * v1 AS result FROM numbers AS v2) FROM numbers AS v1",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:$bag::[
        $bag::[
          {
            result:27.
          }
        ],
        $bag::[
          {
            result:29.0
          }
        ],
        $bag::[
          {
            result:31.
          }
        ],
        $bag::[
          {
            result:33.
          }
        ],
        $bag::[
          {
            result:35.
          }
        ]
      ]
    }
  },
]