Re: File full error in simple select statement
Posted by: Peter Brawley
Date: May 11, 2021 10:45PM

No need for that EXPLAIN result, I find that in 8.0.23 after creating these tables ...

drop table if exists weekendings, dr02;
create table weekendings (
  id int unsigned primary key auto_increment, 
  weekend date 
);
create table dr02( 
  id int unsigned primary key auto_increment, 
  date date, 
  weekend date, 
  code varchar(32), 
  t1 char(1),
  amount decimal(12,2)
);

..., populating them with 800 and 350k rows respectively of random data, adding no optimising indexes, and running this query ...

select weekend, family, sum(amount) as owing
from (
  select w.weekend, d2.code as family, d2.amount
  from weekendings w  
  join dr02        d2 on (w.weekend >= d2.date and d2.t1 <> "g") 
                      or (w.weekend >= d2.weekend and d2.t1 = "g")
  where d2.amount is not null
) x
group by weekend, family
order by weekend, family;

... after a few mins I get your error ...

The table 'C:\WINDOWS\SERVIC~1\NETWOR~1\AppData\Local\Temp\#sql1310_ff_0' is full.

Bigger heap size settings don't help. Tomorrow I'll try it with 8.0.25 and other settings.

Options: ReplyQuote


Subject
Written By
Posted
Re: File full error in simple select statement
May 11, 2021 10:45PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.