From: | Keith Parks <emkxp01(at)mtcc(dot)demon(dot)co(dot)uk> |
---|---|
To: | hackers(at)postgresql(dot)org |
Subject: | Unexpected subselect result. |
Date: | 1998-02-18 22:13:56 |
Message-ID: | 199802182213.WAA23713@mtcc.demon.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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=>
From | Date | Subject | |
---|---|---|---|
Next Message | Vadim B. Mikheev | 1998-02-19 01:13:04 | Re: [HACKERS] new Group BY code |
Previous Message | Yurik V. Nazaroff | 1998-02-18 21:26:21 | Re: AW: [HACKERS] Re: Subselects open issue Nr. NEW |