Обсуждение: 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