generate_base_implied_equalities_broken(PlannerInfo*root,

{

RestrictInfo*restrictinfo= (RestrictInfo*)lfirst(lc);

if (bms_membership(restrictinfo->required_relids)!=BMS_MULTIPLE)

if (ec->ec_has_const||

bms_membership(restrictinfo->required_relids)!=BMS_MULTIPLE)

distribute_restrictinfo_to_rels(root,restrictinfo);

}

}

--

-- Tests for the planner's "equivalence class" mechanism

--

-- One thing that's not tested well during normal querying is the logic

-- for handling "broken" ECs. This is because an EC can only become broken

-- if its underlying btree operator family doesn't include a complete set

-- of cross-type equality operators. There are not (and should not be)

-- any such families built into Postgres; so we have to hack things up

-- to create one. We do this by making two alias types that are really

-- int8 (so we need no new C code) and adding only some operators for them

-- into the standard integer_ops opfamily.

create type int8alias1;

create function int8alias1in(cstring) returns int8alias1

strict immutable language internal as 'int8in';

NOTICE: return type int8alias1 is only a shell

create function int8alias1out(int8alias1) returns cstring

strict immutable language internal as 'int8out';

NOTICE: argument type int8alias1 is only a shell

create type int8alias1 (

input = int8alias1in,

output = int8alias1out,

like = int8

);

create type int8alias2;

create function int8alias2in(cstring) returns int8alias2

strict immutable language internal as 'int8in';

NOTICE: return type int8alias2 is only a shell

create function int8alias2out(int8alias2) returns cstring

strict immutable language internal as 'int8out';

NOTICE: argument type int8alias2 is only a shell

create type int8alias2 (

input = int8alias2in,

output = int8alias2out,

like = int8

);

create cast (int8 as int8alias1) without function;

create cast (int8 as int8alias2) without function;

create cast (int8alias1 as int8) without function;

create cast (int8alias2 as int8) without function;

create function int8alias1eq(int8alias1, int8alias1) returns bool

strict immutable language internal as 'int8eq';

create operator = (

procedure = int8alias1eq,

leftarg = int8alias1, rightarg = int8alias1,

commutator = =,

restrict = eqsel, join = eqjoinsel,

merges

);

alter operator family integer_ops using btree add

operator 3 = (int8alias1, int8alias1);

create function int8alias2eq(int8alias2, int8alias2) returns bool

strict immutable language internal as 'int8eq';

create operator = (

procedure = int8alias2eq,

leftarg = int8alias2, rightarg = int8alias2,

commutator = =,

restrict = eqsel, join = eqjoinsel,

merges

);

alter operator family integer_ops using btree add

operator 3 = (int8alias2, int8alias2);

create function int8alias1eq(int8, int8alias1) returns bool

strict immutable language internal as 'int8eq';

create operator = (

procedure = int8alias1eq,

leftarg = int8, rightarg = int8alias1,

restrict = eqsel, join = eqjoinsel,

merges

);

alter operator family integer_ops using btree add

operator 3 = (int8, int8alias1);

create function int8alias1eq(int8alias1, int8alias2) returns bool

strict immutable language internal as 'int8eq';

create operator = (

procedure = int8alias1eq,

leftarg = int8alias1, rightarg = int8alias2,

restrict = eqsel, join = eqjoinsel,

merges

);

alter operator family integer_ops using btree add

operator 3 = (int8alias1, int8alias2);

create function int8alias1lt(int8alias1, int8alias1) returns bool

strict immutable language internal as 'int8lt';

create operator < (

procedure = int8alias1lt,

leftarg = int8alias1, rightarg = int8alias1

);

alter operator family integer_ops using btree add

operator 1 < (int8alias1, int8alias1);

create function int8alias1cmp(int8, int8alias1) returns int

strict immutable language internal as 'btint8cmp';

alter operator family integer_ops using btree add

function 1 int8alias1cmp (int8, int8alias1);

create table ec0 (ff int8 primary key, f1 int8, f2 int8);

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ec0_pkey" for table "ec0"

create table ec1 (ff int8 primary key, f1 int8alias1, f2 int8alias2);

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ec1_pkey" for table "ec1"

create table ec2 (xf int8 primary key, x1 int8alias1, x2 int8alias2);

NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "ec2_pkey" for table "ec2"

-- for the moment we only want to look at nestloop plans

set enable_hashjoin = off;

set enable_mergejoin = off;

--

-- Note that for cases where there's a missing operator, we don't care so

-- much whether the plan is ideal as that we don't fail or generate an

-- outright incorrect plan.

--

explain (costs off)

select * from ec0 where ff = f1 and f1 = '42'::int8;

QUERY PLAN

----------------------------------

Index Scan using ec0_pkey on ec0

Index Cond: (ff = 42::bigint)

Filter: (f1 = 42::bigint)

(3 rows)

explain (costs off)

select * from ec0 where ff = f1 and f1 = '42'::int8alias1;

QUERY PLAN

---------------------------------------

Index Scan using ec0_pkey on ec0

Index Cond: (ff = '42'::int8alias1)

Filter: (f1 = '42'::int8alias1)

(3 rows)

explain (costs off)

select * from ec1 where ff = f1 and f1 = '42'::int8alias1;

QUERY PLAN

---------------------------------------

Index Scan using ec1_pkey on ec1

Index Cond: (ff = '42'::int8alias1)

Filter: (f1 = '42'::int8alias1)

(3 rows)

explain (costs off)

select * from ec1 where ff = f1 and f1 = '42'::int8alias2;

QUERY PLAN

---------------------------------------------------

Seq Scan on ec1

Filter: ((ff = f1) AND (f1 = '42'::int8alias2))

(2 rows)

explain (costs off)

select * from ec1, ec2 where ff = x1 and ff = '42'::int8;

QUERY PLAN

---------------------------------------------------------------

Nested Loop

Join Filter: (ec1.ff = ec2.x1)

-> Index Scan using ec1_pkey on ec1

Index Cond: ((ff = 42::bigint) AND (ff = 42::bigint))

-> Seq Scan on ec2

(5 rows)

explain (costs off)

select * from ec1, ec2 where ff = x1 and ff = '42'::int8alias1;

QUERY PLAN

---------------------------------------------

Nested Loop

-> Index Scan using ec1_pkey on ec1

Index Cond: (ff = '42'::int8alias1)

-> Seq Scan on ec2

Filter: (ec2.x1 = '42'::int8alias1)

(5 rows)

explain (costs off)

select * from ec1, ec2 where ff = x1 and '42'::int8 = x1;

QUERY PLAN

----------------------------------------

Nested Loop

Join Filter: (ec1.ff = ec2.x1)

-> Index Scan using ec1_pkey on ec1

Index Cond: (ff = 42::bigint)

-> Seq Scan on ec2

Filter: (42::bigint = ec2.x1)

(6 rows)

explain (costs off)

select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias1;

QUERY PLAN

---------------------------------------------

Nested Loop

-> Index Scan using ec1_pkey on ec1

Index Cond: (ff = '42'::int8alias1)

-> Seq Scan on ec2

Filter: (ec2.x1 = '42'::int8alias1)

(5 rows)

explain (costs off)

select * from ec1, ec2 where ff = x1 and x1 = '42'::int8alias2;

QUERY PLAN

-----------------------------------------

Nested Loop

-> Seq Scan on ec2

Filter: (x1 = '42'::int8alias2)

-> Index Scan using ec1_pkey on ec1

Index Cond: (ec1.ff = ec2.x1)

(5 rows)

create unique index ec1_expr1 on ec1((ff + 1));

create unique index ec1_expr2 on ec1((ff + 2 + 1));

create unique index ec1_expr3 on ec1((ff + 3 + 1));

create unique index ec1_expr4 on ec1((ff + 4));

explain (costs off)

select * from ec1,

(select ff + 1 as x from

(select ff + 2 as ff from ec1

union all

select ff + 3 as ff from ec1) ss0

union all

select ff + 4 as x from ec1) as ss1

where ss1.x = ec1.f1 and ec1.ff = 42::int8;

QUERY PLAN

-----------------------------------------------------------------

Nested Loop

Join Filter: ((((public.ec1.ff + 2) + 1)) = public.ec1.f1)

-> Index Scan using ec1_pkey on ec1

Index Cond: (ff = 42::bigint)

-> Append

-> Append

-> Seq Scan on ec1

-> Seq Scan on ec1

-> Index Scan using ec1_expr4 on ec1

Index Cond: ((public.ec1.ff + 4) = public.ec1.f1)

(10 rows)

explain (costs off)

select * from ec1,

(select ff + 1 as x from

(select ff + 2 as ff from ec1

union all

select ff + 3 as ff from ec1) ss0

union all

select ff + 4 as x from ec1) as ss1

where ss1.x = ec1.f1 and ec1.ff = 42::int8 and ec1.ff = ec1.f1;

QUERY PLAN

--------------------------------------------------------------------

Nested Loop

Join Filter: ((((public.ec1.ff + 2) + 1)) = public.ec1.f1)

-> Index Scan using ec1_pkey on ec1

Index Cond: ((ff = 42::bigint) AND (ff = 42::bigint))

Filter: (ff = f1)

-> Append

-> Index Scan using ec1_expr2 on ec1

Index Cond: (((public.ec1.ff + 2) + 1) = 42::bigint)

-> Index Scan using ec1_expr3 on ec1

Index Cond: (((public.ec1.ff + 3) + 1) = 42::bigint)

-> Index Scan using ec1_expr4 on ec1

Index Cond: ((public.ec1.ff + 4) = 42::bigint)

(12 rows)

explain (costs off)

select * from ec1,

(select ff + 1 as x from

(select ff + 2 as ff from ec1

union all

select ff + 3 as ff from ec1) ss0

union all

select ff + 4 as x from ec1) as ss1,

(select ff + 1 as x from

(select ff + 2 as ff from ec1

union all

select ff + 3 as ff from ec1) ss0

union all

select ff + 4 as x from ec1) as ss2

where ss1.x = ec1.f1 and ss1.x = ss2.x and ec1.ff = 42::int8;

QUERY PLAN

-----------------------------------------------------------------------------

Nested Loop

Join Filter: ((((public.ec1.ff + 2) + 1)) = (((public.ec1.ff + 2) + 1)))

-> Append

-> Seq Scan on ec1

-> Seq Scan on ec1

-> Seq Scan on ec1

-> Materialize

-> Nested Loop

Join Filter: ((((public.ec1.ff + 2) + 1)) = public.ec1.f1)

-> Index Scan using ec1_pkey on ec1

Index Cond: (ff = 42::bigint)

-> Append

-> Append

-> Seq Scan on ec1

-> Seq Scan on ec1

-> Index Scan using ec1_expr4 on ec1

Index Cond: ((public.ec1.ff + 4) = public.ec1.f1)

(17 rows)

-- check partially indexed scan

drop index ec1_expr3;

explain (costs off)

select * from ec1,

(select ff + 1 as x from

(select ff + 2 as ff from ec1

union all

select ff + 3 as ff from ec1) ss0

union all

select ff + 4 as x from ec1) as ss1

where ss1.x = ec1.f1 and ec1.ff = 42::int8;

QUERY PLAN

-----------------------------------------------------------------

Nested Loop

Join Filter: ((((public.ec1.ff + 2) + 1)) = public.ec1.f1)

-> Index Scan using ec1_pkey on ec1

Index Cond: (ff = 42::bigint)

-> Append

-> Append

-> Seq Scan on ec1

-> Seq Scan on ec1

-> Index Scan using ec1_expr4 on ec1

Index Cond: ((public.ec1.ff + 4) = public.ec1.f1)

(10 rows)

# ----------

# Another group of parallel tests

# ----------

test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap

test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combocid tsearch tsdicts foreign_data window xmlmap equivclass

# ----------

# Another group of parallel tests

test: foreign_data

test: window

test: xmlmap

test: equivclass

test: plancache

test: limit

test: plpgsql