Início / dba / Perguntas / 259604
Accepted
cis
Asked: 2020-02-14 03:06:30 +0800 CST

Cláusula WHERE não impede o retorno da linha, mas altera os valores da função agregada (aqui: contagem)

  • 772

Recentemente descobri um comportamento do PostgreSQL que é estranho e problemático na minha opinião.

Considere esta consulta simples

SELECT 
   'something confidential'
WHERE FALSE;

A cláusula sem sentido WHERE FALSEse assemelha aqui a uma verificação de permissão muito estrita. Isso funciona como esperado: nada é retornado.

Agora, imagine que você adiciona uma coluna adicional, por exemplo, uma countfunção. Isso dá é esta consulta:

SELECT 
   'something confidential'
   ,count(CURRENT_DATE)
WHERE FALSE;

(O parâmetro de countpoderia ser qualquer coisa. CURRENT_DATEé apenas um exemplo aleatório.)

Agora, obtemos

algo confidencial | 0

Eu acho que isso é de alguma forma de propósito, no sentido de que SELECT count(CURRENT_DATE);retorna 1 se a condição for verdadeira e 0 se a condição for falsa.

No entanto, considero isso problemático em situações em que você não está ciente disso. Então, minhas perguntas são

  1. Por quê? Qual é o pano de fundo desse comportamento?
  2. Como posso fazer essa consulta retornar zero linhas em vez de uma linha com valor 0 para count?
  3. Existe uma maneira direta na consulta para evitar o retorno acidental de linhas que você não deseja retornar adicionando funções de agregação? (Ou seja, além de testes externos, etc.)

Estou usando o PostgreSQL 12.1, mas o comportamento é o mesmo para versões mais antigas.

postgresql security

2 respostas

  1. Best Answer

    Esse é o comportamento padrão do SQL para praticamente todos os bancos de dados.

    Existem três maneiras de como as linhas são retornadas:

    1. sem funções agregadas, a consulta retorna uma linha por linha;
    2. com funções agregadas, mas sem agrupamento, a consulta retorna exatamente uma linha;
    3. com funções agregadas e agrupamento, a consulta retorna uma linha para cada grupo (ou seja, para cada combinação única de valores nas colunas agrupadas).

    Em outras palavras, agregar sem agrupar se comporta como se houvesse um único grupo, ou seja, como se você tivesse escrito GROUP BY ()(uma cláusula GROUP BY com uma lista vazia resulta em um único grupo 'do nada', semelhante a como uma consulta sem resultados FROM em uma única linha 'do nada').

    Para evitar o retorno desta linha de grupo único, você pode

    • adicione um GROUP BY explícito, para que você termine no caso 3. acima (depois de WHERE filtrar todas as linhas, não há valor a partir do qual um grupo possa ser criado):

      SELECT 
   'something confidential',
   count(CURRENT_DATE)
WHERE FALSE
GROUP BY 1;  -- refers to the first column

    • mova a agregação para uma subconsulta, para que a verificação de permissão afete a saída disso:

      SELECT *
FROM (SELECT
          'something confidential',
          count(CURRENT_DATE)
     ) AS subquery
WHERE FALSE;

    • mova a verificação de permissão para a cláusula HAVING (se isso for possível), para que a saída para o único grupo seja descartada:

      SELECT 
   'something confidential',
   count(CURRENT_DATE)
HAVING FALSE;
    • 3

  2. Este é o comportamento esperado (não apenas para Postgres). Se observarmos a ordem lógica de avaliação de uma consulta:

    1. A PARTIR DE
    2. ONDE
    3. AGRUPAR POR
    4. SELECIONAR

    Vou adicionar uma tabela (T) com uma coluna (x) e uma linha no exemplo

    Nós temos:

    SELECT COUNT(1) as CNT
FROM ( VALUES (1) ) AS t(x) 
WHERE false
GROUP BY ()   -- empty set default

    O resultado é uma linha com valor 0. Em seguida, adicionamos uma constante 'algo confidencial' a essa linha e terminamos com:

    SELECT 'something confidential', CNT
FROM 
    SELECT COUNT(1) as CNT
    FROM ( VALUES (1) ) AS t(x) 
    WHERE false
    GROUP BY ()   -- empty set default
) AS u

'something confidential', 0
    • 0

relate perguntas