CREATE TABLE test (a INTEGER, b INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO test VALUES (11, 22, 1), (12, 21, 2), (13, 22, 3);

Affected Rows: 3

select b from test where a = 12;

+----+
| b  |
+----+
| 21 |
+----+

SELECT b FROM test ORDER BY a DESC;

+----+
| b  |
+----+
| 22 |
| 21 |
| 22 |
+----+

SELECT a, b FROM test ORDER BY a;

+----+----+
| a  | b  |
+----+----+
| 11 | 22 |
| 12 | 21 |
| 13 | 22 |
+----+----+

SELECT a, b FROM test ORDER BY a DESC;

+----+----+
| a  | b  |
+----+----+
| 13 | 22 |
| 12 | 21 |
| 11 | 22 |
+----+----+

SELECT a, b FROM test ORDER BY b, a;

+----+----+
| a  | b  |
+----+----+
| 12 | 21 |
| 11 | 22 |
| 13 | 22 |
+----+----+

SELECT a, b FROM test ORDER BY 2, 1;

+----+----+
| a  | b  |
+----+----+
| 12 | 21 |
| 11 | 22 |
| 13 | 22 |
+----+----+

SELECT a, b FROM test ORDER BY b DESC, a;

+----+----+
| a  | b  |
+----+----+
| 11 | 22 |
| 13 | 22 |
| 12 | 21 |
+----+----+

SELECT a, b FROM test ORDER BY b, a DESC;

+----+----+
| a  | b  |
+----+----+
| 12 | 21 |
| 13 | 22 |
| 11 | 22 |
+----+----+

SELECT a, b FROM test ORDER BY b, a DESC LIMIT 1;

+----+----+
| a  | b  |
+----+----+
| 12 | 21 |
+----+----+

SELECT a, b FROM test ORDER BY b, a DESC LIMIT 1 OFFSET 1;

+----+----+
| a  | b  |
+----+----+
| 13 | 22 |
+----+----+

SELECT a, b FROM test ORDER BY b, a DESC OFFSET 1;

+----+----+
| a  | b  |
+----+----+
| 13 | 22 |
| 11 | 22 |
+----+----+

SELECT a, b FROM test WHERE a < 13 ORDER BY b;

+----+----+
| a  | b  |
+----+----+
| 12 | 21 |
| 11 | 22 |
+----+----+

SELECT a, b FROM test WHERE a < 13 ORDER BY 2;

+----+----+
| a  | b  |
+----+----+
| 12 | 21 |
| 11 | 22 |
+----+----+

SELECT a, b FROM test WHERE a < 13 ORDER BY b DESC;

+----+----+
| a  | b  |
+----+----+
| 11 | 22 |
| 12 | 21 |
+----+----+

SELECT b, a FROM test WHERE a < 13 ORDER BY b DESC;

+----+----+
| b  | a  |
+----+----+
| 22 | 11 |
| 21 | 12 |
+----+----+

SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER BY b % 2;

+---+-------------+
| f | SUM(test.a) |
+---+-------------+
| 0 | 24          |
| 1 | 12          |
+---+-------------+

SELECT b % 2 AS f, a FROM test ORDER BY b % 2, a;

+---+----+
| f | a  |
+---+----+
| 0 | 11 |
| 0 | 13 |
| 1 | 12 |
+---+----+

SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER BY f;

+---+-------------+
| f | SUM(test.a) |
+---+-------------+
| 0 | 24          |
| 1 | 12          |
+---+-------------+

SELECT b % 2 AS f, SUM(a) FROM test GROUP BY f ORDER BY 1;

+---+-------------+
| f | SUM(test.a) |
+---+-------------+
| 0 | 24          |
| 1 | 12          |
+---+-------------+

SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY k;

+---+
| k |
+---+
| 1 |
| 2 |
| 3 |
+---+

-- ORDER BY on alias in right-most query
-- CONTROVERSIAL: SQLite allows both "k" and "l" to be referenced here, Postgres and MonetDB give an error.
SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY l;

Error: 3000(PlanQuery), Failed to plan SQL: No field named l. Valid fields are k.

-- Not compatible with duckdb, work in gretimedb
SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY 1-k;

+---+
| k |
+---+
| 3 |
| 2 |
| 1 |
+---+

-- Not compatible with duckdb, give an error in greptimedb
-- TODO(LFC): Failed to meet the expected error:
-- expected:
--   Error: 3000(PlanQuery), Schema error: No field named 'a'. Valid fields are 'k'.
SELECT a-10 AS k FROM test UNION SELECT a-10 AS l FROM test ORDER BY a-10;

Error: 3000(PlanQuery), Failed to plan SQL: Error during planning: For SELECT DISTINCT, ORDER BY expressions a must appear in select list

-- Not compatible with duckdb, give an error in greptimedb
-- TODO(LFC): Failed to meet the expected error:
-- expected:
--   Error: 3000(PlanQuery), Schema error: No field named 'a'. Valid fields are 'k'.
SELECT a-10 AS k FROM test UNION SELECT a-11 AS l FROM test ORDER BY a-11;

Error: 3000(PlanQuery), Failed to plan SQL: Error during planning: For SELECT DISTINCT, ORDER BY expressions a must appear in select list

DROP TABLE test;

Affected Rows: 0