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/[email protected]