Re: [HACKERS] Two division by 0 errors in optimizer/plan/planner.c and optimizer/path/costsize.c

[Piotr Stefaniak]

On 2016-03-28 11:33, Aleksander Alekseev wrote:

Hello, Piotr.

Thanks for report. But I'm having some difficulties reproducing issues
you described.


Oh, if you want backtraces then either set a conditional breakpoint or 
add your own Assert like I did.



Also it would be a good idea to include these steps to regression tests.


I agree and I generally think that the more test cases touch previously 
not covered code paths the better, even if it had to be run as a 
different make(1) target. Although it seems that at least some people 
would agree (see [1]), the "make check" split somehow isn't happening.


[1] ca+tgmoymofe94+3wg3spg9sqajkv4sixjey+rdrmamye-vq...@mail.gmail.com



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Two division by 0 errors in optimizer/plan/planner.c and optimizer/path/costsize.c

[Aleksander Alekseev]

Hello, Piotr.

Thanks for report. But I'm having some difficulties reproducing issues
you described.

I compiled PostgreSQL from master branch on FreeBSD 10.2 using this
command:

```
CC=/usr/local/bin/gcc49 CFLAGS="-O0 -g" \
  ./configure --enable-cassert --enable-debug \
  --prefix=/home/eax/postgresql-install \
  && gmake clean && gmake -j2 -s
```

Then I run reinit.sh:

```
#!/usr/bin/env bash

P=~/postgresql-install

pkill -9 postgres
make install

rm -rf $P/data
$P/bin/initdb -D $P/data

echo "max_prepared_transactions = 100" >> $P/data/postgresql.conf
echo "wal_level = hot_standby" >> $P/data/postgresql.conf
echo "wal_keep_segments = 128" >> $P/data/postgresql.conf
echo "max_connections = 10" >> $P/data/postgresql.conf
echo "listen_addresses = '*'" >> $P/data/postgresql.conf

echo '' > $P/data/logfile

echo "host all all 0.0.0.0/0 trust" >> $P/data/pg_hba.conf
echo "host replication all 0.0.0.0/0 trust" >> $P/data/pg_hba.conf
echo "local replication all trust" >> $P/data/pg_hba.conf

$P/bin/pg_ctl -w -D $P/data -l $P/data/logfile start
$P/bin/createdb `whoami`
$P/bin/psql -c "create table test(k int primary key, v text);"
```

..., connected to PostgreSQL using psql, in second terminal I attached
to the backend process using gdb710 and input `c`. Now in psql:

```
eax=# create table tt5(x int);
CREATE TABLE
eax=# create table b_star(x int);
CREATE TABLE
eax=# insert into b_star values (1), (2), (3);
INSERT 0 3
eax=# insert into tt5 values (2), (3), (4), (5);
INSERT 0 4
eax=# select 1
eax-# from public.tt5 as subq_0
eax-# where EXISTS (
eax(#select 1
eax(#from public.b_star as ref_0
eax(#where false
eax(# );
 ?column? 
--
(0 rows)

eax=# select 1
eax-# from unnest('{}'::boolean[]) a (x)
eax-# left join (
eax(#select *
eax(#from unnest('{}'::boolean[])
eax(#where false
eax(# ) b (x) on a.x = b.x;
 ?column? 
--
(0 rows)

```

Everything seems to work, no stacktraces in gdb.

Could you please provide more concrete steps to reproduce these
issues i.e, OS and compiler version, compilation flags (or package
version), cluster config, database schema, etc? These steps are required
at least to make sure that fixed code really fixes a problem. Also it
would be a good idea to include these steps to regression tests.

-- 
Best regards,
Aleksander Alekseev
http://eax.me/


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Two division by 0 errors in optimizer/plan/planner.c and optimizer/path/costsize.c

[Tom Lane]

Piotr Stefaniak  writes:
> On the exact same note, something like this (again reduced from what 
> sqlsmith produced):
> leads to vardata.rel->tuples being zero here:
>   if (vardata.rel)
>   ndistinct *= vardata.rel->rows / vardata.rel->tuples;

Ugh.  That's a bit worse because it'll be 0/0, ie you get a NaN.
Thanks for the report.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Two division by 0 errors in optimizer/plan/planner.c and optimizer/path/costsize.c

[Tom Lane]

Piotr Stefaniak  writes:
> I'm not saying this is necessarily a bug since the whole function deals 
> with floats, but perhaps it's interesting to note that ndistinct can be 
> 0 in src/backend/utils/adt/selfuncs.c:estimate_hash_bucketsize:

I think it's basically cosmetic unless you've got a machine that traps
zero divide, but still that's good to fix.  Thanks for the report!

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Two division by 0 errors in optimizer/plan/planner.c and optimizer/path/costsize.c

[Piotr Stefaniak]

On 2016-03-26 19:29, Piotr Stefaniak wrote:

I'm not saying this is necessarily a bug since the whole function deals
with floats, but perhaps it's interesting to note that ndistinct can be
0 in src/backend/utils/adt/selfuncs.c:estimate_hash_bucketsize:


On the exact same note, something like this (again reduced from what 
sqlsmith produced):


select 1
from unnest('{}'::boolean[]) a (x)
left join (
  select *
  from unnest('{}'::boolean[])
  where false
) b (x) on a.x = b.x;

leads to vardata.rel->tuples being zero here:
if (vardata.rel)
ndistinct *= vardata.rel->rows / vardata.rel->tuples;

Back-trace attached.

#0  estimate_hash_bucketsize (root=0xf3cf98, hashkey=0xf44398, nbuckets=1024) 
at src/backend/utils/adt/selfuncs.c:3543
#1  0x007141a4 in final_cost_hashjoin (root=0xf3cf98, path=0xf47118, 
workspace=0x7fffd950, sjinfo=0xf45460, semifactors=0x7fffdae8) at 
src/backend/optimizer/path/costsize.c:2856
#2  0x0075c134 in create_hashjoin_path (root=0xf3cf98, 
joinrel=0xf46438, jointype=JOIN_LEFT, workspace=0x7fffd950, 
sjinfo=0xf45460, semifactors=0x7fffdae8, outer_path=0xf461a0, 
inner_path=0xf45a98, restrict_clauses=0xf466f0, required_outer=0x0, 
hashclauses=0xf471d8) at src/backend/optimizer/util/pathnode.c:2133
#3  0x0072074a in try_hashjoin_path (root=0xf3cf98, joinrel=0xf46438, 
outer_path=0xf461a0, inner_path=0xf45a98, hashclauses=0xf471d8, 
jointype=JOIN_LEFT, extra=0x7fffdad0) at 
src/backend/optimizer/path/joinpath.c:523
#4  0x007219e2 in hash_inner_and_outer (root=0xf3cf98, 
joinrel=0xf46438, outerrel=0xf44cd0, innerrel=0xf44fa8, jointype=JOIN_LEFT, 
extra=0x7fffdad0) at src/backend/optimizer/path/joinpath.c:1403
#5  0x00720058 in add_paths_to_joinrel (root=0xf3cf98, 
joinrel=0xf46438, outerrel=0xf44cd0, innerrel=0xf44fa8, jointype=JOIN_LEFT, 
sjinfo=0xf45460, restrictlist=0xf466f0) at 
src/backend/optimizer/path/joinpath.c:211
#6  0x00722e38 in make_join_rel (root=0xf3cf98, rel1=0xf44cd0, 
rel2=0xf44fa8) at src/backend/optimizer/path/joinrels.c:771
#7  0x007222dd in make_rels_by_clause_joins (root=0xf3cf98, 
old_rel=0xf44cd0, other_rels=0xf463b8) at 
src/backend/optimizer/path/joinrels.c:274
#8  0x00721f86 in join_search_one_level (root=0xf3cf98, level=2) at 
src/backend/optimizer/path/joinrels.c:96
#9  0x0070dc4d in standard_join_search (root=0xf3cf98, levels_needed=2, 
initial_rels=0xf46380) at src/backend/optimizer/path/allpaths.c:2124
#10 0x0070dbc6 in make_rel_from_joinlist (root=0xf3cf98, 
joinlist=0xf452c8) at src/backend/optimizer/path/allpaths.c:2055
#11 0x0070b304 in make_one_rel (root=0xf3cf98, joinlist=0xf452c8) at 
src/backend/optimizer/path/allpaths.c:175
#12 0x007352be in query_planner (root=0xf3cf98, tlist=0xf440b8, 
qp_callback=0x739ec7 , qp_extra=0x7fffde10) at 
src/backend/optimizer/plan/planmain.c:246
#13 0x00737d89 in grouping_planner (root=0xf3cf98, inheritance_update=0 
'\000', tuple_fraction=0) at src/backend/optimizer/plan/planner.c:1673
#14 0x00736535 in subquery_planner (glob=0xf3ad88, parse=0xf3a458, 
parent_root=0x0, hasRecursion=0 '\000', tuple_fraction=0) at 
src/backend/optimizer/plan/planner.c:758
#15 0x00735688 in standard_planner (parse=0xf3a458, cursorOptions=256, 
boundParams=0x0) at src/backend/optimizer/plan/planner.c:307
#16 0x007353ca in planner (parse=0xf3a458, cursorOptions=256, 
boundParams=0x0) at src/backend/optimizer/plan/planner.c:177
#17 0x00800d28 in pg_plan_query (querytree=0xf3a458, cursorOptions=256, 
boundParams=0x0) at src/backend/tcop/postgres.c:798
#18 0x00800ddb in pg_plan_queries (querytrees=0xf3cf60, 
cursorOptions=256, boundParams=0x0) at src/backend/tcop/postgres.c:857
#19 0x00801080 in exec_simple_query (query_string=0xf077a8 "select 
1\nfrom unnest('{}'::boolean[]) a (x)\nleft join (\n  select *\n  from 
unnest('{}'::boolean[])\n  where false\n) b (x) on a.x = b.x;") at 
src/backend/tcop/postgres.c:1022
#20 0x00805342 in PostgresMain (argc=1, argv=0xe958d0, dbname=0xe95730 
"postgres", username=0xe95710 "me") at src/backend/tcop/postgres.c:4059
#21 0x0077ed31 in BackendRun (port=0xeb2950) at 
src/backend/postmaster/postmaster.c:4258
#22 0x0077e495 in BackendStartup (port=0xeb2950) at 
src/backend/postmaster/postmaster.c:3932
#23 0x0077ac19 in ServerLoop () at 
src/backend/postmaster/postmaster.c:1690
#24 0x0077a24e in PostmasterMain (argc=3, argv=0xe94850) at 
src/backend/postmaster/postmaster.c:1298
#25 0x006c6216 in main (argc=3, argv=0xe94850) at 
src/backend/main/main.c:228
-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Two division by 0 errors in optimizer/plan/planner.c and optimizer/path/costsize.c

[Piotr Stefaniak]

I'm not saying this is necessarily a bug since the whole function deals 
with floats, but perhaps it's interesting to note that ndistinct can be 
0 in src/backend/utils/adt/selfuncs.c:estimate_hash_bucketsize:

/*
 * Initial estimate of bucketsize fraction is 1/nbuckets as long as the
 * number of buckets is less than the expected number of distinct 
values;
 * otherwise it is 1/ndistinct.
 */
if (ndistinct > nbuckets)
estfract = 1.0 / nbuckets;
else
estfract = 1.0 / ndistinct;

for this query:
select subq_0.c1 as c0
from (
  select ref_0.a as c0, (select NULL::integer from 
information_schema.user_defined_types limit 1 offset 1) as c1

  from public.rtest_nothn3 as ref_0
  limit 130
) as subq_0
left join (
  select sample_0.x as c0
  from public.insert_tbl as sample_0
  where false
) as subq_1 on subq_0.c1 = subq_1.c0;


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Two division by 0 errors in optimizer/plan/planner.c and optimizer/path/costsize.c

[Tom Lane]

Piotr Stefaniak  writes:
> using sqlsmith and UBSan I have found these two division by zero errors:

Hmm, thanks.  Seems there's a bit of a disagreement as to whether
path->rows is allowed to be zero or not.  It normally isn't; but
we've created an exception that provably-empty relations have zero
rowcount, so now these places had better deal with the case.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Two division by 0 errors in optimizer/plan/planner.c and optimizer/path/costsize.c

[Piotr Stefaniak]

Hi,

using sqlsmith and UBSan I have found these two division by zero errors:

src/backend/optimizer/plan/planner.c:4846
/* Convert absolute # of tuples to a fraction; no need to clamp */
if (tuple_fraction >= 1.0)
{
tuple_fraction /= best_path->rows;
}

and

src/backend/optimizer/path/costsize.c:3029
if (subplan->subLinkType == EXISTS_SUBLINK)
{
/* we only need to fetch 1 tuple */
sp_cost.per_tuple += plan_run_cost / plan->plan_rows;
}

The first is triggered by this query (reduced by me from the original 
query string generated by sqlsmith):


select 1
from (
  select ref_0.location as c0
  from public.city as ref_0
) as subq_0
where EXISTS (
  select 1
  from (
select sample_0.collname as c0
from pg_catalog.pg_collation as sample_0
  ) as subq_1
  right join public.tt5 as ref_2
inner join pg_catalog.pg_constraint as ref_4
on (ref_2.z = ref_4.coninhcount )
  on (subq_1.c0 = ref_4.conname ),
  lateral (
select 1
from public.shoelace_candelete as ref_5
where false
  ) as subq_2
);

#0  get_cheapest_fractional_path (rel=0x77ec32a8, tuple_fraction=1) 
at src/backend/optimizer/plan/planner.c:4846
#1  0x007422a1 in make_subplan (root=0xf49778, 
orig_subquery=0x77f593c8, subLinkType=EXISTS_SUBLINK, subLinkId=0, 
testexpr=0x0, isTopQual=1 '\001') at 
src/backend/optimizer/plan/subselect.c:546
#2  0x0074470d in process_sublinks_mutator (node=0x77f610b0, 
context=0x7fffd900) at src/backend/optimizer/plan/subselect.c:1974
#3  0x00744670 in SS_process_sublinks (root=0xf49778, 
expr=0x77f610b0, isQual=1 '\001') at 
src/backend/optimizer/plan/subselect.c:1947
#4  0x00736621 in preprocess_expression (root=0xf49778, 
expr=0x77f610b0, kind=0) at src/backend/optimizer/plan/planner.c:848
#5  0x00736700 in preprocess_qual_conditions (root=0xf49778, 
jtnode=0xf5f790) at src/backend/optimizer/plan/planner.c:893
#6  0x00735ff3 in subquery_planner (glob=0xf3ef70, 
parse=0xf3e9a0, parent_root=0x0, hasRecursion=0 '\000', 
tuple_fraction=0) at src/backend/optimizer/plan/planner.c:600
#7  0x0073566b in standard_planner (parse=0xf3e9a0, 
cursorOptions=256, boundParams=0x0) at 
src/backend/optimizer/plan/planner.c:307
#8  0x007353ad in planner (parse=0xf3e9a0, cursorOptions=256, 
boundParams=0x0) at src/backend/optimizer/plan/planner.c:177
#9  0x00800d3b in pg_plan_query (querytree=0xf3e9a0, 
cursorOptions=256, boundParams=0x0) at src/backend/tcop/postgres.c:798
#10 0x00800dee in pg_plan_queries (querytrees=0xf53648, 
cursorOptions=256, boundParams=0x0) at src/backend/tcop/postgres.c:857
#11 0x00801093 in exec_simple_query (query_string=0xf07dd8 
"select 1\nfrom (\n  select ref_0.location as c0\n  from public.city as 
ref_0\n) as subq_0\nwhere EXISTS (\n  select 1\n  from (\nselect 
sample_0.collname as c0\nfrom pg_catalog.pg_collation as sample_0\n 
 ) as subq_1\n  right join public.tt5 as ref_2\ninner join 
pg_catalog.pg_constraint as ref_4\non (ref_2.z = ref_4.coninhcount 
)\n  on (subq_1.c0 = ref_4.conname ),\n  lateral (\nselect 1\n 
from public.shoelace_candelete as ref_5\nwhere false\n  ) as 
subq_2\n);") at src/backend/tcop/postgres.c:1022
#12 0x00805355 in PostgresMain (argc=1, argv=0xe95ee0, 
dbname=0xe95d40 "regression", username=0xe95d20 "me") at 
src/backend/tcop/postgres.c:4059
#13 0x0077ed44 in BackendRun (port=0xeb2f80) at 
src/backend/postmaster/postmaster.c:4258
#14 0x0077e4a8 in BackendStartup (port=0xeb2f80) at 
src/backend/postmaster/postmaster.c:3932
#15 0x0077ac2c in ServerLoop () at 
src/backend/postmaster/postmaster.c:1690
#16 0x0077a261 in PostmasterMain (argc=5, argv=0xe94e10) at 
src/backend/postmaster/postmaster.c:1298
#17 0x006c623c in main (argc=5, argv=0xe94e10) at 
src/backend/main/main.c:228


The second one is triggered by this (again, reduced from the original):

select 1
from public.tt5 as subq_0
where EXISTS (
  select 1
  from public.b_star as ref_0
  where false
);

#0  cost_subplan (root=0xf3e718, subplan=0xf42780, plan=0xf3fcd8) at 
src/backend/optimizer/path/costsize.c:3029
#1  0x00742eb9 in build_subplan (root=0xf3e718, plan=0xf3fcd8, 
subroot=0xf3f6a8, plan_params=0x0, subLinkType=EXISTS_SUBLINK, 
subLinkId=0, testexpr=0x0, adjust_testexpr=1 '\001', unknownEqFalse=1 
'\001') at src/backend/optimizer/plan/subselect.c:887
#2  0x007422c0 in make_subplan (root=0xf3e718, 
orig_subquery=0xf09628, subLinkType=EXISTS_SUBLINK, subLinkId=0, 
testexpr=0x0, isTopQual=1 '\001') at 
src/backend/optimizer/plan/subselect.c:551
#3  0x007446d7 in process_sublinks_mutator (node=0xf3f100, 
context=0x7fffd900) at src/backend/optimizer/plan/subselect.c:1974
#4  0x0074463a in SS_process_sublinks (root=0xf3e718, 
expr=0xf3f100,