Thread: Unexpected subselect result.
Hi, I was having a play with subselects and found this, not very clever, nested subselect didn't work as I expected. Any Ideas? I also noticed that the subselect regression tests include only int and float columns in the tests. Keith. NOTICE: QUERY PLAN: Index Scan on t1 (cost=2.77 size=16 width=32) InitPlan -> Unique (cost=188.57 size=0 width=0) InitPlan -> Aggregate (cost=188.57 size=0 width=0) -> Seq Scan on t3 (cost=188.57 size=4199 width=4) -> Sort (cost=188.57 size=0 width=0) -> Seq Scan on t2 (cost=188.57 size=64 width=12) EXPLAIN disks=> select * from tracks t1 where t1.artist = ( disks-> select distinct t2.artist from tracks t2 where t2.trackno = ( disks-> select max(t3.trackno) from tracks t3 disks-> ) disks-> ); diskid|trackno|artist |song ------+-------+-------------+------------- 51| 1|Elvis Presley|Hound Dog 65| 1|Elvis Presley|She's Not You (2 rows) disks=> select max(t3.trackno) from tracks t3; max --- 26 (1 row) disks=> select distinct t2.artist from tracks t2 where t2.trackno = 26; artist ------------- Elvis Presley (1 row) disks=> select * from tracks t1 where t1.artist = 'Elvis Presley'; diskid|trackno|artist |song ------+-------+-------------+----------------------------------- 51| 1|Elvis Presley|Hound Dog 65| 1|Elvis Presley|She's Not You 65| 2|Elvis Presley|Return To Sender 65| 3|Elvis Presley|(You're The) Devil In Disguise 65| 4|Elvis Presley|Crying In The Chapel 65| 5|Elvis Presley|Love Letters 65| 6|Elvis Presley|If I Can Dream 65| 7|Elvis Presley|In The Ghetto 65| 8|Elvis Presley|Suspicious Minds 65| 9|Elvis Presley|Don't Cry Daddy 65| 10|Elvis Presley|The Wonder Of You 65| 11|Elvis Presley|I Just Can't Help Believin' 65| 12|Elvis Presley|An American Trilogy 65| 13|Elvis Presley|Burning Love 65| 14|Elvis Presley|Always On My Mind 65| 15|Elvis Presley|My Boy 65| 16|Elvis Presley|Suspicion 65| 17|Elvis Presley|Moody Blue 65| 18|Elvis Presley|Way Down 65| 19|Elvis Presley|It's Only Love 66| 1|Elvis Presley|Heartbreak Hotel 66| 2|Elvis Presley|Blue Suede Shoes 66| 3|Elvis Presley|Hound Dog 66| 4|Elvis Presley|Love Me Tender 66| 5|Elvis Presley|Too Much 66| 6|Elvis Presley|All Shook Up 66| 7|Elvis Presley|Teddy Bear 66| 8|Elvis Presley|Paralysed 66| 9|Elvis Presley|Party 66| 10|Elvis Presley|Jailhouse Rock 66| 11|Elvis Presley|Don't 66| 12|Elvis Presley|Wear My Ring Around Your Neck 66| 13|Elvis Presley|Hard Headed Woman 66| 14|Elvis Presley|King Creole 66| 15|Elvis Presley|One Night 66| 16|Elvis Presley|A Fool Such As I 66| 17|Elvis Presley|A Big Hunk O' Love 66| 18|Elvis Presley|Stuck On You 66| 19|Elvis Presley|The Girl Of My Best Friend 66| 20|Elvis Presley|It's Now Or Never 66| 21|Elvis Presley|Are You Lonesome Tonight? 66| 22|Elvis Presley|Wooden Heart 66| 23|Elvis Presley|Surrender 66| 24|Elvis Presley|(Marie's The Name) His Latest Flame 66| 25|Elvis Presley|Can't Help Falling In Love 66| 26|Elvis Presley|Good Luck Charm 231| 11|Elvis Presley|Can't Help Falling In Love 248| 1|Elvis Presley|Always On My Mind 248| 2|Elvis Presley|I Just Can't Help Believin' 248| 3|Elvis Presley|Suspicious Minds 248| 4|Elvis Presley|Can't Help Falling In Love 248| 5|Elvis Presley|Are You Lonesome Tonight? 248| 6|Elvis Presley|The Girl Of My Best Friend 248| 7|Elvis Presley|It's Now Or Never 248| 8|Elvis Presley|Love Me Tender 248| 9|Elvis Presley|Fever 248| 10|Elvis Presley|Surrender 248| 11|Elvis Presley|Love Me 248| 12|Elvis Presley|Loving You 248| 13|Elvis Presley|She's Not You 248| 14|Elvis Presley|A Fool Such As I 248| 15|Elvis Presley|Suspicion 248| 16|Elvis Presley|Love Letters 248| 17|Elvis Presley|And I Love You So 248| 18|Elvis Presley|Help Me Make It Through The Night 248| 19|Elvis Presley|You Don't Have To Say You Love Me 248| 20|Elvis Presley|Kentucky Rain 248| 21|Elvis Presley|Gentle On My Mind 248| 22|Elvis Presley|Let It Be Me 248| 23|Elvis Presley|Spanish Eyes 248| 24|Elvis Presley|It's Only Love 248| 25|Elvis Presley|The Wonder Of You 248| 26|Elvis Presley|Bridge Over Troubled Water (73 rows) disks=>
Keith Parks wrote: > > Hi, > > I was having a play with subselects and found this, not very clever, > nested subselect didn't work as I expected. > > Any Ideas? > > I also noticed that the subselect regression tests include only > int and float columns in the tests. Thanks, Keith! Vadim *** nodeSubplan.c.orig Thu Feb 19 08:54:19 1998 --- nodeSubplan.c Thu Feb 19 09:10:25 1998 *************** *** 193,198 **** --- 193,206 ---- break; } + /* + * If this is uncorrelated subquery then its plan will be closed + * (see below) and this tuple will be free-ed - bad for not byval + * types... + */ + if ( plan->extParam == NULL ) + tup = heap_copytuple (tup); + foreach (lst, node->setParam) { ParamExecData *prm = &(plan->state->es_param_exec_vals[lfirsti(lst)]);
Sorry, I decided to copy subselect' tuple unconditionally... Vadim *** nodeSubplan.c.orig Thu Feb 19 08:54:19 1998 --- nodeSubplan.c Thu Feb 19 09:51:53 1998 *************** *** 193,198 **** --- 193,207 ---- break; } + /* + * If this is uncorrelated subquery then its plan will be closed + * (see below) and this tuple will be free-ed - bad for not byval + * types... But is free-ing possible in the next ExecProcNode in + * this loop ? Who knows... Someday we'll keep track of saved + * tuples... + */ + tup = heap_copytuple (tup); + foreach (lst, node->setParam) { ParamExecData *prm = &(plan->state->es_param_exec_vals[lfirsti(lst)]);
> I also noticed that the subselect regression tests include only > int and float columns in the tests. Yeah, if you or someone wants to augment it that would be great. Change and add as much as you want; I did it in a rush... - Tom