envs::{
  foo:[
    { 'a': 1 },
    { 'a': 2 },
    { 'a': 3 },
    { 'a': 4 },
    { 'a': 5 }
  ]
}

limit_offset::[
  // OFFSET 0 should not affect results
  {
    name:"offset 0",
    statement:"SELECT a FROM foo OFFSET 0",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:$bag::[
        {'a': 1}, {'a': 2}, {'a': 3}, {'a': 4}, {'a': 5}
      ]
    }
  },
    
  // OFFSET 1 should skip first result
  {
    name:"offset 1",
    statement:"SELECT a FROM foo ORDER BY a OFFSET 1",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:[
        {'a': 2}, {'a': 3}, {'a': 4}, {'a': 5}
      ]
    }
  },

  // OFFSET 2 should skip first two results
  {
    name:"offset 2",
    statement:"SELECT a FROM foo ORDER BY a OFFSET 2",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:[
        {'a': 3}, {'a': 4}, {'a': 5}
      ]
    }
  },

  // OFFSET 2^31 should return no results
  {
    name:"offset 2^31",
    statement:"SELECT a FROM foo OFFSET 2147483648",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:$bag::[]
    }
  },

 // OFFSET 2^63 should return no results
  {
    name:"offset 2^63",
    statement:"SELECT a FROM foo OFFSET 9223372036854775808",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:$bag::[]
    }
  },

  // LIMIT 1 and OFFSET 1 should return the second result
  {
    name:"limit 1 offset 1",
    statement:"SELECT a FROM foo ORDER BY a LIMIT 1 OFFSET 1",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:[
        {'a': 2}
      ]
    }
  },

  // LIMIT 10 and OFFSET 1 should skip first result
  {
    name:"limit 10 offset 1",
    statement:"SELECT a FROM foo ORDER BY a LIMIT 10 OFFSET 1",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:[
        {'a': 2}, {'a': 3}, {'a': 4}, {'a': 5}
      ]
    }
  },

  // LIMIT 10 and OFFSET 10 should return no result
  {
    name:"limit 10 offset 10",
    statement:"SELECT a FROM foo LIMIT 10 OFFSET 10",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:$bag::[]
    }
  },

  // LIMIT 2 and OFFSET 2 should return third and fourth results
  {
    name:"limit 2 offset 2",
    statement:"SELECT a FROM foo GROUP BY a ORDER BY a LIMIT 2 OFFSET 2",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:[
        {'a': 3}, {'a': 4}
      ],
    }
  },

  // LIMIT and OFFSET applied after GROUP BY
  {
    name:"limit offset after group by",
    statement:"SELECT a FROM foo GROUP BY a ORDER BY a LIMIT 1 OFFSET 1",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:[
        {'a': 2}
      ],
    }
  },

  // OFFSET value can be subtraction of 2 numbers
  {
    name:"offset 2-1",
    statement:"SELECT a FROM foo ORDER BY a OFFSET 2 - 1",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:[
        {'a': 2}, {'a': 3}, {'a': 4}, {'a': 5}
      ]
    }
  },

  // OFFSET value can be addition of 2 numbers
  {
    name:"offset 2+1",
    statement:"SELECT a FROM foo ORDER BY a OFFSET 2 + 1",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:[
        {'a': 4}, {'a': 5}
      ]
    }
  },

  // OFFSET value can be multiplication of 2 numbers
  {
    name:"offset 2*1",
    statement:"SELECT a FROM foo ORDER BY a OFFSET 2 * 1",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:[
        {'a': 3}, {'a': 4}, {'a': 5}
      ]
    }
  },

  // OFFSET value can be division of 2 numbers
  {
    name:"offset 2/1",
    statement:"SELECT a FROM foo ORDER BY a OFFSET 4 / 2",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:[
        {'a': 3}, {'a': 4}, {'a': 5}
      ]
    }
  },

  // OFFSET with GROUP BY and HAVING
  {
    name:"offset group by having",
    statement:"SELECT a FROM foo GROUP BY a HAVING a > 2 ORDER BY a LIMIT 1 OFFSET 1",
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:[
        {'a': 4}
      ],
    }
  },

  // OFFSET with PIVOT
  {
    name:"offset with pivot",
    statement:'''
      PIVOT foo.a AT foo.b
      FROM <<{'a': 1, 'b':'I'}, {'a': 2, 'b':'II'}, {'a': 3, 'b':'III'}>> AS foo
      ORDER BY a
      LIMIT 1 OFFSET 1
    ''',
    assert:{
      evalMode:[EvalModeCoerce, EvalModeError],
      result:EvaluationSuccess,
      output:{'II': 2},
    }
  },

  // OFFSET -1 should throw exception
  {
    name: "offset -1",
    statement: "SELECT a FROM foo OFFSET -1",
    assert: [
      {
        evalMode:[EvalModeCoerce, EvalModeError],
        result: EvaluationFail,
      }
    ]
  },

  // OFFSET 1-2 should throw exception in error mode and do nothing when coerced
  {
    name: "offset 1-2",
    statement: "SELECT a FROM foo OFFSET 1-2",
    assert:[
      {
        evalMode:EvalModeCoerce,
        result:EvaluationSuccess,
        output:$bag::[
          {'a': 1}, {'a': 2}, {'a': 3}, {'a': 4}, {'a': 5}
        ]
      },
      {
        evalMode:EvalModeError,
        result:EvaluationFail,
      }
    ]
  },

  // OFFSET string value should throw exception in error mode and do nothing when coerced
  {
    name: "offset <str>",
    statement: "SELECT a FROM foo OFFSET 'this won''t work'",
    assert:[
      {
        evalMode:EvalModeCoerce,
        result:EvaluationSuccess,
        output:$bag::[
          {'a': 1}, {'a': 2}, {'a': 3}, {'a': 4}, {'a': 5}
        ]
      },
      {
        evalMode:EvalModeError,
        result:EvaluationFail,
      }
    ]
  },

  // OFFSET non-integer value should throw exception in error mode and do nothing when coerced
  {
    name: "offset <decimal>>",
    statement: "SELECT a FROM foo OFFSET 2.5",
    assert:[
      {
        evalMode:EvalModeCoerce,
        result:EvaluationSuccess,
        output:$bag::[
          {'a': 1}, {'a': 2}, {'a': 3}, {'a': 4}, {'a': 5}
        ]
      },
      {
        evalMode:EvalModeError,
        result:EvaluationFail,
      }
    ]
  },
]