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
Attachments:
DFtext/plain; charset=us-ascii; name=DFDownload
*** 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
Attachments:
DFtext/plain; charset=us-ascii; name=DFDownload
*** 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
VAdim,
Great work.
Keith.
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
.
.
.
.
.
248| 25|Elvis Presley|The Wonder Of You
248| 26|Elvis Presley|Bridge Over Troubled Water
(73 rows)
disks=>
Vadim B. Mikheev <vadim@sable.krasnoyarsk.su>
Show quoted text
Sorry, I decided to copy subselect' tuple unconditionally...
Vadim
Import Notes
Resolved by subject fallback