| Bug #17611 | Incorrect return from view | ||
|---|---|---|---|
| Submitted: | 21 Feb 2006 11:21 | Modified: | 17 May 2006 17:22 |
| Reporter: | Daniel Guimarães | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.18-nt-max | OS: | Windows (Windows XP Professional with SP2) |
| Assigned to: | CPU Architecture: | Any | |
[21 Feb 2006 15:11]
MySQL Verification Team
Thank you for the bug report. Could you please provide the complete script test case with a dump of tables and views?. You can upload it at ftp://ftp.mysql.com/pub/mysql/upload into a zip file called i.e.: bug17611.zip and let me know when done. Thanks in advance.
[22 Feb 2006 11:05]
Daniel Guimarães
Script with used tables by view uploaded to: ftp://ftp.mysql.com/pub/mysql/upload/bug17611.zip [] Daniel
[3 Mar 2006 10:58]
Daniel Guimarães
Tables used by view
Attachment: bug17611.zip (application/x-zip-compressed, text), 21.82 KiB.
[17 May 2006 17:22]
MySQL Verification Team
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Additional info:
Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.22
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database sge;
Query OK, 1 row affected (0.06 sec)
mysql> use sge
Database changed
mysql> source c:/a/test_view.sql
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.28 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 520 rows affected (0.08 sec)
Records: 520 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.11 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1131 rows affected (0.17 sec)
Records: 1131 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.08 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 700 rows affected (0.09 sec)
Records: 700 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 46 rows affected (0.03 sec)
Records: 46 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from
-> (select
-> itinerariolinharota.linha_id,
-> itinerariolinharota.id as itinerariolinharota_id,
-> itinerariolinharota.rota_id,
-> complementorota.id as complementorota_id,
-> complementorota.seccionamento_id,
-> complementorota.kmpercurso,
-> complementorota.kmdesviochegada,
-> complementorota.kmdesviosaida,
-> complementorota.tempopercurso,
-> complementorota.tempodesviochegada,
-> complementorota.tempodesviosaida,
-> (select kmpercurso from complementorota
-> where id = itinerariolinharota.complementorotainicial_id) as
-> kminicialtrecho,
-> (select kmpercurso from complementorota
-> where id = itinerariolinharota.complementorotafinal_id) as kmfinaltrecho,
-> least((select kminicialtrecho), (select kmfinaltrecho)) as kmmenortrecho,
-> greatest((select kminicialtrecho), (select kmfinaltrecho)) as kmmaiortrecho
-> from itinerariolinharota
-> join rota on rota.id = itinerariolinharota.rota_id
-> join complementorota on complementorota.rota_id = rota.id
-> where
-> complementorota.kmpercurso between
-> (select kmmenortrecho) and (select kmmaiortrecho)
-> ) as subquery;
+----------+
| count(*) |
+----------+
| 5917 |
+----------+
1 row in set (0.25 sec)
mysql> create or replace view view_linharota as
-> select
-> itinerariolinharota.linha_id,
-> itinerariolinharota.id as itinerariolinharota_id,
-> itinerariolinharota.rota_id,
-> complementorota.id as complementorota_id,
-> complementorota.seccionamento_id,
-> complementorota.kmpercurso,
-> complementorota.kmdesviochegada,
-> complementorota.kmdesviosaida,
-> complementorota.tempopercurso,
-> complementorota.tempodesviochegada,
-> complementorota.tempodesviosaida,
-> (select kmpercurso from complementorota
-> where id = itinerariolinharota.complementorotainicial_id) as
-> kminicialtrecho,
-> (select kmpercurso from complementorota
-> where id = itinerariolinharota.complementorotafinal_id) as kmfinaltrecho,
-> least((select kminicialtrecho), (select kmfinaltrecho)) as kmmenortrecho,
-> greatest((select kminicialtrecho), (select kmfinaltrecho)) as kmmaiortrecho
-> from itinerariolinharota
-> join rota on rota.id = itinerariolinharota.rota_id
-> join complementorota on complementorota.rota_id = rota.id
-> where
-> complementorota.kmpercurso between
-> (select kmmenortrecho) and (select kmmaiortrecho);
Query OK, 0 rows affected (0.02 sec)
mysql> select count(*) from view_linharota;
+----------+
| count(*) |
+----------+
| 5917 |
+----------+
1 row in set (0.19 sec)
mysql>
Description: /*---------------------------------------------------------- SELECT COUNT(*) FROM SubQuery (Return: 5917 - Ok) ----------------------------------------------------------*/ SELECT COUNT(*) FROM (SELECT ItinerarioLinhaRota.Linha_Id, ItinerarioLinhaRota.Id AS ItinerarioLinhaRota_Id, ItinerarioLinhaRota.Rota_Id, ComplementoRota.Id AS ComplementoRota_Id, ComplementoRota.Seccionamento_Id, ComplementoRota.KmPercurso, ComplementoRota.KmDesvioChegada, ComplementoRota.KmDesvioSaida, ComplementoRota.TempoPercurso, ComplementoRota.TempoDesvioChegada, ComplementoRota.TempoDesvioSaida, (SELECT KmPercurso FROM ComplementoRota WHERE Id = ItinerarioLinhaRota.ComplementoRotaInicial_Id) AS KmInicialTrecho, (SELECT KmPercurso FROM ComplementoRota WHERE Id = ItinerarioLinhaRota.ComplementoRotaFinal_Id) AS KmFinalTrecho, LEAST((SELECT KmInicialTrecho), (SELECT KmFinalTrecho)) AS KmMenorTrecho, GREATEST((SELECT KmInicialTrecho), (SELECT KmFinalTrecho)) AS KmMaiorTrecho FROM ItinerarioLinhaRota JOIN Rota ON Rota.Id = ItinerarioLinhaRota.Rota_Id JOIN ComplementoRota ON ComplementoRota.Rota_Id = Rota.Id WHERE ComplementoRota.KmPercurso BETWEEN (SELECT KmMenorTrecho) AND (SELECT KmMaiorTrecho) ) AS SubQuery; /*-------------------------------------------------------- Create view based on SubQuery --------------------------------------------------------*/ CREATE OR REPLACE VIEW View_LinhaRota AS SELECT ItinerarioLinhaRota.Linha_Id, ItinerarioLinhaRota.Id AS ItinerarioLinhaRota_Id, ItinerarioLinhaRota.Rota_Id, ComplementoRota.Id AS ComplementoRota_Id, ComplementoRota.Seccionamento_Id, ComplementoRota.KmPercurso, ComplementoRota.KmDesvioChegada, ComplementoRota.KmDesvioSaida, ComplementoRota.TempoPercurso, ComplementoRota.TempoDesvioChegada, ComplementoRota.TempoDesvioSaida, (SELECT KmPercurso FROM ComplementoRota WHERE Id = ItinerarioLinhaRota.ComplementoRotaInicial_Id) AS KmInicialTrecho, (SELECT KmPercurso FROM ComplementoRota WHERE Id = ItinerarioLinhaRota.ComplementoRotaFinal_Id) AS KmFinalTrecho, LEAST((SELECT KmInicialTrecho), (SELECT KmFinalTrecho)) AS KmMenorTrecho, GREATEST((SELECT KmInicialTrecho), (SELECT KmFinalTrecho)) AS KmMaiorTrecho FROM ItinerarioLinhaRota JOIN Rota ON Rota.Id = ItinerarioLinhaRota.Rota_Id JOIN ComplementoRota ON ComplementoRota.Rota_Id = Rota.Id WHERE ComplementoRota.KmPercurso BETWEEN (SELECT KmMenorTrecho) AND (SELECT KmMaiorTrecho); /*-------------------------------------------------------- SELECT COUNT(*) FROM View (Return: 1709 -- incorrect!) --------------------------------------------------------*/ SELECT COUNT(*) FROM View_LinhaRota; /***************************************************************** ADITIONAL INFORMATION *****************************************************************/ MySQL: 5.0.18-nt-max SO...: Windows XP Professional with SP2 /*------------------------------------ Source code generated by mysqldump ------------------------------------*/ CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_linharota` AS select `itinerariolinharota`.`Linha_id` AS `Linha_Id`, `itinerariolinharota`.`id` AS `ItinerarioLinhaRota_Id`, `itinerariolinharota`.`Rota_id` AS `Rota_Id`, `complementorota`.`id` AS `ComplementoRota_Id`, `complementorota`.`Seccionamento_id` AS `Seccionamento_Id`, `complementorota`.`KmPercurso` AS `KmPercurso`, `complementorota`.`KmDesvioChegada` AS `KmDesvioChegada`, `complementorota`.`KmDesvioSaida` AS `KmDesvioSaida`, `complementorota`.`TempoPercurso` AS `TempoPercurso`, `complementorota`.`TempoDesvioChegada` AS `TempoDesvioChegada`, `complementorota`.`TempoDesvioSaida` AS `TempoDesvioSaida`, (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaInicial_id`)) AS `KmInicialTrecho`, (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaFinal_id`)) AS `KmFinalTrecho`, least((select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaInicial_id`)) AS `KmInicialTrecho`), (select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaFinal_id`)) AS `KmFinalTrecho`) ) AS `KmMenorTrecho`, greatest((select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaInicial_id`)) AS `KmInicialTrecho`), (select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaFinal_id`)) AS `KmFinalTrecho`) ) AS `KmMaiorTrecho` from ((`itinerariolinharota` join `rota` on ((`rota`.`id` = `itinerariolinharota`.`Rota_id`))) join `complementorota` on((`complementorota`.`Rota_id` = `rota`.`id`))) where (`complementorota`.`KmPercurso` between (select least((select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaInicial_id`)) AS `KmInicialTrecho`), (select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaFinal_id`)) AS `KmFinalTrecho`)) AS `KmMenorTrecho`) and (select greatest((select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaInicial_id`)) AS `KmInicialTrecho`), (select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaFinal_id`)) AS `KmFinalTrecho`)) AS `KmMaiorTrecho`)); How to repeat: /*---------------------------------------------------------- SELECT COUNT(*) FROM SubQuery (Return: 5917 - Ok) ----------------------------------------------------------*/ SELECT COUNT(*) FROM (SELECT ItinerarioLinhaRota.Linha_Id, ItinerarioLinhaRota.Id AS ItinerarioLinhaRota_Id, ItinerarioLinhaRota.Rota_Id, ComplementoRota.Id AS ComplementoRota_Id, ComplementoRota.Seccionamento_Id, ComplementoRota.KmPercurso, ComplementoRota.KmDesvioChegada, ComplementoRota.KmDesvioSaida, ComplementoRota.TempoPercurso, ComplementoRota.TempoDesvioChegada, ComplementoRota.TempoDesvioSaida, (SELECT KmPercurso FROM ComplementoRota WHERE Id = ItinerarioLinhaRota.ComplementoRotaInicial_Id) AS KmInicialTrecho, (SELECT KmPercurso FROM ComplementoRota WHERE Id = ItinerarioLinhaRota.ComplementoRotaFinal_Id) AS KmFinalTrecho, LEAST((SELECT KmInicialTrecho), (SELECT KmFinalTrecho)) AS KmMenorTrecho, GREATEST((SELECT KmInicialTrecho), (SELECT KmFinalTrecho)) AS KmMaiorTrecho FROM ItinerarioLinhaRota JOIN Rota ON Rota.Id = ItinerarioLinhaRota.Rota_Id JOIN ComplementoRota ON ComplementoRota.Rota_Id = Rota.Id WHERE ComplementoRota.KmPercurso BETWEEN (SELECT KmMenorTrecho) AND (SELECT KmMaiorTrecho) ) AS SubQuery; /*-------------------------------------------------------- Create view based on SubQuery --------------------------------------------------------*/ CREATE OR REPLACE VIEW View_LinhaRota AS SELECT ItinerarioLinhaRota.Linha_Id, ItinerarioLinhaRota.Id AS ItinerarioLinhaRota_Id, ItinerarioLinhaRota.Rota_Id, ComplementoRota.Id AS ComplementoRota_Id, ComplementoRota.Seccionamento_Id, ComplementoRota.KmPercurso, ComplementoRota.KmDesvioChegada, ComplementoRota.KmDesvioSaida, ComplementoRota.TempoPercurso, ComplementoRota.TempoDesvioChegada, ComplementoRota.TempoDesvioSaida, (SELECT KmPercurso FROM ComplementoRota WHERE Id = ItinerarioLinhaRota.ComplementoRotaInicial_Id) AS KmInicialTrecho, (SELECT KmPercurso FROM ComplementoRota WHERE Id = ItinerarioLinhaRota.ComplementoRotaFinal_Id) AS KmFinalTrecho, LEAST((SELECT KmInicialTrecho), (SELECT KmFinalTrecho)) AS KmMenorTrecho, GREATEST((SELECT KmInicialTrecho), (SELECT KmFinalTrecho)) AS KmMaiorTrecho FROM ItinerarioLinhaRota JOIN Rota ON Rota.Id = ItinerarioLinhaRota.Rota_Id JOIN ComplementoRota ON ComplementoRota.Rota_Id = Rota.Id WHERE ComplementoRota.KmPercurso BETWEEN (SELECT KmMenorTrecho) AND (SELECT KmMaiorTrecho); /*-------------------------------------------------------- SELECT COUNT(*) FROM View (Return: 1709 -- incorrect!) --------------------------------------------------------*/ SELECT COUNT(*) FROM View_LinhaRota; /***************************************************************** ADITIONAL INFORMATION *****************************************************************/ MySQL: 5.0.18-nt-max SO...: Windows XP Professional with SP2 /*------------------------------------ Source code generated by mysqldump ------------------------------------*/ CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_linharota` AS select `itinerariolinharota`.`Linha_id` AS `Linha_Id`, `itinerariolinharota`.`id` AS `ItinerarioLinhaRota_Id`, `itinerariolinharota`.`Rota_id` AS `Rota_Id`, `complementorota`.`id` AS `ComplementoRota_Id`, `complementorota`.`Seccionamento_id` AS `Seccionamento_Id`, `complementorota`.`KmPercurso` AS `KmPercurso`, `complementorota`.`KmDesvioChegada` AS `KmDesvioChegada`, `complementorota`.`KmDesvioSaida` AS `KmDesvioSaida`, `complementorota`.`TempoPercurso` AS `TempoPercurso`, `complementorota`.`TempoDesvioChegada` AS `TempoDesvioChegada`, `complementorota`.`TempoDesvioSaida` AS `TempoDesvioSaida`, (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaInicial_id`)) AS `KmInicialTrecho`, (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaFinal_id`)) AS `KmFinalTrecho`, least((select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaInicial_id`)) AS `KmInicialTrecho`), (select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaFinal_id`)) AS `KmFinalTrecho`) ) AS `KmMenorTrecho`, greatest((select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaInicial_id`)) AS `KmInicialTrecho`), (select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaFinal_id`)) AS `KmFinalTrecho`) ) AS `KmMaiorTrecho` from ((`itinerariolinharota` join `rota` on ((`rota`.`id` = `itinerariolinharota`.`Rota_id`))) join `complementorota` on((`complementorota`.`Rota_id` = `rota`.`id`))) where (`complementorota`.`KmPercurso` between (select least((select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaInicial_id`)) AS `KmInicialTrecho`), (select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaFinal_id`)) AS `KmFinalTrecho`)) AS `KmMenorTrecho`) and (select greatest((select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaInicial_id`)) AS `KmInicialTrecho`), (select (select `complementorota`.`KmPercurso` AS `KmPercurso` from `complementorota` where (`complementorota`.`id` = `itinerariolinharota`.`ComplementoRotaFinal_id`)) AS `KmFinalTrecho`)) AS `KmMaiorTrecho`));