Result of a SELECT in Postgresql is not the same in Sqlite

Question

Good evening guys, I developed a Query in Postgresql to apply a SELECT in my data imposing some rules, and these rules are very important for me to get the right result at the end of the selection.

The filter I needed to develop was this:

GSM which is repeated, and has in both STATUS = 'TEMPORARY ANOMALY' and its MOTIVO_ENVIO is the same, only import one record, whose DATA_ALTERACAO is more recent.

His resolution to run on Postgresql was this:

SELECT ex.gsm, ex.motivo_envio, ex.status, ex.data_ativacao, ex.data_importacao, ex.data_alteracao
FROM (SELECT gsm, MAX(data_alteracao) AS last_date FROM anomalias GROUP BY gsm) lst
INNER JOIN anomalias ex ON (lst.gsm = ex.gsm) AND (ex.data_alteracao = lst.last_date)
WHERE ex.status = 'ANOMALIA TEMPORÁRIA'
AND EXISTS (SELECT tmp.gsm FROM anomalias tmp
WHERE tmp.gsm = ex.gsm
AND tmp.motivo_envio = ex.motivo_envio
AND tmp.status = ex.status
GROUP BY tmp.gsm, tmp.motivo_envio, tmp.status
HAVING COUNT(tmp.gsm) > 1)

This filter returns to me 9 records, and it’s exactly the right amount of records I needed. My problem is being when I put this filter to turn on SQLite he returns me 3 records the more, and this can not happen, I need much that it obeys this Query, I copied exactly the same way it is in Postgresql and put in Sqlite, but it is returning me undesirable data.

---

Can someone help me please, I do not know if this query I did is the best method to follow the rule I need, the rule as mentioned above is this:

GSM which is repeated, and has in both STATUS = 'TEMPORARY ANOMALY' and its MOTIVO_ENVIO is the same, only import one record, whose DATA_ALTERACAO is more recent.

If anyone needs the contents of the database to test here: https://ghostbin.com/paste/9uqnn

Answer 1

The difference may be in how each of the databases is interpreting the strings containing dates in the format DD/MM/YYYY.

The SQLite3 interprets strings containing dates in the format ISO8601, that is to say: YYYY-MM-DD, as long as the PostgreSQL uses an environment variable called DateStyle to make this control.

If you intend to use strings containing dates in the format DD/MM/YYYY in the SQLite3, you must use the function strftime(), look at you:

INSERT INTO
    export
VALUES
    (
      '31992387535',
      'TROCA DE APARELHOS_REEMBOLSO - REBATE',
      'LIBERADO',
      strftime('%d/%m/%Y','27/02/2019'),
      strftime('%d/%m/%Y','27/02/2019'),
      strftime('%d/%m/%Y','01/03/2019')
    );

Or adjust your strings to the format expected by SQLite3:

INSERT INTO
    export
VALUES
    (
      '31992387535',
      'TROCA DE APARELHOS_REEMBOLSO - REBATE',
      'LIBERADO',
      '2019-02-27',
      '2019-02-27',
      '2019-03-01'
    );

I also noticed that you use special characters in your strings, for example:

...
ex.status = 'ANOMALIA TEMPORÁRIA'
...

The strings containing these special characters were saved correctly in the database where you are running your SELECT ?!