in phoenix-core/src/it/java/org/apache/phoenix/end2end/join/SubqueryUsingSortMergeJoinIT.java [61:263]
public static synchronized Collection<Object> data() {
List<Object> testCases = Lists.newArrayList();
testCases.add(new String[][] {
{}, {
"SORT-MERGE-JOIN (SEMI) TABLES\n" +
" SORT-MERGE-JOIN (INNER) TABLES\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
" SERVER SORTED BY [\"I.supplier_id\"]\n" +
" CLIENT MERGE SORT\n" +
" AND\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + "\n" +
" CLIENT SORTED BY [\"I.item_id\"]\n" +
"AND (SKIP MERGE)\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + " ['000000000000001'] - [*]\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" +
" CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [I.NAME]",
"SORT-MERGE-JOIN \\(LEFT\\) TABLES\n" +
" SORT-MERGE-JOIN \\(LEFT\\) TABLES\n" +
" CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_FULL_NAME + "\n" +
" CLIENT MERGE SORT\n" +
" AND\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\".+.item_id\", .+.NAME\\]\n" +
" CLIENT MERGE SORT\n" +
" PARALLEL ANTI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"]\\\n" +
" CLIENT MERGE SORT\n" +
" CLIENT SORTED BY \\[.*.CO_ITEM_ID, .*.CO_ITEM_NAME\\]\n" +
"AND\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\".+.item_id\", .+.NAME\\]\n" +
" CLIENT MERGE SORT\n" +
" SKIP-SCAN-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" +
" CLIENT MERGE SORT\n" +
" DYNAMIC SERVER FILTER BY \"" + JOIN_ITEM_TABLE_FULL_NAME + ".item_id\" IN \\(\\$\\d+.\\$\\d+\\)\n" +
"CLIENT FILTER BY \\(\\$\\d+.\\$\\d+ IS NOT NULL OR \\$\\d+.\\$\\d+ IS NOT NULL\\)",
"SORT-MERGE-JOIN \\(SEMI\\) TABLES\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_FULL_NAME + "\n" +
"AND \\(SKIP MERGE\\)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"O.customer_id\"\\]\n" +
" CLIENT MERGE SORT\n" +
" PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
" PARALLEL LEFT-JOIN TABLE 1\\(DELAYED EVALUATION\\)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" +
" CLIENT MERGE SORT\n" +
" DYNAMIC SERVER FILTER BY \"I.item_id\" IN \\(\"O.item_id\"\\)\n" +
" AFTER-JOIN SERVER FILTER BY \\(I.NAME = 'T2' OR O.QUANTITY > \\$\\d+.\\$\\d+\\)",
}});
testCases.add(new String[][] {
{
"CREATE INDEX \"idx_customer\" ON " + JOIN_CUSTOMER_TABLE_FULL_NAME + " (name)",
"CREATE INDEX \"idx_item\" ON " + JOIN_ITEM_TABLE_FULL_NAME + " (name) INCLUDE (price, discount1, discount2, \"supplier_id\", description)",
"CREATE INDEX \"idx_supplier\" ON " + JOIN_SUPPLIER_TABLE_FULL_NAME + " (name)"
}, {
"SORT-MERGE-JOIN (SEMI) TABLES\n" +
" SORT-MERGE-JOIN (INNER) TABLES\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
" SERVER SORTED BY [\"I.0:supplier_id\"]\n" +
" CLIENT MERGE SORT\n" +
" AND\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_supplier\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER SORTED BY [\"S.:supplier_id\"]\n" +
" CLIENT MERGE SORT\n" +
" CLIENT SORTED BY [\"I.:item_id\"]\n" +
"AND (SKIP MERGE)\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + " ['000000000000001'] - [*]\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" +
" CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [\"I.0:NAME\"]",
"SORT-MERGE-JOIN \\(LEFT\\) TABLES\n" +
" SORT-MERGE-JOIN \\(LEFT\\) TABLES\n" +
" CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_FULL_NAME + "\n" +
" CLIENT MERGE SORT\n" +
" AND\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY \\[\".+.0:NAME\", \".+.:item_id\"\\]\n" +
" CLIENT SORTED BY \\[\".+.:item_id\", \".+.0:NAME\"\\]\n"+
" PARALLEL ANTI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" +
" CLIENT MERGE SORT\n" +
" CLIENT SORTED BY \\[.*.CO_ITEM_ID, .*.CO_ITEM_NAME\\]\n" +
"AND\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY \\[\".+.0:NAME\", \".+.:item_id\"\\]\n" +
" CLIENT SORTED BY \\[\".+.:item_id\", \".+.0:NAME\"\\]\n"+
" PARALLEL SEMI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" +
" CLIENT MERGE SORT\n" +
"CLIENT FILTER BY \\(\\$\\d+.\\$\\d+ IS NOT NULL OR \\$\\d+.\\$\\d+ IS NOT NULL\\)",
"SORT-MERGE-JOIN \\(SEMI\\) TABLES\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER SORTED BY \\[\"Join.idx_customer.:customer_id\"\\]\n" +
" CLIENT MERGE SORT\n" +
"AND \\(SKIP MERGE\\)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"O.customer_id\"\\]\n" +
" CLIENT MERGE SORT\n" +
" PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
" PARALLEL LEFT-JOIN TABLE 1\\(DELAYED EVALUATION\\)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" +
" CLIENT MERGE SORT\n" +
" AFTER-JOIN SERVER FILTER BY \\(\"I.0:NAME\" = 'T2' OR O.QUANTITY > \\$\\d+.\\$\\d+\\)",
}});
testCases.add(new String[][] {
{
"CREATE LOCAL INDEX " + JOIN_CUSTOMER_INDEX + " ON " + JOIN_CUSTOMER_TABLE_FULL_NAME + " (name)",
"CREATE LOCAL INDEX "+ JOIN_ITEM_INDEX + " ON " + JOIN_ITEM_TABLE_FULL_NAME +
" (name) INCLUDE (price, discount1, discount2, \"supplier_id\", description)",
"CREATE LOCAL INDEX " + JOIN_SUPPLIER_INDEX + " ON " + JOIN_SUPPLIER_TABLE_FULL_NAME + " (name)"
}, {
"SORT-MERGE-JOIN (SEMI) TABLES\n" +
" SORT-MERGE-JOIN (INNER) TABLES\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME +
"(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" +
" SERVER SORTED BY [\"I.0:supplier_id\"]\n" +
" CLIENT MERGE SORT\n" +
" AND\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_SUPPLIER_INDEX_FULL_NAME +
"(" + JOIN_SUPPLIER_TABLE_FULL_NAME + ") [1]\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER SORTED BY [\"S.:supplier_id\"]\n" +
" CLIENT MERGE SORT\n" +
" CLIENT SORTED BY [\"I.:item_id\"]\n" +
"AND (SKIP MERGE)\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + " ['000000000000001'] - [*]\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY [\"item_id\"]\n" +
" CLIENT MERGE SORT\n" +
"CLIENT SORTED BY [\"I.0:NAME\"]",
"SORT-MERGE-JOIN \\(LEFT\\) TABLES\n" +
" SORT-MERGE-JOIN \\(LEFT\\) TABLES\n" +
" CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_FULL_NAME + "\n" +
" CLIENT MERGE SORT\n" +
" AND\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME +
"\\(" + JOIN_ITEM_TABLE_FULL_NAME + "\\) \\[1\\]\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY \\[\".+.0:NAME\", \".+.:item_id\"\\]\n" +
" CLIENT MERGE SORT\n" +
" CLIENT SORTED BY \\[\".+.:item_id\", \".+.0:NAME\"\\]\n" +
" PARALLEL ANTI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" +
" CLIENT MERGE SORT\n" +
" CLIENT SORTED BY \\[.*.CO_ITEM_ID, .*.CO_ITEM_NAME\\]\n" +
"AND\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME +
"\\(" + JOIN_ITEM_TABLE_FULL_NAME + "\\) \\[1\\]\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY \\[\".+.0:NAME\", \".+.:item_id\"\\]\n" +
" CLIENT MERGE SORT\n" +
" CLIENT SORTED BY \\[\".+.:item_id\", \".+.0:NAME\"\\]\n" +
" PARALLEL SEMI-JOIN TABLE 0 \\(SKIP MERGE\\)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" +
" CLIENT MERGE SORT\n" +
" DYNAMIC SERVER FILTER BY \"" + JOIN_ITEM_INDEX_FULL_NAME + ".:item_id\" IN \\(\\$\\d+" +
".\\$\\d+\\)\n" +
"CLIENT FILTER BY \\(\\$\\d+.\\$\\d+ IS NOT NULL OR \\$\\d+.\\$\\d+ IS NOT NULL\\)",
"SORT-MERGE-JOIN \\(SEMI\\) TABLES\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_CUSTOMER_INDEX_FULL_NAME +
"\\(" + JOIN_CUSTOMER_TABLE_FULL_NAME + "\\) \\[1\\]\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER SORTED BY \\[\"" + JOIN_CUSTOMER_INDEX_FULL_NAME + ".:customer_id\"\\]\n" +
" CLIENT MERGE SORT\n" +
"AND \\(SKIP MERGE\\)\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME +
"\\(" + JOIN_ITEM_TABLE_FULL_NAME + "\\) \\[1\\]\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"O.customer_id\"\\]\n" +
" CLIENT MERGE SORT\n" +
" PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
" PARALLEL LEFT-JOIN TABLE 1\\(DELAYED EVALUATION\\)\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ORDER_TABLE_FULL_NAME + "\n" +
" SERVER AGGREGATE INTO DISTINCT ROWS BY \\[\"item_id\"\\]\n" +
" CLIENT MERGE SORT\n" +
" DYNAMIC SERVER FILTER BY \"I.:item_id\" IN \\(\"O.item_id\"\\)\n" +
" AFTER-JOIN SERVER FILTER BY \\(\"I.0:NAME\" = 'T2' OR O.QUANTITY > \\$\\d+.\\$\\d+\\)",
}});
return testCases;
}