| /* |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, software |
| * distributed under the License is distributed on an "AS IS" BASIS, |
| * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| * See the License for the specific language governing permissions and |
| * limitations under the License. |
| */ |
| package org.apache.cassandra.cql3.validation.operations; |
| |
| import org.junit.Test; |
| |
| import org.apache.cassandra.cql3.CQLTester; |
| |
| import static java.util.Arrays.asList; |
| import static org.junit.Assert.assertTrue; |
| |
| public class SelectOrderByTest extends CQLTester |
| { |
| @Test |
| public void testNormalSelectionOrderSingleClustering() throws Throwable |
| { |
| for (String descOption : new String[]{"", " WITH CLUSTERING ORDER BY (b DESC)"}) |
| { |
| createTable("CREATE TABLE %s (a int, b int, c int, PRIMARY KEY (a, b))" + descOption); |
| execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, 0, 0); |
| execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, 1, 1); |
| execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, 2, 2); |
| |
| beforeAndAfterFlush(() -> { |
| assertRows(execute("SELECT * FROM %s WHERE a=? ORDER BY b ASC", 0), |
| row(0, 0, 0), |
| row(0, 1, 1), |
| row(0, 2, 2) |
| ); |
| |
| assertRows(execute("SELECT * FROM %s WHERE a=? ORDER BY b DESC", 0), |
| row(0, 2, 2), |
| row(0, 1, 1), |
| row(0, 0, 0) |
| ); |
| |
| // order by the only column in the selection |
| assertRows(execute("SELECT b FROM %s WHERE a=? ORDER BY b ASC", 0), |
| row(0), row(1), row(2)); |
| |
| assertRows(execute("SELECT b FROM %s WHERE a=? ORDER BY b DESC", 0), |
| row(2), row(1), row(0)); |
| |
| // order by a column not in the selection |
| assertRows(execute("SELECT c FROM %s WHERE a=? ORDER BY b ASC", 0), |
| row(0), row(1), row(2)); |
| |
| assertRows(execute("SELECT c FROM %s WHERE a=? ORDER BY b DESC", 0), |
| row(2), row(1), row(0)); |
| }); |
| } |
| } |
| |
| @Test |
| public void testFunctionSelectionOrderSingleClustering() throws Throwable |
| { |
| for (String descOption : new String[]{"", " WITH CLUSTERING ORDER BY (b DESC)"}) |
| { |
| createTable("CREATE TABLE %s (a int, b int, c int, PRIMARY KEY (a, b))" + descOption); |
| execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, 0, 0); |
| execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, 1, 1); |
| execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, 2, 2); |
| |
| beforeAndAfterFlush(() -> { |
| // order by the only column in the selection |
| assertRows(execute("SELECT blobAsInt(intAsBlob(b)) FROM %s WHERE a=? ORDER BY b ASC", 0), |
| row(0), row(1), row(2)); |
| |
| assertRows(execute("SELECT blobAsInt(intAsBlob(b)) FROM %s WHERE a=? ORDER BY b DESC", 0), |
| row(2), row(1), row(0)); |
| |
| // order by a column not in the selection |
| assertRows(execute("SELECT blobAsInt(intAsBlob(c)) FROM %s WHERE a=? ORDER BY b ASC", 0), |
| row(0), row(1), row(2)); |
| |
| assertRows(execute("SELECT blobAsInt(intAsBlob(c)) FROM %s WHERE a=? ORDER BY b DESC", 0), |
| row(2), row(1), row(0)); |
| |
| assertInvalid("SELECT * FROM %s WHERE a=? ORDER BY c ASC", 0); |
| assertInvalid("SELECT * FROM %s WHERE a=? ORDER BY c DESC", 0); |
| }); |
| } |
| } |
| |
| @Test |
| public void testFieldSelectionOrderSingleClustering() throws Throwable |
| { |
| String type = createType("CREATE TYPE %s (a int)"); |
| |
| for (String descOption : new String[]{"", " WITH CLUSTERING ORDER BY (b DESC)"}) |
| { |
| createTable("CREATE TABLE %s (a int, b int, c frozen<" + type + " >, PRIMARY KEY (a, b))" + descOption); |
| execute("INSERT INTO %s (a, b, c) VALUES (?, ?, {a: ?})", 0, 0, 0); |
| execute("INSERT INTO %s (a, b, c) VALUES (?, ?, {a: ?})", 0, 1, 1); |
| execute("INSERT INTO %s (a, b, c) VALUES (?, ?, {a: ?})", 0, 2, 2); |
| |
| beforeAndAfterFlush(() -> { |
| // order by a column not in the selection |
| assertRows(execute("SELECT c.a FROM %s WHERE a=? ORDER BY b ASC", 0), |
| row(0), row(1), row(2)); |
| |
| assertRows(execute("SELECT c.a FROM %s WHERE a=? ORDER BY b DESC", 0), |
| row(2), row(1), row(0)); |
| |
| assertRows(execute("SELECT blobAsInt(intAsBlob(c.a)) FROM %s WHERE a=? ORDER BY b DESC", 0), |
| row(2), row(1), row(0)); |
| }); |
| dropTable("DROP TABLE %s"); |
| } |
| } |
| |
| @Test |
| public void testNormalSelectionOrderMultipleClustering() throws Throwable |
| { |
| createTable("CREATE TABLE %s (a int, b int, c int, d int, PRIMARY KEY (a, b, c))"); |
| execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 0, 0); |
| execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 1, 1); |
| execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 2, 2); |
| execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 0, 3); |
| execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 1, 4); |
| execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 2, 5); |
| |
| beforeAndAfterFlush(() -> { |
| assertRows(execute("SELECT * FROM %s WHERE a=? ORDER BY b ASC", 0), |
| row(0, 0, 0, 0), |
| row(0, 0, 1, 1), |
| row(0, 0, 2, 2), |
| row(0, 1, 0, 3), |
| row(0, 1, 1, 4), |
| row(0, 1, 2, 5) |
| ); |
| |
| assertRows(execute("SELECT * FROM %s WHERE a=? ORDER BY b DESC", 0), |
| row(0, 1, 2, 5), |
| row(0, 1, 1, 4), |
| row(0, 1, 0, 3), |
| row(0, 0, 2, 2), |
| row(0, 0, 1, 1), |
| row(0, 0, 0, 0) |
| ); |
| |
| assertRows(execute("SELECT * FROM %s WHERE a=? ORDER BY b DESC, c DESC", 0), |
| row(0, 1, 2, 5), |
| row(0, 1, 1, 4), |
| row(0, 1, 0, 3), |
| row(0, 0, 2, 2), |
| row(0, 0, 1, 1), |
| row(0, 0, 0, 0) |
| ); |
| |
| assertInvalid("SELECT * FROM %s WHERE a=? ORDER BY c ASC", 0); |
| assertInvalid("SELECT * FROM %s WHERE a=? ORDER BY c DESC", 0); |
| assertInvalid("SELECT * FROM %s WHERE a=? ORDER BY b ASC, c DESC", 0); |
| assertInvalid("SELECT * FROM %s WHERE a=? ORDER BY b DESC, c ASC", 0); |
| assertInvalid("SELECT * FROM %s WHERE a=? ORDER BY d ASC", 0); |
| |
| // select and order by b |
| assertRows(execute("SELECT b FROM %s WHERE a=? ORDER BY b ASC", 0), |
| row(0), row(0), row(0), row(1), row(1), row(1)); |
| assertRows(execute("SELECT b FROM %s WHERE a=? ORDER BY b DESC", 0), |
| row(1), row(1), row(1), row(0), row(0), row(0)); |
| |
| // select c, order by b |
| assertRows(execute("SELECT c FROM %s WHERE a=? ORDER BY b ASC", 0), |
| row(0), row(1), row(2), row(0), row(1), row(2)); |
| assertRows(execute("SELECT c FROM %s WHERE a=? ORDER BY b DESC", 0), |
| row(2), row(1), row(0), row(2), row(1), row(0)); |
| |
| // select c, order by b, c |
| assertRows(execute("SELECT c FROM %s WHERE a=? ORDER BY b ASC, c ASC", 0), |
| row(0), row(1), row(2), row(0), row(1), row(2)); |
| assertRows(execute("SELECT c FROM %s WHERE a=? ORDER BY b DESC, c DESC", 0), |
| row(2), row(1), row(0), row(2), row(1), row(0)); |
| |
| // select d, order by b, c |
| assertRows(execute("SELECT d FROM %s WHERE a=? ORDER BY b ASC, c ASC", 0), |
| row(0), row(1), row(2), row(3), row(4), row(5)); |
| assertRows(execute("SELECT d FROM %s WHERE a=? ORDER BY b DESC, c DESC", 0), |
| row(5), row(4), row(3), row(2), row(1), row(0)); |
| }); |
| } |
| |
| @Test |
| public void testFunctionSelectionOrderMultipleClustering() throws Throwable |
| { |
| createTable("CREATE TABLE %s (a int, b int, c int, d int, PRIMARY KEY (a, b, c))"); |
| execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 0, 0); |
| execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 1, 1); |
| execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 0, 2, 2); |
| execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 0, 3); |
| execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 1, 4); |
| execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", 0, 1, 2, 5); |
| |
| beforeAndAfterFlush(() -> { |
| assertInvalid("SELECT blobAsInt(intAsBlob(b)) FROM %s WHERE a=? ORDER BY c ASC", 0); |
| assertInvalid("SELECT blobAsInt(intAsBlob(b)) FROM %s WHERE a=? ORDER BY c DESC", 0); |
| assertInvalid("SELECT blobAsInt(intAsBlob(b)) FROM %s WHERE a=? ORDER BY b ASC, c DESC", 0); |
| assertInvalid("SELECT blobAsInt(intAsBlob(b)) FROM %s WHERE a=? ORDER BY b DESC, c ASC", 0); |
| assertInvalid("SELECT blobAsInt(intAsBlob(b)) FROM %s WHERE a=? ORDER BY d ASC", 0); |
| |
| // select and order by b |
| assertRows(execute("SELECT blobAsInt(intAsBlob(b)) FROM %s WHERE a=? ORDER BY b ASC", 0), |
| row(0), row(0), row(0), row(1), row(1), row(1)); |
| assertRows(execute("SELECT blobAsInt(intAsBlob(b)) FROM %s WHERE a=? ORDER BY b DESC", 0), |
| row(1), row(1), row(1), row(0), row(0), row(0)); |
| |
| assertRows(execute("SELECT b, blobAsInt(intAsBlob(b)) FROM %s WHERE a=? ORDER BY b ASC", 0), |
| row(0, 0), row(0, 0), row(0, 0), row(1, 1), row(1, 1), row(1, 1)); |
| assertRows(execute("SELECT b, blobAsInt(intAsBlob(b)) FROM %s WHERE a=? ORDER BY b DESC", 0), |
| row(1, 1), row(1, 1), row(1, 1), row(0, 0), row(0, 0), row(0, 0)); |
| |
| // select c, order by b |
| assertRows(execute("SELECT blobAsInt(intAsBlob(c)) FROM %s WHERE a=? ORDER BY b ASC", 0), |
| row(0), row(1), row(2), row(0), row(1), row(2)); |
| assertRows(execute("SELECT blobAsInt(intAsBlob(c)) FROM %s WHERE a=? ORDER BY b DESC", 0), |
| row(2), row(1), row(0), row(2), row(1), row(0)); |
| |
| // select c, order by b, c |
| assertRows(execute("SELECT blobAsInt(intAsBlob(c)) FROM %s WHERE a=? ORDER BY b ASC, c ASC", 0), |
| row(0), row(1), row(2), row(0), row(1), row(2)); |
| assertRows(execute("SELECT blobAsInt(intAsBlob(c)) FROM %s WHERE a=? ORDER BY b DESC, c DESC", 0), |
| row(2), row(1), row(0), row(2), row(1), row(0)); |
| |
| // select d, order by b, c |
| assertRows(execute("SELECT blobAsInt(intAsBlob(d)) FROM %s WHERE a=? ORDER BY b ASC, c ASC", 0), |
| row(0), row(1), row(2), row(3), row(4), row(5)); |
| assertRows(execute("SELECT blobAsInt(intAsBlob(d)) FROM %s WHERE a=? ORDER BY b DESC, c DESC", 0), |
| row(5), row(4), row(3), row(2), row(1), row(0)); |
| }); |
| } |
| |
| /** |
| * Check ORDER BY support in SELECT statement |
| * migrated from cql_tests.py:TestCQL.order_by_test() |
| */ |
| @Test |
| public void testSimpleOrderBy() throws Throwable |
| { |
| createTable("CREATE TABLE %s (k int, c int, v int, PRIMARY KEY (k, c)) WITH COMPACT STORAGE"); |
| |
| for (int i = 0; i < 10; i++) |
| execute("INSERT INTO %s (k, c, v) VALUES (0, ?, ?)", i, i); |
| |
| beforeAndAfterFlush(() -> { |
| assertRows(execute("SELECT v FROM %s WHERE k = 0 ORDER BY c DESC"), |
| row(9), row(8), row(7), row(6), row(5), row(4), row(3), row(2), row(1), row(0)); |
| }); |
| |
| createTable("CREATE TABLE %s (k int, c1 int, c2 int, v int, PRIMARY KEY (k, c1, c2)) WITH COMPACT STORAGE"); |
| |
| for (int i = 0; i < 4; i++) |
| for (int j = 0; j < 2; j++) |
| execute("INSERT INTO %s (k, c1, c2, v) VALUES (0, ?, ?, ?)", i, j, i * 2 + j); |
| |
| beforeAndAfterFlush(() -> { |
| assertInvalid("SELECT v FROM %s WHERE k = 0 ORDER BY c DESC"); |
| assertInvalid("SELECT v FROM %s WHERE k = 0 ORDER BY c2 DESC"); |
| assertInvalid("SELECT v FROM %s WHERE k = 0 ORDER BY k DESC"); |
| |
| assertRows(execute("SELECT v FROM %s WHERE k = 0 ORDER BY c1 DESC"), |
| row(7), row(6), row(5), row(4), row(3), row(2), row(1), row(0)); |
| |
| assertRows(execute("SELECT v FROM %s WHERE k = 0 ORDER BY c1"), |
| row(0), row(1), row(2), row(3), row(4), row(5), row(6), row(7)); |
| }); |
| } |
| |
| /** |
| * More ORDER BY checks (#4160) |
| * migrated from cql_tests.py:TestCQL.more_order_by_test() |
| */ |
| @Test |
| public void testMoreOrderBy() throws Throwable |
| { |
| createTable("CREATE TABLE %s (row text, number int, string text, PRIMARY KEY(row, number)) WITH COMPACT STORAGE "); |
| |
| execute("INSERT INTO %s (row, number, string) VALUES ('row', 1, 'one')"); |
| execute("INSERT INTO %s (row, number, string) VALUES ('row', 2, 'two')"); |
| execute("INSERT INTO %s (row, number, string) VALUES ('row', 3, 'three')"); |
| execute("INSERT INTO %s (row, number, string) VALUES ('row', 4, 'four')"); |
| |
| beforeAndAfterFlush(() -> { |
| assertRows(execute("SELECT number FROM %s WHERE row='row' AND number < 3 ORDER BY number ASC"), |
| row(1), row(2)); |
| |
| assertRows(execute("SELECT number FROM %s WHERE row='row' AND number >= 3 ORDER BY number ASC"), |
| row(3), row(4)); |
| |
| assertRows(execute("SELECT number FROM %s WHERE row='row' AND number < 3 ORDER BY number DESC"), |
| row(2), row(1)); |
| |
| assertRows(execute("SELECT number FROM %s WHERE row='row' AND number >= 3 ORDER BY number DESC"), |
| row(4), row(3)); |
| |
| assertRows(execute("SELECT number FROM %s WHERE row='row' AND number > 3 ORDER BY number DESC"), |
| row(4)); |
| |
| assertRows(execute("SELECT number FROM %s WHERE row='row' AND number <= 3 ORDER BY number DESC"), |
| row(3), row(2), row(1)); |
| }); |
| } |
| |
| /** |
| * Check we don't allow order by on row key (#4246) |
| * migrated from cql_tests.py:TestCQL.order_by_validation_test() |
| */ |
| @Test |
| public void testInvalidOrderBy() throws Throwable |
| { |
| createTable("CREATE TABLE %s( k1 int, k2 int, v int, PRIMARY KEY (k1, k2))"); |
| |
| execute("INSERT INTO %s (k1, k2, v) VALUES (?, ?, ?)", 0, 0, 0); |
| execute("INSERT INTO %s (k1, k2, v) VALUES (?, ?, ?)", 1, 1, 1); |
| execute("INSERT INTO %s (k1, k2, v) VALUES (?, ?, ?)", 2, 2, 2); |
| |
| assertInvalid("SELECT * FROM %s ORDER BY k2"); |
| } |
| |
| /** |
| * Check that order-by works with IN (#4327) |
| * migrated from cql_tests.py:TestCQL.order_by_with_in_test() |
| */ |
| @Test |
| public void testOrderByForInClause() throws Throwable |
| { |
| createTable("CREATE TABLE %s (my_id varchar, col1 int, value varchar, PRIMARY KEY (my_id, col1))"); |
| |
| execute("INSERT INTO %s (my_id, col1, value) VALUES ( 'key1', 1, 'a')"); |
| execute("INSERT INTO %s (my_id, col1, value) VALUES ( 'key2', 3, 'c')"); |
| execute("INSERT INTO %s (my_id, col1, value) VALUES ( 'key3', 2, 'b')"); |
| execute("INSERT INTO %s (my_id, col1, value) VALUES ( 'key4', 4, 'd')"); |
| |
| beforeAndAfterFlush(() -> { |
| assertRows(execute("SELECT col1 FROM %s WHERE my_id in('key1', 'key2', 'key3') ORDER BY col1"), |
| row(1), row(2), row(3)); |
| |
| assertRows(execute("SELECT col1 FROM %s WHERE my_id in('key1', 'key2', 'key3') ORDER BY col1 LIMIT 2"), |
| row(1), row(2)); |
| |
| assertRows(execute("SELECT col1 FROM %s WHERE my_id in('key1', 'key2', 'key3') ORDER BY col1 LIMIT 10"), |
| row(1), row(2), row(3)); |
| |
| assertRows(execute("SELECT col1, my_id FROM %s WHERE my_id in('key1', 'key2', 'key3') ORDER BY col1"), |
| row(1, "key1"), row(2, "key3"), row(3, "key2")); |
| |
| assertRows(execute("SELECT my_id, col1 FROM %s WHERE my_id in('key1', 'key2', 'key3') ORDER BY col1"), |
| row("key1", 1), row("key3", 2), row("key2", 3)); |
| }); |
| |
| createTable("CREATE TABLE %s (pk1 int, pk2 int, c int, v text, PRIMARY KEY ((pk1, pk2), c) )"); |
| execute("INSERT INTO %s (pk1, pk2, c, v) VALUES (?, ?, ?, ?)", 1, 1, 2, "A"); |
| execute("INSERT INTO %s (pk1, pk2, c, v) VALUES (?, ?, ?, ?)", 1, 2, 1, "B"); |
| execute("INSERT INTO %s (pk1, pk2, c, v) VALUES (?, ?, ?, ?)", 1, 3, 3, "C"); |
| execute("INSERT INTO %s (pk1, pk2, c, v) VALUES (?, ?, ?, ?)", 1, 1, 4, "D"); |
| |
| beforeAndAfterFlush(() -> { |
| assertRows(execute("SELECT v, ttl(v), c FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c; ", 1, 1, 2), |
| row("B", null, 1), |
| row("A", null, 2), |
| row("D", null, 4)); |
| |
| assertRows(execute("SELECT v, ttl(v), c as name_1 FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c; ", 1, 1, 2), |
| row("B", null, 1), |
| row("A", null, 2), |
| row("D", null, 4)); |
| |
| assertRows(execute("SELECT v FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c; ", 1, 1, 2), |
| row("B"), |
| row("A"), |
| row("D")); |
| |
| assertRows(execute("SELECT v FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c LIMIT 2; ", 1, 1, 2), |
| row("B"), |
| row("A")); |
| |
| assertRows(execute("SELECT v FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c LIMIT 10; ", 1, 1, 2), |
| row("B"), |
| row("A"), |
| row("D")); |
| |
| assertRows(execute("SELECT v as c FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c; ", 1, 1, 2), |
| row("B"), |
| row("A"), |
| row("D")); |
| }); |
| |
| createTable("CREATE TABLE %s (pk1 int, pk2 int, c1 int, c2 int, v text, PRIMARY KEY ((pk1, pk2), c1, c2) )"); |
| execute("INSERT INTO %s (pk1, pk2, c1, c2, v) VALUES (?, ?, ?, ?, ?)", 1, 1, 4, 4, "A"); |
| execute("INSERT INTO %s (pk1, pk2, c1, c2, v) VALUES (?, ?, ?, ?, ?)", 1, 2, 1, 2, "B"); |
| execute("INSERT INTO %s (pk1, pk2, c1, c2, v) VALUES (?, ?, ?, ?, ?)", 1, 3, 3, 3, "C"); |
| execute("INSERT INTO %s (pk1, pk2, c1, c2, v) VALUES (?, ?, ?, ?, ?)", 1, 1, 4, 1, "D"); |
| |
| beforeAndAfterFlush(() -> { |
| assertRows(execute("SELECT v, ttl(v), c1, c2 FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c1, c2; ", 1, 1, 2), |
| row("B", null, 1, 2), |
| row("D", null, 4, 1), |
| row("A", null, 4, 4)); |
| |
| assertRows(execute("SELECT v, ttl(v), c1 as name_1, c2 as name_2 FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c1, c2; ", 1, 1, 2), |
| row("B", null, 1, 2), |
| row("D", null, 4, 1), |
| row("A", null, 4, 4)); |
| |
| assertRows(execute("SELECT v FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c1, c2; ", 1, 1, 2), |
| row("B"), |
| row("D"), |
| row("A")); |
| |
| assertRows(execute("SELECT v as c2 FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c1, c2; ", 1, 1, 2), |
| row("B"), |
| row("D"), |
| row("A")); |
| |
| assertRows(execute("SELECT v as c2 FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c1, c2 LIMIT 2; ", 1, 1, 2), |
| row("B"), |
| row("D")); |
| |
| assertRows(execute("SELECT v as c2 FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c1, c2 LIMIT 10; ", 1, 1, 2), |
| row("B"), |
| row("D"), |
| row("A")); |
| |
| assertRows(execute("SELECT v as c2 FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c1 DESC , c2 DESC; ", 1, 1, 2), |
| row("A"), |
| row("D"), |
| row("B")); |
| |
| assertRows(execute("SELECT v as c2 FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c1 DESC , c2 DESC LIMIT 2; ", 1, 1, 2), |
| row("A"), |
| row("D")); |
| |
| assertRows(execute("SELECT v as c2 FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c1 DESC , c2 DESC LIMIT 10; ", 1, 1, 2), |
| row("A"), |
| row("D"), |
| row("B")); |
| |
| assertInvalidMessage("LIMIT must be strictly positive", |
| "SELECT v as c2 FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c1 DESC , c2 DESC LIMIT 0; ", 1, 1, 2); |
| }); |
| } |
| |
| @Test |
| public void testOrderByForInClauseWithNullValue() throws Throwable |
| { |
| createTable("CREATE TABLE %s (a int, b int, c int, s int static, d int, PRIMARY KEY (a, b, c))"); |
| |
| execute("INSERT INTO %s (a, b, c, d) VALUES (1, 1, 1, 1)"); |
| execute("INSERT INTO %s (a, b, c, d) VALUES (1, 1, 2, 1)"); |
| execute("INSERT INTO %s (a, b, c, d) VALUES (2, 2, 1, 1)"); |
| execute("INSERT INTO %s (a, b, c, d) VALUES (2, 2, 2, 1)"); |
| |
| execute("UPDATE %s SET s = 1 WHERE a = 1"); |
| execute("UPDATE %s SET s = 2 WHERE a = 2"); |
| execute("UPDATE %s SET s = 3 WHERE a = 3"); |
| |
| beforeAndAfterFlush(() -> { |
| assertRows(execute("SELECT a, b, c, d, s FROM %s WHERE a IN (1, 2, 3) ORDER BY b DESC"), |
| row(2, 2, 2, 1, 2), |
| row(2, 2, 1, 1, 2), |
| row(1, 1, 2, 1, 1), |
| row(1, 1, 1, 1, 1), |
| row(3, null, null, null, 3)); |
| |
| assertRows(execute("SELECT a, b, c, d, s FROM %s WHERE a IN (1, 2, 3) ORDER BY b ASC"), |
| row(3, null, null, null, 3), |
| row(1, 1, 1, 1, 1), |
| row(1, 1, 2, 1, 1), |
| row(2, 2, 1, 1, 2), |
| row(2, 2, 2, 1, 2)); |
| |
| assertRows(execute("SELECT a, b, c, d, s FROM %s WHERE a IN (1, 2, 3) ORDER BY b DESC , c DESC"), |
| row(2, 2, 2, 1, 2), |
| row(2, 2, 1, 1, 2), |
| row(1, 1, 2, 1, 1), |
| row(1, 1, 1, 1, 1), |
| row(3, null, null, null, 3)); |
| |
| assertRows(execute("SELECT a, b, c, d, s FROM %s WHERE a IN (1, 2, 3) ORDER BY b ASC, c ASC"), |
| row(3, null, null, null, 3), |
| row(1, 1, 1, 1, 1), |
| row(1, 1, 2, 1, 1), |
| row(2, 2, 1, 1, 2), |
| row(2, 2, 2, 1, 2)); |
| }); |
| } |
| |
| /** |
| * Test reversed comparators |
| * migrated from cql_tests.py:TestCQL.reversed_comparator_test() |
| */ |
| @Test |
| public void testReversedComparator() throws Throwable |
| { |
| createTable("CREATE TABLE %s (k int, c int, v int, PRIMARY KEY (k, c)) WITH CLUSTERING ORDER BY (c DESC);"); |
| |
| for(int i =0; i < 10; i++) |
| execute("INSERT INTO %s (k, c, v) VALUES (0, ?, ?)", i, i); |
| |
| beforeAndAfterFlush(() -> { |
| assertRows(execute("SELECT c, v FROM %s WHERE k = 0 ORDER BY c ASC"), |
| row(0, 0), row(1, 1), row(2, 2), row(3, 3), row(4, 4), |
| row(5, 5), row(6, 6), row(7, 7), row(8, 8), row(9, 9)); |
| |
| assertRows(execute("SELECT c, v FROM %s WHERE k = 0 ORDER BY c DESC"), |
| row(9, 9), row(8, 8), row(7, 7), row(6, 6), row(5, 5), |
| row(4, 4), row(3, 3), row(2, 2), row(1, 1), row(0, 0)); |
| }); |
| |
| createTable("CREATE TABLE %s (k int, c1 int, c2 int, v text, PRIMARY KEY (k, c1, c2)) WITH CLUSTERING ORDER BY (c1 ASC, c2 DESC)"); |
| |
| for(int i = 0; i < 10; i++) |
| for(int j = 0; j < 10; j++) |
| execute("INSERT INTO %s (k, c1, c2, v) VALUES (0, ?, ?, ?)", i, j, String.format("%d%d", i, j)); |
| |
| beforeAndAfterFlush(() -> { |
| assertInvalid("SELECT c1, c2, v FROM %s WHERE k = 0 ORDER BY c1 ASC, c2 ASC"); |
| assertInvalid("SELECT c1, c2, v FROM %s WHERE k = 0 ORDER BY c1 DESC, c2 DESC"); |
| |
| Object[][] expectedRows = new Object[100][]; |
| for(int i = 0; i < 10; i++) |
| for(int j = 9; j >= 0; j--) |
| expectedRows[i * 10 + (9 - j)] = row(i, j, String.format("%d%d", i, j)); |
| |
| assertRows(execute("SELECT c1, c2, v FROM %s WHERE k = 0 ORDER BY c1 ASC"), |
| expectedRows); |
| |
| assertRows(execute("SELECT c1, c2, v FROM %s WHERE k = 0 ORDER BY c1 ASC, c2 DESC"), |
| expectedRows); |
| |
| for(int i = 9; i >= 0; i--) |
| for(int j = 0; j < 10; j++) |
| expectedRows[(9 - i) * 10 + j] = row(i, j, String.format("%d%d", i, j)); |
| |
| assertRows(execute("SELECT c1, c2, v FROM %s WHERE k = 0 ORDER BY c1 DESC, c2 ASC"), |
| expectedRows); |
| |
| assertInvalid("SELECT c1, c2, v FROM %s WHERE k = 0 ORDER BY c2 DESC, c1 ASC"); |
| }); |
| } |
| |
| /** |
| * Migrated from cql_tests.py:TestCQL.multiordering_test() |
| */ |
| @Test |
| public void testMultiordering() throws Throwable |
| { |
| createTable("CREATE TABLE %s (k text, c1 int, c2 int, PRIMARY KEY (k, c1, c2) ) WITH CLUSTERING ORDER BY (c1 ASC, c2 DESC)"); |
| |
| for (int i = 0; i < 2; i++) |
| for (int j = 0; j < 2; j++) |
| execute("INSERT INTO %s (k, c1, c2) VALUES ('foo', ?, ?)", i, j); |
| |
| beforeAndAfterFlush(() -> { |
| assertRows(execute("SELECT c1, c2 FROM %s WHERE k = 'foo'"), |
| row(0, 1), row(0, 0), row(1, 1), row(1, 0)); |
| |
| assertRows(execute("SELECT c1, c2 FROM %s WHERE k = 'foo' ORDER BY c1 ASC, c2 DESC"), |
| row(0, 1), row(0, 0), row(1, 1), row(1, 0)); |
| |
| assertRows(execute("SELECT c1, c2 FROM %s WHERE k = 'foo' ORDER BY c1 DESC, c2 ASC"), |
| row(1, 0), row(1, 1), row(0, 0), row(0, 1)); |
| |
| assertInvalid("SELECT c1, c2 FROM %s WHERE k = 'foo' ORDER BY c2 DESC"); |
| assertInvalid("SELECT c1, c2 FROM %s WHERE k = 'foo' ORDER BY c2 ASC"); |
| assertInvalid("SELECT c1, c2 FROM %s WHERE k = 'foo' ORDER BY c1 ASC, c2 ASC"); |
| }); |
| } |
| |
| /** |
| * Migrated from cql_tests.py:TestCQL.in_with_desc_order_test() |
| */ |
| @Test |
| public void testSelectInStatementWithDesc() throws Throwable |
| { |
| createTable("CREATE TABLE %s (k int, c1 int, c2 int, PRIMARY KEY (k, c1, c2))"); |
| execute("INSERT INTO %s(k, c1, c2) VALUES (0, 0, 0)"); |
| execute("INSERT INTO %s(k, c1, c2) VALUES (0, 0, 1)"); |
| execute("INSERT INTO %s(k, c1, c2) VALUES (0, 0, 2)"); |
| |
| beforeAndAfterFlush(() -> { |
| assertRows(execute("SELECT * FROM %s WHERE k=0 AND c1 = 0 AND c2 IN (2, 0) ORDER BY c1 DESC"), |
| row(0, 0, 2), |
| row(0, 0, 0)); |
| }); |
| } |
| |
| /** |
| * Test that columns don't need to be selected for ORDER BY when there is a IN (#4911), |
| * migrated from cql_tests.py:TestCQL.in_order_by_without_selecting_test() |
| */ |
| @Test |
| public void testInOrderByWithoutSelecting() throws Throwable |
| { |
| createTable("CREATE TABLE %s (k int, c1 int, c2 int, v int, PRIMARY KEY (k, c1, c2))"); |
| |
| execute("INSERT INTO %s (k, c1, c2, v) VALUES (0, 0, 0, 0)"); |
| execute("INSERT INTO %s (k, c1, c2, v) VALUES (0, 0, 1, 1)"); |
| execute("INSERT INTO %s (k, c1, c2, v) VALUES (0, 0, 2, 2)"); |
| execute("INSERT INTO %s (k, c1, c2, v) VALUES (1, 1, 0, 3)"); |
| execute("INSERT INTO %s (k, c1, c2, v) VALUES (1, 1, 1, 4)"); |
| execute("INSERT INTO %s (k, c1, c2, v) VALUES (1, 1, 2, 5)"); |
| |
| beforeAndAfterFlush(() -> { |
| assertRows(execute("SELECT * FROM %s WHERE k=0 AND c1 = 0 AND c2 IN (2, 0)"), |
| row(0, 0, 0, 0), |
| row(0, 0, 2, 2)); |
| assertRows(execute("SELECT * FROM %s WHERE k=0 AND c1 = 0 AND c2 IN (2, 0) ORDER BY c1 ASC, c2 ASC"), |
| row(0, 0, 0, 0), |
| row(0, 0, 2, 2)); |
| |
| // check that we don 't need to select the column on which we order |
| assertRows(execute("SELECT v FROM %s WHERE k=0 AND c1 = 0 AND c2 IN (2, 0)"), |
| row(0), |
| row(2)); |
| assertRows(execute("SELECT v FROM %s WHERE k=0 AND c1 = 0 AND c2 IN (2, 0) ORDER BY c1 ASC"), |
| row(0), |
| row(2)); |
| assertRows(execute("SELECT v FROM %s WHERE k=0 AND c1 = 0 AND c2 IN (2, 0) ORDER BY c1 DESC"), |
| row(2), |
| row(0)); |
| |
| assertRows(execute("SELECT v FROM %s WHERE k IN (1, 0)"), |
| row(0), |
| row(1), |
| row(2), |
| row(3), |
| row(4), |
| row(5)); |
| |
| assertRows(execute("SELECT v FROM %s WHERE k IN (1, 0) ORDER BY c1 ASC"), |
| row(0), |
| row(1), |
| row(2), |
| row(3), |
| row(4), |
| row(5)); |
| |
| // we should also be able to use functions in the select clause (additional test for CASSANDRA - 8286) |
| Object[][] results = getRows(execute("SELECT writetime(v) FROM %s WHERE k IN (1, 0) ORDER BY c1 ASC")); |
| |
| // since we don 't know the write times, just assert that the order matches the order we expect |
| assertTrue(isFirstIntSorted(results)); |
| }); |
| } |
| |
| @Test |
| public void testInOrderByWithTwoPartitionKeyColumns() throws Throwable |
| { |
| for (String option : asList("", "WITH CLUSTERING ORDER BY (col_3 DESC)")) |
| { |
| createTable("CREATE TABLE %s (col_1 int, col_2 int, col_3 int, PRIMARY KEY ((col_1, col_2), col_3)) " + option); |
| execute("INSERT INTO %s (col_1, col_2, col_3) VALUES(?, ?, ?)", 1, 1, 1); |
| execute("INSERT INTO %s (col_1, col_2, col_3) VALUES(?, ?, ?)", 1, 1, 2); |
| execute("INSERT INTO %s (col_1, col_2, col_3) VALUES(?, ?, ?)", 1, 1, 13); |
| execute("INSERT INTO %s (col_1, col_2, col_3) VALUES(?, ?, ?)", 1, 2, 10); |
| execute("INSERT INTO %s (col_1, col_2, col_3) VALUES(?, ?, ?)", 1, 2, 11); |
| |
| beforeAndAfterFlush(() -> { |
| assertRows(execute("select * from %s where col_1=? and col_2 IN (?, ?) order by col_3;", 1, 1, 2), |
| row(1, 1, 1), |
| row(1, 1, 2), |
| row(1, 2, 10), |
| row(1, 2, 11), |
| row(1, 1, 13)); |
| |
| assertRows(execute("select * from %s where col_1=? and col_2 IN (?, ?) order by col_3 desc;", 1, 1, 2), |
| row(1, 1, 13), |
| row(1, 2, 11), |
| row(1, 2, 10), |
| row(1, 1, 2), |
| row(1, 1, 1)); |
| |
| assertRows(execute("select * from %s where col_2 IN (?, ?) and col_1=? order by col_3;", 1, 2, 1), |
| row(1, 1, 1), |
| row(1, 1, 2), |
| row(1, 2, 10), |
| row(1, 2, 11), |
| row(1, 1, 13)); |
| |
| assertRows(execute("select * from %s where col_2 IN (?, ?) and col_1=? order by col_3 desc;", 1, 2, 1), |
| row(1, 1, 13), |
| row(1, 2, 11), |
| row(1, 2, 10), |
| row(1, 1, 2), |
| row(1, 1, 1)); |
| }); |
| } |
| } |
| |
| @Test |
| public void testSelectWithReversedTypeInReverseOrderWithStaticColumnsWithoutStaticRow() throws Throwable |
| { |
| createTable("CREATE TABLE %s (a int, b int, c int, d int static, PRIMARY KEY (a, b)) WITH CLUSTERING ORDER BY (b DESC);"); |
| |
| execute("INSERT INTO %s (a, b, c) VALUES (1, 1, 1);"); |
| execute("INSERT INTO %s (a, b, c) VALUES (1, 2, 2);"); |
| execute("INSERT INTO %s (a, b, c) VALUES (1, 3, 3);"); |
| |
| // read in comparator order |
| assertRows(execute("SELECT b, c FROM %s WHERE a = 1 ORDER BY b DESC;"), |
| row(3, 3), |
| row(2, 2), |
| row(1, 1)); |
| |
| // read in reverse comparator order |
| assertRows(execute("SELECT b, c FROM %s WHERE a = 1 ORDER BY b ASC;"), |
| row(1, 1), |
| row(2, 2), |
| row(3, 3)); |
| |
| /* |
| * Flush the sstable. We *should* see the same results when reading in both directions, but prior to CASSANDRA-14910 |
| * fix this would now have returned an empty result set when reading in reverse comparator order. |
| */ |
| flush(); |
| |
| // read in comparator order |
| assertRows(execute("SELECT b, c FROM %s WHERE a = 1 ORDER BY b DESC;"), |
| row(3, 3), |
| row(2, 2), |
| row(1, 1)); |
| |
| // read in reverse comparator order |
| assertRows(execute("SELECT b, c FROM %s WHERE a = 1 ORDER BY b ASC;"), |
| row(1, 1), |
| row(2, 2), |
| row(3, 3)); |
| } |
| |
| @Test |
| public void testSelectWithReversedTypeInReverseOrderWithStaticColumnsWithStaticRow() throws Throwable |
| { |
| createTable("CREATE TABLE %s (a int, b int, c int, d int static, PRIMARY KEY (a, b)) WITH CLUSTERING ORDER BY (b DESC)"); |
| |
| execute("INSERT INTO %s (a, d) VALUES (1, 0);"); |
| |
| execute("INSERT INTO %s (a, b, c) VALUES (1, 1, 1);"); |
| execute("INSERT INTO %s (a, b, c) VALUES (1, 2, 2);"); |
| execute("INSERT INTO %s (a, b, c) VALUES (1, 3, 3);"); |
| |
| // read in comparator order |
| assertRows(execute("SELECT b, c, d FROM %s WHERE a = 1 ORDER BY b DESC;"), |
| row(3, 3, 0), |
| row(2, 2, 0), |
| row(1, 1, 0)); |
| |
| // read in reverse comparator order |
| assertRows(execute("SELECT b, c, d FROM %s WHERE a = 1 ORDER BY b ASC;"), |
| row(1, 1, 0), |
| row(2, 2, 0), |
| row(3, 3, 0)); |
| |
| flush(); |
| |
| // read in comparator order |
| assertRows(execute("SELECT b, c, d FROM %s WHERE a = 1 ORDER BY b DESC;"), |
| row(3, 3, 0), |
| row(2, 2, 0), |
| row(1, 1, 0)); |
| |
| // read in reverse comparator order |
| assertRows(execute("SELECT b, c, d FROM %s WHERE a = 1 ORDER BY b ASC;"), |
| row(1, 1, 0), |
| row(2, 2, 0), |
| row(3, 3, 0)); |
| } |
| |
| private boolean isFirstIntSorted(Object[][] rows) |
| { |
| for (int i = 1; i < rows.length; i++) |
| { |
| Long prev = (Long)rows[i-1][0]; |
| Long curr = (Long)rows[i][0]; |
| |
| if (prev > curr) |
| return false; |
| } |
| |
| return true; |
| } |
| } |
