search

tds-fdw / tds_fdw

A PostgreSQL foreign data wrapper to connect to TDS databases (Sybase and Microsoft SQL Server)
Other
380 stars 102 forks source link

PG 12 common table expressions #251

Open Jnb2387 opened 4 years ago

Jnb2387 commented 4 years ago

When using pg 12 and tds_fdw 2.0.1 I can not get this query to return any results, but with the same data and using pg 9.5 and tds_fdw 2.0.0-alpha.3 it returns results.

query from postgres WITH query documentation.

WITH regional_sales AS ( SELECT region, SUM(amount) AS total_sales FROM orders GROUP BY region ), top_regions AS ( SELECT region FROM regional_sales WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales) ) SELECT region, product, SUM(quantity) AS product_units, SUM(amount) AS product_sales FROM orders WHERE region IN (SELECT region FROM top_regions) GROUP BY region, product;

if table orders is a foreign table I get no results using pg 12, but it works just fine in 9.5. As soon as I make the orders table a static table in postgres it returns results in pg 12.

merlinm commented 4 years ago

hm. what happens when you do WITH MATERIALIZED?