You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Knut Anders Hatlen <kn...@oracle.com> on 2012/12/03 14:12:45 UTC
Re: NullPointerException in Derby 10.9.1.0
Zorro <hz...@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,
--
Knut Anders
Re: NullPointerException in Derby 10.9.1.0
Posted by Zorro <hz...@gmail.com>.
Op 4-12-2012 23:08, Knut Anders Hatlen schreef:
> Kim Haase <ca...@oracle.com> writes:
>
>> Should I file a doc JIRA to clarify that ALTER TABLE adds the new
>> column at the end?
> That would be great.
>
>> 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?
> We probably only allow changing the maximum length of columns that
> already are VARCHAR or VARCHAR FOR BIT DATA. Since changing the maximum
> length can be done in the table meta-data without changing the stored
> format of each value, that's easier to support than the general case.
> For example, changing the type from INT to DOUBLE would require an
> update of every row in the table, since they have different formats.
> That's my guess, at least.
Hi Knut,
Thanks for looking at this.
As you described it seemed to be related to the Order By in the Select
phrase of the bulk Insert.
Therefore I removed the Order By of the Select and now the bulk Insert
went well.
The Order By was not necessary for my conversion.
Out of convenience I used copy/paste to the Insert statement.
I hope my issue helped to clarify the Insert procedure in Derby.
Kind regards,
Harm-Jan Zwinderman
Re: NullPointerException in Derby 10.9.1.0
Posted by Knut Anders Hatlen <kn...@oracle.com>.
Kim Haase <ca...@oracle.com> writes:
> Should I file a doc JIRA to clarify that ALTER TABLE adds the new
> column at the end?
That would be great.
> 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?
We probably only allow changing the maximum length of columns that
already are VARCHAR or VARCHAR FOR BIT DATA. Since changing the maximum
length can be done in the table meta-data without changing the stored
format of each value, that's easier to support than the general case.
For example, changing the type from INT to DOUBLE would require an
update of every row in the table, since they have different formats.
That's my guess, at least.
--
Knut Anders
Re: NullPointerException in Derby 10.9.1.0
Posted by Kim Haase <ca...@oracle.com>.
On 12/ 4/12 06:10 AM, Knut Anders Hatlen wrote:
> david myers<da...@gmail.com> writes:
>
>> On 03/12/12 14:12, Knut Anders Hatlen wrote:
>>
>> Zorro<hz...@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
Re: NullPointerException in Derby 10.9.1.0
Posted by Knut Anders Hatlen <kn...@oracle.com>.
david myers <da...@gmail.com> writes:
> On 03/12/12 14:12, Knut Anders Hatlen wrote:
>
> Zorro <hz...@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 *.
--
Knut Anders
Re: NullPointerException in Derby 10.9.1.0
Posted by david myers <da...@gmail.com>.
On 03/12/12 14:12, Knut Anders Hatlen wrote:
> Zorro <hz...@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.
David.