MySQL Forums
Forum List  »  Optimizer & Parser

Field calculated using the WHERE
Posted by: Michael Silva
Date: January 13, 2011 03:22PM

I need help
Before it was possible to run the query below usually calculated in MySQL 5.0.41-community,
after a few above this version is no more possible.

Running the following tests in MySQL 5.0.41

Creating the sample tables:
**********************************************************

CREATE TABLE `viagem` (
`ID` int(10) unsigned NOT NULL auto_increment,
`DataSaida` date NOT NULL,
`HoraSaida` time NOT NULL,
`Confirmado` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `viagemsituacao` (
`id` int(10) unsigned NOT NULL auto_increment,
`Viagem_ID` int(10) unsigned NOT NULL,
`Situacao` enum('Normal','Bloqueada','Cancelada','Concluída') NOT NULL,
`Obs` varchar(100) NOT NULL,
`DataSituacao` date NOT NULL,
`HoraSituacao` time NOT NULL,
PRIMARY KEY (`id`),
KEY `Viagem` (`Viagem_ID`),
CONSTRAINT `FK_ViagemSituacao_Viagem` FOREIGN KEY (`Viagem_ID`) REFERENCES `viagem` (`ID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `viagem` (`ID`, `DataSaida`, `HoraSaida`, `Confirmado`) VALUES
(1,'2011-01-13','13:30:00',1),
(2,'2011-01-13','13:45:00',1),
(3,'2011-01-13','14:15:00',1),
(4,'2011-01-13','15:30:00',1),
(5,'2011-01-13','16:15:00',1),
(6,'2011-01-13','17:00:00',1);

COMMIT;

INSERT INTO `viagemsituacao` (`id`, `Viagem_ID`, `Situacao`, `Obs`, `DataSituacao`, `HoraSituacao`) VALUES
(1,1,'Normal','Normal','2011-01-13','01:00:00'),
(2,2,'Bloqueada','Bloqueada','2011-01-13','01:00:00'),
(3,3,'Cancelada','Cancelada','2011-01-13','01:00:00'),
(4,4,'Concluída','Concluída','2011-01-13','01:00:00'),
(5,5,'Normal','Normal','2011-01-13','01:00:00'),
(6,6,'Normal','Normal','2011-01-13','01:00:00');

COMMIT;



See the following query:
**********************************************************
FIRST TEST (Assuming zero):
**********************************************************
SELECT @DataViagem := "2011-01-13";
SELECT @IncluirBloqueado := 0; -- If you want to include the line of the calculated field, use 1 else 0.
SELECT @IncluirCancelado := 0; -- If you want to include the line of the calculated field, use 1 else 0.
SELECT @IncluirConcluido := 0; -- If you want to include the line of the calculated field, use 1 else 0.

SELECT
Viagem.Id,
Viagem.DataSaida,
Viagem.HoraSaida,
(SELECT Situacao FROM ViagemSituacao
WHERE Viagem_Id = Viagem.Id
ORDER BY Id DESC LIMIT 1) AS Viagem_Situacao
FROM Viagem
WHERE Viagem.Confirmado
AND Viagem.DataSaida = @DataViagem
AND (@IncluirBloqueado OR (SELECT Viagem_Situacao) <> "Bloqueada")
AND (@IncluirCancelado OR (SELECT Viagem_Situacao) <> "Cancelada")
AND (@IncluirConcluido OR (SELECT Viagem_Situacao) <> "Concluída")

Results: 3 rows fetched.



See the following query:
**********************************************************
FIRST TEST (Assuming one):
**********************************************************
SELECT @DataViagem := "2011-01-13";
SELECT @IncluirBloqueado := 1; -- If you want to include the line of the calculated field, use 1 else 0.
SELECT @IncluirCancelado := 1; -- If you want to include the line of the calculated field, use 1 else 0.
SELECT @IncluirConcluido := 1; -- If you want to include the line of the calculated field, use 1 else 0.

SELECT
Viagem.Id,
Viagem.DataSaida,
Viagem.HoraSaida,
(SELECT Situacao FROM ViagemSituacao
WHERE Viagem_Id = Viagem.Id
ORDER BY Id DESC LIMIT 1) AS Viagem_Situacao
FROM Viagem
WHERE Viagem.Confirmado
AND Viagem.DataSaida = @DataViagem
AND (@IncluirBloqueado OR (SELECT Viagem_Situacao) <> "Bloqueada")
AND (@IncluirCancelado OR (SELECT Viagem_Situacao) <> "Cancelada")
AND (@IncluirConcluido OR (SELECT Viagem_Situacao) <> "Concluída")

Results: 6 rows fetched.


Running the same tests on the MySQL version 5.5.8, error occurs. Unknown column 'Viagem_Situacao' in the list.

Why this is no longer possible?
Do you have any solution or do I really need to rewrite all my queries?

***********************************************************
Sincerely,
Michael Claydson

Options: ReplyQuote


Subject
Views
Written By
Posted
Field calculated using the WHERE
2879
January 13, 2011 03:22PM
1242
January 15, 2011 12:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.