in phoenix-core/src/it/java/org/apache/phoenix/end2end/join/SubqueryIT.java [55:284]
public static synchronized Collection<Object> data() {
List<Object> testCases = Lists.newArrayList();
testCases.add(new String[][] {
{}, {
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
" SERVER SORTED BY \\[I.NAME\\]\n" +
"CLIENT MERGE SORT\n" +
" PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + "\n" +
" SKIP-SCAN-JOIN TABLE 1\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" +
" DYNAMIC SERVER FILTER BY \"I.item_id\" IN \\(\\$\\d+.\\$\\d+\\)",
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SUPPLIER_TABLE_FULL_NAME + "\n" +
" SERVER SORTED BY [I.NAME]\n" +
"CLIENT MERGE SORT\n" +
" PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
" PARALLEL SEMI-JOIN TABLE 1(DELAYED EVALUATION) (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",
"CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_FULL_NAME + "\n" +
"CLIENT MERGE SORT\n" +
" PARALLEL LEFT-JOIN TABLE 0\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" +
" PARALLEL LEFT-JOIN TABLE 1\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" +
" AFTER-JOIN SERVER FILTER BY \\(\\$\\d+.\\$\\d+ IS NOT NULL OR \\$\\d+.\\$\\d+ IS NOT NULL\\)",
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_ITEM_TABLE_FULL_NAME + "\n" +
" SERVER SORTED BY [I.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",
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_CUSTOMER_TABLE_FULL_NAME + "\n" +
" SKIP-SCAN-JOIN TABLE 0\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+\\)\n" +
" DYNAMIC SERVER FILTER BY \"" + JOIN_CUSTOMER_TABLE_FULL_NAME + ".customer_id\" IN \\(\\$\\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)"
}, {
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
" PARALLEL INNER-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_supplier\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" PARALLEL SEMI-JOIN TABLE 1 \\(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",
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_supplier\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" SERVER SORTED BY [\"I.0:NAME\"]\n" +
"CLIENT MERGE SORT\n" +
" PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
" PARALLEL SEMI-JOIN TABLE 1(DELAYED EVALUATION) (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",
"CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_FULL_NAME + "\n" +
"CLIENT MERGE SORT\n" +
" PARALLEL LEFT-JOIN TABLE 0\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" +
" 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" +
" PARALLEL LEFT-JOIN TABLE 1\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" +
" 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" +
" AFTER-JOIN SERVER FILTER BY \\(\\$\\d+.\\$\\d+ IS NOT NULL OR \\$\\d+.\\$\\d+ IS NOT NULL\\)",
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_item\n" +
" SERVER FILTER BY FIRST KEY ONLY\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",
"CLIENT PARALLEL 1-WAY FULL SCAN OVER " + JOIN_SCHEMA + ".idx_customer\n" +
" SERVER FILTER BY FIRST KEY ONLY\n" +
" PARALLEL SEMI-JOIN TABLE 0 \\(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)"
}, {
"CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME +
"\\(" + JOIN_ITEM_TABLE_FULL_NAME + "\\) \\[1\\]\n" +
"CLIENT MERGE SORT\n" +
" PARALLEL INNER-JOIN TABLE 0\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" +
" CLIENT MERGE SORT\n" +
" PARALLEL SEMI-JOIN TABLE 1 \\(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" +
" DYNAMIC SERVER FILTER BY \"I.:item_id\" IN \\(\\$\\d+.\\$\\d+\\)",
"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 [\"I.0:NAME\"]\n" +
"CLIENT MERGE SORT\n" +
" PARALLEL LEFT-JOIN TABLE 0\n" +
" CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + JOIN_ITEM_INDEX_FULL_NAME +
"(" + JOIN_ITEM_TABLE_FULL_NAME + ") [1]\n" +
" CLIENT MERGE SORT\n" +
" PARALLEL SEMI-JOIN TABLE 1(DELAYED EVALUATION) (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",
"CLIENT PARALLEL 4-WAY FULL SCAN OVER " + JOIN_COITEM_TABLE_FULL_NAME + "\n" +
"CLIENT MERGE SORT\n" +
" PARALLEL LEFT-JOIN TABLE 0\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" +
" 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" +
" PARALLEL LEFT-JOIN TABLE 1\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" +
" 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" +
" AFTER-JOIN SERVER FILTER BY \\(\\$\\d+.\\$\\d+ IS NOT NULL OR \\$\\d+.\\$\\d+ IS NOT NULL\\)",
"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" +
"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",
"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" +
"CLIENT MERGE SORT\n" +
" PARALLEL SEMI-JOIN TABLE 0 \\(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+\\)\n" +
" DYNAMIC SERVER FILTER BY \"" + JOIN_CUSTOMER_INDEX_FULL_NAME + ".:customer_id\" IN " +
"\\(\\$\\d+.\\$\\d+\\)"
}});
return testCases;
}