BUG #12760: Lateral files with more than 2 laterals - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      12760
Logged by:          momomo
Email address:      moe1234512345@gmail.com
PostgreSQL version: 9.3.5
Operating system:   UBUNTU
Description:

set geqo_threshold  = 2;

SELECT *
FROM generate_series(1, 1) A,
lateral ( SELECT B from generate_series(1, 1) B where B = A limit 1 ) AS
B0,
lateral ( SELECT C from generate_series(1, 1) C where C = A and C != B limit
1 ) AS C0


Here is the original query:

SET geqo_threshold  = 24;
SELECT
        A.name, A.symbol,
        P0.percent AS P0_percent,
        P1.percent AS P1_percent,
        P2.percent AS P2_percent,
        P3.percent AS P3_percent

        /*,
        B0.usd AS P0_start, C0.usd AS P0_end, B0.date AS P0_starttime,
C0.date AS P0_endtime,
        B1.usd AS P1_start, C1.usd AS P1_end, B1.date AS P1_starttime,
C1.date AS P1_endtime*/


FROM ticker A,

lateral ( SELECT B0.usd, B0.date from priceclose B0 where B0.date BETWEEN
'2014-10-31' AND '2015-01-15' AND B0.ticker = A.symbol ORDER BY B0.date ASC
limit 1 ) AS B0,

lateral ( SELECT C0.usd, C0.date from priceclose C0 where C0.date BETWEEN
'2014-10-31' AND '2015-01-15' AND C0.ticker = A.symbol AND C0.date !=
B0.date ORDER BY C0.date DESC limit 1 ) AS C0,


lateral ( SELECT B1.usd, B1.date from priceclose B1 where B1.date BETWEEN
'2015-01-08' AND '2015-12-10' AND B1.ticker = A.symbol ORDER BY B1.date ASC
limit 1 ) AS B1,

lateral ( SELECT C1.usd, C1.date from priceclose C1 where C1.date BETWEEN
'2015-01-08' AND '2015-12-10' AND C1.ticker = A.symbol AND C1.date !=
B1.date ORDER BY C1.date DESC limit 1 ) AS C1,


lateral ( SELECT B2.usd, B2.date from priceclose B2 where B2.date BETWEEN
'2015-01-31' AND '2015-12-10' AND B2.ticker = A.symbol ORDER BY B2.date ASC
limit 1 ) AS B2,

lateral ( SELECT C2.usd, C2.date from priceclose C2 where C2.date BETWEEN
'2015-01-31' AND '2015-12-10' AND C2.ticker = A.symbol AND C2.date !=
B2.date ORDER BY C2.date DESC limit 1 ) AS C2,


lateral ( SELECT B3.usd, B3.date from priceclose B3 where B3.date BETWEEN
'2015-02-06' AND '2015-12-10' AND B3.ticker = A.symbol ORDER BY B3.date ASC
limit 1 ) AS B3,

lateral ( SELECT C3.usd, C3.date from priceclose C3 where C3.date BETWEEN
'2015-02-06' AND '2015-12-10' AND C3.ticker = A.symbol AND C3.date !=
B3.date ORDER BY C3.date DESC limit 1 ) AS C3,


lateral ( SELECT C0.usd / B0.usd AS percent ) AS P0,

lateral ( SELECT C1.usd / B1.usd AS percent ) AS P1,

lateral ( SELECT C2.usd / B2.usd AS percent ) AS P2,

lateral ( SELECT C3.usd / B3.usd AS percent ) AS P3

WHERE

P0.percent BETWEEN 0.4 AND 0.7

AND

P1.percent BETWEEN 1.08 AND 10

AND

P2.percent BETWEEN 1.03 AND 10

AND

P3.percent BETWEEN 1.01 AND 10

ORDER BY P1.percent DESC;


Note, setting geqo_threshold = 24 makes this error disappear.