Início / dba / Perguntas / 164285
Accepted
SQB
Asked: 2017-02-15 06:54:15 +0800 CST

A junção externa em uma exibição com colunas calculadas executa o cálculo para registros ausentes ("externos")

  • 772

Eu tenho uma visão no MySQL, que tem alguns campos calculados - COALESCEe afins. Se eu fizer uma junção externa esquerda nessa exibição (para que os registros dessa exibição possam estar ausentes), os campos calculados ainda serão calculados para os registros ausentes, em vez de aparecer como NULL.

Exemplo

Testei o exemplo a seguir no MySQL versões 5.5.40-36.1-log e 5.5.53-38.5 (porque é o que tenho em mãos), bem como no SQL Fiddle ‡ versões 5.5 e 5.6.

Configurar

-- A simple view
CREATE OR REPLACE VIEW foo
AS
      SELECT 1 AS x
UNION SELECT 2
UNION SELECT 3;

-- Another simple view
CREATE OR REPLACE VIEW bar
AS
      SELECT 1 AS y
UNION SELECT 2
UNION SELECT 3;

-- A (contrived) view with a calculated column
CREATE OR REPLACE VIEW baz
AS
SELECT
   f.x,
   b.y,
   SIGN(COALESCE(b.y, 0)) AS z
FROM foo f
LEFT JOIN bar b ON f.x = 2 * b.y;

Consulta

-- A query that does a left outer join
-- on the view with the calculated column
SELECT *
FROM foo f
LEFT JOIN baz b ON f.x = b.y;

Resultado esperado

Eu esperaria o seguinte resultado, onde vemos uma linha com registros de baze duas linhas sem.

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | - |
| 3 | - | - | - |

Resultado atual

Mas, em vez disso, recebo o seguinte, com de fato uma linha com registros de baz, mas duas linhas onde não há registros, bazexceto para a coluna calculada, que é calculada para todas as três linhas!

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | 0 |
| 3 | - | - | 0 |

Assim zé calculado, mesmo quando baznão retorna nenhum registro (como evidenciado pelos NULLs para xe y).

Meu caso real envolve uma junção entre a visão e uma tabela na qual a visão é baseada. Eu repliquei isso no exemplo, usando apenas visualizações. Eu também testei sem a "auto-junção"; isso não faz diferença.

Gambiarra

O que funciona para mim, está se escondendo bazem uma sub-seleção, assim.

SELECT *
FROM foo f
LEFT JOIN (
   SELECT *
   FROM baz
) b ON f.x = b.y;

Isso produz o resultado esperado:

| x | x | y | z |
-----------------
| 1 | 2 | 1 | 1 |
| 2 | - | - | - |
| 3 | - | - | - |

Este é o comportamento esperado? Por quê?

‡: funcionou para mim ontem, não consigo fazer funcionar hoje.

mysql mysql-5.5

2 respostas

  1. Best Answer

    Se as duas consultas:

    SELECT * 
FROM foo f 
  LEFT JOIN baz b 
  ON f.x = b.y;

    e

    SELECT * 
FROM foo f 
  LEFT JOIN (SELECT * FROM baz) b 
  ON f.x = b.y;

    retornar resultados diferentes, então é um bug e as definições de tabela e visualização são irrelevantes. Isso nunca deveria acontecer.

    Antes de postar um relatório de bug, verifique sua visão e consultas na versão (5.5.54). Seu 5.5.40 tem 14 versões secundárias e cerca de 2,5 anos mais antigo que a versão 5.5 mais recente.

    Se o bug ainda persistir, envie um relatório de bug para o MySQL com este exemplo. Um bug semelhante já foi relatado: Retornar resultado inconsistente ao usar inline SELECT vs VIEW para left join , portanto, se você relatar, sugiro que adicione seus exemplos lá.

    Os desenvolvedores do MySQL confirmaram isso e sugeriram algumas soluções alternativas (até que seja corrigido). Você pode tentar a sugestão para criar as visualizações com ALGORITHM = TEMPTABLE:

    Existem algumas soluções alternativas:

    Em todas as versões, a consulta usando a visualização dará o resultado correto se a visualização for criada com " CREATE ALGORITHM=TEMPTABLE VIEW ..."

    Para 5.7/8.0, ambas as consultas fornecerão o resultado correto definindo optimizer_switch='derived_merge=off'

    Na versão 8.0, a dica NO_MERGE pode ser usada para obter o comportamento correto em ambos os casos: SELECT /*+ NO_MERGE(t2) */ ...

    Dito isto, a consulta é mais complexa do que o necessário. Você poderia ter usado algo mais simples (embora eu não goste de ter um resultado com colunas com nomes idênticos):

    CREATE VIEW baz2
AS
SELECT
   f2.x AS x
   f1.x AS x,
   CASE WHEN f1.x IS NOT NULL THEN f2.x END AS y,
   SIGN(f1.x) AS z
FROM foo f2                   -- changed:   f2 LEFT JOIN f1
  LEFT JOIN bar f1
  ON f1.x = 2 * f2.x;

    e

    SELECT * FROM baz2 ;
    • 3

  2. No MySQL 5.7.11 estamos obtendo os resultados esperados.
    Parece um bug nas versões anteriores.

    +---+--------+--------+------------+
| x | x      | y      | z          |
+---+--------+--------+------------+
| 1 | 2      | 1      | 1          |
+---+--------+--------+------------+
| 2 | (null) | (null) | (null)     |
+---+--------+--------+------------+
| 3 | (null) | (null) | (null)     |
+---+--------+--------+------------+
    • 1

relate perguntas