MySQL Bugs: #17859: Left outer Join of two views causes server crash

Bug #17859	Left outer Join of two views causes server crash
Submitted:	2 Mar 2006 12:50	Modified:	10 Apr 2006 16:56
Reporter:	Charlie Farrow	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.18	OS:	Windows (WIN XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
When executing a join on two views that originate from the same table, the server crashes.

How to repeat:
See attached SQL file, restore it.
Then execute the following query:

select * from vretro_stock_val as A 
left outer join Vstock_val as B 
on A.stock_id=B.stock_id 

The server processes this request forever, on what i believe should be a simple left join beween two views.

If you need any more help, let me know.

Thank you for a problem report. Please, take a look at EXPLAIN results I've got on your test case:

mysql> explain select * from vretro_stock_val as A
    -> left outer join Vstock_val as B
    -> on A.stock_id=B.stock_id \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: component_list
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 354
        Extra:
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: stock
         type: ref
possible_keys: COMPONENT_ID
          key: COMPONENT_ID
      key_len: 4
          ref: wellandpowerdb.component_list.COMPONENT_ID
         rows: 8
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: set_list
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: wellandpowerdb.stock.SET_ID
         rows: 1
        Extra: Using index
*************************** 4. row ***************************
           id: 1
  select_type: PRIMARY
        table: dispatch_body
         type: ref
possible_keys: set_id
          key: set_id
      key_len: 4
          ref: wellandpowerdb.set_list.SET_ID
         rows: 1
        Extra:
*************************** 5. row ***************************
           id: 1
  select_type: PRIMARY
        table: dispatch_headers
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: wellandpowerdb.dispatch_body.DISPATCH_ID
         rows: 1
        Extra:
*************************** 6. row ***************************
           id: 1
  select_type: PRIMARY
        table: component_list
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 354
        Extra:
*************************** 7. row ***************************
           id: 1
  select_type: PRIMARY
        table: stock
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1428
        Extra:
*************************** 8. row ***************************
           id: 1
  select_type: PRIMARY
        table: set_list
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: wellandpowerdb.stock.SET_ID
         rows: 1
        Extra:
8 rows in set (0.05 sec)

They mean that:

mysql> select 324*8*354*1428;
+----------------+
| 324*8*354*1428 |
+----------------+
|     1310287104 |
+----------------+
1 row in set (0.00 sec)

^^^^ this many rows should be returned by that query. Surely, it will work for ages. It is not a bug - you just asked to do it.

OK... I can see the explain and the results show this number of rows should be returned, however, surly this is incorrect if you look at the two views.

One each view has a list of stock items, with a primary key of STOCK_ID.

In each of these views, there are < 800 rows, most of which are duplicates in the other.

Therefore, doing a left outer join on these two views should surly produce a maximum number of rows in the first view, because there is never a duplicated key of course.

Isn't this the case? Should these views not perform as if they were two temporary tables in memory?

Charlie

Also, looking at using an inner join (this is not the result I want by the way i do need left outer syntax) 
the rows returned are 675. Now by doing a simple select from each view again you can see the fact as commented below the keys match, therefore, because we are joining in the key there should never be more rows than in the first table using this query? do you agree that?

So using the inner join we get 675 rows. Now using the left outer join syntax I want to return the rows in A that match B, so I can do a comparrison. I could for example select non-matching rows using where B.STOCK_ID is NULL.

The query should therefore formed like this:

select * from vstock_val as A
left outer join vretro_stock_val as B
on A.stock_id=B.stock_id 
where B.stock_id is null.

Now given that there are 675 mathing rows in A inner on B and there are 1370 rows in B (and because the keys are unique, the maximum matches per row is one), the maximum number of rows returned is still 675!

Actually I guess it would make more sence joined the other way round, with B joined to A, that way the maximum number of rows should be 1370 and if you include the where clause the result will have  1370 as a maximum, less one for each sucessfully matched row.

This still crashes the server.

it does actually terminate, just takes a long time:

1370 rows in set (16 min 59.84 sec)    (on a laptop running at 600mhz right now)

LOL! ok, so we know it takes a long time and its not crashing the server, that looks like a good thing!

However, don't you think you could perform this query faster by hand? Want me to have a go and tell you how long it takes me? 

The server is clearly going about it the wrong way dont you think? It takes virtually no time to select from the view... (o.0070secs or less for each on my very low spec server)
and if these were tables, it would take a similar amount of time to join them i think??

So surely this is a bug, all be it possibly not the bug i thought it was?

We have some internal discussion on this issue. Maybe, it was helpful. I've tried to reproduce your test case on today's 5.0.21-BK build on Linux, and got a different plan:

mysql> select count(*) from vstock_val;
+----------+
| count(*) |
+----------+
| 697      |
+----------+
1 row in set (0.03 sec)

mysql> select count(*) from vretro_stock_val;
+----------+
| count(*) |
+----------+
| 1370     |
+----------+
1 row in set (0.02 sec)

mysql> select 1370*697;
+----------+
| 1370*697 |
+----------+
| 954890   |
+----------+
1 row in set (0.00 sec)

mysql> explain select * from vretro_stock_val as A
    ->     left outer join vstock_val as B
    ->     on A.stock_id=B.stock_id \G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: stock
         type: ALL
possible_keys: COMPONENT_ID
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1428
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: stock
         type: eq_ref
possible_keys: PRIMARY,COMPONENT_ID
          key: PRIMARY
      key_len: 4
          ref: wellandpowerdb.stock.STOCK_ID
         rows: 1
        Extra:
*************************** 3. row ***************************
           id: 1
  select_type: PRIMARY
        table: component_list
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: wellandpowerdb.stock.COMPONENT_ID
         rows: 1
        Extra:
*************************** 4. row ***************************
           id: 1
  select_type: PRIMARY
        table: set_list
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: wellandpowerdb.stock.SET_ID
         rows: 1
        Extra:
*************************** 5. row ***************************
           id: 1
  select_type: PRIMARY
        table: set_list
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: wellandpowerdb.stock.SET_ID
         rows: 1
        Extra: Using index
*************************** 6. row ***************************
           id: 1
  select_type: PRIMARY
        table: component_list
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: wellandpowerdb.stock.COMPONENT_ID
         rows: 1
        Extra: Using where
*************************** 7. row ***************************
           id: 1
  select_type: PRIMARY
        table: dispatch_body
         type: ref
possible_keys: set_id
          key: set_id
      key_len: 4
          ref: wellandpowerdb.set_list.SET_ID
         rows: 1
        Extra:
*************************** 8. row ***************************
           id: 1
  select_type: PRIMARY
        table: dispatch_headers
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: wellandpowerdb.dispatch_body.DISPATCH_ID
         rows: 1
        Extra:
8 rows in set (0.01 sec)

mysql> select * from vretro_stock_val as A left outer join vstock_val as B
on A.stock_id=B.stock_id\G
...
*************************** 1369. row ***************************
      STOCK_ID: 1727
  COMPONENT_ID: 1221
COMPONENT_NAME: Perkins 2306C-E14TAG3/ECO40-1S
   STOCK_VALUE: 465
      STOCK_IN: 1140604551
     STOCK_OUT: NULL
      STOCK_ID: 1727
  COMPONENT_ID: 1221
COMPONENT_NAME: Perkins 2306C-E14TAG3/ECO40-1S
   STOCK_VALUE: 465
*************************** 1370. row ***************************
      STOCK_ID: 1726
  COMPONENT_ID: 1221
COMPONENT_NAME: Perkins 2306C-E14TAG3/ECO40-1S
   STOCK_VALUE: 465
      STOCK_IN: 1140516253
     STOCK_OUT: NULL
      STOCK_ID: 1726
  COMPONENT_ID: 1221
COMPONENT_NAME: Perkins 2306C-E14TAG3/ECO40-1S
   STOCK_VALUE: 465
1370 rows in set (0.08 sec)

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

So, please, check the newer version, 5.0.20 (it will appear soon), and reopen this report in case of similar problems.