É incrível mas tudo o que a Microsoft faz é um lixo. Não consigo lembrar de nada que me faça dizer "Nossa, eles detonaram com ____ (seu aplicativo aqui)".
Estou fazendo um job que, infelizmente, deve ser feito em SQL Server. Como não conheço muito sobre ele, estou fazendo as coisas direto em SQL, sem usar procedures, view ou outra feature mais complicada/avançada.
Em um certo momento, precisei fazer um LEFT JOIN com COUNT. O problema é que o SQL Server é tão burro que preciso definir cada campo que não faz parte da agregação na cláusula GROUP BY. Mas adivinha... Isso modifica o resultado!
Para ilustrar, veja um exemplo de "tópicos" e "respostas".
CREATE TABLE topics (
id INT(11) AUTO_INCREMENT NOT NULL,
title VARCHAR(255)
);
CREATE TABLE replies (
id INT(11) AUTO_INCREMENT NOT NULL,
topic_id INT(11)
title VARCHAR(255)
);
INSERT INTO topics (title) VALUES ('Topic #1');
INSERT INTO topics (title) VALUES ('Topic #2');
INSERT INTO replies (title, topic_id) VALUES ('Reply topic #2', 2);Se eu fizesse a seguinte seleção, deveria me retornar o tópico com a quantidade de respostas:
SELECT
topics.title AS title,
COUNT(replies.id) AS replies
FROM
topics
LEFT JOIN replies ON
topics.id = replies.topic_id
GROUP BY
replies.id;title: Topic #1, replies: 0
title: Topic #2, replies: 1Isso funciona no MySQL e provavelmente deve funcionar no PostgreSQL. Mas no MS-SQL Server recebo a seguinte mensagem:
Server: Msg 8120, Level 16, State 1, Line 1
Column 'topics.title' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.E tem gente que ainda paga caro por isso!
- Permalink
- Trackback
- Comentários (23)
- Ao som de: The Ataris – Rock 'n' Roll High School
Textos escritos por
Comentários #
Primeiramente parabenizo o seu trabalho aqui no blog.
Tente resolver o seu problema utilizando a função MAX.
Exemplo: MAX(topics.title) AS title,
Se é uma falha ou não, eu não sei, porém quando utilizo LEFT JOIN com GROUP BY sempre utilizo MAX para campos que não estão agrupados.
Um grande abraço!
Bom, apesar de não usar mais SQL Server não concordo com o que você falou,
Lembre que cada aplicação de banco de dados possui algumas diferenças com relação a forma
de execução das consultas, até onde sei, todos os campos que são inseridos em um select devem também estar contidos em um group by caso uma função de agregação seja usada.
O que pode acontecer no MySql é ele adicionar o campo automaticamente.
Mas segue ai uma nova froma de fazer a mesma consulta :) não testei ainda, mas deve funcionar.
SELECT
topics.title AS title,
(SELECT COUNT(replies.id) FROM replies WHERE replies.topic_id = topics.topics.id) AS replies
FROM
topics
Velho, se não me engano no Oracle também é assim.
@Gustavo: Obrigado pelo comentário! Vou tentar isso.
@Pretto: Entendo as particularidades de cada banco. O MySQL, por exemplo, tem o LIMIT. E deve ter mais um monte de funcionalidades que não rolam em outros DBs. O ponto é: funcionaria se eu não tivesse o campo "topics.title" no SELECT. Ou seja, a implementação existe, só não é bem feita.
Fala ai Nando !
Pois é no SQL é assim mesmo ou você coloca em uma função de agregação (COUNT, MAX, SUM, etc) ou na cláusula GROUP BY.
Já sobre o LIMIT você pode utilizar o TOP assim: SELECT TOP 10 id, description FROM tabela
Se precisar de umas ajudas com o MS SQL manda pode mandar por e-mail ou Gtalk, que eu tento te ajudar ! ;)
Até mais,
Heheh.. acabei de testar. Não funciona no PostgreSQL também.
Acho que nesse caso, o MySQL é quem tá comendo bola (ou, novamente, violando os padrões :) )
ERROR: column "topics.title" must appear in the GROUP BY clause or be used in an aggregate function
Parabens pelas matérias, mas esta query vejo um problema veja:
A sua query:
SELECT
topics.title AS title,
COUNT(replies.id) AS replies
FROM
topics
LEFT JOIN replies ON
topics.id = replies.topic_id
GROUP BY
replies.id; {Não existe o id no select por isso o erro deve ser substituido para topics.title}
A minha query:
SELECT
topics.title AS title,
COUNT(replies.id) AS replies
FROM
topics
LEFT JOIN replies ON
topics.id = replies.topic_id
GROUP BY
topics.title;
title replies
------------------ -----------
Topic #1 0
Topic #2 1
(2 row(s) affected)
Espero ter ajudado.
Clementino
É...
Mas não fale mal da M$ não.
Existe uma coisa que ela fez muuito bem: Age of Empires!!! uahuauhhuahuuhahua
Talvez seja a única coisa... mas tudo bem. =-)
[]'s
Danilo
A query demonstrada pelo Gustavo funciona tanto no MySQL quanto no PostgreSQL.
Ah, fala sério! Odeio vocês! :P
@Danilo: Nem. Jogo bom é Counter Strike, e não foi a MS quem fez! :P
@Gustavo: Não sei porque mas não está retornando todas as linhas com MAX. Anyway, resolvi de outra maneira (stored procedure).
Correndo o risco de cair numa flame war, lá vão meus dois centavos. O SQL Server, a partir da versão 7 tem evoluído muito. Eu uso desde a época do 6.5 quando a descendência com o Sybase ainda era mais óbvia (e o pacote todo era bastante inflexível). Atualmente acho que ele é um banco bastante razoável. No fundo, não existe nenhum fabricante de banco de dados que siga o padrão ANSI ou ISO, seja 92, 99, 2003.
Exemplos disso é poder PL/SQL, Java no Oracle, T-SQL no SQL Server, Perl ou mesmo C no PostgreSQL. Os tipos de dados costumam ser bem diferentes, as funções são diferentes e assim por diante. O máximo que podemos fazer é se apoiar em alguma camada de abstração (Hibernate, iBatis, ActiveRecord) e cruzar os dedos.
Eu mesmo gosto muito do MySQL, já usei em produção algumas vezes. O Oracle - apesar de toda sua reputação - ainda não me faz sentir confortável (mas aí deve ser minha culpa por não conhecer mais dos seus detalhes). O SQL Server vem servindo um banco de produção pequeno (alguns gigabytes de datatiles, mais de 3 mil usuários) nos últimos 3 anos, sem nenhum down time acidental e continua firme e forte (felizmente).
Não sou fã da MS (todos sabem que sou evangelista Apple) mas existem bons produtos, além disso outras empresas também fazem vários produtos muito ruins. Mesmo o MySQL e PostgreSQL apesar bons, ainda tem diversas pendências. Nossa única saída: escolher a dedo quando der, caso a caso, ou se adaptar ao ambiente.
@Akita: Concordo com você sobre os padrões. Eu, definitivamente, não gosto de MS. Fato. SQL Server? Sempre fugi dele. Desta vez não deu. Não sou DBA. Outro fato. Por isso, me sinto confortável com o MySQL: ele é simples e sempre serviu aos meus propósitos.
Posso ter pegado pesado ao dizer que o SQL Server não presta? Claro! Não conheço seus recursos a fundo. Mas pelo pouco que mexi, odiei!
"Nossa, eles detonaram com ____ (seu aplicativo aqui)".
"Nossa, eles detonaram com o NOTEPAD".
kakak
o unico aplicativo deles q não teve falha remota(ainda)
8)
SQL-ANSI diz que:
Todas as colunas que não possuem funções agregadas devem estar em um GROUP BY.
usar MAX pode ser util se for retornar apenas a maior... o correto seria usar DISTINCT apenas para remover repetições...
E por incrivel que pareça a MS tem ótimos produtos, não devemos taxar tudo de ruim apenas por não ser SL, alguem já viu algum programa melhor q o Project??? ou o VISIO? tem vários ai que tentam fazer parecido.. mas não funcionam...
Deviamos deixar as preferencias de lado. A Cézar o que é de Cézar!!!
O MySql nem implementava integridade referencial ate a versao anterior :S
Vamos analizar antes de formar nossas opiniões... :)
Nada se compara ao Oracle, porém tudo tem sua aplicação :)
Abraços
Seguir o padrão Sql-ANSI você não terá problema nenhum, independente de qualquer banco
'a microsoft nao presta, bla, bla bla...' sempre essa ladainha...
no Oracle tb eh assim: as clausulas tem que estar no group by. Como o colega falou, isso eh SQL ANSI.
Ola pessoal...
Estava exatamente com o mesmo problema que o Nando Vieira e quando li o titulo do post, cheguei a mostrar para meu superior e comentar "Viu! Nao eh soh eu que falo isso..." hehe.
Mas pesquisando um pouco mais acabei por entender o que acontece realmente pro traz deste problema e descobri que estamos errados na nossa maneira de pensar!
Um ponto para MS!
O ponto aqui eh que tentar agrupar colunas nao agrupaveis eh uma acao ilogica!
O que o MySQL retorna eh um resultado no mino sem sentido nalgum, pois ele escolhe o valor dos demais campos que nao o da coluna de agrupamento, aleatoriamente!
Isso as vezes pode parecer pratico, mas eh no minimo inconssistente.
No helper do Enterprise Manager da MS, se tu pesquisar por grouping rows tu vais ver uma certa explicacao comentando sobre isso.
E quanto ao fato do SQL Server trazer resultados "errados" quando tu coloca todos os campos, isso nao eh verdade. Quando tu coloca todos os campos ele agrupa por todos, o que retorna um result set com tudo, afinal eh isso mesmo que tu pediu (soh que co o group by fica tudo organizadinho, hehe).
Abaixo vou postar uma parte do texto do helpper da MS:
ColumnA | ColumnB | ColumnC
--------+---------+-------
1 | abc | 5
1 | def | 4
1 | ghi | 9
2 | jkl | 8
2 | mno | 3
If ColumnA is the grouping column, there will be two rows in the result set, one summarizing the information for the value 1, and the other summarizing the information for value 2.
When ColumnA is the grouping column, the only way ColumnB or ColumnC can be referenced is if they are parameters in an aggregate function that can return a single value for each value in ColumnA. It is legal for the select list to include expressions such as MAX(ColumnB), SUM(ColumnC), or AVG(ColumnC):
SELECT ColumnA,
MAX(ColumnB) AS MaxB,
SUM(ColumnC) AS SumC
FROM TableX
GROUP BY ColumnA
This select returns two rows, one for each unique value in ColumnA:
ColumnA | MaxB | SumC
--------+--------+-------
1 | ghi | 18
2 | mno | 11
(2 row(s) affected)
It is not legal, however, to have just the expression ColumnB in the select list:
SELECT ColumnA,
ColumnB,
SUM(ColumnC) AS SumC
FROM TableX
GROUP BY ColumnA
Because the GROUP BY can return only one row with a value of 1 in ColumnA, there is no way to return the three values of ColumnB (abc, def, and ghi) associated with the value 1 in ColumnA.
Tche... depois da teoria aih acima... eu acabei pensando num hackzinho...
quando os demais campos desejados sao numeros, tu pode utilizar a funcao soma e somar o resultado com zero...
SELECT collumn_01, SUM(collumn_02 + 0 ) FROM table_name GROUP BY collumn_01 ;
Imagino que se alguem tiver uma FUNCAO que trabalhe sobre String fazendo nada, deve funfar tbm...
Ou seja, ISSO EH UMA GAMBIARRA!
OBS.: experimentei usar o CAST (pq seria a maneira mais facil fazer cast pro proprio tipo como por exemplo cast de um varchar para varchar, etc) mas nao funfa.. eh uma pena... afinal essa seria uma solucao facil de ser usada em Adapters de queries :(
Bem... quem manda querer fazer gambiarra!
Semana Complicada...
Já tiveram uma semana complicada, de bug atrás de bug? Os meus últimos dias da semana passada foram assim.
Eu e o Ricardo pegamos a missão de passar um certo trecho de código de Delphi para PHP. Como nem eu nem ele lembramos muito de Delphi, e ...
Sem comentários,
só lamento....
Acho que devemos ter mais conceito antes de criticar..
André Hass Microsoft MVP Windows Server System - SQL Server
Já dizia Zaratrusta, o homem tem pavor do desconhecido, ele demonstra sua ignorancia com gestos e palavras hostis.
.É impressionante como o desconhecimento sobre a linguagem torna esses comentário
absurdo, acho companheiro que você deve estudar um pouco mais sobre linguagem sql!!!!!
Este problema já é cultural, não sabe usar a ferramenta e já sai dizendo que ele é ruim, imagino como a ferramenta pode ser ruim a ponto de dar suporte a uma empresa do porte da Xerox e não conseguir satisfazer uma simples consulta, que estava errada!