Re: [sqlsmith] Failed to generate plan on lateral subqueries

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