Início / dba / Perguntas / 332585
Accepted
mvorisek
Asked: 2023-10-27 21:14:15 +0800 CST

A comparação SQLite dos mesmos tipos de operandos se comporta de maneira diferente

  • 772

Baseado em documentos Sqlite: https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison , especialmente esta declaração:

Se um operando tiver afinidade INTEGER, REAL ou NUMERIC e o outro operando tiver TEXT ou BLOB ou nenhuma afinidade então a afinidade NUMERIC será aplicada ao outro operando.

Eu esperaria a seguinte consulta:

CREATE TABLE `invoice` (
  `id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  `amount` DOUBLE PRECISION DEFAULT NULL
);

insert into `invoice` (`amount`) values (4.0);
insert into `invoice` (`amount`) values (15.0);
insert into `invoice` (`amount`) values (4.0);

select *,
    typeof(amount), amount = '4',
    typeof(sum(amount)), sum(amount) = '4', sum(amount) = '4.0', sum(amount) = 4
from invoice
group by id;

para retornar o mesmo resultado para sum(amount) = '4'cada amount = '4'linha, pois ambos os tipos de operando têm o mesmo tipo em cada comparação (verificado usando typeof(), para não, SUM()a comparação está funcionando conforme o esperado).

Demonstração: http://sqlfiddle.com/#!5/59238/2

sqlite

2 respostas

  1. Acho que você percebeu uma pequena inconsistência no comportamento do SQLite! Todo o código abaixo está disponível no violino aqui .

    Primeiro temos:

    SELECT 4 = '4' AS "INT_to_TEXT";  -- No coercion because it's not a field!

    Resultado:

    INT_to_TEXT
          0  <<--- FALSE!

    Então fazemos isso:

    SELECT 4 = CAST('4' AS INTEGER) AS "Casted";  -- <<-- Explicit CAST required

    Resultado:

    Casted
     1  <<--- TRUE!

    Então, agora tentamos a tabela:

    SELECT
  amount = 4  AS "Int",
  amount = '4' AS "Coerced" -- <<-- Implicit coercion occurs here because it's a field!
FROM
  invoice;

    Resultado:

    Int Coerced
  1       1    = 4  therefore TRUE
  0       0    = 15 therefore FALSE  4 != 15 any way you do it!
  1       1    = 4  therefore TRUE

    Finalmente,

    SELECT
  SUM(amount) AS    "The sum (INT)",
  SUM(amount) = 4 AS "Expected", -- <<---- t/f/t as expected
  SUM(amount) = '4' AS "No coercion 1",    -- <<--- All FALSE
  SUM(amount) = CAST('4' AS INTEGER) AS "Casted", -- <<--- t/f/t as expected 
  SUM(amount) = '4.0' "No coercion 2"   -- <<--- All FALSE
FROM
  invoice
GROUP BY id;

    Resultado:

    The sum (INT)  Expected  No coercion 1  Casted  No coercion 2
            4         1              0       1              0
           15         0              0       0              0
            4         1              0       1              0

    Portanto, os casos em que é f/f/f são aqueles em que você tenta forçar SUM(amount) a corresponder a TEXT! Parece-me que o que está acontecendo é que o SQLite fica confuso quando você tenta coagir implicitamente uma string para corresponder a um SUM. Achei que poderiam ser todas funções agregadas, mas não é!

    Veja isso:

    SELECT
  COUNT(*) = 3,
  COUNT(*) = '3',   -- <<---- t/f COUNT() has the same behaviour as SUM
  MAX(amount) = 15,
  MAX(amount) = 15  -- <<---- t/t But, MAX() does not! 
FROM
  invoice;

    Resultado:

    COUNT(*) = 3  COUNT(*) = '3' MAX(amount) = 15 MAX(amount) = 15
           1              0                 1                1

    Então, para COUNT, é t/f, mas para MAX, é t/t!

    Como eu disse, acho que você descobriu uma discrepância no comportamento do SQLite, a menos, é claro, que D. Richard Hipp esteja usando alguma lógica que eu não entendi muito bem - e ele é um biscoito muito inteligente! Você deve denunciar ou eu devo?

    Embora, o que o lunático realmente gostaria de fazer isso "na vida real" esteja muito além do meu nível salarial! :-)

    E, finalmente, apenas para sua informação, você não precisa de crases para SQLite - até mesmo o MySQL não os exige mais! +1 para uma postagem interessante!

    • 1
  2. Best Answer

    A afinidade de tipo não deve ser confundida com Data Type (que o Sqlite chama de Storage Class ).

    Cada valor no Sqlite possui uma classe de armazenamento, que é sempre NULL, INTEGER, REAL, TEXT ou BLOB.

    4 é um valor da classe de armazenamento INTEGER. 'xyz' é um valor da classe de armazenamento TEXT.

    Observe que typeof()retorna a classe Storage de um valor, não a afinidade de tipo da coluna.

    Como no Sqlite as colunas não precisam ter uma classe de armazenamento específica, e você pode armazenar 4 ou 'xyz' na mesma coluna, o Sqlite introduziu o conceito de Type Affinity, que é uma indicação de quais tipos de valores DEVEM ser armazenados em essa coluna, se possível. Então, se você declarar uma coluna

    amount DOUBLE PRECISION

    na verdade, você está dizendo que amount deve conter valores REAIS (tem um tipo REAL affinity ), mas os valores reais podem ser de qualquer tipo.

    O SQLite tenta satisfazer essa afinidade quando você insere um valor '4'into amount: ele o converte em um valor REAL antes de armazená-lo. Mas se você inserir um valor de 'xyz', ele não poderá convertê-lo em REAL e armazenará o valor como TEXTO.

    Quando você usa a coluna amountem uma consulta, o Sqlite presume que os valores armazenados na coluna devem ser valores REAIS. Então quando ele fizer a comparação amount = '4', ele aplicará regras de afinidade de tipo: '4'será convertido para REAL e comparado com o que está armazenado na coluna.

    Como a afinidade de tipo é ditada pela definição da coluna no momento da criação , e não pelos valores únicos contidos, isso implica que:

    Uma . A afinidade do tipo amountsempre será REAL, mesmo quando o valor contido em uma linha for 'xyz'.

    B. _ Qualquer coisa que não seja uma coluna nunca foi declarada com afinidade de tipo, portanto, qualquer expressão que não seja uma simples referência a uma coluna de uma tabela real, ou que não seja explicitamente CAST, não possui afinidade de tipo.

    Isso significa que 4e '4'não possuem afinidade de tipo (mesmo que sejam constantes da classe de armazenamento INTEGER e TEXT, respectivamente). Além disso amount+10, sum(amount)ou min(amount)são todas expressões que não possuem afinidade de tipo (você não declarou ao Sqlite qual tipo essas expressões deveriam ser), mas, quando avaliadas para cada linha, elas serão avaliadas como um valor em uma classe de armazenamento específica .

    Isso é por que:

    • 4 = '4'é FALSO, porque nenhum deles tem afinidade de tipo e o sqlite compara um número inteiro com uma string
    • amount = '4'é TRUE quando a 4é armazenado na linha, porque como amount tem uma afinidade de tipo REAL, o sqlite espera que todos os valores contidos sejam reais e converte '4'para 4antes da comparação.
    • sum(amount) = '4'é FALSE mesmo quando a soma é realmente 4, porque sum(amount)não é uma simples referência a uma coluna, é uma expressão e, portanto, não tem afinidade de tipo. 4é então comparado '4'e o resultado é falso como no primeiro exemplo.
    • mas CAST(sum(amount) AS REAL) = '4' é TRUE quando a soma é realmente 4, porque o CAST atribui uma afinidade do tipo REAL à expressão da mão esquerda, então o sqlite sabe que precisa converter a mão direita '4'para 4antes da comparação.

    Além disso, se bem entendi, embora as classes de armazenamento sejam verificadas e avaliadas em tempo de execução (porque linhas diferentes podem ter amountvalores diferentes de classes de armazenamento diferentes), acho que a afinidade de tipo é avaliada no momento da preparação, portanto, qualquer conversão necessária é realmente estabelecida, para cada linha, antes de verificar os valores nas linhas.

    • 1

relate perguntas