NotificationsYou must be signed in to change notification settings
Fork6
Star31

Commitb762fed

committed

Fix LATERAL join test in test memoize.sql

The test in question was meant to be testing Memoize to ensure it workedcorrectly when the inner side of the join contained lateral vars, however,nothing in the lateral subquery stopped it from being pulled up into themain query, so the planner did that, and that meant no more lateral vars.Here we add a simple ORDER BY to stop the planner from being able topullup the lateral subquery.Author: Richard GuoDiscussion:https://postgr.es/m/CAMbWs4_LHJaN4L-tXpKMiPFnsCJWU1P8Xh59o0W7AA6UN99=cQ@mail.gmail.comBackpatch-through: 14, where Memoize was added.

1 parent8dd4389 commitb762fedCopy full SHA for b762fed

File tree

2 files changed

-5

lines changed

src/test/regress
- expected
  - memoize.out
- sql
  - memoize.sql

2 files changed

-5

lines changed

`‎src/test/regress/expected/memoize.out`

Lines changed: 5 additions & 3 deletions

Original file line number	Diff line number	Diff line change
`@@ -63,7 +63,8 @@ WHERE t2.unique1 < 1000;`
`63`	`63`	`-- Try with LATERAL joins`
`64`	`64`	`SELECT explain_memoize('`
`65`	`65`	`SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,`
`66`		`-LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2`
	`66`	`+LATERAL (SELECT t2.unique1 FROM tenk1 t2`
	`67`	`+ WHERE t1.twenty = t2.unique1 ORDER BY 1) t2`
`67`	`68`	`WHERE t1.unique1 < 1000;', false);`
`68`	`69`	`explain_memoize`
`69`	`70`	`-------------------------------------------------------------------------------------------`
`@@ -74,7 +75,7 @@ WHERE t1.unique1 < 1000;', false);`
`74`	`75`	`Rows Removed by Filter: 9000`
`75`	`76`	`-> Memoize (actual rows=1 loops=N)`
`76`	`77`	`Cache Key: t1.twenty`
`77`		`- Cache Mode:logical`
	`78`	`+ Cache Mode:binary`
`78`	`79`	`Hits: 980 Misses: 20 Evictions: Zero Overflows: 0 Memory Usage: NkB`
`79`	`80`	`-> Index Only Scan using tenk1_unique1 on tenk1 t2 (actual rows=1 loops=N)`
`80`	`81`	`Index Cond: (unique1 = t1.twenty)`
`@@ -83,7 +84,8 @@ WHERE t1.unique1 < 1000;', false);`
`83`	`84`
`84`	`85`	`-- And check we get the expected results.`
`85`	`86`	`SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,`
`86`		`-LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2`
	`87`	`+LATERAL (SELECT t2.unique1 FROM tenk1 t2`
	`88`	`+ WHERE t1.twenty = t2.unique1 ORDER BY 1) t2`
`87`	`89`	`WHERE t1.unique1 < 1000;`
`88`	`90`	`count \| avg`
`89`	`91`	`-------+--------------------`

`‎src/test/regress/sql/memoize.sql`

Lines changed: 4 additions & 2 deletions

Original file line number	Diff line number	Diff line change
`@@ -47,12 +47,14 @@ WHERE t2.unique1 < 1000;`
`47`	`47`	`-- Try with LATERAL joins`
`48`	`48`	`SELECT explain_memoize('`
`49`	`49`	`SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,`
`50`		`-LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2`
	`50`	`+LATERAL (SELECT t2.unique1 FROM tenk1 t2`
	`51`	`+ WHERE t1.twenty = t2.unique1 ORDER BY 1) t2`
`51`	`52`	`WHERE t1.unique1 < 1000;', false);`
`52`	`53`
`53`	`54`	`-- And check we get the expected results.`
`54`	`55`	`SELECTCOUNT(*),AVG(t2.unique1)FROM tenk1 t1,`
`55`		`-LATERAL (SELECTt2.unique1FROM tenk1 t2WHEREt1.twenty=t2.unique1) t2`
	`56`	`+LATERAL (SELECTt2.unique1FROM tenk1 t2`
	`57`	`+WHEREt1.twenty=t2.unique1ORDER BY1) t2`
`56`	`58`	`WHEREt1.unique1<1000;`
`57`	`59`
`58`	`60`	`-- Reduce work_mem and hash_mem_multiplier so that we see some cache evictions`

0 commit comments

Comments

(0)

Movatterモバイル変換

Navigation Menu

Search code, repositories, users, issues, pull requests...

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Commitb762fed

File tree

2 files changed

2 files changed

`‎src/test/regress/expected/memoize.out`

`‎src/test/regress/sql/memoize.sql`

0 commit comments