Field calculated using the WHERE
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