(mysqlbug died because it couldn't find rmail. So, I'm sending this
manually.)
>Description:
I have a join pulling two columns (both named 'id') from two different
tables. When these results are returned, in the result set, they are both
called 'id', with no way to refer to them explicitly. I would expect them to
be named table1.id and table2.id. Even MS Access is smart enough to qualify
their names: that is how I discovered the bug. I moved from an attached
table query to an ODBCDirect query, and leg_text.id was no longer in the
recordset. :)
>How-To-Repeat:
This query:
SELECT leg_header.id, leg_text.id, body_list.leg_char,
leg_types.leg_type_char, leg_header.session_number, leg_header.leg_number,
leg_text.leg_version, leg_states.state_desc, leg_header.original_date,
body_list_1.body_name, leg_text.full_title, leg_text.short_title,
leg_text.leg_preamble, leg_text.active_text, leg_text.leg_purpose,
leg_text.fiscal_impact, IF(sponsor_type_id=1,CONCAT(people.fname, ' ',
people.lname), CONCAT(body_list_2.body_name, ' Committee')) AS primary_sponsor
FROM leg_header INNER JOIN body_list ON leg_header.body_id_of_origin =
body_list.id
INNER JOIN leg_types ON leg_header.leg_type_id = leg_types.id
INNER JOIN leg_states ON leg_header.current_state_id = leg_states.id
LEFT JOIN people ON leg_header.primary_sponsor_id = people.id
LEFT JOIN body_list AS body_list_1 ON leg_header.latest_committee_id =
body_list_1.id
LEFT JOIN body_list AS body_list_2 ON leg_header.primary_sponsor_id =
body_list_2.id
INNER JOIN leg_text ON leg_header.current_version_id = leg_text.id
WHERE leg_header.id = 8
For purpose of example, I ran this query:
mysql> SELECT leg_header.id, leg_text.id
FROM leg_header INNER JOIN body_list ON leg_header.body_id_of_origin =
body_list.id
INNER JOIN leg_types ON leg_header.leg_type_id = leg_types.id
INNER JOIN leg_states ON leg_header.current_state_id =leg_states.id
LEFT JOIN people ON leg_header.primary_sponsor_id = people.id
LEFT JOIN body_list AS body_list_1 ON
leg_header.latest_committee_id = body_list_1.id
LEFT JOIN body_list AS body_list_2 ON
leg_header.primary_sponsor_id = body_list_2.id
INNER JOIN leg_text ON leg_header.current_version_id = leg_text.id
WHERE leg_header.id = 8\g
+----+----+
| id | id |
+----+----+
| 8 | 15 |
+----+----+
1 row in set (0.01 sec)
mysql>
That could make it VERY hard to get at your data, no? :)
>Fix:
On second column with same name, use an AS to rename the column.
It works, but is a kludge. If there is a conflict with column names, they
should be returned as table.column in the result set.
Possibly this: Since SELECTing two columns of the same name requires you to
qualify them with the table name, then MySQL should return any column the way
it is SELECTed. Thus, if you SELECT table1.id, table2.id, then in in the
result set, it should have table1.id and table2.id as the column headings.
>Submitter-Id: Joshua J. Kugler
>Originator: Joshua J. Kugler
>Organization: Associated Students of the University of Alaska Fairbanks
>MySQL support: none
>Synopsis: Join query does not produce unique names in result
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.38 (Source distribution)
>Environment: Dual PIII-500, 512MB RAM, 3 9GB SCSI on a RAID 5
System: Linux deuel.as.uaf.edu 2.4.4-jjk-20010430 #2 SMP Mon Apr 30 17:14:00
AKDT 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i586-mandrake-linux/2.95.2/specs
gcc version 2.95.2 19991024 (release)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 Feb 22 15:50 /lib/libc.so.6 ->
libc-2.1.3.so
-rwxr-xr-x 1 root root 5257684 Jan 18 07:39 /lib/libc-2.1.3.so
-rw-r--r-- 1 root root 20732410 Jan 18 07:38 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Jan 18 07:38 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql
--localstatedir=/usr/local/mysql/data --enable-assembler --$
Perl: This is perl, version 5.005_03 built for i686-linux
--
Joshua Kugler, Information Services Director
Associated Students of the University of Alaska Fairbanks
[EMAIL PROTECTED], 907-474-7601
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
