Thread
-
Query causesExecSubPlan: NULL value error
Rick Smith <rsmith@artschool.com> — 2000-06-12T17:13:08Z
============================================================================ POSTGRESQL BUG REPORT TEMPLATE ============================================================================ Your name : Richard Smith Your email address : Rsmith@artschool.com System Configuration --------------------- Architecture (example: Intel Pentium) :Intel Pentium Operating System (example: Linux 2.0.26 ELF) :Red Hat Linux 6.2 (Stock) PostgreSQL version (example: PostgreSQL-6.5.3) :PostgreSQL-6.5.3 Compiler used (example: gcc 2.8.0) :n/a Please enter a FULL description of your problem: ------------------------------------------------ I am studying Postgres and have been working thru Bruce Momjian's book. In figure 8.9 on page 79 I am doing the second query in that figure. I have typed it in exactly as he presented it but I get a: "ERROR: ExecSubPlan: null value returned by expression subselect" type error. I belive my table structures are ok, because the previous examples (including the one on the same page) have worked on it. I emailed this problem to Bruce and he replied: "That is strange. It sounds like a PostgreSQL bug. My guess is that there is one or two rows that is causing this. Please report it to the bugs list so we can fix it." I have copied and pasted the query and the error message below: test1=> SELECT f1.firstname, f1.lastname, f1.age FROM friend f1 WHERE age = ( test1-> SELECT MAX(f2.age) FROM friend f2 WHERE f1.state = f2.state ) test1-> ORDER BY firstname, lastname ; ERROR: ExecSubPlan: null value returned by expression subselect test1=> I have copied the data set: test1=> SELECT * FROM friend ; firstname |lastname |city |state|age ---------------+--------------------+---------------+-----+--- Cindy |Anderson |Denver |CO | 23 Mike |Nichols |Tampa Bay |FL | 20 Mark |Middleton |Indianapolis |IN | Jack |Burger | | | 27 Dean |Yeager |Plymouth |MA | 24 Ned |Millstone |Cedar Creek |MD | 27 Sandy |Gleason |Ocean City |MA | 33 Victor |Tabor |Williamsport |PA | 22 Dick |Gleason |Ocean City |NJ | 20 Mari-Jo |Anderson |Long Island |NY | 32 Pete |Moxeiter |Saskatoon |SK | 35 Sid |Gleason |Denver |CO | 21 Sam |I Am |New York |NY | 27 Sam |Jackson |Allentown |PA | 23 Deb |Bouchier |Saskatoon |SK | 32 Natalie |Fisher |Fortbywater |NJ | 22 (16 rows) test1=> Please describe a way to repeat the problem. Please try to provide a concise reproducible example, if at all possible: ---------------------------------------------------------------------- This problem occures everytime I run the query. -
Re: Query causesExecSubPlan: NULL value error
Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-12T17:34:48Z
"Rick Smith" <rsmith@artschool.com> writes: > PostgreSQL version (example: PostgreSQL-6.5.3) :PostgreSQL-6.5.3 ^^^^^^^^^^^^^^^^ > ERROR: ExecSubPlan: null value returned by expression subselect 6.5 and older don't cope with sub-selects returning NULL. This is fixed in Postgres 7.0. regards, tom lane -
Re: Query causesExecSubPlan: NULL value error
Bruce Momjian <pgman@candle.pha.pa.us> — 2000-06-12T18:10:59Z
Cool. Thanks, Tom. > "Rick Smith" <rsmith@artschool.com> writes: > > PostgreSQL version (example: PostgreSQL-6.5.3) :PostgreSQL-6.5.3 > ^^^^^^^^^^^^^^^^ > > ERROR: ExecSubPlan: null value returned by expression subselect > > 6.5 and older don't cope with sub-selects returning NULL. This is fixed > in Postgres 7.0. > > regards, tom lane > -- Bruce Momjian | http://www.op.net/~candle pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026