Thread

Unexpected subselect result.

Keith Parks <emkxp01@mtcc.demon.co.uk> — 1998-02-18T22:13:56Z

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=>

Re: [HACKERS] Unexpected subselect result.

Vadim Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-19T02:28:16Z

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

Re: [HACKERS] Unexpected subselect result.

Vadim Mikheev <vadim@sable.krasnoyarsk.su> — 1998-02-19T02:55:52Z
```
Sorry, I decided to copy subselect' tuple unconditionally...

Vadim
```

Re: [HACKERS] Unexpected subselect result.

Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-02-19T05:00:08Z

> 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