http://git-wip-us.apache.org/repos/asf/cassandra/blob/bb56193a/test/unit/org/apache/cassandra/cql3/validation/operations/SelectOrderByTest.java ---------------------------------------------------------------------- diff --cc test/unit/org/apache/cassandra/cql3/validation/operations/SelectOrderByTest.java index 6e06419,32d800a..bb35458 --- a/test/unit/org/apache/cassandra/cql3/validation/operations/SelectOrderByTest.java +++ b/test/unit/org/apache/cassandra/cql3/validation/operations/SelectOrderByTest.java @@@ -36,31 -36,37 +36,33 @@@ public class SelectOrderByTest extends execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, 1, 1); execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, 2, 2); - 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)); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); -- - assertRows(execute("SELECT c FROM %s WHERE a=? ORDER BY b DESC", 0), - row(2), row(1), row(0)); ++ 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)); - } ++ }); } } @@@ -74,22 -80,28 +76,24 @@@ execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, 1, 1); execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", 0, 2, 2); - // 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)); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); - ++ 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)); + 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)); + // 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)); + 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); + assertInvalid("SELECT * FROM %s WHERE a=? ORDER BY c ASC", 0); + assertInvalid("SELECT * FROM %s WHERE a=? ORDER BY c DESC", 0); - } ++ }); } } @@@ -105,15 -117,20 +109,17 @@@ execute("INSERT INTO %s (a, b, c) VALUES (?, ?, {a: ?})", 0, 1, 1); execute("INSERT INTO %s (a, b, c) VALUES (?, ?, {a: ?})", 0, 2, 2); - // 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)); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); ++ 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)); - ++ 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)); - ++ 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"); } } @@@ -129,62 -146,68 +135,64 @@@ 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); - 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)); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); -- - // 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)); ++ 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 @@@ -198,41 -221,46 +206,42 @@@ 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); - 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)); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); -- - // 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)); ++ 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)); - } ++ }); } /** @@@ -247,8 -275,14 +256,10 @@@ for (int i = 0; i < 10; i++) execute("INSERT INTO %s (k, c, v) VALUES (0, ?, ?)", i, i); - 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)); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); - ++ 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"); @@@ -256,15 -290,21 +267,17 @@@ for (int j = 0; j < 2; j++) execute("INSERT INTO %s (k, c1, c2, v) VALUES (0, ?, ?, ?)", i, j, i * 2 + j); - 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"); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); - ++ 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 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)); + 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)); - } ++ }); } /** @@@ -281,23 -321,29 +294,25 @@@ execute("INSERT INTO %s (row, number, string) VALUES ('row', 3, 'three')"); execute("INSERT INTO %s (row, number, string) VALUES ('row', 4, 'four')"); - assertRows(execute("SELECT number FROM %s WHERE row='row' AND number < 3 ORDER BY number ASC"), - row(1), row(2)); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); - ++ 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 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(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), 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(4)); - assertRows(execute("SELECT number FROM %s WHERE row='row' AND number <= 3 ORDER BY number DESC"), - row(3), row(2), row(1)); + assertRows(execute("SELECT number FROM %s WHERE row='row' AND number <= 3 ORDER BY number DESC"), + row(3), row(2), row(1)); - } ++ }); } /** @@@ -330,20 -376,20 +345,22 @@@ execute("INSERT INTO %s (my_id, col1, value) VALUES ( 'key3', 2, 'b')"); execute("INSERT INTO %s (my_id, col1, value) VALUES ( 'key4', 4, 'd')"); - assertRows(execute("SELECT col1 FROM %s WHERE my_id in('key1', 'key2', 'key3') ORDER BY col1"), - row(1), row(2), row(3)); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); - ++ 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 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 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 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)); + 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"); @@@ -351,34 -397,31 +368,36 @@@ 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"); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); - - 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 as c FROM %s where pk1 = ? AND pk2 IN (?, ?) ORDER BY c; ", 1, 1, 2), - row("B"), - row("A"), - row("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"); @@@ -386,51 -429,31 +405,53 @@@ 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"); - 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")); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); -- - 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); ++ 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 @@@ -447,33 -470,39 +468,35 @@@ execute("UPDATE %s SET s = 2 WHERE a = 2"); execute("UPDATE %s SET s = 3 WHERE a = 3"); - 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)); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); -- - 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)); ++ 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)); - } ++ }); } /** @@@ -488,13 -517,19 +511,15 @@@ for(int i =0; i < 10; i++) execute("INSERT INTO %s (k, c, v) VALUES (0, ?, ?)", i, i); - 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)); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); - ++ 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)); + 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)"); @@@ -502,28 -537,34 +527,30 @@@ 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)); - 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"); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); - ++ 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)); + 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"), + expectedRows); - assertRows(execute("SELECT c1, c2, v FROM %s WHERE k = 0 ORDER BY c1 ASC, c2 DESC"), - 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)); + 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); + 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"); + assertInvalid("SELECT c1, c2, v FROM %s WHERE k = 0 ORDER BY c2 DESC, c1 ASC"); - } ++ }); } /** @@@ -534,22 -575,28 +561,24 @@@ { 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); - - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); ++ beforeAndAfterFlush(() -> { ++ 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); - 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'"), + 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 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)); + 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"); + 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"); - } ++ }); } /** @@@ -563,9 -610,15 +592,11 @@@ execute("INSERT INTO %s(k, c1, c2) VALUES (0, 0, 1)"); execute("INSERT INTO %s(k, c1, c2) VALUES (0, 0, 2)"); - 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)); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); - ++ 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)); - } ++ }); } /** @@@ -584,45 -637,51 +615,47 @@@ 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)"); - 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")); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); -- - // since we don 't know the write times, just assert that the order matches the order we expect - assertTrue(isFirstIntSorted(results)); ++ 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 @@@ -637,33 -696,39 +670,35 @@@ 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); - 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)); - for (boolean flush : new boolean[]{true, false}) - { - if (flush) - flush(); -- - 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)); ++ 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)); - } ++ }); } }
http://git-wip-us.apache.org/repos/asf/cassandra/blob/bb56193a/test/unit/org/apache/cassandra/cql3/validation/operations/SelectTest.java ---------------------------------------------------------------------- diff --cc test/unit/org/apache/cassandra/cql3/validation/operations/SelectTest.java index 1b6fe9b,ac1ba4c..a5f153e --- a/test/unit/org/apache/cassandra/cql3/validation/operations/SelectTest.java +++ b/test/unit/org/apache/cassandra/cql3/validation/operations/SelectTest.java @@@ -22,16 -22,19 +22,18 @@@ import java.util.UUID import org.junit.Test; --import junit.framework.Assert; ++import org.apache.cassandra.cql3.CQLTester; import org.apache.cassandra.cql3.UntypedResultSet; import org.apache.cassandra.cql3.restrictions.StatementRestrictions; import org.apache.cassandra.exceptions.InvalidRequestException; - import org.apache.cassandra.utils.ByteBufferUtil; - import org.apache.cassandra.cql3.CQLTester; -import org.apache.cassandra.cql3.CQLTester; - ++import static org.apache.cassandra.utils.ByteBufferUtil.EMPTY_BYTE_BUFFER; ++import static org.apache.cassandra.utils.ByteBufferUtil.bytes; import static org.junit.Assert.assertEquals; import static org.junit.Assert.assertTrue; -import static org.apache.cassandra.utils.ByteBufferUtil.EMPTY_BYTE_BUFFER; -import static org.apache.cassandra.utils.ByteBufferUtil.bytes; ++import junit.framework.Assert; + /** * Test column ranges and ordering with static column in table */ @@@ -2576,4 -2324,385 +2578,349 @@@ public class SelectTest extends CQLTest assertInvalidMessage("PRIMARY KEY column \"c2\" cannot be restricted as preceding column \"c1\" is not restricted", "SELECT * FROM %s WHERE pk = 1 AND c2 = 1 ALLOW FILTERING;"); } + + @Test - public void testIndexQueryWithCompositePartitionKey() throws Throwable - { - createTable("CREATE TABLE %s (p1 int, p2 int, v int, PRIMARY KEY ((p1, p2)))"); - assertInvalidMessage("Partition key parts: p2 must be restricted as other parts are", - "SELECT * FROM %s WHERE p1 = 1 AND v = 3 ALLOW FILTERING"); - - createIndex("CREATE INDEX ON %s(v)"); - - execute("INSERT INTO %s(p1, p2, v) values (?, ?, ?)", 1, 1, 3); - execute("INSERT INTO %s(p1, p2, v) values (?, ?, ?)", 1, 2, 3); - execute("INSERT INTO %s(p1, p2, v) values (?, ?, ?)", 2, 1, 3); - - assertRows(execute("SELECT * FROM %s WHERE p1 = 1 AND v = 3 ALLOW FILTERING"), - row(1, 2, 3), - row(1, 1, 3)); - } - - @Test + public void testEmptyRestrictionValue() throws Throwable + { + for (String options : new String[] { "", " WITH COMPACT STORAGE" }) + { + createTable("CREATE TABLE %s (pk blob, c blob, v blob, PRIMARY KEY ((pk), c))" + options); + execute("INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)", + bytes("foo123"), bytes("1"), bytes("1")); + execute("INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)", + bytes("foo123"), bytes("2"), bytes("2")); + - for (boolean flush : new boolean[]{false, true}) - { - if (flush) - flush(); ++ beforeAndAfterFlush(() -> { + + assertInvalidMessage("Key may not be empty", "SELECT * FROM %s WHERE pk = textAsBlob('');"); + assertInvalidMessage("Key may not be empty", "SELECT * FROM %s WHERE pk IN (textAsBlob(''), textAsBlob('1'));"); + + assertInvalidMessage("Key may not be empty", + "INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)", + EMPTY_BYTE_BUFFER, bytes("2"), bytes("2")); + + // Test clustering columns restrictions + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c = textAsBlob('');")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) = (textAsBlob(''));")); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c IN (textAsBlob(''), textAsBlob('1'));"), + row(bytes("foo123"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) IN ((textAsBlob('')), (textAsBlob('1')));"), + row(bytes("foo123"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c > textAsBlob('');"), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("2"), bytes("2"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) > (textAsBlob(''));"), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("2"), bytes("2"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c >= textAsBlob('');"), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("2"), bytes("2"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) >= (textAsBlob(''));"), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("2"), bytes("2"))); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c <= textAsBlob('');")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) <= (textAsBlob(''));")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c < textAsBlob('');")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) < (textAsBlob(''));")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c > textAsBlob('') AND c < textAsBlob('');")); - } ++ }); + + if (options.contains("COMPACT")) + { - assertInvalidMessage("Missing PRIMARY KEY part c", ++ assertInvalidMessage("Invalid empty or null value for column c", + "INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)", + bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4")); + } + else + { + execute("INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)", + bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4")); + - for (boolean flush : new boolean[]{false, true}) - { - if (flush) - flush(); - ++ beforeAndAfterFlush(() -> { + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c = textAsBlob('');"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) = (textAsBlob(''));"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c IN (textAsBlob(''), textAsBlob('1'));"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4")), + row(bytes("foo123"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) IN ((textAsBlob('')), (textAsBlob('1')));"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4")), + row(bytes("foo123"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c > textAsBlob('');"), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("2"), bytes("2"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) > (textAsBlob(''));"), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("2"), bytes("2"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c >= textAsBlob('');"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4")), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("2"), bytes("2"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) >= (textAsBlob(''));"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4")), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("2"), bytes("2"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c <= textAsBlob('');"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) <= (textAsBlob(''));"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4"))); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c < textAsBlob('');")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) < (textAsBlob(''));")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c >= textAsBlob('') AND c < textAsBlob('');")); - } ++ }); + } + + // Test restrictions on non-primary key value - assertInvalidMessage("Predicates on non-primary-key columns (v) are not yet supported for non secondary index queries", - "SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND v = textAsBlob('') ALLOW FILTERING;"); ++ assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND v = textAsBlob('') ALLOW FILTERING;")); ++ ++ execute("INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)", ++ bytes("foo123"), bytes("3"), EMPTY_BYTE_BUFFER); ++ ++ beforeAndAfterFlush(() -> { ++ assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND v = textAsBlob('') ALLOW FILTERING;"), ++ row(bytes("foo123"), bytes("3"), EMPTY_BYTE_BUFFER)); ++ }); + } + } + + @Test + public void testEmptyRestrictionValueWithMultipleClusteringColumns() throws Throwable + { + for (String options : new String[] { "", " WITH COMPACT STORAGE" }) + { + createTable("CREATE TABLE %s (pk blob, c1 blob, c2 blob, v blob, PRIMARY KEY (pk, c1, c2))" + options); + execute("INSERT INTO %s (pk, c1, c2, v) VALUES (?, ?, ?, ?)", bytes("foo123"), bytes("1"), bytes("1"), bytes("1")); + execute("INSERT INTO %s (pk, c1, c2, v) VALUES (?, ?, ?, ?)", bytes("foo123"), bytes("1"), bytes("2"), bytes("2")); + - for (boolean flush : new boolean[]{false, true}) - { - if (flush) - flush(); ++ beforeAndAfterFlush(() -> { + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('');")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('1') AND c2 = textAsBlob('');")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) = (textAsBlob('1'), textAsBlob(''));")); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 IN (textAsBlob(''), textAsBlob('1')) AND c2 = textAsBlob('1');"), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('1') AND c2 IN (textAsBlob(''), textAsBlob('1'));"), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) IN ((textAsBlob(''), textAsBlob('1')), (textAsBlob('1'), textAsBlob('1')));"), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 > textAsBlob('');"), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('1') AND c2 > textAsBlob('');"), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) > (textAsBlob(''), textAsBlob('1'));"), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('1') AND c2 >= textAsBlob('');"), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2"))); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('1') AND c2 <= textAsBlob('');")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) <= (textAsBlob('1'), textAsBlob(''));")); - } ++ }); + + execute("INSERT INTO %s (pk, c1, c2, v) VALUES (?, ?, ?, ?)", + bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4")); + - for (boolean flush : new boolean[]{false, true}) - { - if (flush) - flush(); - ++ beforeAndAfterFlush(() -> { + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('');"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('') AND c2 = textAsBlob('1');"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) = (textAsBlob(''), textAsBlob('1'));"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 IN (textAsBlob(''), textAsBlob('1')) AND c2 = textAsBlob('1');"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4")), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) IN ((textAsBlob(''), textAsBlob('1')), (textAsBlob('1'), textAsBlob('1')));"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4")), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) > (textAsBlob(''), textAsBlob('1'));"), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) >= (textAsBlob(''), textAsBlob('1'));"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4")), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) <= (textAsBlob(''), textAsBlob('1'));"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4"))); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) < (textAsBlob(''), textAsBlob('1'));")); - } ++ }); + } + } + + @Test + public void testEmptyRestrictionValueWithOrderBy() throws Throwable + { + for (String options : new String[] { "", + " WITH COMPACT STORAGE", + " WITH CLUSTERING ORDER BY (c DESC)", + " WITH COMPACT STORAGE AND CLUSTERING ORDER BY (c DESC)"}) + { + String orderingClause = options.contains("ORDER") ? "" : "ORDER BY c DESC" ; + + createTable("CREATE TABLE %s (pk blob, c blob, v blob, PRIMARY KEY ((pk), c))" + options); + execute("INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)", + bytes("foo123"), + bytes("1"), + bytes("1")); + execute("INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)", + bytes("foo123"), + bytes("2"), + bytes("2")); + - for (boolean flush : new boolean[]{false, true}) - { - if (flush) - flush(); ++ beforeAndAfterFlush(() -> { + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c > textAsBlob('')" + orderingClause), + row(bytes("foo123"), bytes("2"), bytes("2")), + row(bytes("foo123"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c >= textAsBlob('')" + orderingClause), + row(bytes("foo123"), bytes("2"), bytes("2")), + row(bytes("foo123"), bytes("1"), bytes("1"))); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c < textAsBlob('')" + orderingClause)); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c <= textAsBlob('')" + orderingClause)); - } ++ ++ }); + + if (options.contains("COMPACT")) + { - assertInvalidMessage("Missing PRIMARY KEY part c", ++ assertInvalidMessage("Invalid empty or null value for column c", + "INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)", + bytes("foo123"), + EMPTY_BYTE_BUFFER, + bytes("4")); + } + else + { + execute("INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)", + bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4")); + - for (boolean flush : new boolean[]{false, true}) - { - if (flush) - flush(); ++ beforeAndAfterFlush(() -> { + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c IN (textAsBlob(''), textAsBlob('1'))" + orderingClause), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c > textAsBlob('')" + orderingClause), + row(bytes("foo123"), bytes("2"), bytes("2")), + row(bytes("foo123"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c >= textAsBlob('')" + orderingClause), + row(bytes("foo123"), bytes("2"), bytes("2")), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4"))); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c < textAsBlob('')" + orderingClause)); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c <= textAsBlob('')" + orderingClause), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("4"))); - } ++ }); + } + } + } + + @Test + public void testEmptyRestrictionValueWithMultipleClusteringColumnsAndOrderBy() throws Throwable + { + for (String options : new String[] { "", + " WITH COMPACT STORAGE", + " WITH CLUSTERING ORDER BY (c1 DESC, c2 DESC)", + " WITH COMPACT STORAGE AND CLUSTERING ORDER BY (c1 DESC, c2 DESC)"}) + { + String orderingClause = options.contains("ORDER") ? "" : "ORDER BY c1 DESC, c2 DESC" ; + + createTable("CREATE TABLE %s (pk blob, c1 blob, c2 blob, v blob, PRIMARY KEY (pk, c1, c2))" + options); + execute("INSERT INTO %s (pk, c1, c2, v) VALUES (?, ?, ?, ?)", bytes("foo123"), bytes("1"), bytes("1"), bytes("1")); + execute("INSERT INTO %s (pk, c1, c2, v) VALUES (?, ?, ?, ?)", bytes("foo123"), bytes("1"), bytes("2"), bytes("2")); + - for (boolean flush : new boolean[]{false, true}) - { - if (flush) - flush(); ++ beforeAndAfterFlush(() -> { + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 > textAsBlob('')" + orderingClause), + row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('1') AND c2 > textAsBlob('')" + orderingClause), + row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) > (textAsBlob(''), textAsBlob('1'))" + orderingClause), + row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 = textAsBlob('1') AND c2 >= textAsBlob('')" + orderingClause), + row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1"))); - } ++ }); + + execute("INSERT INTO %s (pk, c1, c2, v) VALUES (?, ?, ?, ?)", + bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4")); + - for (boolean flush : new boolean[]{false, true}) - { - if (flush) - flush(); ++ beforeAndAfterFlush(() -> { + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c1 IN (textAsBlob(''), textAsBlob('1')) AND c2 = textAsBlob('1')" + orderingClause), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) IN ((textAsBlob(''), textAsBlob('1')), (textAsBlob('1'), textAsBlob('1')))" + orderingClause), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) > (textAsBlob(''), textAsBlob('1'))" + orderingClause), + row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c1, c2) >= (textAsBlob(''), textAsBlob('1'))" + orderingClause), + row(bytes("foo123"), bytes("1"), bytes("2"), bytes("2")), + row(bytes("foo123"), bytes("1"), bytes("1"), bytes("1")), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"), bytes("4"))); - } ++ }); + } + } }
