[Gfoss] differenza tra SQLITE e PostgreSQL e relative estensioni spaziali

[a.furieri a lqt.it]

On Wed, 25 May 2016 16:56:29 +0200, Totò Fiandaca wrote:
> Salve a tutti.
>

ciao Toto',

mi permetto di riarrangiare i punti della tua domanda
per maggiore chiarezza.


> Vorrei chiedere alla lista perchè si verifica questo diverso 
> comportamento
> utilizzando lo stesso script SQL :
>
> Perchè questi due comportamenti differenti? a cosa è dovuto?
>

qua la risposta e' facile: perche' qualsiasi standard ha sempre
delle "zone grige", che si prestano ad interpretazioni ambigue.
in questi casi ciascuna implementazioni di fatto e' libera di
seguire la strada che ritiene piu' logica ed appropriata, anche
se magari e' diversa da altre.
in altri casi avviene invece che lo standard viene volutamente
ignorato o reinterpretato o magari "stiracchiato" perche' in questo
modo si ritiene di potere offrire migliori funzionalita'.

SQL di fatto e' una luminosa conferma di questa regola generale.
di norma uno statement perfettamente legittimo per il DBMS "A"
dara' errori se cerchi di eseguirlo sul DBMS "B" e viceversa.

intendiamoci: la sostanza sara' sempre quella perche' SQL e' uno
standard abbastanza rigoroso, ma alcuni piccoli dettagli di modesta
entita' e' facile che richiedano qualche aggiustamento "dialettale".


> Select pippo, Max (filed_1) as max_h, geometryfrom plutoGroup by 
> pippo
>

la tua query e' un ottimo esempio di "caso dubbio".
l'interpretazione letterale delle regole dettata dallo standard
ISO SQL-92 indicherebbe che _tutte_ le colonne che appaiono nella
SELECT devono apparire anche nella corrispondente clausola GROUP BY,
ad eccezione delle colonne che corrispondono al risultato di una
funzione di aggregazione.
nel caso speciico della tua query: nella GROUP BY manca la colonna
"geometry".

SQLite in questo caso invece e' piu' tollerante, e ti consente
anche di inserire nella SELECT delle colonne senza equivalente
nella GROUP BY (anche se chiaramente il risultato che otterrai
per quella colonna sara' del tutto arbitrario).
Tuttavia c'e' un'ulteriore eccezione: se nella SELECT
e' presente la funzione Min() o Max() allora per le colonne
non dichiarate nella GROUP BY verra' riportato proprio il
valore minimo (o massimo) di tutta la serie.

pare abbastanza chiaro che questa feature puo' spesso
risultare molto comoda: ma deve essere altrettanto chiaro
che si sta divergendo dall'interpretazione piu' restrittiva
e letterale dello standard ISO SQL-92


> ​questa query funziona bene (senza dare errori) sia in QGIS (es: nei
> virtual layer) che su spatialite o spatialite_gui;
>

posso assicurarti che spatialite e spatialite_gui non
fanno assolutamente nulla di originale; si limitano a
chiamare il motore standard di SQLite.
non ho idea di cosa faccia QGIS, ma non mi pare improbabile
che anche QGIS non faccia nulla di originale e si limiti
semplicemente a delegare SQLite.


> mentre mi da errore in PostgreSQL/PostGIS (pgAdmin 3)​ in quanto 
> vuole che
> metta (giustamente) la 'geometry' anche nella clausola group by, cosa 
> che
> cambierebbe totalmente l'output!!!!
>

appunto: PostgreSQL esige una conformita' rigorosa con
quanto prescrive alla lettera lo standard ISO SQL-92

ciao Sandro


p.s. tutte le notizie di cui sopra sono chiaramente
riportate nella documentazione a supporto sia di SQLite
che di PostgreSQL.
il fatti che uno sia maggiormente interessato alle estensioni
Spatial (PostGIS o SpatiaLite) non e' mai una buona scusa per
bon studiare a fondo la documentazione base del DBMS che
si sta utilizzando.