Re: Is there value in having optimizer stats for joins/foreignkeys?

[Tomas Vondra]

There seems to be an issue in CreateStatistics(), triggering an assert.

Consider this:

CREATE TABLE t1 (id int);
CREATE TABLE t2 (id int, y int);

CREATE INDEX t2_idx ON t2 (id);

-- this errors-out
CREATE STATISTICS s
    ON t2.y
  FROM t2;

-- this crashes
CREATE STATISTICS s
    ON t2.y
  FROM t1 JOIN t2 ON t1.id = t2.id;

With a single-table stats, this this fails with

ERROR: cannot create extended statistics on a single non-virtual column

but with the join, this gets skipped, because it's gated by

  if (numcols == 1 && !isjoin)
  {
    ...
  }

and so it hits the assert on line ~714:

  Assert(ntypes > 0 && ntypes <= lengthof(types));

I suppose we joins we want to allow single-column stats, but in that
case we should be careful about properly filling some join kinds. It
probably should be treated as expression (STATS_EXT_EXPRESSIONS), but I
haven't tried.


regards

-- 
Tomas Vondra