Uploaded image for project: 'Cassandra'
  1. Cassandra
  2. CASSANDRA-19891

SAI fails queries when multiple columns exist and a non-indexed column is a composite with a map

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Open
    • Normal
    • Resolution: Unresolved
    • 5.0.x, 5.x
    • Feature/2i Index
    • None
    • Correctness - Transient Incorrect Response
    • Critical
    • Normal
    • Fuzz Test
    • All
    • None

    Description

      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

      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';
      

      And the query

      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
      

      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

          @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");
          }
      

      Attachments

        Activity

          People

            maedhroz Caleb Rackliffe
            dcapwell David Capwell
            Caleb Rackliffe
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated: