PG Folks,

Could we have a .conf option to turn OFF the "Adding Missing From Clause for 
Table ... "?    I'd far rather have a fatal query error than have Postgres 
automatically insert the tables it thinks I wanted. This automated adding in 
tables also seems very inconsistent with Postgres' dedication to accuracy 
above all.

Apologies if this is already taken care of in 7.4.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Tue, 29 Apr 2003 14:36:43 -0700
Josh Berkus <josh@agliodbs.com>
"Adding missing from clause"
Josh Berkus <josh@agliodbs.com> writes:
> Could we have a .conf option to turn OFF the "Adding Missing From Clause for 
> Table ... "?

No objection here.  Send a patch.

			regards, tom lane


Tue, 29 Apr 2003 18:38:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: "Adding missing from clause" 
At 02:36 PM 29/04/2003 -0700, Josh Berkus wrote:
>I'd far rather have a fatal query error than have Postgres
>automatically insert the tables it thinks I wanted

I too would dearly like to see this.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.B.N. 75 008 659 498)          |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 03 5330 3172          |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                  |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


Wed, 30 Apr 2003 12:23:14 +1000
Philip Warner <pjw@rhyme.com.au>
Re: "Adding missing from clause"
On Tue, 29 Apr 2003, Tom Lane wrote:

> Josh Berkus <josh@agliodbs.com> writes:
> > Could we have a .conf option to turn OFF the "Adding Missing From Clause for 
> > Table ... "?
> 
> No objection here.  Send a patch.
> 

I presume from this just turning it into an error would attract objection and
as I haven't seen a patch for this come through the lists I've attached one
doing:

* Add enable_implicited_join GUC, defaulting to true. Emit notice if
enabled, throw error if disabled when adding implicit RTE.


-- 
Nigel J. Andrews
Thu, 1 May 2003 10:15:01 +0100 (BST)
"Nigel J. Andrews" <nandrews@investsystems.co.uk>
Re: [HACKERS] "Adding missing from clause"
"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> * Add enable_implicited_join GUC, defaulting to true. Emit notice if
> enabled, throw error if disabled when adding implicit RTE.

I'd call it "enable_implicit_from", I think, but otherwise this seems
reasonable...

			regards, tom lane


Thu, 01 May 2003 10:00:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] "Adding missing from clause" 
Nigel,

> I presume from this just turning it into an error would attract objection
> and as I haven't seen a patch for this come through the lists I've attached
> one doing:

Darn, that was fast.   I was going to tackle this next week; now I don't have 
to.

Grazie mille!

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Thu, 1 May 2003 08:07:51 -0700
Josh Berkus <josh@agliodbs.com>
Re: [HACKERS] "Adding missing from clause"
On Thu, 1 May 2003, Tom Lane wrote:

> "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> > * Add enable_implicited_join GUC, defaulting to true. Emit notice if
> > enabled, throw error if disabled when adding implicit RTE.
> 
> I'd call it "enable_implicit_from", I think, but otherwise this seems
> reasonable...

Fair enough, I'm not particularly sold on my choice so here's a replacement
patch with the GUC name changed. If there's a problem it'll probably be because
I edited the patch and not the source.

Log:

* Add enable_implicit_from GUC, defaulting to true. Emit notice if
  enabled, throw error if disabled when adding implicit RTE.


-- 
Nigel J. Andrews


Thu, 1 May 2003 17:50:56 +0100 (BST)
"Nigel J. Andrews" <nandrews@investsystems.co.uk>
Re: [HACKERS] "Adding missing from clause" (replacement)
Someone slap me, here's the patch attached this time...


On Thu, 1 May 2003, Nigel J. Andrews wrote:

> On Thu, 1 May 2003, Tom Lane wrote:
> 
> > "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> > > * Add enable_implicited_join GUC, defaulting to true. Emit notice if
> > > enabled, throw error if disabled when adding implicit RTE.
> > 
> > I'd call it "enable_implicit_from", I think, but otherwise this seems
> > reasonable...
> 
> Fair enough, I'm not particularly sold on my choice so here's a replacement
> patch with the GUC name changed. If there's a problem it'll probably be because
> I edited the patch and not the source.
> 
> Log:
> 
> * Add enable_implicit_from GUC, defaulting to true. Emit notice if
>   enabled, throw error if disabled when adding implicit RTE.
> 

-- 
Nigel J. Andrews
Thu, 1 May 2003 18:04:49 +0100 (BST)
"Nigel J. Andrews" <nandrews@investsystems.co.uk>
Re: [HACKERS] "Adding missing from clause" (replacement)

Added to TODO:

	* Add config variable to prevent auto-adding missing FROM-clause
	  tables


---------------------------------------------------------------------------

Philip Warner wrote:
> At 02:36 PM 29/04/2003 -0700, Josh Berkus wrote:
> >I'd far rather have a fatal query error than have Postgres
> >automatically insert the tables it thinks I wanted
> 
> I too would dearly like to see this.
> 
> 
> ----------------------------------------------------------------
> Philip Warner                    |     __---_____
> Albatross Consulting Pty. Ltd.   |----/       -  \
> (A.B.N. 75 008 659 498)          |          /(@)   ______---_
> Tel: (+61) 0500 83 82 81         |                 _________  \
> Fax: (+61) 03 5330 3172          |                 ___________ |
> Http://www.rhyme.com.au          |                /           \|
>                                   |    --________--
> PGP key available upon request,  |  /
> and from pgp5.ai.mit.edu:11371   |/
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sun, 1 Jun 2003 00:31:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: "Adding missing from clause"

Your patch has been added to the PostgreSQL unapplied patches list at:

	http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------


Nigel J. Andrews wrote:
> On Thu, 1 May 2003, Tom Lane wrote:
> 
> > "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> > > * Add enable_implicited_join GUC, defaulting to true. Emit notice if
> > > enabled, throw error if disabled when adding implicit RTE.
> > 
> > I'd call it "enable_implicit_from", I think, but otherwise this seems
> > reasonable...
> 
> Fair enough, I'm not particularly sold on my choice so here's a replacement
> patch with the GUC name changed. If there's a problem it'll probably be because
> I edited the patch and not the source.
> 
> Log:
> 
> * Add enable_implicit_from GUC, defaulting to true. Emit notice if
>   enabled, throw error if disabled when adding implicit RTE.
> 
> 
> -- 
> Nigel J. Andrews
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sun, 1 Jun 2003 00:43:37 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] "Adding missing from clause" (replacement)

And patch now in queue.  :-)

---------------------------------------------------------------------------

Bruce Momjian wrote:
> 
> Added to TODO:
> 
> 	* Add config variable to prevent auto-adding missing FROM-clause
> 	  tables
> 
> 
> ---------------------------------------------------------------------------
> 
> Philip Warner wrote:
> > At 02:36 PM 29/04/2003 -0700, Josh Berkus wrote:
> > >I'd far rather have a fatal query error than have Postgres
> > >automatically insert the tables it thinks I wanted
> > 
> > I too would dearly like to see this.
> > 
> > 
> > ----------------------------------------------------------------
> > Philip Warner                    |     __---_____
> > Albatross Consulting Pty. Ltd.   |----/       -  \
> > (A.B.N. 75 008 659 498)          |          /(@)   ______---_
> > Tel: (+61) 0500 83 82 81         |                 _________  \
> > Fax: (+61) 03 5330 3172          |                 ___________ |
> > Http://www.rhyme.com.au          |                /           \|
> >                                   |    --________--
> > PGP key available upon request,  |  /
> > and from pgp5.ai.mit.edu:11371   |/
> > 
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> > 
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sun, 1 Jun 2003 00:45:19 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: "Adding missing from clause"
I have applied the following patch, which does make a few adjustments.

I called the variable add_missing_from, rather than
enable_implicit_from.  The former seemed clearer.

I also enhanced the documentation for the item.

I added comments for the End-of-list records.

Does someone want to make an argument that this variable should default
to false rather than true, for standards compliance?

---------------------------------------------------------------------------

Nigel J. Andrews wrote:
> On Thu, 1 May 2003, Tom Lane wrote:
> 
> > "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> > > * Add enable_implicited_join GUC, defaulting to true. Emit notice if
> > > enabled, throw error if disabled when adding implicit RTE.
> > 
> > I'd call it "enable_implicit_from", I think, but otherwise this seems
> > reasonable...
> 
> Fair enough, I'm not particularly sold on my choice so here's a replacement
> patch with the GUC name changed. If there's a problem it'll probably be because
> I edited the patch and not the source.
> 
> Log:
> 
> * Add enable_implicit_from GUC, defaulting to true. Emit notice if
>   enabled, throw error if disabled when adding implicit RTE.
> 
> 
> -- 
> Nigel J. Andrews
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Index: doc/src/sgml/runtime.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v
retrieving revision 1.183
diff -c -c -r1.183 runtime.sgml
*** doc/src/sgml/runtime.sgml	11 Jun 2003 18:01:13 -0000	1.183
--- doc/src/sgml/runtime.sgml	11 Jun 2003 20:54:40 -0000
***************
*** 1300,1305 ****
--- 1300,1321 ----
  
      <variablelist>
       <varlistentry>
+       <term><varname>ADD_MISSING_FROM</varname> (<type>boolean</type>)</term>
+       <listitem>
+        <para>
+         This parameter controls whether a relation referenced in a query but
+         missing from the FROM clause should be automatically added to
+         the FROM clause. If enabled (the default), the notice
+         <literal>Adding missing FROM-clause entry for table "tablename"</literal>
+         is generated if a relation is automatically added. If not enabled,
+         an error is raised when an additional extra relation is required.
+         For SQL standards compliance, this value should be set to 
+         <literal>false</>.
+        </para>
+       </listitem>
+      </varlistentry>
+ 
+      <varlistentry>
        <term><varname>AUSTRALIAN_TIMEZONES</varname> (<type>boolean</type>)</term>
        <indexterm><primary>Australian time zones</></>
        <listitem>
Index: src/backend/parser/parse_relation.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/parser/parse_relation.c,v
retrieving revision 1.81
diff -c -c -r1.81 parse_relation.c
*** src/backend/parser/parse_relation.c	29 Apr 2003 22:13:10 -0000	1.81
--- src/backend/parser/parse_relation.c	11 Jun 2003 20:54:42 -0000
***************
*** 32,37 ****
--- 32,39 ----
  #include "utils/lsyscache.h"
  #include "utils/syscache.h"
  
+ /* GUC parameter */
+ bool add_missing_from;
  
  static Node *scanNameSpaceForRefname(ParseState *pstate, Node *nsnode,
  						const char *refname);
***************
*** 1861,1867 ****
  		}
  	}
  	if (foundInFromCl)
! 		elog(NOTICE, "Adding missing FROM-clause entry%s for table \"%s\"",
! 			 pstate->parentParseState != NULL ? " in subquery" : "",
! 			 relation->relname);
  }
--- 1863,1876 ----
  		}
  	}
  	if (foundInFromCl)
! 	{
! 		if (add_missing_from)
! 			elog(NOTICE, "Adding missing FROM-clause entry%s for table \"%s\"",
! 				 pstate->parentParseState != NULL ? " in subquery" : "",
! 				 relation->relname);
! 		else
! 			elog(ERROR, "Missing FROM-clause entry%s for table \"%s\"",
! 				 pstate->parentParseState != NULL ? " in subquery" : "",
! 				 relation->relname);
! 	}
  }
Index: src/backend/utils/misc/guc.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/misc/guc.c,v
retrieving revision 1.130
diff -c -c -r1.130 guc.c
*** src/backend/utils/misc/guc.c	11 Jun 2003 18:49:00 -0000	1.130
--- src/backend/utils/misc/guc.c	11 Jun 2003 20:54:49 -0000
***************
*** 43,48 ****
--- 43,49 ----
  #include "optimizer/paths.h"
  #include "optimizer/prep.h"
  #include "parser/parse_expr.h"
+ #include "parser/parse_relation.h"
  #include "storage/fd.h"
  #include "storage/freespace.h"
  #include "storage/lock.h"
***************
*** 550,559 ****
  		{"transaction_read_only", PGC_USERSET, GUC_NO_RESET_ALL}, &XactReadOnly,
  		false, NULL, NULL
  	},
  
  	{
  		{NULL, 0}, NULL, false, NULL, NULL
! 	}
  };
  
  
--- 551,565 ----
  		{"transaction_read_only", PGC_USERSET, GUC_NO_RESET_ALL}, &XactReadOnly,
  		false, NULL, NULL
  	},
+ 	{
+ 		{"add_missing_from", PGC_USERSET}, &add_missing_from,
+ 		true, NULL, NULL
+ 	},
  
+ 	/* End-of-list marker */
  	{
  		{NULL, 0}, NULL, false, NULL, NULL
! 	},
  };
  
  
***************
*** 742,747 ****
--- 748,754 ----
  		0, 0, INT_MAX / 1000, NULL, NULL
  	},
  
+ 	/* End-of-list marker */
  	{
  		{NULL, 0}, NULL, 0, 0, 0, NULL, NULL
  	}
***************
*** 784,789 ****
--- 791,797 ----
  		0.5, 0.0, 1.0, assign_random_seed, show_random_seed
  	},
  
+ 	/* End-of-list marker */
  	{
  		{NULL, 0}, NULL, 0.0, 0.0, 0.0, NULL, NULL
  	}
***************
*** 946,951 ****
--- 954,960 ----
  		XLOG_sync_method_default, assign_xlog_sync_method, NULL
  	},
  
+ 	/* End-of-list marker */
  	{
  		{NULL, 0}, NULL, NULL, NULL, NULL
  	}
Index: src/backend/utils/misc/postgresql.conf.sample
===================================================================
RCS file: /cvsroot/pgsql-server/src/backend/utils/misc/postgresql.conf.sample,v
retrieving revision 1.80
diff -c -c -r1.80 postgresql.conf.sample
*** src/backend/utils/misc/postgresql.conf.sample	11 Jun 2003 18:01:14 -0000	1.80
--- src/backend/utils/misc/postgresql.conf.sample	11 Jun 2003 20:54:49 -0000
***************
*** 208,210 ****
--- 208,211 ----
  #statement_timeout = 0		# 0 is disabled, in milliseconds
  #db_user_namespace = false
  #preload_libraries = ''
+ #add_missing_from = true
Index: src/bin/psql/tab-complete.c
===================================================================
RCS file: /cvsroot/pgsql-server/src/bin/psql/tab-complete.c,v
retrieving revision 1.78
diff -c -c -r1.78 tab-complete.c
*** src/bin/psql/tab-complete.c	11 Jun 2003 18:01:14 -0000	1.78
--- src/bin/psql/tab-complete.c	11 Jun 2003 20:54:52 -0000
***************
*** 492,497 ****
--- 492,498 ----
  		 * the rest should match USERSET and possibly SUSET entries in
  		 * backend/utils/misc/guc.c.
  		 */
+ 		"add_missing_from",
  		"australian_timezones",
  		"client_encoding",
  		"client_min_messages",
Index: src/include/parser/parse_relation.h
===================================================================
RCS file: /cvsroot/pgsql-server/src/include/parser/parse_relation.h,v
retrieving revision 1.39
diff -c -c -r1.39 parse_relation.h
*** src/include/parser/parse_relation.h	4 Sep 2002 20:31:45 -0000	1.39
--- src/include/parser/parse_relation.h	11 Jun 2003 20:54:52 -0000
***************
*** 16,21 ****
--- 16,23 ----
  
  #include "parser/parse_node.h"
  
+ extern bool add_missing_from;
+ 
  extern RangeTblEntry *refnameRangeTblEntry(ParseState *pstate,
  					 const char *schemaname,
  					 const char *refname,
Wed, 11 Jun 2003 18:12:17 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] "Adding missing from clause" (replacement)
Bruce,

> Does someone want to make an argument that this variable should default
> to false rather than true, for standards compliance?

No.  As much as I would like it to default to FALSE, we need to maintain 
backward compatibility for one release.  We can change the default in 
7.5/8.0.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


Wed, 11 Jun 2003 16:25:22 -0700
Josh Berkus <josh@agliodbs.com>
Re: [HACKERS] "Adding missing from clause" (replacement)
Josh Berkus <josh@agliodbs.com> writes:
>> Does someone want to make an argument that this variable should default
>> to false rather than true, for standards compliance?

> No.  As much as I would like it to default to FALSE, we need to maintain 
> backward compatibility for one release.  We can change the default in 
> 7.5/8.0.

Much further out than that.

			regards, tom lane

Wed, 11 Jun 2003 22:34:15 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] "Adding missing from clause" (replacement) 