E' sqlite ad esser buggato o sono io...?

Buonasera a tutti, è da un paio d'ore che mi sto divertendo a bestemmiare contro sqlite, e lui simpaticamente sembra apprezzare il gioco: tuttora non vuol saperne di restituire dei risultati corretti (risultati che invece mysql restituisce).

Allora, il problema è estremamente semplice (o meglio, dopo due ore lo è diventato, avendo capito il problema dov'è ed avendo semplificato l'orrida query).
In pratica ho una tabella così strutturata:

CREATE TABLE `case` (
`nome_casa` text NOT NULL,
`id_schiera` int(11) NOT NULL,
`id_casa` int(11) NOT NULL,
PRIMARY KEY (`id_casa`,`id_schiera`)
);

e questi valori inseriti:

INSERT INTO "case" VALUES('Alessia',11,40);
INSERT INTO "case" VALUES('Barbara',13,41);
INSERT INTO "case" VALUES('Claudia',13,42);
INSERT INTO "case" VALUES('Daniela',11,43);
INSERT INTO "case" VALUES('Elvezia',12,44);

Adesso succede che ho 2 case nella schiera 13 (la 41 e la 42), due nella 11 (la 40 e la 43) ed una sola nella 12.

Cosa voglio ottenere? Voglio semplicemente fare una query che mi indichi l'id della prima casa della schiera.
Quindi io facilmente facendo
SELECT id_casa FROM case GROUP BY id_schiera ORDER BY id_casa ASC;
mi aspetto di ottenere il giusto risultato. No, ovviamente mi viene mostrato il risultato maggiore, e non il primo (i risultati sono 42, 43, 44). Okay, forse qualcosa non va (e non mi meraviglierei così tanto), allora provo DESC anzichè ASC. Ottengo chiaramente 44, 43, 42. Ovvio. Allora provo diversamente:
SELECT DISTINCT(id_schiera), id_casa FROM case;
E quindi ottengo
11|40
13|41
13|42
11|43
12|44
Quindi non ha distinto un beneamato ahem... già.
Se faccio SELECT DISTINCT(id_schiera) FROM case ORDER BY id_casa ASC; invece funziona, ma ovviamente, non avendo l'id della casa... non è poi un grosso guadagno.

IDEE? :D

Ho trovato un modo per evitare questo SIMPATICISSIMO bug, ma è di una schifezza immonda, lo so, però funziona (ma ciò non toglie che preferisco vivamente idee ben migliori, visto che se l'unico sviluppatore di sqlite dovesse decidere di correggere il problema, nulla funzionerebbe più *AL MONDO*).
La schifezza che rende il tutto funzionante è la seguente:

SELECT id FROM (SELECT * FROM case ORDER BY id_casa DESC) GROUP BY id_schiera ORDER BY id_casa ASC;

In pratica lo accontento, e gli rigiro sottosopra la query, quindi quando mi va a prendere poi i valori, erroneamente come solo sqlite sa fare, gli rigiro ancora i valori presi (mentre a me gira ben altro), ed ottengo un risultato "funzionante".
Sì, lo so, ve l'ho già detto, è di uno schifo immane, mi fa ribrezzo solo a guardarlo, ma pare funzioni, quindi buono sviluppo a tutti.
In ogni caso, resto in attesa di eventuali idee ben *migliori*, altrimenti non mi resta altro che contattare lo sviluppatore e dirgliene quattro.

Buona notte a tutti

SELECT id_schiera, min(id_casa) FROM case GROUP BY id_schiera

Dici che puo andar bene?

Ciao!

Guarda, per me va bene, ma la cosa sembra causare svariate magie, per cui ottengo

11|1
12|5
13|2

Al che mi son detto "aggiungiamo allora ORDER BY id", e quindi, chiaramente, ottengo

13|41
11|40
12|44

Cioè, mentre prima, con la tua query, ordinava per id_schiera, con la mia successiva query ha ordinato per *niente*.

Inizio a credere seriamente che sqlite non abbia un vero ordinamento, e si limiti a prendere i valori a caso... :D
Buona idea comunque, forse su un vero motore funzionava, ma dopo quanto accaduto stanotte, posso ben dire che sqlite è ben lungi dall'essere un "vero motore di database". -.-"
(Comunque, ripeto, se state sviluppando qualcosa e vi serve "rapidamente" una soluzione, potete usare la mia query; avevo pensato di aggiungere un flag all'avvio del programma che controlla se sqlite funziona bene o meno, ed implementare la query "normale" e quella "che lo fa contento", in modo tale da evitare che fixando il problema il programma spari numeri a caso)

Mah... Strano... Ho provato a creare una tabella (usando sqlite per windows) con il tuo schema ed inserito i valori che hai postato, poi ho provato a fare la mia query e mi dava errore su case, ho aggiunto gli apici a case, cioè ho scritto 'case', ed ha funzionato giusto. Cioè mi ha dato proprio:

13|41
11|40
12|44

Puoi riprovare? Cosi intanto che ci sono ti sfrutto per fare esperimenti che potrebbero servirmi. :)

Eh, funzionato "giusto" è relativo, cioè... giusto per lui, non per noi :P
Cioè, il risultato corretto dovrebbe essere il seguente (ordinato per id_casa):

11|40
13|41
12|44

o, nel caso ordinasse per id_schiera:

11|40
12|44
13|41

Quest'ultimo risultato sarebbe comunque inutile per me, che voglio essere ordinato per id_casa e non id_schiera. Comunque, nessuno dei due risultati sembra essere ottenibile senza il mio "trucco", quindi per ora posso ancora contattare lo sviluppatore di sqlite e lanciargli qualcosa appresso (in primis perchè nel 2009 i bugs devono essere segnalati non in un apposita sezione, bug reporter o che ne so, no, vanno segnalati in mailing list, per la quale è chiaramente richiesta l'iscrizione).
Riguardo quindi al tuo test, beh, non so che dirti, forse mi son distratto io, di fatti ho incollato un risultato errato (non che quello che mi era stato fornito fosse comunque corretto eh!), relativo alla mia query non semplificata (e che quindi perde senso nell'ambito che vi ho presentato).
A riprovare, posso pure, ma i dati che mi vengon mostrati comunque non son corretti, in base ai risultati che mi aspetterei ricevere che appunto ti ho detto sopra.

Riguardo allo sfruttarmi, fai pure, è sempre utile una chiacchierata relativa alla programmazione, e poi ora come ora non conosco altri sviluppatori android, quindi la cosa non può che esser positiva... e poi chiacchierando chiacchierando spuntan sempre fuori idee geniali, quindi sì, sfruttiamoci pure a vicenda e tiriamo fuori qualcosa di simpatico :P
Ah, se preferisci, ti lascio in mp il contatto msn, se hai qualche idea interessante, mi farebbe piacere ascoltarla, io ti anticipo che sto sviluppando un navigatore (no, tomtom, garmin e google maps navigator non son sufficienti per quel che ho intenzione di fare! :D)
Ora dirai tu "e che c'entra id_casa ed id_schiera con un navigatore?"... eh beh, l'ho detto che ho semplificato il problema o no? Questo è un rapido esempio che mostra appieno il problema e mi lascia libero di non pubblicare informazioni relative al mio progetto :P

PS: Soffri anche tu di insonnia eh? :D

Ahahahah, si insonnia da progetto di tesi. :)

Se lo vuoi ordinato per id_casa allora la stringa completa è:

SELECT id_schiera, min(id_casa) FROM 'case' GROUP BY id_schiera ORDER BY id_casa

Io ho provato tutto con sqlite3 scaricato dal sito ufficiale di sqlite.
Non l'ho provato con android, ma "dovrebbe" (sempre per il discorso della relatività) :D andare lo stesso.

Per lo sfruttare, anche da parte mia se posso cerco di dare una mano.

Se vuoi darmi il tuo contatto MSN nessun problema, ;) non ti assicuro pero la mia reperibilità, almeno, finche ho gli impegni che ho adesso. :(

Sicuro che stai progettando un navigatore e non un teletrasporto?

E, ultima cosa, non mi sono chiesto il cosa c'entra, anche perchè, in ogni caso, quando si ha a che fare con dei programmatori bisogna aspettarsi di tutto. :)

Credo che la risposta alla tua domanda

Quote:

---

Originariamente inviato da bigalex

Cosa voglio ottenere? Voglio semplicemente fare una query che mi indichi l'id della prima casa della schiera.

---

(sulla base delle informazioni aggiunte in seguito) sia

select min(id_casa) from 'case' group by id_schiera order by min(id_casa) asc;

In assenza di min(.) la tua query dovrebbe avere come risposta, in termini d'algebra relazionale,

{{40,43},44,{41,42}}

[ho scelto deliberatamente di ordinare i sottoinsiemi secondo i valori corrispondenti del campo id_schiera, ma si tratta solo di una mia scelta].

che non si può rappresentare direttamente nei termini nei quali SQL produce una risposta a una query; per {40,41} e {42,43}, infatti, occorre scegliere un valore; ho dato solo una scorsa al draft della specifica SQL3 ma non ho trovato una funzione risolutrice, il che equivale a dire che la scelta sembra lasciata all'implementazione.

Incidentalmente, la tua query originale ottiene come risposta

40
41
44

in sqlite

e, come da te appurato,

42
43
44

in sqlite3

per via del fatto che il sort è applicato a valle del group by; l'aggiunta di min(.) nel campo select (e in quello order by) rende uniformi le risposte con i due motori; in assenza del min(.) nel campo order by torna a prevalere la scelta fatta a monte sugli insiemi sopra riportati, come mostrato dalla query

select id_casa,id_schiera,min(id_casa) from 'case' group by id_schiera order by id_casa,id_schiera asc;

che con sqlite3 produce

42|13|41
43|11|40
44|12|44

Che si tratti di una scelta che in questa specifica circostanza produce risultati diversi dalle tue attese è facilmente mostrato cambiando la base in

INSERT INTO 'case' VALUES('Claudia',13,42);
INSERT INTO 'case' VALUES('Barbara',13,41);
INSERT INTO 'case' VALUES('Alessia',11,40);
INSERT INTO 'case' VALUES('Daniela',11,43);
INSERT INTO 'case' VALUES('Elvezia',12,44);

(semplice riordinamento)

e la query chiedendo i massimi in luogo dei minimi:

select id_casa,id_schiera,max(id_casa) from 'case' group by id_schiera order by id_casa desc, id_schiera desc;

questa query produce

44|12|44
42|13|42
40|11|43

in sqlite e

44|12|44
43|11|43
41|13|42

in sqlite3.

HTH,

Andrea.

Quote:

---

Originariamente inviato da Deadly

Ahahahah, si insonnia da progetto di tesi. :)

Se lo vuoi ordinato per id_casa allora la stringa completa è:

SELECT id_schiera, min(id_casa) FROM 'case' GROUP BY id_schiera ORDER BY id_casa

Io ho provato tutto con sqlite3 scaricato dal sito ufficiale di sqlite.
Non l'ho provato con android, ma "dovrebbe" (sempre per il discorso della relatività) :D andare lo stesso.

---

Io ho nuovamente provato con sqlite3 da linux (che pare abbia gli stessi bug di quello dell'android, o almeno mi restituisce gli stessi risultati), ed il risultato è:

13|41.0
11|40.0
12|44.0

Quindi, ancora una volta non ordinato nè per id_casa, nè per id_schiera. Ti consiglio vivamente di, o usare l'emulatore, o installarti linux (anche su una macchina virtuale) per fare le prove... altrimenti poi bestemmi come il sottoscritto :P

Quote:

---

Originariamente inviato da Deadly

Sicuro che stai progettando un navigatore e non un teletrasporto?

---

Eh, il problema è che non riesco a portare il telefono a 88 miglia orarie ed il flusso canalizzatore non si attiva, quindi per ora mi limito al navigatore :P

Quote:

---

Originariamente inviato da Deadly

E, ultima cosa, non mi sono chiesto il cosa c'entra, anche perchè, in ogni caso, quando si ha a che fare con dei programmatori bisogna aspettarsi di tutto. :)

---

Heh, in effetti lo stesso esempio poteva essere usato per fare una versione (gratis, essendoci già a pagamento se ben ricordo) di monopoli per android :P

Quote:

---

Originariamente inviato da Deadly

Credo che la risposta alla tua domanda
[...]

---

Allora. Intanto un applauso, mi hai risolto un enorme problema :D
Avevo capito che il problema riguardasse un "order by" effettuato a posteriori, e per questo ho poi tirato fuori la mia query (che come avevo appunto detto, era oscena :D), ma le tue spiegazioni e la tua soluzione non fanno una piega... davvero, grazie mille :P
Un problema che infatti affligge sqlite è proprio la documentazione, come anche da te constatato (o meglio, tu non hai trovato una funzione risolutiva, ma non è ben chiaro se esista o meno qualcosa, vista la macabra rappresentazione grafica presente :D)... ed infatti spero che al più presto qualcuno ponga fine alla cosa e scriva una vera documentazione. Cioè, alcune cose son davvero buttate lì così, tanto per, senza spiegare nulla -.-"
Quel che non ho capito però dalla tua risposta è se è normale ottenere un risultato simile dalla query "SELECT id_casa FROM case GROUP BY id_schiera ORDER BY id_casa ASC", o si tratta di boh qualcos'altro. Cioè, forse non mi son spiegato, ma il mio dubbio è se mysql mi ha viziato e sqlite3 è pignolo, o se è lecito aspettarsi che l'ordinamento avvenga a monte del "group by".
Grazie mille ancora per la soluzione, che spero possa esser utile anche ad altri eventuali sviluppatori, e buona giornata :D

Ah uhm, in realtà c'è ancora un problema, anche con la tua query.
Il problema penso sia legato al fatto che la query che mi serve fare, è in realtà il risultato di un'altra query (annidata chiaramente all'interno), con delle colonne create appositamente, cioè anch'esse risultato di un'altra query.
Ad esempio, ho la colonna distance (che è proprio quella relativa alla quale devo fare poi l'ordinamento) che è così prodotta:
(ABS(latitude - (SELECT latitude FROM case WHERE id = 18)) + ABS(longitude - (SELECT longitude FROM case WHERE id = 18))) as distance.
Ora, distance in realtà non mi serve numericamente, non essendo realmente significativa della distanza (parlando di un ellissoide al quale è paragonabile la terra, le coordinate subiscono svariate approssimazioni allontanandosi dall'equatore, e non posso quindi usare una così semplice implementazione per ottenere risultati precisi), quindi il risultato non fa altro che trovarmi, approssimativamente, i punti che mi interessano più vicini al punto dato; poi tramite la classe Location dell'sdk mi trovo la distanza tra punti.
Ora il fatto è che, implementando la tua query, ordinando per MIN(distance), mi vengono mostrati i risultati relativi (tornando all'esempio semplificato) alle case più lontane giacenti sulla stessa schiera, e quindi l'ordinamento è ancora una volta fatto "a posteriori", cioè, dopo il "group by", e la query non è ancora risolutiva.
Non ho proprio idea di come risolvere; credo che terrò ancora per un pò la query "brutta", procedendo col resto, e rimanderò una migliore soluzione a quando ci sarà il tempo, tanto per il momento funziona ;)

Ciao ed ancora grazie mille!

Quote:

---

Originariamente inviato da bigalex

...o meglio, tu non hai trovato una funzione risolutiva, ma non è ben chiaro se esista o meno qualcosa, vista la macabra rappresentazione grafica presente :D)

---

:)

Quote:

---

... ed infatti spero che al più presto qualcuno ponga fine alla cosa e scriva una vera documentazione. Cioè, alcune cose son davvero buttate lì così, tanto per, senza spiegare nulla -.-"

---

Io, con quel poco che ricordo di SQL, ho trovato la documentazione abbastanza ben fatta; i diagrammi sintattici, in particolare, mi sono sembrati molto leggibili. Il punto è che si tratta di una documentazione da usare "per differenza" rispetto ad altra documentazione su SQL e su altri RDBMS. Non è un buon punto di partenza per chi debba imparare SQL, ma non mi pare il tuo caso, dati i tuoi destreggiamenti sintattici.

Quote:

---

Quel che non ho capito però dalla tua risposta è se è normale ottenere un risultato simile dalla query "SELECT id_casa FROM case GROUP BY id_schiera ORDER BY id_casa ASC", o si tratta di boh qualcos'altro.

---

Ho parecchia ruggine da rimuovere, e un discreto aggiornamento da eseguire (l'ultima volta che ho scritto in SQL Oracle era appena uscita la versione 7 di Oracle, e il nostro ambiente usava la 5), ma direi che è normale, in assenza di funzioni di raggruppamento (p.es. la min(.) che ho inserito nella mia versione della tua query).

Quote:

---

Cioè, forse non mi son spiegato, ma il mio dubbio è se mysql mi ha viziato e sqlite3 è pignolo, o se è lecito aspettarsi che l'ordinamento avvenga a monte del "group by".

---

Il punto sta nella scelta del candidato per rappresentare l'insieme selezionato; group by andrebbe usato sempre in congiunzione con una funzione di raggruppamento, risolvendo per costruzione il problema dell'identificazione del candidato. Dovrei vedere meglio i tuoi casi del passato, senza i quali posso formulare solo congetture - dato che non conosco i dettagli implementativi di MySQL, ma tenderei a ritenere che MySQL ti restituisse risultati più vicini alle tue aspettative per via di qualche ottimizzazione 'intelligente' presente nel suo motore, oppure -ma qui il grado di plausibilità è fortemente legato al numero di prove da te effettuato e alla loro diversità- potresti esserti trovato nella felice condizione di aver scritto query naturalmente indirizzate ad utilizzare il candidato selezionato dall'engine di MySQL, senza l'intervento di un'euristica sofisticata.

Passo all'altra tua domanda più corposa;

ciao,

Andrea.

Quote:

---

Originariamente inviato da bigalex

Il problema penso sia legato al fatto che la query che mi serve fare, è in realtà il risultato di un'altra query (annidata chiaramente all'interno), con delle colonne create appositamente, cioè anch'esse risultato di un'altra query.

---

Avrei bisogno di vedere il codice per fornire una risposta più precisa, ma il punto, in essenza, resta sempre come trattare il fatto che devi ordinare prima di raggruppare, mentre la semantica SQL ordina dopo aver eseguito il group by, e dopo aver scelto un candidato, quando non sia univoco. Se non risulta possibile scrivere una funzione di raggruppamento da utilizzare direttamente nella parte di result-column della select, il ricorso a query nidificate è inevitabile, a meno di spezzare la query in più porzioni, per mezzo di tabelle di appoggio esplicite, il che non cambia la sostanza della questione.

Saluti,

Andrea.

Quindi tutto il nocciolo del problema riguarda il fatto che sqlite - almeno per quanto io sappia - non riesce ad ordinare prima del raggruppamento, o meglio, non è possibile raggruppare tenendo i primi valori trovati e non gli ultimi.
Se questo fosse vero (e sinceramente non saprei dirti se lo è, non avendo mai lavorato prima d'ora con sqlite nè con sqlite3, ed essendo viziato dalla documentazione particolarmente chiara e piena d'esempi di mysql :) ), si renderebbe quindi necessaria una struttura simile a quella da me fatta.
La cosa che mi sconvolge però è dovergli invertire l'ordine prima, fare il "group by", e reinvertire ancora l'ordine dopo... cioè, io vedo che due passaggi su tre sono futili... -.-"
Spezzare la query, nel mio caso, non è sensato perchè andrebbe a complicare una situazione già complicata di per sè (e quel che mi fa rabbia è che invece sarebbe semplice -.-")
Non vi incollo i dettagli della query perchè dovreste studiarvi la mia implementazione, la struttura delle tabelle... e poi beh perchè essendo il cuore della mia applicazione (che conto di vendere :D) diventerebbe un pò ahem... open source roftl

Tornando sulla documentazione, eh sì, è completa, o meglio, i diagrammi sintattici ci son tutti ed è tutto documentato, ma per chi come me che è abituato a vedere esempi su esempi (parlo della documentazione di mysql), descrizione accurata (e basilare) di ogni funzione... beh, ci si trova sconcertati di fronte ad un tale riassunto insomma :D
Lasciando perdere che poi è da integrare con altro, ma un esempio ogni tanto non farebbe male insomma :P

Quindi riguardo invece le mie esperienze passate, non saprei dirti, o meglio, solitamente credo (ma la tua analisi ha certamente mostrato più esperienza di quanta io possa averne accumulata in questi anni!) di aver correttamente selezionato il candidato giusto, ma non escludo che qualche ottimizzazione eucaristica possa aver giocato dalla mia parte.

Quel che ho notato invece in sqlite è che spesso gli errori non son segnalati.
Ad esempio, in mysql quando faccio una query annidata, mi viene richiesto (e spesso, preso dal resto della query me ne dimentico) di assegnargli un alias. Altrimenti non va affatto.
Con sqlite invece il controllo è più ahem... "lite" :D
Ho infatti provato senza l'assegnazione dell'alias, e si occupa da solo della gestione della query; ovviamente in caso di ambiguità sceglierà il risultato sbagliato (e pur che scegliesse il risultato corretto sarebbe comunque rischioso lasciar la query così com'è), e quindi è comunque necessaria una definizione... solo che nessuno me lo ricorda ed errare è umano :D
Sì, senza dubbio son stato viziato da mysql :D

Mi fa piacere aver "risolto" (a meno che non arrivi qualcuno che s'intenda di sqlite e riporti la sacra annunciazione della soluzione :D) il problema, e ringrazio tutti della collaborazione, sarete nei credits del programma :D

Noto però con mio dispiacere che i post sul forum son più che altro legati a problemi di coloro che si stanno avvicinando da poco alla programmazione di android e me ne dispiaccio molto, sarebbe bello avere un luogo dove condividere esperienze (ma soprattutto problemi con annesse soluzioni!) di programmi anche un pò più complessi, facendo crescere una community italiana di sviluppo.
Beh, io certamente se dovessi avere qualche altro problema vi terrò informati, e sto tenendo d'occhio i thread più nuovi, quindi spero di poter aiutare anche io voi com'è stato fatto con me :D

Buona serata a tutti e grazie ancora!

Quote:

---

Originariamente inviato da bigalex

Quindi tutto il nocciolo del problema riguarda il fatto che sqlite - almeno per quanto io sappia - non riesce ad ordinare prima del raggruppamento, o meglio, non è possibile raggruppare tenendo i primi valori trovati e non gli ultimi.

---

Direi che il nocciolo è che SQLite esegue delle ottimizzazioni che sono solo parzialmente contollabili, e che occorre capirle più a fondo per dirigerle come ci occorre.

Quote:

---

Tornando sulla documentazione, eh sì, è completa, o meglio, i diagrammi sintattici ci son tutti ed è tutto documentato, ma per chi come me che è abituato a vedere esempi su esempi (parlo della documentazione di mysql), descrizione accurata (e basilare) di ogni funzione... beh, ci si trova sconcertati di fronte ad un tale riassunto insomma :D
Lasciando perdere che poi è da integrare con altro, ma un esempio ogni tanto non farebbe male insomma :P

---

:) Il confronto tra le due documentazioni è davvero fuori discussione :)


Per chiudere la questione sull'ottimizzazione, propendo fortemente per una differenza significativa nell'euristica di esecuzione della query da parte dei due motori, in particolare in presenza di un'indicizzazione sulle colonne coinvolte nella select; confrontando

MySQL :: MySQL 5.0 Reference Manual :: 7.2.14 GROUP BY Optimization

e il punto 6 di

The SQLite Query Optimizer Overview

la differenza risulta vidente.

Ora, dato che noi, a meno di trovare un'altra implementazione di un motore SQL sufficientemente leggero da poter girare sui nostri smartphone, è in termini SQLite-iani che dobbiamo ragionare, dobbiamo allenarci per produrre la query ottimale secondo SQLite... :)

Ciao,

Andrea.