MaxCompute:SUBQUERY_MAPJOIN HINT

CREATE TABLE t1(a BIGINT, b BIGINT);
CREATE TABLE t2(a BIGINT, b BIGINT);

SELECT a,
       (/*+ subquery_mapjoin */ SELECT b FROM t2 WHERE a = t1.a)
FROM t1;

SELECT * FROM t1 WHERE a IN     (/*+ subquery_mapjoin */ SELECT a FROM t2 WHERE b = t1.b);
SELECT * FROM t1 WHERE a NOT IN (/*+ subquery_mapjoin */ SELECT a FROM t2 WHERE b = t1.b);

SELECT * FROM t1 WHERE EXISTS     (/*+ subquery_mapjoin */ SELECT * FROM t2 WHERE b = t1.b);
SELECT * FROM t1 WHERE NOT EXISTS (/*+ subquery_mapjoin */ SELECT * FROM t2 WHERE b = t1.b);

-- Wrong: hint is inside the SELECT clause, not immediately after the opening parenthesis
SELECT * FROM t1 WHERE a IN (SELECT /*+ subquery_mapjoin */ a FROM t2 WHERE b = t1.b);

-- Create a partitioned table
CREATE TABLE IF NOT EXISTS sale_detail
(
  shop_name     STRING,
  customer_id   STRING,
  total_price   DOUBLE
)
PARTITIONED BY (sale_date STRING, region STRING);

-- Add partitions
ALTER TABLE sale_detail
  ADD PARTITION (sale_date='2013', region='china')
  PARTITION (sale_date='2014', region='shanghai');

-- Load data
INSERT INTO sale_detail PARTITION (sale_date='2013', region='china')
  VALUES ('s1','c1',100.1), ('s2','c2',100.2), ('s3','c3',100.3);
INSERT INTO sale_detail PARTITION (sale_date='2014', region='shanghai')
  VALUES ('null','c5',null), ('s6','c6',100.4), ('s7','c7',100.5);

-- Create shop_detail from the 2013 partition
SET odps.sql.allow.fullscan=true;
CREATE TABLE shop_detail AS
  SELECT shop_name, customer_id, total_price
  FROM sale_detail
  WHERE sale_date='2013' AND region='china';

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
| null       | c5          | NULL        | 2014       | shanghai   |
| s6         | c6          | 100.4       | 2014       | shanghai   |
| s7         | c7          | 100.5       | 2014       | shanghai   |
+------------+-------------+-------------+------------+------------+

+------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s1         | c1          | 100.1       |
| s2         | c2          | 100.2       |
| s3         | c3          | 100.3       |
+------------+-------------+-------------+

Exemplo 1: Subconsulta escalar

Retorne todas as linhas de shop_detail com pelo menos uma linha correspondente em sale_detail.

SET odps.sql.allow.fullscan=true;
SELECT * FROM shop_detail
WHERE (/*+ subquery_mapjoin */ SELECT COUNT(*) FROM sale_detail
       WHERE sale_detail.shop_name = shop_detail.shop_name) >= 1;

Resultado:

+------------+-------------+-------------+
| shop_name  | customer_id | total_price |
+------------+-------------+-------------+
| s1         | c1          | 100.1       |
| s2         | c2          | 100.2       |
| s3         | c3          | 100.3       |
+------------+-------------+-------------+

Exemplo 2: Subconsulta IN

Obtenha as linhas de sale_detail cujo total_price aparece em shop_detail.

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail
WHERE total_price IN (/*+ subquery_mapjoin */ SELECT total_price FROM shop_detail
                      WHERE customer_id = shop_detail.customer_id);

Resultado:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

Exemplo 3: Subconsulta EXISTS

Selecione as linhas de sale_detail com customer_id correspondente em shop_detail.

SET odps.sql.allow.fullscan=true;
SELECT * FROM sale_detail
WHERE EXISTS (/*+ subquery_mapjoin */ SELECT * FROM shop_detail
              WHERE customer_id = sale_detail.customer_id);

Resultado:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

Exemplo 4: Subconsultas básicas não são compatíveis

Este hint não funciona com subconsultas básicas (tabelas derivadas). A consulta a seguir falha.

SET odps.sql.allow.fullscan=true;
SELECT * FROM (/*+ subquery_mapjoin */ SELECT shop_name FROM sale_detail) a;

Erro:

FAILED: ODPS-0130161:[1,16] Parse exception - invalid subquery_mapjoin hint, should only be used for scalar/in/exists subquery

Exemplo 5: Aviso quando uma subconsulta não é convertida em JOIN

Quando uma subconsulta filtra por uma coluna de partição, o MaxCompute a mantém como subconsulta para permitir a poda de partição, em vez de convertê-la em JOIN. Nesse caso, o hint não tem efeito e o sistema emite um aviso, mas a consulta ainda retorna resultados.

Primeiro, adicione uma coluna sale_date à tabela shop_detail e recarregue os dados:

ALTER TABLE shop_detail ADD COLUMNS IF NOT EXISTS (sale_date STRING);
INSERT OVERWRITE TABLE shop_detail
  VALUES ('s1','c1',100.1,'2013'), ('s2','c2',100.2,'2013'), ('s3','c3',100.3,'2013');

Em seguida, execute a consulta com avisos ativados:

-- Enable warning output (skip this if your project already outputs warnings)
SET odps.compiler.warning.disable=false;

-- sale_date is a partition column of sale_detail.
-- MaxCompute keeps this subquery as-is (no JOIN conversion) to support partition pruning.
-- The hint is ignored and the system issues a warning.
SELECT * FROM sale_detail
WHERE sale_date IN (/*+ subquery_mapjoin */ SELECT sale_date FROM shop_detail);

Resultado:

+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| s1         | c1          | 100.1       | 2013       | china      |
| s2         | c2          | 100.2       | 2013       | china      |
| s3         | c3          | 100.3       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

Aviso:

WARNING:[1,47]  subquery_mapjoin hint does not work because the subquery is not converted to join