Re: NullPointerException in Derby 10.9.1.0
david myers david.myers.scibearsp...@gmail.com writes: On 03/12/12 14:12, Knut Anders Hatlen wrote: Zorro hz0...@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
On 12/ 4/12 06:10 AM, Knut Anders Hatlen wrote: david myersdavid.myers.scibearsp...@gmail.com writes: On 03/12/12 14:12, Knut Anders Hatlen wrote: Zorrohz0...@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: AW: AW: Pivoting tables?
On 28/11/2012 16:30, malte.kem...@de.equens.com wrote: Hi John, It sounds to me that your first solution could work flexible enough, while the other might have lecks of flexibility, haven't they? I am not sure how much you like to studdy and translate it in derby and Java procedures in the example of the page from last answer. I've finally got my head around the various options, and rather than wait for 10.10 so I can experiment with user-defined aggregates, I've cobbled together something based on what you suggested. I programmatically build a query and use it as the table to be displayed by my monstrous showTable() method: (SELECT username,SUM( + total + ) AS total + values + FROM (SELECT username + cases + FROM products WHERE dept=?) AS s + GROUP BY username) AS t where the variables look like this when the items have IDs 53, 75 and 82: cases: ,CASE WHEN itemid=53 THEN cost ELSE NULL END AS c53 + ,CASE WHEN itemid=53 THEN discounted ELSE 0 END AS d53 + ,CASE WHEN itemid=75 THEN cost ELSE NULL END AS c75 + ,CASE WHEN itemid=75 THEN discounted ELSE 0 END AS d75 + ,CASE WHEN itemid=82 THEN cost ELSE NULL END AS c82 + ,CASE WHEN itemid=82 THEN discounted ELSE 0 END AS d82 total: d53+d75+d82 values: ,SUM(c53) AS v53,SUM(c75) AS v75,SUM(c82) AS v82 This speeds things up by a factor of somewhere between 20x and 50x compared to the temporary table approach, which is absolutely wonderful! (The code is still totally opaque, but you can't have everything...) However, if I try to format the username nicely by pulling the user's surname and initials out of the users table, I end up with this: (SELECT surname||','||initials,username,SUM( + total + ) AS total + values + FROM (SELECT username + cases + FROM products WHERE dept=?) AS s,users + WHERE s.username=users.username GROUP BY users.username,surname,initials) AS t which slows things down again by about 15x. In the temporary table approach I was putting the formatted name into the table as I built it, so there wasn't much extra overhead. I'm going to try using a user-defined function to format the name and thus avoid the extra GROUP BY elements (which are my immediate suspect for the performance hit), but does anyone have any other suggestions for improving on this solution? Thanks for all the help! -- John English
Re: NullPointerException in Derby 10.9.1.0
Kim Haase camilla.ha...@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
Vetting Derby. Technical documents?
My company is evaluating whether to use Derby for a desktop/client-server application where security and 21-CFR 11 compliance is important. I'm looking for technical documents attesting to the robustness of Derby's security features. Something like unit test coverage/logs or the results of penetration testing. Third-party reports? Thanks in advance! Steve
