Return-Path: 
 <derby-dev-return-39996-apmail-db-derby-dev-archive=db.apache.org@db.apache.org>
Delivered-To: apmail-db-derby-dev-archive@www.apache.org
Received: (qmail 12587 invoked from network); 3 Apr 2007 18:40:00 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 3 Apr 2007 18:40:00 -0000
Received: (qmail 16475 invoked by uid 500); 3 Apr 2007 18:40:04 -0000
Delivered-To: apmail-db-derby-dev-archive@db.apache.org
Received: (qmail 16452 invoked by uid 500); 3 Apr 2007 18:40:04 -0000
Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:derby-dev-help@db.apache.org>
List-Unsubscribe: <mailto:derby-dev-unsubscribe@db.apache.org>
List-Post: <mailto:derby-dev@db.apache.org>
List-Id: <derby-dev.db.apache.org>
Reply-To: <derby-dev@db.apache.org>
Delivered-To: mailing list derby-dev@db.apache.org
Received: (qmail 16346 invoked by uid 99); 3 Apr 2007 18:40:04 -0000
Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133)
    by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 03 Apr 2007 11:40:04 -0700
X-ASF-Spam-Status: No, hits=0.0 required=10.0
	tests=UNPARSEABLE_RELAY
X-Spam-Check-By: apache.org
Received-SPF: pass (herse.apache.org: local policy)
Received: from [192.18.42.249] (HELO nwk-ea-fw-1.sun.com) (192.18.42.249)
    by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 03 Apr 2007 11:39:54 -0700
Received: from d1-sfbay-10.sun.com ([192.18.39.120])
	by nwk-ea-fw-1.sun.com (8.13.6+Sun/8.12.9) with ESMTP id l33IdTk3003880
	for <derby-dev@db.apache.org>; Tue, 3 Apr 2007 11:39:33 -0700 (PDT)
Received: from conversion-daemon.d1-sfbay-10.sun.com by d1-sfbay-10.sun.com
 (Sun Java System Messaging Server 6.2-6.01 (built Apr  3 2006))
 id <0JFX00701QCD7300@d1-sfbay-10.sun.com>
 (original mail from Richard.Hillegas@Sun.COM) for derby-dev@db.apache.org;
 Tue, 03 Apr 2007 11:39:29 -0700 (PDT)
Received: from [192.9.61.193] by d1-sfbay-10.sun.com
 (Sun Java System Messaging Server 6.2-6.01 (built Apr  3 2006))
 with ESMTPSA id <0JFX00MNSQHTDBZ7@d1-sfbay-10.sun.com> for
 derby-dev@db.apache.org; Tue, 03 Apr 2007 11:39:29 -0700 (PDT)
Date: Tue, 03 Apr 2007 11:41:40 -0700
From: Rick Hillegas <Richard.Hillegas@Sun.COM>
Subject: Re: some comments on collation wiki page
In-reply-to: <461290D5.20602@gmail.com>
Sender: Richard.Hillegas@Sun.COM
To: derby-dev@db.apache.org
Message-id: <46129FE4.90804@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <4611F2C0.2040306@sbcglobal.net> <46126750.1010401@apache.org>
 <d9619e4a0704030839o48d9b41cs72c89518c70efc7c@mail.gmail.com>
 <46128C67.9080606@gmail.com> <461290D5.20602@gmail.com>
User-Agent: Thunderbird 1.5.0.5 (X11/20060828)
X-Virus-Checked: Checked by ClamAV on apache.org

Army wrote:
> Army wrote:
>>
>> Since a parameter maker does not have a "defined" schema, does 
>> "current schema" mean the schema when the statement is prepared, or 
>> the schema when it is executed?
>>
>> For example I can do the following in JDBC:
>>
>> // Default schema ("APP").
>>
>> PreparedStatement ps = conn.prepareStatement(
>>   "select tablename, tabletype from sys.systables where tablename = ?");
>
> <snip>
>
>> If "current schema" means the "schema when the statement is 
>> *prepared*" then both of the above statements would fail (because 
>> there's no CAST on the syscol). That consistency would probably be a 
>> good thing (less confusing for users).
>
> On the other hand, if the statement is changed to:
>
>    // Default schema APP.
>
>    PreparedStatement ps = conn.prepareStatement(
>        "select tablename, tabletype from sys.systables where " +
>        "CAST (tablename as varchar(128)) = ?");
>
> then it might be better to take "current schema" to mean the time of 
> *execution.*  That way the the above statement will run correctly 
> regardless of the current schema.  If "current schema" was determined 
> at compile time, the above statement would only work if "current 
> schema" was a non-system schema.
>
> Since I think we are going to encourage users to CAST system columns 
> when doing comparisons (at least that's what I gathered from the 
> various discussion threads), maybe it would be better to take the 
> "current schema" for a parameter marker at execution time, after all...?
>
> Army
>
I think that all bind-time decisions need to be resolved the same way. 
Otherwise the behavior of our sql interpreter will be very hard to 
explain. A similar issue comes up with the following statement:

  PreparedStatement ps = conn.prepareStatement( "select * from T" );

T could resolve to a different table depending on what your current 
schema is. What happens if this statement is prepared in one schema, 
then the user changes schema, then the user executes the prepared statement?

Regards,
-Rick