http://git-wip-us.apache.org/repos/asf/cassandra/blob/9583b6b3/test/unit/org/apache/cassandra/cql3/validation/entities/SecondaryIndexTest.java ---------------------------------------------------------------------- diff --cc test/unit/org/apache/cassandra/cql3/validation/entities/SecondaryIndexTest.java index 365abe0,4a54a9a..11d2462 --- a/test/unit/org/apache/cassandra/cql3/validation/entities/SecondaryIndexTest.java +++ b/test/unit/org/apache/cassandra/cql3/validation/entities/SecondaryIndexTest.java @@@ -17,36 -17,26 +17,39 @@@ */ package org.apache.cassandra.cql3.validation.entities; +import static org.junit.Assert.assertTrue; +import static org.junit.Assert.fail; + import java.nio.ByteBuffer; import java.util.HashMap; +import java.util.List; import java.util.Locale; import java.util.Map; +import java.util.Set; import java.util.UUID; - -import org.apache.commons.lang.StringUtils; - -import org.junit.Test; +import java.util.concurrent.CountDownLatch; import org.apache.cassandra.cql3.CQLTester; +import org.apache.cassandra.db.ColumnFamily; +import org.apache.cassandra.db.ColumnFamilyStore; +import org.apache.cassandra.db.DecoratedKey; +import org.apache.cassandra.db.IndexExpression; +import org.apache.cassandra.db.composites.CellName; +import org.apache.cassandra.db.index.IndexNotAvailableException; +import org.apache.cassandra.db.index.PerRowSecondaryIndex; +import org.apache.cassandra.db.index.SecondaryIndexSearcher; +import org.apache.cassandra.db.index.composites.CompositesSearcher; import org.apache.cassandra.exceptions.ConfigurationException; +import org.apache.cassandra.exceptions.InvalidRequestException; import org.apache.cassandra.exceptions.SyntaxException; import org.apache.cassandra.utils.FBUtilities; +import org.apache.cassandra.utils.concurrent.OpOrder.Group; +import org.apache.commons.lang3.StringUtils; +import org.junit.Test; -import static org.junit.Assert.assertTrue; -import static org.junit.Assert.fail; + import static org.apache.cassandra.utils.ByteBufferUtil.EMPTY_BYTE_BUFFER; + import static org.apache.cassandra.utils.ByteBufferUtil.bytes; + public class SecondaryIndexTest extends CQLTester { private static final int TOO_BIG = 1024 * 65; @@@ -655,134 -644,141 +658,273 @@@ } @Test + public void testIndexQueriesWithIndexNotReady() throws Throwable + { + createTable("CREATE TABLE %s (pk int, ck int, value int, PRIMARY KEY (pk, ck))"); + + for (int i = 0; i < 10; i++) + for (int j = 0; j < 10; j++) + execute("INSERT INTO %s (pk, ck, value) VALUES (?, ?, ?)", i, j, i + j); + + createIndex("CREATE CUSTOM INDEX testIndex ON %s (value) USING '" + IndexBlockingOnInitialization.class.getName() + + "'"); + try + { + execute("SELECT value FROM %s WHERE value = 2"); + fail(); + } + catch (IndexNotAvailableException e) + { + assertTrue(true); + } + finally + { + execute("DROP index " + KEYSPACE + ".testIndex"); + } + } + ++ @Test + public void testWithEmptyRestrictionValueAndSecondaryIndex() throws Throwable + { + createTable("CREATE TABLE %s (pk blob, c blob, v blob, PRIMARY KEY ((pk), c))"); + createIndex("CREATE INDEX on %s(c)"); + createIndex("CREATE INDEX on %s(v)"); + + 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("1")); + + for (boolean flush : new boolean[]{false, true}) + { + if (flush) + flush(); + + // 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('') AND v = textAsBlob('1') ALLOW FILTERING;"), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("2"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c >= textAsBlob('') AND v = textAsBlob('1') ALLOW FILTERING;"), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("2"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) >= (textAsBlob('')) AND v = textAsBlob('1') ALLOW FILTERING;"), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("2"), bytes("1"))); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c <= textAsBlob('') AND v = textAsBlob('1') ALLOW FILTERING;")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) <= (textAsBlob('')) AND v = textAsBlob('1') ALLOW FILTERING;")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) < (textAsBlob('')) AND v = textAsBlob('1') ALLOW FILTERING;")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c < textAsBlob('') AND v = textAsBlob('1') ALLOW FILTERING;")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c > textAsBlob('') AND c < textAsBlob('') AND v = textAsBlob('1') ALLOW FILTERING;")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) > (textAsBlob('')) AND (c) < (textAsBlob('')) AND v = textAsBlob('1') ALLOW FILTERING;")); + } + + execute("INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)", + bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1")); + + for (boolean flush : new boolean[]{false, true}) + { + if (flush) + flush(); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c = textAsBlob('');"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) = (textAsBlob(''));"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c IN (textAsBlob(''), textAsBlob('1'));"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("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"), EMPTY_BYTE_BUFFER, bytes("1")), + row(bytes("foo123"), bytes("1"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c > textAsBlob('') AND v = textAsBlob('1') ALLOW FILTERING;"), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("2"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c >= textAsBlob('') AND v = textAsBlob('1') ALLOW FILTERING;"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1")), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("2"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) >= (textAsBlob('')) AND v = textAsBlob('1') ALLOW FILTERING;"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1")), + row(bytes("foo123"), bytes("1"), bytes("1")), + row(bytes("foo123"), bytes("2"), bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c <= textAsBlob('') AND v = textAsBlob('1') ALLOW FILTERING;"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"))); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) <= (textAsBlob('')) AND v = textAsBlob('1') ALLOW FILTERING;"), + row(bytes("foo123"), EMPTY_BYTE_BUFFER, bytes("1"))); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c < textAsBlob('') AND v = textAsBlob('1') ALLOW FILTERING;")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) < (textAsBlob('')) AND v = textAsBlob('1') ALLOW FILTERING;")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND c >= textAsBlob('') AND c < textAsBlob('') AND v = textAsBlob('1') ALLOW FILTERING;")); + + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND (c) >= (textAsBlob('')) AND c < textAsBlob('') AND v = textAsBlob('1') ALLOW FILTERING;")); + + // Test restrictions on non-primary key value + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND v = textAsBlob('');")); + } + + execute("INSERT INTO %s (pk, c, v) VALUES (?, ?, ?)", + bytes("foo123"), bytes("3"), EMPTY_BYTE_BUFFER); + + for (boolean flush : new boolean[]{false, true}) + { + if (flush) + flush(); + + assertRows(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND v = textAsBlob('');"), + row(bytes("foo123"), bytes("3"), EMPTY_BYTE_BUFFER)); + } + } + + @Test + public void testEmptyRestrictionValueWithSecondaryIndexAndCompactTables() throws Throwable + { + createTable("CREATE TABLE %s (pk blob, c blob, v blob, PRIMARY KEY ((pk), c)) WITH COMPACT STORAGE"); + assertInvalidMessage("Secondary indexes are not supported on PRIMARY KEY columns in COMPACT STORAGE tables", + "CREATE INDEX on %s(c)"); + + createTable("CREATE TABLE %s (pk blob PRIMARY KEY, v blob) WITH COMPACT STORAGE"); + createIndex("CREATE INDEX on %s(v)"); + + execute("INSERT INTO %s (pk, v) VALUES (?, ?)", bytes("foo123"), bytes("1")); + + // Test restrictions on non-primary key value + assertEmpty(execute("SELECT * FROM %s WHERE pk = textAsBlob('foo123') AND v = textAsBlob('');")); + + execute("INSERT INTO %s (pk, v) VALUES (?, ?)", bytes("foo124"), EMPTY_BYTE_BUFFER); + + assertRows(execute("SELECT * FROM %s WHERE v = textAsBlob('');"), + row(bytes("foo124"), EMPTY_BYTE_BUFFER)); + } ++ + /** + * Custom index used to test the behavior of the system when the index is not ready. + * As Custom indices cannot by <code>PerColumnSecondaryIndex</code> we use a <code>PerRowSecondaryIndex</code> + * to avoid the check but return a <code>CompositesSearcher</code>. + */ + public static class IndexBlockingOnInitialization extends PerRowSecondaryIndex + { + private volatile CountDownLatch latch = new CountDownLatch(1); + + @Override + public void index(ByteBuffer rowKey, ColumnFamily cf) + { + try + { + latch.await(); + } + catch (InterruptedException e) + { + Thread.interrupted(); + } + } + + @Override + public void delete(DecoratedKey key, Group opGroup) + { + } + + @Override + public void init() + { + } + + @Override + public void reload() + { + } + + @Override + public void validateOptions() throws ConfigurationException + { + } + + @Override + public String getIndexName() + { + return "testIndex"; + } + + @Override + protected SecondaryIndexSearcher createSecondaryIndexSearcher(Set<ByteBuffer> columns) + { + return new CompositesSearcher(baseCfs.indexManager, columns) + { + @Override + public boolean canHandleIndexClause(List<IndexExpression> clause) + { + return true; + } + + @Override + public void validate(IndexExpression indexExpression) throws InvalidRequestException + { + } + }; + } + + @Override + public void forceBlockingFlush() + { + } + + @Override + public ColumnFamilyStore getIndexCfs() + { + return baseCfs; + } + + @Override + public void removeIndex(ByteBuffer columnName) + { + latch.countDown(); + } + + @Override + public void invalidate() + { + } + + @Override + public void truncateBlocking(long truncatedAt) + { + } + + @Override + public boolean indexes(CellName name) + { + return false; + } + + @Override + public long estimateResultRows() + { + return 0; + } + } }
http://git-wip-us.apache.org/repos/asf/cassandra/blob/9583b6b3/test/unit/org/apache/cassandra/cql3/validation/operations/SelectOrderByTest.java ---------------------------------------------------------------------- diff --cc test/unit/org/apache/cassandra/cql3/validation/operations/SelectOrderByTest.java index f8ec13c,cf923bc..32d800a --- a/test/unit/org/apache/cassandra/cql3/validation/operations/SelectOrderByTest.java +++ b/test/unit/org/apache/cassandra/cql3/validation/operations/SelectOrderByTest.java @@@ -36,31 -35,31 +36,37 @@@ 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)); -- -- assertRows(execute("SELECT c 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(); ++ ++ 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 -73,22 +80,28 @@@ 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(); -- assertRows(execute("SELECT blobAsInt(intAsBlob(b)) FROM %s WHERE a=? ORDER BY b DESC", 0), -- row(2), row(1), row(0)); ++ // 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)); -- // 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(b)) 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)); ++ // 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)); -- assertInvalid("SELECT * FROM %s WHERE a=? ORDER BY c ASC", 0); -- assertInvalid("SELECT * FROM %s WHERE a=? ORDER BY c DESC", 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); ++ } } } @@@ -105,15 -104,15 +117,20 @@@ 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)); ++ for (boolean flush : new boolean[]{true, false}) ++ { ++ if (flush) ++ flush(); ++ // 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 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)); ++ 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 -128,62 +146,68 @@@ 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)); -- -- // 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)); ++ for (boolean flush : new boolean[]{true, false}) ++ { ++ if (flush) ++ flush(); ++ ++ 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 -197,41 +221,46 @@@ 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)); -- -- // 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)); ++ for (boolean flush : new boolean[]{true, false}) ++ { ++ if (flush) ++ flush(); ++ ++ 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 -246,8 +275,14 @@@ 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(); ++ ++ 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 -255,15 +290,21 @@@ 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(); ++ ++ 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 -280,23 +321,29 @@@ 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(); ++ ++ 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,14 -329,14 +376,20 @@@ 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(); + - 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 FROM %s WHERE my_id in('key1', 'key2', 'key3') ORDER BY col1"), ++ row(1), row(2), row(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)); - 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"); @@@ -345,25 -344,25 +397,31 @@@ 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"); -- 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")); ++ 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")); ++ } 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"); @@@ -371,68 -370,25 +429,80 @@@ 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")); ++ for (boolean flush : new boolean[]{true, false}) ++ { ++ if (flush) ++ flush(); ++ ++ 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")); ++ } + } + + @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"); + - 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)); ++ 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 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)); ++ } } /** @@@ -447,13 -403,13 +517,19 @@@ 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(); ++ ++ 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)"); @@@ -461,28 -417,28 +537,34 @@@ 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(); -- 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)); ++ 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"); -- assertRows(execute("SELECT c1, c2, v FROM %s WHERE k = 0 ORDER BY c1 ASC"), -- expectedRows); ++ 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, c2 DESC"), -- expectedRows); ++ assertRows(execute("SELECT c1, c2, v FROM %s WHERE k = 0 ORDER BY c1 ASC"), ++ 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 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); ++ 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"); ++ } } /** @@@ -497,18 -453,18 +579,24 @@@ 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)); ++ for (boolean flush : new boolean[]{true, false}) ++ { ++ if (flush) ++ flush(); ++ ++ 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"); ++ } } /** @@@ -522,9 -478,9 +610,15 @@@ 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(); ++ ++ 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)); ++ } } /** @@@ -543,87 -499,44 +637,99 @@@ 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)); - row(5), - row(0), - row(1), - row(2)); -- -- 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)); ++ for (boolean flush : new boolean[]{true, false}) ++ { ++ if (flush) ++ flush(); ++ ++ 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); + - 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)); ++ for (boolean flush : new boolean[]{true, false}) ++ { ++ if (flush) ++ flush(); ++ ++ 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)); ++ } + } } private boolean isFirstIntSorted(Object[][] rows)
