db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kim Haase <camilla.ha...@oracle.com>
Subject Re: NullPointerException in Derby 10.9.1.0
Date Tue, 04 Dec 2012 18:16:18 GMT
On 12/ 4/12 06:10 AM, Knut Anders Hatlen wrote:
> david myers<david.myers.scibearspace@gmail.com>  writes:
>
>> On 03/12/12 14:12, Knut Anders Hatlen wrote:
>>
>>      Zorro<hz0885@gmail.com>  writes:
>>
>>
>>          Dear All,
>>
>> When doing in ij a bulk Insert into a table of my Derby database I do
>> get a NullPointerException.
>>
>>
>> Hi Harm-Jan,
>>
>> It looks like you've come across a bug. I managed to reproduce the
>> NullPointerException in my environment, so I filed a bug report and
>> posted the steps I followed in order to reproduce it there:
>> https://issues.apache.org/jira/browse/DERBY-6006
>>
>> Thanks for reporting the problem,
>>
>> Hi Harm-Jan and Knut,
>>
>> first off a bit of a long post, but I hope it may be informative...
>>
>> I've just seen Knut's jira bug ([jira] (DERBY-6006)), and wonder
>> (having looked at the stack trace that was posted) if the '
>> conglomerate' error at the top of the stack is related to a problem I
>> experienced.
>>
>> My problem can be recreated as follows....
>>
>>> Create a table in your db (any structure will do)
>>> programatically take one of your fields and change its data type
>> (from int to float for example).
>>
>>      When you do this programatically the only way to do it is to...
>>      >  create a new 'temp' field
>>      >  copy the values from the original into the new
>>      >  drop the original table
>>      >  rename the 'temp' field so as you can use your table in your
>>      previously created routines etc...
>>> the problem this creates is that the new 'temp' field, although for
>> all intents is the 'same' as the original has a different value in the
>> conglomerates tables.
>>
>> Result:
>> If you have use an external process that inserts data into the table
>> from a select * the order of the fields has changed, and so the insert
>> fails as the original fields have been 'shifted' to the left.
>>
>> EG: Original table field order.
>> field1:field2:changeTypeOfThisfield:field3:field4:field5
>>
>> new field after the modification.
>>
>> field1:field2:field3:field4:field5:changedTypeOfThisField
>>
>> Solution:
>>
>> Programatically capture the names of the fields to ensure they stay in
>> a 'predefined' order.
>>
>> The problem seen by Harm-Jan may have an similar solution, the problem
>> being of course that it is now neccessary to programatically do the
>> insert select (rather then being able to do it directly in ij), which
>> seems a bit brutal.
>>
>> So the reflection for Knut is: Is it possible to that internally the
>> engine is creating a temp / shadow table and making a mess of these
>> conglomerates during that process, and doing something like I have
>> encountered (and how to test if the conglomerates are changing in this
>> way)
>>
>> If so my problem, which I have been considering calling a '
>> documentation bug' on, may be less benign and require a more involved
>> solution.
>>
>> Of course I may be off the mark, it was seeing the 'conglomerates
>> error' that made me connect the 2 in my mind.
> Hi David,
>
> I think you're right that Harm-Jan's insert statement will create a
> temporary table internally, in order to sort the results because of the
> ORDER BY clause, and that it somehow confuses the columns. The ORDER BY
> column is not referenced in the SELECT list, but it still has to be in
> the temporary table so that it can be sorted. This may confuse the
> insertion logic, especially since ORDER BY in INSERT statements is
> fairly new functionality, and some corners of the old code may not be
> prepared for it.
>
> I don't expect a fix for this bug to change what you are seeing with
> SELECT *, though. Adding columns with ALTER TABLE will append the new
> columns to the existing column list (I agree that the documentation
> should have stated this clearly), and I think the SQL standard requires
> SELECT * to use that column ordering. If a SELECT statement has to work
> reliably across schema changes, it will have to use explicit column
> names instead of *.
>

Should I file a doc JIRA to clarify that ALTER TABLE adds the new column 
at the end? It seems obvious, but perhaps we should clarify that a 
programmatic attempt to change the data type of a column will still 
result in the changed column being appended.

I notice it is possible to change the data type of a column using "ALTER 
TABLE ALTER column-name SET DATA TYPE", but you can only change the type 
to VARCHAR or VARCHAR FOR BIT DATA. Do you know why that is?

Thanks,
Kim

Mime
View raw message