On 2015/12/07 2:52, Andreas Seltenreich wrote: > 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. > > 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_tables as 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 98 rows 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_language as 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
FWIW, I'm seeing the following behaviors: * Removing the limit (fetch first...) in lateral sub-queries makes the errors go away for all above queries. * For the last query producing "failed to build any 5-way joins" error, setting join_collapse_limit to 1, 2 and 4 makes the error go away irrespective of whether the limit is kept or not. Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
