mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-05-19 06:20:38 +00:00
56fc77e573
* fix: add missing error display message Signed-off-by: Ruihang Xia <waynestxia@gmail.com> * update sqlness result Signed-off-by: Ruihang Xia <waynestxia@gmail.com> --------- Signed-off-by: Ruihang Xia <waynestxia@gmail.com>
232 lines
4.0 KiB
Plaintext
232 lines
4.0 KiB
Plaintext
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
|
|
|