You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cassandra.apache.org by ms...@apache.org on 2016/08/16 22:42:42 UTC
[41/50] [abbrv] cassandra git commit: Merge branch cassandra-2.1 into
cassandra-2.2
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)