SELECT <tblName>.* with "join left" bug?

I'm running in an odd behavior of the SELECT.

I'm expecting the following two queries to give exactly the same resultset
since the only differenct between them is that one calls for SELECT
ResourceType.* and the other for SELECT ResourceType.name where all the
entries in ResourceType have the field "name" defined.

1) select distinct ResourceType.* from ResourceType left join Res_ResType
on ResourceType.ID=Res_ResType.resTypeID left join Res_Subject on
Res_ResType.resID=Res_Subject.resID where Res_Subject.subID=48 order by name;
[this query returns 12 records, which is incorrect]

2) select distinct ResourceType.name from ResourceType left join
Res_ResType on ResourceType.ID=Res_ResType.resTypeID left join Res_Subject
on Res_ResType.resID=Res_Subject.resID where Res_Subject.subID=48 order by
name;
[this query returns, correctly, 13 records]

There not seem to be any difference unless I use the 'left join' structure,
that is
SELECT ResourceType.* from ResourceType
and
SELECT ResourceType.name from ResourceType
will return the same, correct, number of records.

At the end of the email I included the mysql run with the two queries and
the table structure.

Possibly I'm missing something,

Giulia
============================================================ ======
mysql> select distinct ResourceType.* from ResourceType left join
Res_ResType on ResourceType.ID=Res_ResType.resTypeID left join Res_Subject
on Res_ResType.resID=Res_Subject.resID where Res_Subject.subID=48 order by
name;
+----+---------------------------------------------------+-- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ---+--------+------+
| ID | name |
description
| relUrl | hits |
+----+---------------------------------------------------+-- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ---+--------+------+
| 2 | Archival Collections and Primary Source Databases | Use archival
collections and primary source databases to find historical documents,
primary sources or archival
materials.
| | 427 |
| 12 | Article Databases | Use article
databases to find articles in scholarly journals, magazines, conferences,
etc. A "Core" annotation indicates that a resource is especially useful for
this subject. Core resources appear in alphabetical order at the top of the
list. | | 4655 |
| 5 | Biographical Sources | Use biographical
sources to find information about
people.
| | 243 |
| 21 | Book and Film Review Databases | Use book and
film review databases to look up reviews of books, films, plays,
etc.
| | 200 |
| 8 | Dictionaries, Thesauri and Quotations | Use
dictionaries, thesauri and quotations to look up definitions, synonyms,
translations or to find
quotations.
| | 762 |
| 9 | Directories | Use directories
to find alphabetical or subject listings of people, organizations,
etc.
| | 160 |
| 20 | Dissertation and Thesis Databases | Use dissertation
and thesis databases to locate masters theses and doctoral
dissertations.
| | 411 |
| 10 | Encyclopedias and Almanacs | Use
encyclopedias and almanacs to find quick facts or begin research with
background information on a
topic.
| | 653 |
| 14 | Image and Sound Databases | Use image and
sound databases to find photographs, paintings, other images, and materials
in audio or visual
format.
| | 310 |
| 16 | Library and Book Catalogs | Use library and
book catalogs to look up the holdings of selected academic, research and
public libraries or to get information about published
material.
| | 143 |
| 17 | News Databases | Use news
databases to find newspaper articles, broadcast transcripts, wire service
stories,
etc.
| | 1967 |
| 22 | Specialized Resources | Use specialized
resources to find subject-specific information such as art catalogs, film
credits, music scores, poems, public opinion polls, research reports,
technical reports,
etc. | |
187 |
+----+---------------------------------------------------+-- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ---+--------+------+
12 rows in set (0.17 sec)

mysql> select distinct ResourceType.name from ResourceType left join
Res_ResType on ResourceType.ID=Res_ResType.resTypeID left join Res_Subject
on Res_ResType.resID=Res_Subject.resID where Res_Subject.subID=48 order by
name;
+---------------------------------------------------+
| name |
+---------------------------------------------------+
| Archival Collections and Primary Source Databases |
| Article Databases |
| Biographical Sources |
| Book and Film Review Databases |
| Dictionaries, Thesauri and Quotations |
| Directories |
| Dissertation and Thesis Databases |
| Encyclopedias and Almanacs |
| Image and Sound Databases |
| Library and Book Catalogs |
| News Databases |
| Specialized Resources |
| Statistics and Numeric Data |
+---------------------------------------------------+
13 rows in set (0.07 sec)

mysql> select ResourceType.name from ResourceType;
+---------------------------------------------------+
| name |
+---------------------------------------------------+
| Archival Collections and Primary Source Databases |
| Article Databases |
| Atlases, Maps and Gazetteers |
| Biographical Sources |
| Book and Film Review Databases |
| Book and Text Collections (electronic) |
| Dictionaries, Thesauri and Quotations |
| Directories |
| Dissertation and Thesis Databases |
| Encyclopedias and Almanacs |
| Government Information Sources |
| Handbooks and Manuals |
| Image and Sound Databases |
| Laws and Regulations |
| Library and Book Catalogs |
| News Databases |
| Specialized Resources |
| Statistics and Numeric Data |
+---------------------------------------------------+
18 rows in set (0.01 sec)

mysql> explain ResourceType;
+-------------+------------------+------+-----+---------+--- -------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+--- -------------+
| ID | int(10) unsigned | | PRI | NULL | auto_increment |
| name | varchar(100) | | MUL | | |
| description | varchar(250) | | | | |
| relUrl | varchar(100) | | | | |
| hits | int(10) unsigned | YES | | NULL | |
+-------------+------------------+------+-----+---------+--- -------------+
5 rows in set (0.02 sec)

mysql> explain Res_Subject;
+--------------+------------------+------+-----+---------+-- --------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+-- --------------+
| resSubjectID | int(10) unsigned | | PRI | NULL | auto_increment |
| resID | int(10) unsigned | | MUL | 0 | |
| subID | int(10) unsigned | | MUL | 0 | |
| core | tinyint(1) | | | 0 | |
+--------------+------------------+------+-----+---------+-- --------------+
4 rows in set (0.00 sec)

mysql> explain Res_ResType;
+-----------+------------------+------+-----+---------+----- -----------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----- -----------+
| resResID | int(10) unsigned | | PRI | NULL | auto_increment |
| resID | int(10) unsigned | | MUL | 0 | |
| resTypeID | int(10) unsigned | | MUL | 0 | |
+-----------+------------------+------+-----+---------+----- -----------+
3 rows in set (0.00 sec)


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org