David Capwell created CASSANDRA-19891:
-----------------------------------------
Summary: SAI fails queries when multiple columns exist and a
non-indexed column is a composite with a map
Key: CASSANDRA-19891
URL: https://issues.apache.org/jira/browse/CASSANDRA-19891
Project: Cassandra
Issue Type: Bug
Components: Feature/2i Index
Reporter: David Capwell
Assignee: Caleb Rackliffe
Hit an edge case where SAI fails to create a query plan when multiple columns
are used and one of the columns is a CompositeType with a MapType inside
{code}
CREATE TABLE keyspace_test_00."tbl" (
"pk1" frozen<map<'CompositeType(IntegerType,SimpleDateType)',
'DynamicCompositeType(Q=>LongType,I=>ByteType,6=>LexicalUUIDType)'>>,
"pk2" frozen<tuple<frozen<tuple<float>>>>,
"ck1" frozen<list<frozen<map<'LexicalUUIDType', ascii>>>>,
"ck2" tinyint,
"r1"
frozen<list<'DynamicCompositeType(X=>DecimalType,y=>TimestampType,f=>BooleanType)'>>
static,
"r2" 'DynamicCompositeType(P=>ShortType)',
"r3"
'CompositeType(FrozenType(ListType(DoubleType)),FrozenType(MapType(LongType,DurationType)),DoubleType)',
"r4" frozen<list<frozen<list<time>>>>,
"r5"
'CompositeType(CompositeType(ShortType,SimpleDateType,BooleanType),CompositeType(FloatType),MapType(ByteType,TimeType))',
"r6" set<smallint>,
PRIMARY KEY (("pk1", "pk2"), "ck1", "ck2")
) WITH CLUSTERING ORDER BY ("ck1" ASC, "ck2" DESC);
CREATE INDEX index_test_01 ON keyspace_test_00."tbl"("r5") USING
'legacy_local_table';
CREATE INDEX index_test_02 ON keyspace_test_00."tbl"(FULL("ck1")) USING 'SAI';
CREATE INDEX index_test_03 ON keyspace_test_00."tbl"(FULL("pk1")) USING 'SAI';
CREATE INDEX index_test_04 ON keyspace_test_00."tbl"(FULL("r1")) USING
'legacy_local_table';
CREATE INDEX index_test_05 ON keyspace_test_00."tbl"("r3") USING
'legacy_local_table';
CREATE INDEX index_test_06 ON keyspace_test_00."tbl"(FULL("r4")) USING 'SAI';
CREATE INDEX index_test_07 ON keyspace_test_00."tbl"("r2") USING 'SAI';
CREATE INDEX index_test_08 ON keyspace_test_00."tbl"("pk2") USING
‘legacy_local_table';
{code}
And the query
{code}
SELECT *
FROM keyspace_test_00."tbl"
WHERE "r5" =
0x0010000230bd00000457f0bd31000001000000000700049f647252000000260000000200000001f300000008000001c4e14bba4b00000001260000000800003f2b300d385d00
AND "ck1" = ? AND "pk1" = ? AND "r1" = ? AND "r3" =
0x001c00000002000000083380d171eace676900000008e153bb97fdd5c22e00006d000000030000000897c5493857999fc000000013f08cc4fad0f04d0de51cff28d4ae743d2da1c40000000857108e8c372c868400000013f0cc6bca55f0ee240b27ff12c77a7b7dc3c665000000086c07d25fcdd3403500000013f0745922bdf0ac44c9b5ffd80f025ded9a211d000008200547f5da7a43aa00
AND "r4" = ? AND "r2" = 0x8050000255e200 AND "pk2" = ((-1.2651989E-23))
ALLOW FILTERING
{code}
I played around with this more and see you can also simplify to just SAI indexes
The following test fails due to this issue but the single column query case (on
the complex column) passes
{code}
@Test
public void sai()
{
createKeyspace("CREATE KEYSPACE keyspace_test_00 WITH REPLICATION =
{'class': 'SimpleStrategy', 'replication_factor': 1}");
schemaChange("CREATE TABLE keyspace_test_00.\"tbl\" (\n" +
" \"pk1\"
frozen<map<'CompositeType(IntegerType,SimpleDateType)',
'DynamicCompositeType(Q=>LongType,I=>ByteType,6=>LexicalUUIDType)'>>,\n" +
" \"pk2\" frozen<tuple<frozen<tuple<float>>>>,\n" +
" \"ck1\" frozen<list<frozen<map<'LexicalUUIDType',
ascii>>>>,\n" +
" \"ck2\" tinyint,\n" +
" \"r1\"
frozen<list<'DynamicCompositeType(X=>DecimalType,y=>TimestampType,f=>BooleanType)'>>
static,\n" +
" \"r2\" 'DynamicCompositeType(P=>ShortType)',\n" +
" \"r3\"
'CompositeType(FrozenType(ListType(DoubleType)),FrozenType(MapType(LongType,DurationType)),DoubleType)',\n"
+
" \"r4\" frozen<list<frozen<list<time>>>>,\n" +
" \"r5\"
'CompositeType(CompositeType(ShortType,SimpleDateType,BooleanType),CompositeType(FloatType),MapType(ByteType,TimeType))',\n"
+
" \"r6\" set<smallint>,\n" +
" PRIMARY KEY ((\"pk1\", \"pk2\"), \"ck1\", \"ck2\")\n" +
") WITH CLUSTERING ORDER BY (\"ck1\" ASC, \"ck2\" DESC);");
schemaChange("CREATE INDEX ON keyspace_test_00.\"tbl\"(FULL(\"ck1\"))
USING 'SAI'");
schemaChange("CREATE INDEX ON keyspace_test_00.\"tbl\"(FULL(\"pk1\"))
USING 'SAI'");
schemaChange("CREATE INDEX ON keyspace_test_00.\"tbl\"(FULL(\"r4\"))
USING 'SAI'");
schemaChange("CREATE INDEX ON keyspace_test_00.\"tbl\"(\"r2\") USING
'SAI'");
schemaChange("CREATE INDEX ON keyspace_test_00.\"tbl\"(\"r3\") USING
'SAI'");
executeFormattedQuery("SELECT *\n" +
"FROM keyspace_test_00.\"tbl\"\n" +
"WHERE " +
" \"r3\" =
0x001c00000002000000083380d171eace676900000008e153bb97fdd5c22e00006d000000030000000897c5493857999fc000000013f08cc4fad0f04d0de51cff28d4ae743d2da1c40000000857108e8c372c868400000013f0cc6bca55f0ee240b27ff12c77a7b7dc3c665000000086c07d25fcdd3403500000013f0745922bdf0ac44c9b5ffd80f025ded9a211d000008200547f5da7a43aa00
" +
"ALLOW FILTERING");
executeFormattedQuery("SELECT *\n" +
"FROM keyspace_test_00.\"tbl\"\n" +
"WHERE \"r5\" =
0x0010000230bd00000457f0bd31000001000000000700049f647252000000260000000200000001f300000008000001c4e14bba4b00000001260000000800003f2b300d385d00
" +
" AND \"r3\" =
0x001c00000002000000083380d171eace676900000008e153bb97fdd5c22e00006d000000030000000897c5493857999fc000000013f08cc4fad0f04d0de51cff28d4ae743d2da1c40000000857108e8c372c868400000013f0cc6bca55f0ee240b27ff12c77a7b7dc3c665000000086c07d25fcdd3403500000013f0745922bdf0ac44c9b5ffd80f025ded9a211d000008200547f5da7a43aa00
" +
" AND \"r2\" = 0x8050000255e200 " + // SAI
" AND \"pk2\" = ((-1.2651989E-23))\n" +
"ALLOW FILTERING");
}
{code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
