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

[bigalex]

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?

[bigalex]

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

[Deadly]

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

Dici che puo andar bene?

Ciao!

[bigalex]

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...
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)

[Deadly]

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.

[bigalex]

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! )
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?

[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à) 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.

[af101]

Credo che la risposta alla tua domanda

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.

[bigalex]

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à) 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

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

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

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

Allora. Intanto un applauso, mi hai risolto un enorme problema
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 ), 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 )... 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

[bigalex]

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!