[jira] [Commented] (DERBY-6953) Support Standard syntax for retrieving INSERTed key values

[Rick Hillegas (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-6953?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16097472#comment-16097472
 ] 

Rick Hillegas commented on DERBY-6953:
--

Thanks for that reference, Lukas. The grammar stanzas you cited are from the 
2016 Standard, part 2, section 7.6 . According to GR 5b, for 
an INSERT, with  NEW, the contents of the  are defined by section 15.10 (Effect of inserting tables into base 
tables). It appears to be the set of all inserted rows, including all of their 
columns. My reading of this language is that the user would phrase an insert 
statement like this:

{noformat}
select keyCol from new table
(
  insert into t(a, b, c) select (x, y, z) from s
)
;
{noformat}

See, for instance, 
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/apsg/src/tpc/db2z_selectvaluesmerge.html

I think that there is a fair amount of language to build here in order to wire 
this into Derby's access control language and trigger mechanism. However, the 
solution is attractive. For best performance, we would want to push the 
projection (keyCol in the example above) down into the  so that the engine only collects the desired column (keyCol) as the 
engine processes the insert.

Thanks,
-Rick


> Support Standard  syntax for retrieving INSERTed key 
> values
> 
>
> Key: DERBY-6953
> URL: https://issues.apache.org/jira/browse/DERBY-6953
> Project: Derby
>  Issue Type: Improvement
>  Components: SQL
>Affects Versions: 10.13.1.1
>Reporter: Lukas Eder
>
> The SQL standard supports an interesting syntax that can be used as a  reference>:
> {code}
>  ::=
>TABLE   
>  ::=
> 
>   | 
>   | 
>   | 
>  ::=
> FINAL
>   | NEW
>   | OLD
> {code}
> This is currently supported by DB2. Databases like Firebird, Oracle (in 
> PL/SQL), PostgreSQL support an alternative syntax through the RETURNING 
> keyword that can be appended to . SQL Server has an 
> OUTPUT keyword that can be placed in the middle of a .
> These statements are incredibly useful to retrieve generated ID values but 
> also trigger-generated values after a DML operation for an arbitrary number 
> of inserted / updated / deleted / merged rows.
> It would allow people to bypass the many problems that are currently still 
> open related to Statement.getGeneratedKeys(). Quite likely, if these clauses 
> were made available, Statement.getGeneratedKeys() could be implemented by 
> patching the user-defined SQL to be wrapped with a  
> clause.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (DERBY-6953) Support Standard syntax for retrieving INSERTed key values

[Rick Hillegas (JIRA)]

 [ 
https://issues.apache.org/jira/browse/DERBY-6953?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Rick Hillegas updated DERBY-6953:
-
Summary: Support Standard  syntax for retrieving 
INSERTed key values  (was: Support the SQL Standard)

> Support Standard  syntax for retrieving INSERTed key 
> values
> 
>
> Key: DERBY-6953
> URL: https://issues.apache.org/jira/browse/DERBY-6953
> Project: Derby
>  Issue Type: Improvement
>  Components: SQL
>Affects Versions: 10.13.1.1
>Reporter: Lukas Eder
>
> The SQL standard supports an interesting syntax that can be used as a  reference>:
> {code}
>  ::=
>TABLE   
>  ::=
> 
>   | 
>   | 
>   | 
>  ::=
> FINAL
>   | NEW
>   | OLD
> {code}
> This is currently supported by DB2. Databases like Firebird, Oracle (in 
> PL/SQL), PostgreSQL support an alternative syntax through the RETURNING 
> keyword that can be appended to . SQL Server has an 
> OUTPUT keyword that can be placed in the middle of a .
> These statements are incredibly useful to retrieve generated ID values but 
> also trigger-generated values after a DML operation for an arbitrary number 
> of inserted / updated / deleted / merged rows.
> It would allow people to bypass the many problems that are currently still 
> open related to Statement.getGeneratedKeys(). Quite likely, if these clauses 
> were made available, Statement.getGeneratedKeys() could be implemented by 
> patching the user-defined SQL to be wrapped with a  
> clause.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Re: JDK 9 EA Build 178 & JDK 8u152 b05 are available on jdk.java.net

[Rick Hillegas]

I set out to log a bug with this information. However, the dmg of build 
178 which I downloaded this morning installed fine. Using that JDK, 
Derby compiles cleanly, the javadoc generates cleanly, and the tests run 
cleanly.


Thanks,
-Rick

On 7/19/17 8:00 AM, Rory O'Donnell wrote:


Hi Rick,

Can you include logs in bug report :

~/Library/Application\ Support/JREInstaller/JREInstallLog.txt
/var/log/system.log *(IMPT: **This is a system log. Please make sure 
only Java/Java Installer information is included)*


Rgds,Rory

On 19/07/2017 03:50, Rick Hillegas wrote:

On 7/18/17 1:40 AM, Rory O'Donnell wrote:


Hi Rick,

We tried this on the following releases - OSX 10.10.3 , 10.11.4, 
10.11.6 & 10.12.5 without any issues.


Thanks for running those experiments, everyone. I have downloaded the 
jdk dmg 4 times, using two different browsers (Firefox 54.0.1 and 
Safari 9.1.1). The behavior is always the same: a hang during initial 
verification.


I also tried downloading the jre instead. After I double clicked on 
the installer, I got a popup window with this message:


"A newer version of Java is already installed.

You are trying to install Java 9, however 10.6.0 is already 
installed. Visit java.com/newerversionexists.com for more information."


For the record, this is the version stamp on my currently installed 
Java 9 jdk:


java version "9-ea"
Java(TM) SE Runtime Environment (build 9-ea+174)
Java HotSpot(TM) 64-Bit Server VM (build 9-ea+174, mixed mode)

Is there some log file I should inspect or some tracing trick I 
should try in order to tease more information out of the installer?


Thanks,
-Rick


Can you try to download again ?

Rgds,Rory


On 18/07/2017 02:05, Rick Hillegas wrote:

Hi Rory,

When I try to install the dmg image on my mac, (OSX 10.11.5), I get 
as far as initial verification, a little window which says 
"Verifying JDK 9.pkg". The progress indicator never moves and, in 
fact, it never registers any progress. It just hangs. This was not 
my experience with previous versions of JDK 9.


Thanks,
-Rick

On 7/17/17 4:56 AM, Rory O'Donnell wrote:

Hi Rick,


*JDK 9 Early Access*  build 178  is available at : - jdk.java.net/9/

A summary of all the changes in this build are listed here 
.


Changes which were introduced since the last availability email 
that may be of interest :


  * b175 - Module system implementation refresh**(6/2017 update)
  * b175 - no longer has "-ea" in the version string and the
system property "java version" is now simply "9"
 o

*java -version*

>java version "9"
>Java(TM) SE Runtime Environment (build 9+175)
>Java HotSpot(TM) 64-Bit Server VM (build 9+175, mixed mode)
 o

*Bundle name changes:* e.g. jdk-9+175_linux-x86_bin.tar.gz


*JDK 8u152 Early Access*  build 05 is available at : - 
jdk.java.net/8/ 


A summary of all the changes in this build are listed here 
.


Rgds,Rory
--
Rgds,Rory O'Donnell
Quality Engineering Manager
Oracle EMEA , Dublin, Ireland





--
Rgds,Rory O'Donnell
Quality Engineering Manager
Oracle EMEA , Dublin, Ireland





--
Rgds,Rory O'Donnell
Quality Engineering Manager
Oracle EMEA , Dublin, Ireland

[jira] [Commented] (DERBY-6856) Make it possible to build Derby using JDK 9

[Rick Hillegas (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-6856?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16097416#comment-16097416
 ] 

Rick Hillegas commented on DERBY-6856:
--

Using build 178 of JDK 9, Derby compiles cleanly, the javadoc generates 
cleanly, and the tests run cleanly.

> Make it possible to build Derby using JDK 9
> ---
>
> Key: DERBY-6856
> URL: https://issues.apache.org/jira/browse/DERBY-6856
> Project: Derby
>  Issue Type: Improvement
>  Components: Build tools
>Affects Versions: 10.12.1.1
>Reporter: Rick Hillegas
> Attachments: derby-6856-01-ab-addShardingKey.diff, 
> derby-6856-01-ac-cleanup.diff, derby-6856-02-aa-addShardingKey.diff, 
> derby-6856-03-aa-autoboxingDeprecationWarnings.diff, 
> derby-6856-03-ab-autoboxingDeprecationWarnings.diff, 
> derby-6856-04-aa-autoboxingDeprecationWarnings-part2.diff, 
> derby-6856-04-ab-autoboxingDeprecationWarnings-part2.diff, 
> derby-6856-05-ac-roundingMode-Class.newInstance.diff, 
> derby-6856-05-af-roundingMode-Class.getDeclaredConstructor.diff, 
> derby-6856-05-ag-roundingMode-Class.newInstance.diff, 
> derby-6856-06-aa-observable.diff, derby-6856-07-aa-oneMoreNewInstance.diff, 
> derby-6856-08-aa-cleanupJavadoc.diff, derby-6856-09-aa-javadocEntities.diff, 
> derby-6856-10-aa-disable-permissions-subverting-test.diff, 
> derby-6856-11-aa-jigsawResourceLocation.diff, derby-6856-XX-ab-base.diff, 
> derby-6856-XX-ac-base.diff, derby-6856-XX-ad-base.diff, 
> derby-6856-XX-ae-base.diff, PTest.java, ptestScript
>
>
> Derby can't be built with JDK 9. Java 9 introduces new JDBC classes like 
> java.sql.ShardingKey and methods which refer to these new classes.
> In addition, project Jigsaw has created a new way to name classes (see 
> http://openjdk.java.net/jeps/220). This breaks the PropertySetter build tool 
> which we use so that old JVMs can compile Derby and so that Derby can be 
> compiled to run on old JVMs.
> It is likely that we will need to leave this issue open throughout the 
> development cycle of Java 9.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (DERBY-6938) Obtain cardinality estimates and true estimates for base tables as well as for intermediate results for queries involving multiple joins.

[Bryan Pendleton (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-6938?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16097372#comment-16097372
 ] 

Bryan Pendleton commented on DERBY-6938:


Thank you for exploring this behavior in more detail, and for the clear 
explanation. It is very helpful!

Perhaps the critical question here involves the Optimizer's prediction about 
whether the intermediate results will fit in memory or not. It seems like that 
is the
area where the quality and accuracy of the cardinality and selectivity
estimates is crucial, since if those estimates are poor, the Optimizer will
have an inaccurate prediction about whether the intermediate results
will fit into memory or not, and it might avoid a hash join in a case where it
would in fact be the best approach, or vice versa might choose a hash join
in a case where the intermediate results are in fact very large and thus the
hash join will perform very poorly.


>  Obtain cardinality estimates and true estimates for base tables as well as 
> for intermediate results for queries involving multiple joins. 
> ---
>
> Key: DERBY-6938
> URL: https://issues.apache.org/jira/browse/DERBY-6938
> Project: Derby
>  Issue Type: Sub-task
>  Components: SQL
>Reporter: Harshvardhan Gupta
>Assignee: Harshvardhan Gupta
> Attachments: explain.txt, traceout.txt
>
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (DERBY-6938) Obtain cardinality estimates and true estimates for base tables as well as for intermediate results for queries involving multiple joins.

[Harshvardhan Gupta (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-6938?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16097272#comment-16097272
 ] 

Harshvardhan Gupta commented on DERBY-6938:
---

Summarizing  my analysis of HASH based joins so far -

Derby has the capability to perform HASH Joins spilling to disk but has long 
ignored them due to the absence of a cost model around such a behaviour. 
DERBY-1259 also talks about the history of HASH Joins and the changes made as 
part of DERBY-106.

>  Obtain cardinality estimates and true estimates for base tables as well as 
> for intermediate results for queries involving multiple joins. 
> ---
>
> Key: DERBY-6938
> URL: https://issues.apache.org/jira/browse/DERBY-6938
> Project: Derby
>  Issue Type: Sub-task
>  Components: SQL
>Reporter: Harshvardhan Gupta
>Assignee: Harshvardhan Gupta
> Attachments: explain.txt, traceout.txt
>
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (DERBY-6938) Obtain cardinality estimates and true estimates for base tables as well as for intermediate results for queries involving multiple joins.

[Harshvardhan Gupta (JIRA)]

 [ 
https://issues.apache.org/jira/browse/DERBY-6938?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Harshvardhan Gupta updated DERBY-6938:
--
Attachment: traceout.txt

Following my previous comment I am also attaching the sample optimizer trace of 
Query 1a of job dataset where Optimizer ignores the HASH Tables which it 
predicts will spill to disk.

trace output String - "Skipping access path due to excess memory usage, maximum 
is 1048576".

>  Obtain cardinality estimates and true estimates for base tables as well as 
> for intermediate results for queries involving multiple joins. 
> ---
>
> Key: DERBY-6938
> URL: https://issues.apache.org/jira/browse/DERBY-6938
> Project: Derby
>  Issue Type: Sub-task
>  Components: SQL
>Reporter: Harshvardhan Gupta
>Assignee: Harshvardhan Gupta
> Attachments: explain.txt, traceout.txt
>
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (DERBY-6938) Obtain cardinality estimates and true estimates for base tables as well as for intermediate results for queries involving multiple joins.

[Harshvardhan Gupta (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-6938?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16097260#comment-16097260
 ] 

Harshvardhan Gupta commented on DERBY-6938:
---

Hi Bryan,

The Optimizer produces memory and cost estimates for all the possible access 
paths that it can build. Derby will only consider those HASH Joins to go 
through which it _predicts _ will fit into memory and rejects all others 
(including those which will be very efficient for some queries as opposed to 
loop based joins).

However, the memory predictions of Derby may not match memory requirement at 
execution and HASH table may become larger than what Derby predicted. DERBY-106 
was an effort to mitigate the OOM exceptions arising out of this behaviour. So 
although HASH tables do spill to disk but only for those access path go forward 
to execution where Derby predicts that the HASH Tables will not spill to disk.

The above observations of mine match with the comment on DERBY-1259 which was 
written a decade ago.

Coming to the resolution of this problem, we need to investigate how to measure 
the cost of execution when Derby will eventually allow those HASH based access 
paths that it predicts will spill to disk instead of simply ignoring them.

Also, interesting is the observation that Derby currently allows hash based 
joins when users specify their join strategies via Query Hints. 


>  Obtain cardinality estimates and true estimates for base tables as well as 
> for intermediate results for queries involving multiple joins. 
> ---
>
> Key: DERBY-6938
> URL: https://issues.apache.org/jira/browse/DERBY-6938
> Project: Derby
>  Issue Type: Sub-task
>  Components: SQL
>Reporter: Harshvardhan Gupta
>Assignee: Harshvardhan Gupta
> Attachments: explain.txt
>
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Comment Edited] (DERBY-6938) Obtain cardinality estimates and true estimates for base tables as well as for intermediate results for queries involving multiple joins.

[Harshvardhan Gupta (JIRA)]

[ 
https://issues.apache.org/jira/browse/DERBY-6938?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16097260#comment-16097260
 ] 

Harshvardhan Gupta edited comment on DERBY-6938 at 7/22/17 11:41 AM:
-

Hi Bryan,

The Optimizer produces memory and cost estimates for all the possible access 
paths that it can build. Derby will only consider those HASH Joins to go 
through which it _predicts_ will fit into memory and rejects all others 
(including those which will be very efficient for some queries as opposed to 
loop based joins).

However, the memory predictions of Derby may not match memory requirement at 
execution and HASH table may become larger than what Derby predicted. DERBY-106 
was an effort to mitigate the OOM exceptions arising out of this behaviour. So 
although HASH tables do spill to disk but only for those access path go forward 
to execution where Derby predicts that the HASH Tables will not spill to disk.

The above observations of mine match with the comment on DERBY-1259 which was 
written a decade ago.

Coming to the resolution of this problem, we need to investigate how to measure 
the cost of execution when Derby will eventually allow those HASH based access 
paths that it predicts will spill to disk instead of simply ignoring them.

Also, interesting is the observation that Derby currently allows hash based 
joins when users specify their join strategies via Query Hints. 



was (Author: harshvardhan145):
Hi Bryan,

The Optimizer produces memory and cost estimates for all the possible access 
paths that it can build. Derby will only consider those HASH Joins to go 
through which it _predicts _ will fit into memory and rejects all others 
(including those which will be very efficient for some queries as opposed to 
loop based joins).

However, the memory predictions of Derby may not match memory requirement at 
execution and HASH table may become larger than what Derby predicted. DERBY-106 
was an effort to mitigate the OOM exceptions arising out of this behaviour. So 
although HASH tables do spill to disk but only for those access path go forward 
to execution where Derby predicts that the HASH Tables will not spill to disk.

The above observations of mine match with the comment on DERBY-1259 which was 
written a decade ago.

Coming to the resolution of this problem, we need to investigate how to measure 
the cost of execution when Derby will eventually allow those HASH based access 
paths that it predicts will spill to disk instead of simply ignoring them.

Also, interesting is the observation that Derby currently allows hash based 
joins when users specify their join strategies via Query Hints. 


>  Obtain cardinality estimates and true estimates for base tables as well as 
> for intermediate results for queries involving multiple joins. 
> ---
>
> Key: DERBY-6938
> URL: https://issues.apache.org/jira/browse/DERBY-6938
> Project: Derby
>  Issue Type: Sub-task
>  Components: SQL
>Reporter: Harshvardhan Gupta
>Assignee: Harshvardhan Gupta
> Attachments: explain.txt
>
>




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)