Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <26019903.1159375611144.JavaMail.jira@brutus>
Date: Wed, 27 Sep 2006 09:46:51 -0700 (PDT)
From: "A B (JIRA)" <derby-dev@db.apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-1866) Assert failure in sane mode for
 queries that used to work in 10.1.2.1
In-Reply-To: <8834277.1158690388278.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

    [ http://issues.apache.org/jira/browse/DERBY-1866?page=comments#action_12438165 ] 
            
A B commented on DERBY-1866:
----------------------------

Hi Bryan, thanks for looking at the patch and for being willing to ask some good questions.  Below are my (perhaps slightly lengthy) responses.  If this doesn't address your questions or leaves you with new questions, please don't hesitate to ask again...

Thanks,
Army

----

Q1: At this point in the processing, have constructs like views
   and synonyms already been transformed and replaced by their
   underlying "real" tables? 

Yes.  Transformations and table resolution occur during the "binding" and "preprocessing" stages of query compilation--and both of those stages occur before optimization begins.  So at this point a view will be represented by a ProjectRestrictNode whose child is a SelectNode, and a synonym will be represented by whatever FromTable it (the synonym) is actually referring to.

Table numbers are also assigned during binding/preprocessing, so by the time we get to the code in d1866_v1.patch, all FromTables (aka  "Optimizables") in the entire query will have an assigned table number (if required--in some cases it's not necessary and thus will be -1).  Additionally any column reference which points to one of those FromTables will have the table number for that FromTable stored locally (namely, in ColumnReference.tableNumber).

Note that when a ColumnReference is "remapped" to point to a different FromTable, its local information--including tableNumber--is updated accordingly.  Note also that a "FromTable" is not restricted to base tables--anything that can be specified in the FROM list of a SELECT query will be represented by some instance of FromTable, whether it be a subquery, a base table, a union node, etc.  Every FromTable has its own "table number", with the exception of ProjectRestrictNodes.  For a PRN, if the PRN's child is itself a FromTable (as opposed to, say, a SelectNode) then the PRN's table number will be -1 and any attempts to "get" the PRN's table number will return the table number of the PRN's child.  If the PRN's child is not a FromTable, then the PRN will have it's own table number.

Q2: If the optimizer is choosing to access an index for a table, rather
  than accessing the table itself, does the table number change depending
  on whether it is an index or a base table which is being processed
  by the ProjectRestrictNode?

Great question.  Short answer is "no" :)

The thing to note here is that "table number" is strictly a language-created, compilation time value to allow binding, preprocessing, optimization, and code generation to distinguish between the various FromTables in the original query.  A table number is not stored on disk and it is independent of the access path decisions (including whether or not an index is used) made by the optimizer.  Furthermore, there is no link between a given table number and the actual on-disk table that it points to.  Table number 0 could be for T1 in one query, T2 in another query, and T100 in a third query.

As a simple (but admittedly meaningless) example, take the following query:

  select t1.i, x1.j from t1, t1 x1 where t1.i = x1.j;

At bind time Derby will assign every item in the FROM list a table number.  So in this case, "T1" gets table number 0 and "T1 X1" gets table number 1.  The fact that both FromTables are really pointing to the same base table doesn't matter.  For the duration of compilation/optimization, they are represented by two different instances of FromTable and are considered two different "tables", each having its own table number.  (For the record, in this particular example the different FromTables will in fact point to the same underlying tableDescriptor field).

Given that, the predicate "t1.i = x1.j" will have a left ColumnReference pointing to a FromBaseTable representing T1 with table number "0" and a right ColumnReference pointing to a different FromBaseTable representing X1 (i.e. T1 again) with table number "1".

If the optimizer then decides to use an index for T1, the table number doesn't change--the optimizer just decides that for "the FromBaseTable whose table number is 0 we will use an index".  In fact, once assigned, the table number for a specific FromTable remains the same for the duration of the compilation of the statement.

That was a round-about way of getting to the answer, but hopefully that's more helpful than confusing...

As I said, if you still have questions/confusion, please do ask again.  It's always good to answer questions like these, as it makes me re-check what I think I "know" and forces me to verify my replies by looking at the code again...

> Assert failure in sane mode for queries that used to work in 10.1.2.1
> ---------------------------------------------------------------------
>
>                 Key: DERBY-1866
>                 URL: http://issues.apache.org/jira/browse/DERBY-1866
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.2.1.5, 10.1.4.0, 10.1.3.2
>            Reporter: A B
>         Assigned To: A B
>             Fix For: 10.3.0.0, 10.2.2.0
>
>         Attachments: d1866_v1.patch, derby.log, repro.sql
>
>
> Derby-1777 gives a database and a small program called "ViewerInit" that prepares a bunch of large queries involving nested subqueries, unions, and join predicates.  The actual bug described in DERBY-1777 is an NPE, and that's what the patch for DERBY-1777 addresses.
> However, once the NPEs are fixed, some of the queries in that same program now fail with ASSERT failures when running in SANE mode; this Jira issue is for addressing those assert failures.
> While this does constitute a regression, I don't know yet what the root cause of the problem is, so I hesitate to make it a 10.2 blocker--hence urgency is "Normal".  I'm still investigating the queries to try to track down where the problem is, but all I've been able to deduce so far is that a) the assertion occurs for a scoped predicate and thus the pushing of join predicates into UNIONs is somehow involved, and b) in INSANE mode the query compiles without problem and appears (based on some early and very incomplete testing) to execute without problem.  But more investigation is required to determine if the execution/results are actually correct, and to understand more about why the assertion is being thrown.
> I'm marking the fixin as 10.2.2.0 for now since I don't enough to make this a blocker for 10.2.1.  Hopefully more info will be forthcoming...

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira