UPDATE (DML) command does not recognize all filters

venanciofire commented 2 months ago

Details

UPDATE (DML) command does not recognize when the filter satisfies all conditions, for example if the record date is equal to today it is updating all fields mentioned in the SET but if it is different from today it recognizes all filter conditions and does not update. And even inserting in a Trigger the bug persists.

Steps to reproduce

UPDATE TBL_A
   SET NM_BANCO = FI.EMISSOR,
       DIAS_COMISSAO = julianday(IC.dt_fluxo_fim - IC.dt_fluxo_ini)

  FROM TBL_A AS IC
       INNER JOIN
       TBL_B AS FI ON (FI.APOLICE_AJUSTADA = IC.NR_CONTRATO) 
 WHERE 1 = 1
       AND IC.DT_COMISSAO = strftime('%d/%m/%Y', 'now') 
       AND FI.INSTRUMENTO = 'Fiança'
       AND FI.BOLETA_ATUAL = ( SELECT MAX(BOLETA_ATUAL) 
                               FROM TBL_B
                              WHERE APOLICE_AJUSTADA = IC.NR_CONTRATO
                         );

Operating system

Windows 11

SQLiteStudio version

v3.4.4

JrgMyr commented 1 month ago

Hi Carlos. Is that a "feature" of SQLite or of SQLiteStudio? Can you reproduce the error with the original library from sqlite.org on the command line?

venanciofire commented 1 month ago

Hi @JrgMyr SQLiteStudio. It doesn't show any error, the query just doesn't behave like in other relational databases. I used the original library via command line and the result is the same, it doesn't accept the filter. Did it work for you?

pawelsalawa / sqlitestudio