¿Alguien me puede decir qué está mal en esta consulta?

[qvixote]

Les cuento que en mi trabajo (un colegio) hace tiempo se instaló Claroline 1.7.0 y sobre él se comenzó a desarrollar otros módulos que tienen que ver con la gestión del colegio más que con el trabajo en sala (Claroline es una plataforma de aprendizaje a distancia).

El problema comienza cuando nos falla el servidor y armamos otro nuevo, pero este con PHP5 a diferencia del anterior que tenía PHP4. También actualizamos las versiones de MySQL, pero en este momento no podría poner las versiones.

Bueno, el resultado fue que algunas partes de claroline dejaron de funcionar, y buscando y buscando me tenido que hacer algunas modificaciones en las consultas, más que nada cambios de orden, y han funcionado, pero hay una consulta que me está haciendo la vida imposible y no puedo entender por qué MySQL me dice que está mal.

La consulta es la siguiente:

Código:

SELECT `U`.`user_id` AS `authId`,
  CONCAT(`U`.`nom`, ' ', `U`.`prenom`) AS `name`,
  COUNT(`S`.`id`) AS `submissionCount`,
  `S`.`title`,
  MIN(`S`.`creation_date`)

FROM `AcaDB`.`user` as `U`,
  `AcaDB`.`cours_user` as `CU`

LEFT JOIN `No2008287`.`wrk_submission` AS `S` ON `S`.`user_id` = `U`.`user_id`
  AND ( `S`.`assignment_id` = 1 OR `S`.`assignment_id` IS NULL )
  AND `S`.`original_id` IS NULL WHERE `U`.`user_id` = `CU`.`user_id`
  AND `CU`.`code_cours` = '2008287'

GROUP BY `U`.`user_id`

ORDER BY `CU`.`statut` ASC, `CU`.`tutor` DESC, `U`.`nom` ASC, `U`.`prenom` ASC

Y el error que me da es el siguiente:

Código:

#1054 - Unknown column 'U.user_id' in 'on clause'

Como si la columna no existiera, pero si existe. La estructura de la tabla que se supone da el conflicto es la siguiente:

Código:

	user_id  	int(11)
	nom 	varchar(60) 	
	prenom 	varchar(60) 
	username 	varchar(20) 	
	password 	varchar(50) 	
	authSource 	varchar(50) 	
	email 	varchar(100) 	
	statut 	tinyint(4) 		
	vida 	tinyint(4) 	
	officialCode 	varchar(40) 	
	phoneNumber 	varchar(30) 	
	pictureUri 	varchar(250) 
	creatorId 	int(11) 	
	dir 	varchar(255)
	maildir 	varchar(255)

¿Alguien me podría decir qué es lo que está mal en esa consulta? Porque en el otro servidor funcionaba sin problemas, pero acá no.

Me despido agradeciendo de antemano cualquier ayuda que puedan prestarme.

[gnzsoloyo]

El hecho de que en un server funcionara y en otro no puede ser por un conflicto de versiones. Habría que ver qué versión de MySQL está corriendo en cada uno.
En cuanto a la consulta en sí, yo la encuentro personalmente confusa. Me parece que estás cruzando segmentos que MySQL no puede resolver adecuadamente.
Para que se entienda mejor, un segmento de condicionales como:

Código:

 AND  (`S`.`assignment_id` = 1  OR  `S`.`assignment_id` IS NULL )
AND  `S`.`original_id` IS NULL

no pertenece al predicado de un LEFT JOIN ... ON, sino a un WHERE.
Esto es, un JOIN establece una relación entre valores de dos campos de dos tablas distintas, condición que se determina en el ON.
El segmento que escribiste no determina ningua relación entre las dos, por lo que MySQL debe estar haciendo otra cosa y entremezclandola con el ON. Es posible que esté tomando 'U.user_id' como alias o como tabla de origen de otro valor, por lo que no puede resolverlo y lo declara desconocido (estoy hipotetizando, nunca vi una consulta escrita así en todos estos años).
Mi sugerencia es que cambies de lugar ese segmento y lo pongas donde tendría uso lógico: en el WHERE, por ejemplo:

Código:

SELECT `U`.`user_id` AS `authId`, 
       CONCAT(`U`.`nom`, ' ', `U`.`prenom`) AS `name`,
	COUNT(`S`.`id`) AS `submissionCount`, `S`.`title`, 
       MIN(`S`.`creation_date`)
FROM `AcaDB`.`user` as `U`, `AcaDB`.`cours_user` as `CU`
LEFT JOIN  `No2008287`.`wrk_submission` AS `S` 
ON  `S`.`user_id` = `U`.`user_id`
WHERE `U`.`user_id` = `CU`.`user_id` 
     AND `CU`.`code_cours` = '2008287'
     AND (`S`.`assignment_id` = 1  OR  `S`.`assignment_id` IS NULL)
     AND `S`.`original_id` IS NULL 
GROUP BY  `U`.`user_id`
ORDER BY `CU`.`statut` ASC, `CU`.`tutor` DESC, 
     `U`.`nom` ASC, `U`.`prenom` ASC

[lucasarts_18]

Iniciado por gnzsoloyo

En cuanto a la consulta en sí, yo la encuentro personalmente confusa. Me parece que estás cruzando segmentos que MySQL no puede resolver adecuadamente.
Para que se entienda mejor, un segmento de condicionales como:

Código:

 AND  (`S`.`assignment_id` = 1  OR  `S`.`assignment_id` IS NULL )
AND  `S`.`original_id` IS NULL

no pertenece al predicado de un LEFT JOIN ... ON, sino a un WHERE.

Hola, esto me es correcto, tiene el mismo funcionamiento tanto en el where como en el join , funciona para en ambas partes, no así si da lo mismo en cuanto a rendimiento, si es filtrado podría ir mejor en el where.

Por otra parte lo que si me parece extraño, y creo que no se puede usar as en el join para darle un alias a la tabla, yo te recomiendo como dice nuestro compañero pasar la parte de filtrado al where y no usar alias, pon el nombre completo de la tabla.

Haz las pruebas y nos cuenta que tal.

Hasta Luego .-

[qvixote]

Hola!

Gracias por las respuestas.

Las versiones de mysql-server del servidor antiguo, donde si funcionaba la consulta, y del servidor nuevo donde no funciona son 4.0.21 y 5.0.45 respectivamente.

La consulta no la hice yo, la hizo la comunidad de desarrollo de Claroline.

Haré la prueba modificando la consulta como me recomiendan y veremos cómo me va.

¡Muchas gracias!

[qvixote]

Acabo de probar la consulta con los cambios y me sigue dando el mismo error.

[quimfv]

Creo que el problema lo puedes tener en que el LEFT JOIN se esta haciendo entre
`AcaDB`.`cours_user` as `CU`y `No2008287`.`wrk_submission` AS `S` y NO sobre (`AcaDB`.`user` as `U`, `AcaDB`.`cours_user` as `CU`) por eso da error la clausula ON ya que esta pertenece al LEFT JOIN. Deberias definir la relacion de `AcaDB`.`user` as `U` con las otras tablas.

Código:

FROM `AcaDB`.`user` as `U`,
  `AcaDB`.`cours_user` as `CU`

LEFT JOIN `No2008287`.`wrk_submission` AS `S` ON `S`.`user_id` = `U`.`user_id`
  AND ( `S`.`assignment_id` = 1 OR `S`.`assignment_id` IS NULL )
  AND `S`.`original_id` IS NULL WHERE `U`.`user_id` = `CU`.`user_id`
  AND `CU`.`code_cours` = '2008287'

intenta esto

....

Código:

....

FROM (`AcaDB`.`user` as `U` 
                    JOIN  `AcaDB`.`cours_user` as `CU` 
                             ON `U`.`user_id` = `CU`.`user_id`)  
             LEFT JOIN `No2008287`.`wrk_submission` AS `S` 
                          ON `S`.`user_id` = `U`.`user_id`
  WHERE  (`S`.`assignment_id` = 1 
                         OR `S`.`assignment_id` IS NULL )  
               AND `S`.`original_id` IS NULL  
               AND `CU`.`code_cours` = '2008287'

JOIN tu sabras si debe ser JOIN o LEFT JOIN.


Quim

[qvixote]

¡Eureka!

¡Muchas gracias, Quim!

Tenían todos razón del lugar del problema. Ahora he corregido la consulta de la firma que Quim me recomendó y todo sale muy bien, ya no tengo problemas. Ahora me toca buscar todas las partes del sistema donde haya un error similar y corregirlo. Ahora sé cómo.


Muchas gracias a todos, nuevamente.

[qvixote]

Como comentario final, he descubierto lo que realmente faltaba: paréntesis en el FROM.

Al aplicar la correción que me propuso Quim funcionó perfectamente, pero en verdad la primera consulta no estaba mal, sólo le faltaba encerrar entre paréntesis lo que estaba después del FROM y antes de los LEFT JOIN. Así, la consulta de esta forma:

Código:

SELECT `U`.`user_id` AS `authId`,
  CONCAT(`U`.`nom`, ' ', `U`.`prenom`) AS `name`,
  COUNT(`S`.`id`) AS `submissionCount`,
  `S`.`title`,
  MIN(`S`.`creation_date`)

FROM (`AcaDB`.`user` as `U`, `AcaDB`.`cours_user` as `CU`)

LEFT JOIN `No2008287`.`wrk_submission` AS `S` ON `S`.`user_id` = `U`.`user_id`
  AND ( `S`.`assignment_id` = 1 OR `S`.`assignment_id` IS NULL )
  AND `S`.`original_id` IS NULL WHERE `U`.`user_id` = `CU`.`user_id`
  AND `CU`.`code_cours` = '2008287'

GROUP BY `U`.`user_id`

ORDER BY `CU`.`statut` ASC, `CU`.`tutor` DESC, `U`.`nom` ASC, `U`.`prenom` ASC

funciona sin problemas. Eso debe ser una exigencia de MySQL 5. A tenerlo en cuenta para consultas futuras.

Saludos.