MySQL Bugs: #26282: bad table (view) name with queries containing view on the same table

Bug #26282	bad table (view) name with queries containing view on the same table
Submitted:	12 Feb 2007 11:47	Modified:	7 Aug 2007 16:04
Reporter:	fabrice GOSSART	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:	5.0.27	OS:	Linux (FC5)
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
table name is origin table rather than view name.

How to repeat:
table categ idcateg,idsample1,idsample2
table sample idsample, value1, value2 
create 2 view on this table now
create view sample1 as select * from table where value1=xxxx
create view sample2 as elect * from table where value1=yyyy

select categ.idcateg,sample1.value1,sample2.value1 left join sample1 on categ.idsample1=sample1.idsample left join sample2 on categ.idsample2=sample2.idsample

Ok for that, my php api mysql_field_table returns (categ,sample1,sample2)

for the same request with an order by clause on the view field
select categ.idcateg,sample1.value1,sample2.value1 left join sample1 on categ.idsample1=sample1.idsample left join sample2 on categ.idsample2=sample2.idsample
order by categ.idsample1
 my php api mysql_field_table returns (categ,sample,sample)

Thank you for a problem report. Please, specify the exact version of MySQL server used and send the exact CREATE TABLE and CREATE VIEW statements to demonstrate the behaviour described.

here is a sample to have the problem
// db creation script

CREATE DATABASE `animal` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `animal`;
CREATE TABLE `animal` (
  `IdAnimal` int(11) NOT NULL auto_increment,
  `Name` varchar(100) NOT NULL,
  PRIMARY KEY  (`IdAnimal`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

INSERT INTO `animal` (`IdAnimal`, `Name`) VALUES 
(1, 'Hippopotamus'),
(2, 'Elefant'),
(3, 'mouse'),
(4, 'bear');

CREATE TABLE `specs` (
  `IdSpec` int(11) NOT NULL auto_increment,
  `IdAnimal` int(11) NOT NULL,
  `Categ` enum('color','weight') NOT NULL,
  `Specvalue` varchar(100) NOT NULL,
  PRIMARY KEY  (`IdSpec`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=18 ;

INSERT INTO `specs` (`IdSpec`, `IdAnimal`, `Categ`, `Specvalue`) VALUES 
(9, 1, 'color', 'grey'),
(10, 1, 'weight', 'very heavy'),
(11, 2, 'color', 'grey'),
(12, 2, 'weight', 'very heavy'),
(13, 3, 'color', 'grey'),
(14, 3, 'weight', 'light'),
(15, 4, 'color', 'brown'),
(16, 4, 'color', 'white'),
(17, 4, 'weight', 'heavy');

create view animalcolor as select * from specs where Categ='color';
create view animalweight as select * from specs where Categ='weight';

// now a php script to see the problem
<html>

<head>
<title>Sans Titre</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
<meta name="generator" content="HAPedit 3.1">
</head>
<body bgcolor="#FFFFFF">
<?
echo "begin";
$link = mysql_connect('localhost','animal','mouse');   //user=animal, password=mouse
$db = mysql_select_db('animal',$link);  //db=animal

echo "first query OK<BR>";
$sql="SELECT animal.*,animalcolor.*,animalweight.*
FROM animal
LEFT JOIN animalcolor ON animal.IdAnimal = animalcolor.IdAnimal
LEFT JOIN animalweight ON animal.IdAnimal = animalweight.IdAnimal";

$res = mysql_query( $sql);
if ($res)
{
  $rows = mysql_num_fields($res);
  for ($i=0; $i<$rows; $i++)
  {
      echo mysql_field_table($res,$i)." _ ".mysql_field_name($res,$i)."<br/>";
  }
}

echo "<BR>nowsame query with order by : tables name are not the same !<BR>";
$sql.= " order by animalcolor.Categ,animalweight.Categ ";
 $res = mysql_query( $sql);
if ($res)
{
  $rows = mysql_num_fields($res);
  for ($i=0; $i<$rows; $i++)
  {
      echo mysql_field_table($res,$i)." _ ".mysql_field_name($res,$i)."<br/>";
  }
}

?>
</body>

</html>

Category changed to more appropriate

test case

Attachment: bug26282.c (text/plain), 1.50 KiB.

Thank you for the report.

Verified as described using attache C test case.

Can't repeat (foo.txt and bar.txt contain metadata output from the query 
with and without ORDER BY, as printed by mysql --debug-info)
--- foo.txt	2007-08-07 20:00:49.284786917 +0400
+++ bar.txt	2007-08-07 20:00:18.607038692 +0400
@@ -8,7 +8,7 @@
 Length:     11
 Max_length: 1
 Decimals:   0
-Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY 
+Flags:      NOT_NULL NUM 
 
 Field   2:  `Name`
 Catalog:    `def`
@@ -32,7 +32,7 @@
 Length:     11
 Max_length: 2
 Decimals:   0
-Flags:      PRI_KEY AUTO_INCREMENT NUM PART_KEY 
+Flags:      NUM 
 
 Field   4:  `IdAnimal`
 Catalog:    `def`
@@ -80,7 +80,7 @@
 Length:     11
 Max_length: 2
 Decimals:   0
-Flags:      PRI_KEY AUTO_INCREMENT NUM PART_KEY 
+Flags:      NUM 
 
 Field   8:  `IdAnimal`
 Catalog:    `def`
@@ -122,11 +122,11 @@
 +----------+--------------+--------+----------+-------+-----------+--------+----------+--------+------------+
 | IdAnimal | Name         | IdSpec | IdAnimal | Categ | Specvalue | IdSpec | IdAnimal | Categ  | Specvalue  |
 +----------+--------------+--------+----------+-------+-----------+--------+----------+--------+------------+
-|        1 | Hippopotamus |      9 |        1 | color | grey      |     10 |        1 | weight | very heavy | 
 |        2 | Elefant      |     11 |        2 | color | grey      |     12 |        2 | weight | very heavy | 
 |        3 | mouse        |     13 |        3 | color | grey      |     14 |        3 | weight | light      | 
 |        4 | bear         |     15 |        4 | color | brown     |     17 |        4 | weight | heavy      | 
+|        1 | Hippopotamus |      9 |        1 | color | grey      |     10 |        1 | weight | very heavy | 
 |        4 | bear         |     16 |        4 | color | white     |     17 |        4 | weight | heavy      | 
 +----------+--------------+--------+----------+-------+-----------+--------+----------+--------+------------+
-5 rows in set (0.00 sec)
+5 rows in set (0.01 sec)

This must have been fixed along with some sr5_1 bug.