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
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
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

-- ORDER BY for partition table
CREATE TABLE IF NOT EXISTS `t` (
  `tag` STRING NULL,
  `ts` TIMESTAMP(3) NOT NULL,
  `num` BIGINT NULL,
  TIME INDEX (`ts`),
  PRIMARY KEY (`tag`)
)
PARTITION ON COLUMNS (`tag`) (
  tag <= 'z',
  tag > 'z'
);

Affected Rows: 0

INSERT INTO t (tag, ts, num) VALUES
    ('abc', 0, 1),
    ('abc', 3000, 2),
    ('abc', 6000, 3),
    ('abc', 9000, 4),
    ('abc', 12000, 5),
    ('zzz', 3000, 6),
    ('zzz', 6000, 7),
    ('zzz', 9000, 8),
    ('zzz', 0, 9),
    ('zzz', 3000, 10);

Affected Rows: 10

select * from t where num > 3 order by ts desc limit 2;

+-----+---------------------+-----+
| tag | ts                  | num |
+-----+---------------------+-----+
| abc | 1970-01-01T00:00:12 | 5   |
| abc | 1970-01-01T00:00:09 | 4   |
+-----+---------------------+-----+

select tag from t where num > 6 order by ts desc limit 2;

+-----+
| tag |
+-----+
| zzz |
| zzz |
+-----+

select tag from t where num > 6 order by ts;

+-----+
| tag |
+-----+
| zzz |
| zzz |
| zzz |
| zzz |
+-----+

-- SQLNESS REPLACE (-+) -
-- SQLNESS REPLACE (\s\s+) _
-- SQLNESS REPLACE (peers.*) REDACTED
-- SQLNESS REPLACE (metrics.*) REDACTED
-- SQLNESS REPLACE region=\d+\(\d+,\s+\d+\) region=REDACTED
-- SQLNESS REPLACE (RoundRobinBatch.*) REDACTED
-- SQLNESS REPLACE num_ranges=\d+ num_ranges=REDACTED
-- SQLNESS REPLACE ,\sfilter=\[[^]]+\] 
explain analyze select tag from t where num > 6 order by ts desc limit 2;

+-+-+-+
| stage | node | plan_|
+-+-+-+
| 0_| 0_|_ProjectionExec: expr=[tag@0 as tag] REDACTED
|_|_|_SortPreservingMergeExec: [ts@1 DESC], fetch=2 REDACTED
|_|_|_SortExec: TopK(fetch=2), expr=[ts@1 DESC], preserve_partitioning=[true] REDACTED
|_|_|_MergeScanExec: REDACTED
|_|_|_|
| 1_| 0_|_SortPreservingMergeExec: [ts@1 DESC], fetch=2 REDACTED
|_|_|_SortExec: TopK(fetch=2), expr=[ts@1 DESC], preserve_partitioning=[true] REDACTED
|_|_|_FilterExec: num@2 > 6, projection=[tag@0, ts@1] REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":1, "files":0, "file_ranges":0} REDACTED
|_|_|_|
| 1_| 1_|_SortPreservingMergeExec: [ts@1 DESC], fetch=2 REDACTED
|_|_|_SortExec: TopK(fetch=2), expr=[ts@1 DESC], preserve_partitioning=[true] REDACTED
|_|_|_FilterExec: num@2 > 6, projection=[tag@0, ts@1] REDACTED
|_|_|_SeqScan: region=REDACTED, "partition_count":{"count":1, "mem_ranges":1, "files":0, "file_ranges":0} REDACTED
|_|_|_|
|_|_| Total rows: 2_|
+-+-+-+

drop table t;

Affected Rows: 0

-- ORDER BY with projections
CREATE TABLE test (
  c1 INTEGER,
  c2 INTEGER,
  c3 STRING,
  c4 DOUBLE,
  ts TIMESTAMP TIME INDEX,
  PRIMARY KEY (c1, c3, c2)
);

Affected Rows: 0

INSERT INTO test VALUES (1, NULL, 'a', 3.0, 1), (2, 3, 'b', 4.0, 2), (3, 4, 'c', 5.0, 3);

Affected Rows: 3

SELECT c1, c3 FROM test ORDER BY c2;

+----+----+
| c1 | c3 |
+----+----+
| 2  | b  |
| 3  | c  |
| 1  | a  |
+----+----+

SELECT c1, c3 FROM test ORDER BY c2 NULLS FIRST;

+----+----+
| c1 | c3 |
+----+----+
| 1  | a  |
| 2  | b  |
| 3  | c  |
+----+----+

SELECT c1, c3 FROM test ORDER BY c3, c1;

+----+----+
| c1 | c3 |
+----+----+
| 1  | a  |
| 2  | b  |
| 3  | c  |
+----+----+

SELECT c2 FROM test ORDER BY ts;

+----+
| c2 |
+----+
|    |
| 3  |
| 4  |
+----+

drop table test;

Affected Rows: 0

