Query not working on Virtual Layer but working on the real layer

Question

I'm using QGIS 3.10.10 because of project compatibility. I have the following issue with a query used to filter a virtual layer:

If I try to apply a filter to the layer INFRASTRUTTURE as follows:

SELECT SUM(L_TOT) FROM (SELECT ((N_TUBI12 - XNTUBI12)*LUNGHEZZA) AS L_TOT
    FROM INFRASTRUTTURE
    WHERE N_TUBI12 <> '0' or N_TUBI12 <> null)

I get the error:

An error occurred when executing the query. The data provider said: OGR[3] error 1: SQL Expression Parsing Error: syntax error, unexpected '(', expecting string or identifier. Occurred around : SELECT SUM(L_TOT) FROM (SELECT TIPOLOGIA, N_TUBI12, XNTUBI12, L*

If I try to filter in two steps (creating a new layer L_TUBI_12 in the process to store the result), no error is found and it works perfectly:

SELECT ((N_TUBI12 - XNTUBI12)*LUNGHEZZA) AS L_TOT
    FROM INFRASTRUTTURE
    WHERE N_TUBI12 <> '0' or N_TUBI12 <> null

SELECT SUM(L_TOT) FROM L_TUBI_12

Now, if I create a virtual layer and I try the first query, no error is shown but the result is NULL. I tried forcing the output as int (because it was Qstring by default) but the result is still NULL.

SELECT SUM(L_TOT) FROM (SELECT ((N_TUBI12 - XNTUBI12)*LUNGHEZZA) AS L_TOT
    FROM INFRASTRUTTURE
    WHERE N_TUBI12 <> '0' or N_TUBI12 <> null)

The problem seems to be the additional operation - XNTUBI12 because, if removed, it gives a number as result. The subtract operator is basically not working in the virtual layer.

Do you know why this behaviour? Is there any workaround?

Answer 1

Try

WHERE N_TUBI12 IS NOT '0'

instead of

WHERE N_TUBI12 <> '0' or N_TUBI12 <> null

I had a problem with query once, where I had a field called 'Archived' to exclude trees from my map that no longer exist, but wanted to retain their location and data. I recall having the same issue where my query included a

WHERE "Archived" <> '1'

that didn't give me the expected result. I tried using

WHERE "Archived" is NOT '1'

and it worked. Only then I learnt that in SQLITE <> 'x' and IS NOT 'x' were different. Hope this helps you get closer to solving your mystery.

P.S. I now use "Archived" is NOT True.