[sqlsmith] Failed to generate plan on lateral subqueries

Поиск
Список
Период
Сортировка
От Andreas Seltenreich
Тема [sqlsmith] Failed to generate plan on lateral subqueries
Дата
Msg-id 87fuzftqg5.fsf@ex.ansel.ydns.eu
обсуждение исходный текст
Ответы Re: [sqlsmith] Failed to generate plan on lateral subqueries  (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>)
Re: [sqlsmith] Failed to generate plan on lateral subqueries  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: [sqlsmith] Failed to generate plan on lateral subqueries  (Peter Geoghegan <pg@heroku.com>)
Список pgsql-hackers
Hi,

I've added new grammar rules to sqlsmith and improved some older ones.
This was rewarded with a return of "failed to generate plan" errors.
The failing queries all contain a lateral subquery.  The shortest of the
failing queries are below.  They were run against the regression db of
master as of db07236.

regards,
Andreas

smith=# select msg, query from error where  (firstline(msg) ~~ 'ERROR:  failed to build any%'  or firstline(msg) ~~
'ERROR: could not devise a query plan%') and t > now() - interval '1 day' order by length(query) asc limit 3;
 

ERROR:  failed to build any 8-way joins
select ref_96.foreign_table_schema as c0, sample_87.is_supported as c1
from information_schema.sql_packages as sample_87 tablesample system (0.2)   right join
information_schema._pg_foreign_tablesas ref_96   on (sample_87.feature_id = ref_96.foreign_table_catalog ), lateral
(select      sample_87.is_verified_by as c0,       ref_97.indexed_col as c1,       coalesce(sample_87.feature_id,
ref_96.foreign_server_name)as c2,       4 as c3     from       public.comment_test as ref_97     where ref_97.id ~>~
ref_97.indexed_col    fetch first 73 rows only) as subq_33
 
where ref_96.foreign_table_name ~~ subq_33.c1

ERROR:  could not devise a query plan for the given query
select subq_43.c0 as c0
from (select         ref_181.installed as c0       from         pg_catalog.pg_available_extension_versions as ref_181,
      lateral (select               ref_181.name as c0,               ref_181.installed as c1             from
    pg_catalog.pg_conversion as ref_182             where ref_182.conname ~~* ref_181.version             fetch first
98rows only) as subq_42       where (subq_42.c0 is not NULL)         or (subq_42.c1 is NULL)) as subq_43   right join
pg_catalog.pg_languageas sample_177 tablesample system (2.8)   on (subq_43.c0 = sample_177.lanispl )
 
where sample_177.lanowner < sample_177.lanvalidator

ERROR:  failed to build any 5-way joins
select ref_239.id2 as c0, 40 as c1, ref_239.id2 as c2, ref_238.aa as c3
from public.tt5 as sample_289 tablesample system (8.1)       inner join information_schema.element_types as ref_237
 on (sample_289.x = ref_237.character_maximum_length )     left join public.b as ref_238     on
(ref_237.character_maximum_length= ref_238.aa )   left join public.num_exp_mul as ref_239   on
(ref_237.numeric_precision_radix= ref_239.id1 ), lateral (select       sample_290.b as c0,       sample_289.y as c1,
  ref_239.id2 as c2     from       public.rtest_t8 as sample_290 tablesample bernoulli (4.6)     where (sample_290.b >
ref_238.bb)      and (sample_289.y > ref_239.expected)     fetch first 91 rows only) as subq_64
 
where (subq_64.c1 > sample_289.y) and (sample_289.y = ref_239.expected)
fetch first 133 rows only



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Konstantin Knizhnik
Дата:
Сообщение: Re: Logical replication and multimaster
Следующее
От: Andrew Dunstan
Дата:
Сообщение: Re: a word-choice question