Gebruik naam van AS in Where

[Verwijderd]

Hallo, ik heb een probleem met een query. Zie voorbeeld :

code:

SELECT
     IFNULL(assigned, 0) AS new_assigned,
     requested
FROM
     tabel
WHERE
     requested > new_assigned;

Schijnbaar kan ik dus geen naam in een WHERE gebruiken waar de naam is toegewezen door een AS, in dit geval dus new_assigned.

Weet iemand hoe ik dit kan doen ? want nu krijg ik de error "Unknown column".

Alvast bedankt

[PowerSp00n]

having...

[.oisyn]

...mag niet zonder GROUP BY als het geen aggregate function betreft. IFNULL() is geen aggregate function.

@wavetb: Wat is er mis met simpelweg opnieuw die IFNULL() in de WHERE stoppen?

[ Voor 28% gewijzigd door .oisyn op 28-05-2009 15:02 ]

[P_de_B]

Gewoon het statement voor de 'AS' herhalen:

SQL:

SELECT
     IFNULL(assigned, 0) AS new_assigned,
     requested
FROM
     tabel
WHERE
     requested > IFNULL(assigned, 0) ;

[Wolfboy]

Met P_de_B, alleen in HAVING kan je dit soort dingen doen omdat dit na afloop wordt uitgevoerd. Tijdens het uitvoeren van de WHERE zijn de aliasen en dergelijken nog niet beschikbaar omdat dit niet nodig is.

Als dat wel zo was dan zou de database dus voor _alle_ rijen de functies moeten uitvoeren terwijl die IFNULL misschien maar voor een paar items uitgevoerd hoeft te worden.

[Verwijderd]

Dankje, alleen id it voorbeeld heb ik maar een WHERE waar HAVING inderdaad een goede vervanger voor is zie ik.

Hoe los ik het op als ik dit heb ?

code:

SELECT
     IFNULL(assigned, 0) AS new_assigned,
     requested
     c1
     c2
     c4
     c5

FROM
     tabel
WHERE
     requested > new_assigned
AND
     c1 = 'negative''
AND 
     c2 < 10
ORDER BY
     c3;

Want als ik dit doe :

code:

SELECT
     IFNULL(assigned, 0) AS new_assigned,
     requested
     c1
     c2
     c4
     c5

FROM
     tabel
HAVING
     requested > new_assigned
WHERE
     c1 = 'negative''
AND 
     c2 < 10
ORDER BY
     c3;

dan krijg ik een error

[.oisyn]

Wolfboy schreef op donderdag 28 mei 2009 @ 15:05:
Met P_de_B, alleen in HAVING kan je dit soort dingen doen omdat dit na afloop wordt uitgevoerd.

En ook alleen in MySQL.

Verwijderd schreef op donderdag 28 mei 2009 @ 15:08:
Dankje, alleen id it voorbeeld heb ik maar een WHERE waar HAVING inderdaad een goede vervanger voor is zie ik.

nee, Nee, NEE! HAVING is geen goede vervanging voor WHERE. Heb je überhaupt gelezen wat zojuist gezegd is? 't Is altijd jammer dat de eerste oplossing in een draad hier op GoT altijd meteen zo'n prutsoplossing bevat.

dan krijg ik een error

Duh, lees de reacties en de documentatie van je RDBMS.

Doe het gewoon zo:

SQL:

SELECT
     IFNULL(assigned, 0) AS new_assigned,
     requested
     c1
     c2
     c4
     c5

FROM
     tabel
WHERE
     requested > IFNULL(assigned, 0) AND
     c1 = 'negative' AND 
     c2 < 10
ORDER BY
     c3;

Wat is daar nou precies mis mee?

.edit @ hieronder:

ja ik was bezig met typen dus had het nog neit gezien.

Vooruit, het is je vergeven

[ Voor 77% gewijzigd door .oisyn op 28-05-2009 15:16 ]

[Verwijderd]

ja ik was bezig met typen dus had het nog neit gezien. in dit geval werkt het door de IFNULL gewoon in de where te zetten.

[PowerSp00n]

.oisyn schreef op donderdag 28 mei 2009 @ 15:11:
nee, Nee, NEE! HAVING is geen goede vervanging voor WHERE. Heb je überhaupt gelezen wat zojuist gezegd is? 't Is altijd jammer dat de eerste oplossing in een draad hier op GoT altijd meteen zo'n prutsoplossing bevat.

Toegegeven dat het een beetje voorbarige reactie was, lees ik nu dat having inderdaad voor aggregate functions is. Fijn dat je met de 'mogelijkheden' in MySQL wel eens foute dingen kan leren...

[RobertMe]

Wat uitleg: Een van de eerste dingen die je database doet is de WHERE evalueren, het eerste leest ie alle tabellen en de joins, en dan gaat ie al meteen de WHERE doen, dan komt GROUP BY, dan de SELECT, dan HAVING en dan pas LIMIT en OFFSET. Doordat de WHERE word geevalueerd voordat de SELECT word geevalueerd, is de alias dus nog helemaal niet bekend.

En HAVING is zoals gezegd geen oplossing, omdat die alleen bij GROUP BY hoord, zodat je nog HAVING MAX(...) > 10 of zo kan doen, maar dus niet om nog iets met je aliassen te doen.

[Haan]

PowerSp00n schreef op donderdag 28 mei 2009 @ 15:29:
[...]


Toegegeven dat het een beetje voorbarige reactie was, lees ik nu dat having inderdaad voor aggregate functions is. Fijn dat je met de 'mogelijkheden' in MySQL wel eens foute dingen kan leren...

It's not a bug, it's a feature

[RobertMe]

Haan schreef op donderdag 28 mei 2009 @ 15:33:
[...]

It's not a bug, it's a feature

Dan heeft MySQL wel veel unieke features Is het een compleet op zichzelf staand product, kun je het geen RDMS meer noemen

[.oisyn]

PowerSp00n schreef op donderdag 28 mei 2009 @ 15:29:
Fijn dat je met de 'mogelijkheden' in MySQL wel eens foute dingen kan leren...

Idd. Maar pak MySQL er nu eens bij, met een (liefst beetje grote) tabel met een id als primary key, en vergelijk dan de volgende outputs:

SQL:

DESCRIBE SELECT id FROM tabel WHERE id = 23
DESCRIBE SELECT id FROM tabel HAVING id = 23

http://dev.mysql.com/doc/...up-by-hidden-columns.html

[ Voor 7% gewijzigd door .oisyn op 28-05-2009 15:37 ]

[PowerSp00n]

.oisyn schreef op donderdag 28 mei 2009 @ 15:35:
Idd. Maar pak MySQL er nu eens bij, met een (liefst beetje grote) tabel met een id als primary key, en vergelijk dan de volgende outputs:

SQL:

DESCRIBE SELECT id FROM tabel WHERE id = 23
DESCRIBE SELECT id FROM tabel HAVING id = 23

Uiteraard, dat is geen goed idee. Ik was echter in de veronderstelling dat in bovenstaande situatie van de TS having de oplossing was, niet echt dus ...

Haan schreef op donderdag 28 mei 2009 @ 15:33:
It's not a bug, it's a feature

Volgens de docs wel inderdaad

The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and allows HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.

[Wolfboy]

.oisyn schreef op donderdag 28 mei 2009 @ 15:11:
[...]

En ook alleen in MySQL.

Inderdaad. De andere databases hebben optimizers met enige intelligentie... Het is me meer dan eens overkomen dat MySQL wegens wazig index gebruik de ene keer 3 minuten en de andere keer 1 seconde bezig is met een query.

Waarom kiezen er niet meer mensen voor iets als Postgres in plaats van MySQL...

[cariolive23]

Zorg er met MySQL wel voor dat je 3 keer controleert met welke configuratie settings je een query uitvoert. Deze settings zijn door iedere database user op ieder moment te wijzigen en kunnen enorme invloed hebben op het resultaat van de query. Wat de ene keer resultaat X oplevert, kan de andere keer zomaar resultaat Y opleveren. Of zelfs een foutmelding, maar daar is MySQL niet zo goed in.

Eigenlijk moet je per setting een eigen query opstellen of bij ongewenste settings eerst weer de juiste settings gaan instellen. En dat bij iedere query weer opnieuw.... Ik blijf het bijzonder vinden dat mensen zoveel tijd (en dus geld) blijven uitgeven aan het werken met MySQL.

I.p.v. IFNULL kun je ook COALESCE() gebruiken, werkt ook in andere databases.

[Cousin Boneless]

SQL:

SELECT
     IFNULL(assigned, 0) AS new_assigned,
     requested
FROM
     tabel
WHERE
     requested > new_assigned;

Schijnbaar kan ik dus geen naam in een WHERE gebruiken waar de naam is toegewezen door een AS, in dit geval dus new_assigned.

Weet iemand hoe ik dit kan doen ? want nu krijg ik de error "Unknown column".

Dit is best te doen met een WHERE, maar dan moet je er even een inline view van maken:

SQL:

select t.new_assigned
from (
    select ifnull(assigned, 0) as new_assigned, requested
    from tabel
) t
where t.requested > t.new_assigned

[cariolive23]

Fraaie query maar ik vraag me wel af of MySQL nu nog van een index gebruik kan maken. Een index op assigned gaat in elk geval niet werken en een index op new_assigned bestaat niet. Dat is tenslotte het resultaat van een bewerking in een subquery.

Ik verwacht dat dit een relatief langzame query is, zeker wanneer er veel data in de tabel staat. Een sequential scan word je niet vrolijk van op een flinke hap data.

[Vozze]

Cousin Boneless schreef op vrijdag 29 mei 2009 @ 13:11:
[...]


Dit is best te doen met een WHERE, maar dan moet je er even een inline view van maken:

SQL:

select t.new_assigned
from (
    select ifnull(assigned, 0) as new_assigned, requested
    from tabel
) t
where t.requested > t.new_assigned

De oplossing van .oysin en P_de_B gebruikt ook een WHERE-clausule die in het geval van de TS prima volstaat.
Waarom zou je in vredesnaam een subselect gaan gebruiken om alleen maar een alias te kunnen gebruiken in je WHERE-clausule?

[RobertMe]

cariolive23 schreef op vrijdag 29 mei 2009 @ 15:17:
Ik verwacht dat dit een relatief langzame query is, zeker wanneer er veel data in de tabel staat. Een sequential scan word je niet vrolijk van op een flinke hap data.

Als het een echt snelle query moet zijn, zou je het beste natuurlijk een functionele index aan kunnen maken, maarja we hebben het hier over MySQL die weer geen functionele indexen ondersteund

[cariolive23]

@RobertMe: Dat MySQL beperkt is, dat is bekend, maar geen enkele database kan uit de voeten met slechte queries. En dat is wat er in het voorbeeld van Cousin Boneless aan de hand is, de query is zo geschreven dat er geen (functionele) index (meer) aanwezig is wanneer de WHERE wordt toegepast. En daar kan geen enkele functionele index wat aan veranderen. Wat er niet is, dat is er niet.

Een snelle query begint bij een goede en vooral slimme query waarbij je gebruik kunt maken van de beschikbare indexen. Wanneer je een query schrijft waarbij je bij voorbaat al weet dat er geen indexen kunnen worden gebruikt, dan weet je dat je een probleem hebt. En uiteraard ga je met EXPLAIN aan de slag om te zien hoe de query nu daadwerkelijk door de database wordt uitgevoerd.

[RobertMe]

cariolive23 schreef op vrijdag 29 mei 2009 @ 16:02:
@RobertMe: Dat MySQL beperkt is, dat is bekend, maar geen enkele database kan uit de voeten met slechte queries. En dat is wat er in het voorbeeld van Cousin Boneless aan de hand is, de query is zo geschreven dat er geen (functionele) index (meer) aanwezig is wanneer de WHERE wordt toegepast. En daar kan geen enkele functionele index wat aan veranderen. Wat er niet is, dat is er niet.

Een snelle query begint bij een goede en vooral slimme query waarbij je gebruik kunt maken van de beschikbare indexen. Wanneer je een query schrijft waarbij je bij voorbaat al weet dat er geen indexen kunnen worden gebruikt, dan weet je dat je een probleem hebt. En uiteraard ga je met EXPLAIN aan de slag om te zien hoe de query nu daadwerkelijk door de database wordt uitgevoerd.

Dat snap ik, ik doelde dan ook op een functionele index op die IFNULL(assigned, 0). En hopelijk doet MySQL dan sowieos maar die IFNULL een keer berekenen, voor de WHERE, en dat ie in de SELECT nog bekend is.

[Cousin Boneless]

De oplossing van .oysin en P_de_B gebruikt ook een WHERE-clausule die in het geval van de TS prima volstaat.
Waarom zou je in vredesnaam een subselect gaan gebruiken om alleen maar een alias te kunnen gebruiken in je WHERE-clausule?

Omdat het misschien een beetje triviaal voorbeeld is lijkt dit niet zo'n bezwaar, maar als je bijvoorbeeld een Pythagoras functie hebt en daar een filter overheen haalt en het resultaat in volgorde van afstand terug wil hebben, dan wil je echt niet 3 keer dezelfde formule terugzien. Niet leesbaar en niet te onderhouden.

[ Voor 14% gewijzigd door Cousin Boneless op 30-05-2009 12:13 ]

[.oisyn]

RobertMe schreef op vrijdag 29 mei 2009 @ 16:12:
En hopelijk doet MySQL dan sowieos maar die IFNULL een keer berekenen

Nou dat zal echt geen zak uitmaken hoor. Over wat voor "berekening" heb je het nou eigenlijk, gewoon even controleren of ie NULL is of niet.

Bovendien verneukt die IFNULL in z'n geheel niet het gebruik van een index.

SQL:

DESCRIBE SELECT a, b FROM testtable WHERE a>IFNULL(b,0)

1	SIMPLE	testtable	index	NULL	a	10	NULL	2	Using where; Using index

MySQL 5.nogiets, met een index op (a, b)

Of je (a,b) of (b,a) als index gebruikt hangt een beetje van je usecase af. Feitelijk wil je als eerst de kolom die het minst uniek is, omdat er een volledige scan over alle unieke waardes plaatsvindt. Pas naarna kunnen er, per unieke waarde, waarden uit de andere kolom worden gefilterd adhv de index.

Een functionele index zou hier handig zijn. Als dat niet kan kun je er ook voor kiezen om een extra kolom c aan te maken die altijd de waarde a-IFNULL(b,0) heeft. Vervolgens kun je filteren op WHERE c > 0.

[ Voor 32% gewijzigd door .oisyn op 29-05-2009 17:44 ]

[cariolive23]

Bovendien verneukt die IFNULL in z'n geheel niet het gebruik van een index.

Niet in de query die je hier benoemt, wel in de query die gebruik maakt van een subquery in de FROM. Andere query, andere situatie.

Kortom, voor iedere query die je uitvoert en iedere aanpassing die je op een query maakt, weer opnieuw met EXPLAIN aan de slag. Het is de enige manier om te zien hoe een query nu wordt uitgevoerd.

[.oisyn]

cariolive23 schreef op vrijdag 29 mei 2009 @ 17:55:
[...]

Niet in de query die je hier benoemt, wel in de query die gebruik maakt van een subquery in de FROM.

Die andere query heeft dan ook geen IFNULL in de WHERE, dus daar had ik het sowieso niet over

[Cousin Boneless]

Hopelijk iets waar binnenkort niet meer over hoeft worden nagedacht.. leek me nogal een basale optimalisatie. Maar kennelijk toch niet helemaal:
http://assets.en.oreilly....%206_0%20Presentation.pdf (pagina 33)
http://lists.mysql.com/internals/35705

Volgens mij ging SQLServer hier al in de vorige eeuw veel slimmer mee om. Maar die is dan ook al wat langer in ontwikkeling.

[XWB]

Wolfboy schreef op donderdag 28 mei 2009 @ 16:02:
[...]
Inderdaad. De andere databases hebben optimizers met enige intelligentie... Het is me meer dan eens overkomen dat MySQL wegens wazig index gebruik de ene keer 3 minuten en de andere keer 1 seconde bezig is met een query.

Ook met subquerys is MySQL erg vaag; x aantal losse querys doen er vaak veel sneller over dan x aantal subquerys. Nouja, in MySQL 5.4 schijnt dit weer een stuk beter te zijn.