Bug #7071 Lost connection: INNER JOIN with left table column in WHERE clause

• Submitted: 7 Dec 2004 12:35
• Modified: 7 Dec 2004 16:15
• Reporter: Heidi Hunter
• Status: Can't repeat
• Category: MySQL Server: Command-line Clients
• Severity: S2 (Serious)
• Version: 3.23.42
• OS: Linux (RedHat 8.0)
• CPU Architecture: Any

[7 Dec 2004 12:35] Heidi Hunter

Description:
When executing a query with an doing INNER JOIN and using a condition with a column from the left table in the WHERE clause, the client reported Error 2013 when there was only one row in the right table, which should have been matched to rows in the left table, but the same query worked when more rows were added to the table, even though they weren't matched.

When I changed the USING for an equivalent ON clause, and added the Table1.key1=1 condition to the ON clause, it worked fine in all cases.

How to repeat:
create table Table1 (key1 int unsigned not null, 

key2 int not null, key3 int unsigned not null, primary key (key1,key2,key3)); 
create table Table2 (key1 int unsigned not null, 

key2 int not null, type int not null, primary key (key1,key2)); 
insert into Table1 values (1, 995, 61), (1 , 995 , 62) , (1 , 995 , 63) , (1 , 995 , 64) , (1 , 995 , 65) , (1 , 995 , 66) , (1 , 995 , 610) , (1 , 995 , 611) , (1 , 995 , 612) , (1 , 995 , 613) , (1 , 995 , 614) , (1 , 995 , 701) , (1 , 995 , 702) , (1 , 995 , 61001) , (1 , 995 , 61002); 

insert into Table2 values (1, 995, 2); 

# either will lose connection during query 
select key3,count(Table1.key2) from Table1 inner join Table2 using(key1 ,key2) where Table1.key1=1 and (type=1 or type=2) group by key3; 
select key3 from Table1 inner join Table2 using(key1,key2) where Table1.key1=1 and (type=1 or type=2);

# now it will work 
insert into Table2 values (1, 10, 1); 

select key3,count(Table1.key2) from Table1 inner join Table2 using(key1 ,key2) where Table1.key1=1 and (type=1 or type=2) group by key3; 
select key3 from Table1 inner join Table2 using(key1,key2) where Table1.key1=1 and (type=1 or type=2);

[7 Dec 2004 16:15] MySQL Verification Team

You have a pretty older server version. I wasn't able to repeat with
the latest BK source:

mysql> insert into Table2 values (1, 995, 2); 
Query OK, 1 row affected (0.00 sec)

mysql> select key3,count(Table1.key2) from Table1 inner join Table2 using(key1 ,key2)
    -> where Table1.key1=1 and (type=1 or type=2) group by key3; 
+-------+--------------------+
| key3  | count(Table1.key2) |
+-------+--------------------+
|    61 |                  1 |
|    62 |                  1 |
|    63 |                  1 |
|    64 |                  1 |
|    65 |                  1 |
|    66 |                  1 |
|   610 |                  1 |
|   611 |                  1 |
|   612 |                  1 |
|   613 |                  1 |
|   614 |                  1 |
|   701 |                  1 |
|   702 |                  1 |
| 61001 |                  1 |
| 61002 |                  1 |
+-------+--------------------+
15 rows in set (0.02 sec)

mysql> select key3 from Table1 inner join Table2 using(key1,key2) where Table1.key1=1
    -> and (type=1 or type=2);
+-------+
| key3  |
+-------+
|    61 |
|    62 |
|    63 |
|    64 |
|    65 |
|    66 |
|   610 |
|   611 |
|   612 |
|   613 |
|   614 |
|   701 |
|   702 |
| 61001 |
| 61002 |
+-------+
15 rows in set (0.01 sec)

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 3.23.59-debug-log |
+-------------------+
1 row in set (0.00 sec)

mysql>

[9 Dec 2004 13:59] Heidi Hunter

Thank you for taking a look. I know we're on an old version, I was only hoping for a little insight if possible!
Anyway, we really appreciate your examining a problem with a back version!
-Heidi