Confessions of a Dangerous Mind

Brain.FlushBuffer()
posts - 176, comments - 234, trackbacks - 93

SQL Server: strano comportamento della clausola IN

Oggi mi è stato sottoposto un problema veramente curioso. Una collega mi ha fatto notare che durante un’operazione di manutenzione di un DB SQL Server 2008 R2 ha lanciato una query di DELETE che non si è comportata come previsto. In pratica, questa query stava cancellando arbitrariamente i dati dalla tabella senza rispettare la clausola WHERE che compariva regolarmente nel testo della query.

Il comando dato al server è il seguente:

DELETE FROM dbo.Utenti WHERE IDUtente IN (SELECT IDUtente FROM dbo.Operatori)

Come mai il comportamento è strano? Perchè effettivamente in questa query c’è un errore! La tabella Operatori NON contiene il campo IDUtente, per cui, lanciato da solo, il comando

SELECT IDUtente FROM dbo.Operatori

Dà un errore di tipo:

Msg 207, Level 16, State 1, Line 1 Invalid column name 'IDUtente'.

Lo stesso errore non viene riportato se la query viene lanciata come subquery della clausola IN, bensì la query viene eseguita completamente, cancellando TUTTI i record dalla tabella Utenti, ignorando per cui la clausola WHERE IDUtente IN (…).

E’ da notare come utilizzando un nome di campo non esistente nè nella tabella Utenti, nè nella tabella Operatori la query vada in errore; per cui è da supporre che il comando non vada in errore proprio perchè il campo IDUtente compare nella tabella Utenti (cosa comunque errata).

Per ricreare il “problema” ed indagare ulteriormente, ho creato un piccolo DB con 2 tabelle, Utenti e Operatori, così strutturate:

  • Utenti
    • IDUtente [int] PK
    • Nome [varchar(50)]
  • Operatori
    • IDOperatore [int] PK
    • Nome [varchar(50)]

nella tabella Utenti ho 5 records (con IDUtente 1,2,3,4,5), mentre in quella Operatori ne ho 2 (con IDOperatore 1,2)

Questa query seleziona tutti gli utenti:

SELECT * FROM dbo.Utenti

Questa query seleziona solo gli utenti 1 e 2

SELECT * FROM dbo.Utenti WHERE IDUtente IN (SELECT IDOperatore FROM dbo.Operatori)

Questa query restituisce un errore:

SELECT IDUtente FROM dbo.Operatori

Msg 207, Level 16, State 1, Line 1 Invalid column name 'IDUtente'.

Questa query, stranamente, restituisce TUTTI gli utenti:

SELECT * FROM dbo.Utenti WHERE IDUtente IN (SELECT IDUtente FROM dbo.Operatori)

Non dovrebbe restituire tutti gli utenti, in quanto il campo IDUtente non fa parte della tabella Operatori. Altrettanto stranamente anche questa query restituisce tutti gli utenti:

SELECT * FROM dbo.Utenti WHERE IDUtente IN (SELECT IDUtente)

Per induzione, penso che SQL interpreti il campo IDUtente come campo dell’unica tabella elencata nella clausola FROM, anche se questo è a mio avviso fuorviante.

Quindi, quando fate una query di delete con una clausola IN, fate particolare attenzione ai nomi dei campi, e magari prima di eseguirla provate a fare una SELECT.

Print | posted on Wednesday, July 27, 2011 12:42 PM | Filed Under [ Tech Tips ]

Feedback

Gravatar

# re: SQL Server: strano comportamento della clausola IN

Brrr... un brivido freddo è sceso lungo la schiena! Quante volte ho eseguito query del genere: fortuna ha voluto che abbia sempre eseguito (e verificato) l'output del select prima di fare il delete.
7/27/2011 2:10 PM | Mauro Destro
Gravatar

# re: SQL Server: strano comportamento della clausola IN

@Davide: Il comportamente che vedi, può sembrare "strano" ma è corretto. Se usi gli ALIAS vedrai che tutto torna:


SELECT * FROM dbo.Utenti AS u WHERE u.IDUtente IN (SELECT u.IDUtente FROM dbo.Operatori AS o)

In pratica stai facendo una cross-join, dato che chiedi alla query più interna di restituirti un valore della tabella esterna; questo significa che la subquery restituira sempre tutti i valori presenti in u.IDUtente, e quindi la clausola IN non filtra nulla.

Se provi a mettere un campo che proprio non esiste, invece, ti viene correttamete restituito un errore.

@Gian Luca: la codebase di SQL Server non contiene più una riga di codice preso da Sybase dalla versione 2000 (se non ricordo male) :)

@Mauro: Transazioni e Database Snapshot sono li apposta per evitare brividi freddi :)
7/27/2011 4:23 PM | Davide Mauri
Gravatar

# re: SQL Server: strano comportamento della clausola IN

Un "problemino" a monte, di tipo strettamente logico, e' che la chiave primaria si dovrebbe chiamare ID, mentre IDUtente e' appropriato per una chiave esterna. In questo caso ne consegue che nella tabella Operatori ci sarebbe comunque una chiave primaria di nome IDUtente, perche' fa anche da chiave esterna verso la tabella Utenti. Queste best practice hanno a che fare in generale con la leggibilita', il che in pratica comporta meno errori...

-LV
7/27/2011 11:29 PM | LudovicoVan
Gravatar

# re: SQL Server: strano comportamento della clausola IN

P.S. Fra l'altro di solito in una query su piu' tabelle si usano gli alias e si qualificano i campi. Torniamo quindi al post di Davide che non e' solo una spiegazione ma anche un'indicazione su come scrivere codice TSQL.

-LV
7/28/2011 12:15 AM | LudovicoVan
Gravatar

# re: SQL Server: strano comportamento della clausola IN

Per finire la carrellata: tornando al problema dei nomi, e' vero che si lavora con quello che si ha, pero' quando la situazione si fa ingestibile, conviene creare un layer di viste e procedure sopra all'esistente per rinormalizzare l'accesso ai dati.

Sono indicazioni generali.

-LV
7/28/2011 12:21 AM | LudovicoVan
Gravatar

# re: SQL Server: strano comportamento della clausola IN

@LudovicoVan: ti ringrazio per le indicazioni. Spero che il post, integrato dalle "best practices" suggerite possa essere d'aiuto a qualcuno.
7/28/2011 12:27 AM | info@codesapiens.net
Gravatar

# re: SQL Server: strano comportamento della clausola IN

@davide: hai detto tutto tu! spiegazione chiara e concisa :)

@LudovicoVan: non sono del tutto daccordo che "la chiave primaria si dovrebbe chiamare ID". Non è detto, ma magari il tuo condizionale si riferisce proprio a questo, che la primary key sia un campo solo della tabella (in questo caso per ovvii motivi il campo "ID" non può esistere), oppure che la primary key non coincida con un campo che ha anche un significato diverso e preciso (facendo un esempio banale, il codice fiscale). Insomma, quello che intendo è che la primary key è un sì un attributo del record (e, più ampiamente, della tabella), ma non per forza una colonna distinta dalle colonne che esso già contiene.
Poi, io personalmente spesso tendo a preferire "chiavi surrogate" int identity (o guid dove necessario), e in questo caso il semplice nome "ID" potrebbe essere sicuramente più appropriato (anche se normalmente scelgo anche per loro un nome parlante, anche se "stupido" in quanto replica il nome della tabella..es: IdUtente), ma credo sia in ogni caso una scelta che tende a non inficiare la scrittura di codice TSQL, in particolare se, come giustamente dici, si applica la buona regola degli ALIAS.
Daccordissimo invece sul layer di normalizzazione.

Ciao
7/28/2011 1:23 AM | Francesco Milano
Gravatar

# re: SQL Server: strano comportamento della clausola IN

@ludovicovan: questa "lotta" credo esista da quando esistono i DB relazionali.
Una corrente di pensiero preferisce quelle che vengono chiamate "chiavi surrogate", e cioè un campo asettico dal contenuto del record e distinto (solitamente un campo int identity); un'altra corrente invece ritiene che la primary key, ove possibile, sia da ricercare tra le colonne stesse del record, in quanto parte delle sua informazioni rappresentano, in modo "naturale", una primary key.

In questa lotta arcaica, fatta di pro e contro (spazio disco/memoria, significato, rapidità di ricerca e confronto, ecc) di entrambi i metodi, personalmente non amo entrarci, anche se come ti ho detto prima sono in linea con il tuo pensiero, cioè quello del campo surrogato solo e distinto, "nomenclatura" a parte. Mi pareva però corretto nominare anche l'altra corrente di pensiero, cioè quella della primary key "naturale".

Poi, nota a margine: parto sempre dall'idea e approccio mentale che chi mi sta davanti ne sappia più di me: tu, come davide e come gli altri che hanno postato qui o che scrivono blog tecnici. Tra l'altro, nonstante non abbia (ancora) un blog qui su UGI.net seguo da tempo la community e ho avuto modo di apprezzare in passato alcuni tuoi interventi/commenti. Quindi non è questione di non volerti/poterti credere, era semplicemente un tentativo di instaurare un confronto che poteva essere un approfondimento su un argomento "scottante" per chi si trovasse a passare di qua :)
7/28/2011 10:46 AM | Francesco Milano
Gravatar

# re: SQL Server: strano comportamento della clausola IN

Se era una questione di opinioni o di fattori contingenti lo avrei detto, ma semplicemente non e' cosi'. In questo caso non c'e' nulla di scottante ne' alcuna lotta da fare, quelle che citavo sono best practices consolidate, punto: i tempi cambiano, le pratiche si evolvono, certi dibattiti sono semplicemente superati da tempo, se mai sono esistiti... Piuttosto c'e' tanta e sempre piu' confusione/disinformazione in giro.

-LV
7/28/2011 1:45 PM | LudovicoVan
Gravatar

# re: SQL Server: strano comportamento della clausola IN

Un po' della confusione credo derivi dal fatto che molti tendono ad applicare le "best practices" senza conoscere il perchè queste siano effettivamente "best" (e questo solitamente deriva dall'estrapolare esclusivamente il codice dagli articoli/blog senza leggere il resto - e poi dal non leggere i libri, sicuramente). Capire il perchè una strada sia realmente migliore delle altre, come nel caso che hai portato tu della chiave unica e distinta, credo aiuti poi a diffondere ancora di più delle buone abitudini di design e programmazione dei DB.
7/28/2011 5:33 PM | Francesco Milano
Gravatar

# re: SQL Server: strano comportamento della clausola IN

Ma magari fossero almeno applicate! Oggi come oggi si parla solo di stile mio contro stile tuo, et similia: con buona pace di qualunque vera conoscenza in materia. In ogni caso spiegare il perche' di una best practice non e' sempre facile o immediato perche', per definizione, sono indicazioni operative desunte da lunga e attenta pratica, un po' come chiedere ad un marinaio il perche' arrotola la cima in un modo anziche' un altro... magari manco se lo ricorda piu', ma stai sicuro che un motivo c'e' e se ti ostini a fare altrimenti prima o poi lo capirai che era meglio fare cosi', pagandone le conseguenze. Certe cose si imparano o verificano appunto solo con la pratica: sono practices, non principii...

-LV
7/28/2011 8:26 PM | LudovicoVan
Gravatar

# re: SQL Server: strano comportamento della clausola IN

@Francesco: Non mi convince... perché se fosse come scrivi, allora la select interna dovrebbe dare errore quando non è in grado di trovare il campo nella tabella referenziata dalla sua from. Se invece a quel livello lo scope delle colonne della from "padre" continua ad esistere (ed è quello che succede), quando non si usano gli alias io mi aspetterei un errore vista l'ambiguità.
7/30/2011 3:25 PM | AlessandroD
Gravatar

# re: SQL Server: strano comportamento della clausola IN

@AlessandroD: no, non dovrebbe dare errore: se il campo IDUtente dell'esempio non esiste nella SELECT più interna viene referenziato quella della SELECT più esterna, dato che è in scope (ottenendo in questo caso, come spiegava Davide, un CROSS JOIN tra i due set di dati). Viceversa, se la SELECT interna trova in scope un campo IDUtente più vicino utilizza quello per una scelta di priorità dell'engine.
Detto questo, posso essere daccordo con te che una segnalazione da interfaccia potrebbe essere utile, ma in ogni caso si dovrebbe trattare di un warning, non un errore, in quanto poi la query può effettivamente essere eseguita (anche se il risultato potrebbe non essere quello voluto da chi l'ha scritta).
Vedilo un po' come quando in C# erediti una classe e dichiari al suo interno un metodo con la stessa firma di un metodo della classe base: il compilatore segnala un warning, ma poi la compilazione viene correttamente eseguita.
Ciao
7/30/2011 8:36 PM | Francesco Milano
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET