You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Knut Anders Hatlen (JIRA)" <ji...@apache.org> on 2007/05/14 11:17:16 UTC
[jira] Created: (DERBY-2642) Convert
lang/dynamicLikeOptimization.sql to JUnit
Convert lang/dynamicLikeOptimization.sql to JUnit
-------------------------------------------------
Key: DERBY-2642
URL: https://issues.apache.org/jira/browse/DERBY-2642
Project: Derby
Issue Type: Improvement
Components: Test
Affects Versions: 10.3.0.0
Reporter: Knut Anders Hatlen
Priority: Minor
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2642) Convert
lang/dynamicLikeOptimization.sql to JUnit
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12496200 ]
Knut Anders Hatlen commented on DERBY-2642:
-------------------------------------------
Thanks Army! I'll add an isDynamicLikeOptimized() method to the RuntimeStatisticsParser class and try to convert the test to JUnit.
> Convert lang/dynamicLikeOptimization.sql to JUnit
> -------------------------------------------------
>
> Key: DERBY-2642
> URL: https://issues.apache.org/jira/browse/DERBY-2642
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.3.0.0
> Reporter: Knut Anders Hatlen
> Assigned To: Knut Anders Hatlen
> Priority: Minor
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Assigned: (DERBY-2642) Convert
lang/dynamicLikeOptimization.sql to JUnit
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2642?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen reassigned DERBY-2642:
-----------------------------------------
Assignee: Knut Anders Hatlen
> Convert lang/dynamicLikeOptimization.sql to JUnit
> -------------------------------------------------
>
> Key: DERBY-2642
> URL: https://issues.apache.org/jira/browse/DERBY-2642
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.3.0.0
> Reporter: Knut Anders Hatlen
> Assigned To: Knut Anders Hatlen
> Priority: Minor
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2642) Convert
lang/dynamicLikeOptimization.sql to JUnit
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2642?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-2642:
--------------------------------------
Derby Info: [Patch Available]
> Convert lang/dynamicLikeOptimization.sql to JUnit
> -------------------------------------------------
>
> Key: DERBY-2642
> URL: https://issues.apache.org/jira/browse/DERBY-2642
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.3.0.0
> Reporter: Knut Anders Hatlen
> Assigned To: Knut Anders Hatlen
> Priority: Minor
> Attachments: derby-2642-1.diff, derby-2642-1.stat, derby-2642-2.diff, derby-2642-2.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2642) Convert
lang/dynamicLikeOptimization.sql to JUnit
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2642?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-2642:
--------------------------------------
Derby Info: (was: [Patch Available])
Committed revision 539744.
> Convert lang/dynamicLikeOptimization.sql to JUnit
> -------------------------------------------------
>
> Key: DERBY-2642
> URL: https://issues.apache.org/jira/browse/DERBY-2642
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.3.0.0
> Reporter: Knut Anders Hatlen
> Assigned To: Knut Anders Hatlen
> Priority: Minor
> Attachments: derby-2642-1.diff, derby-2642-1.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2642) Convert
lang/dynamicLikeOptimization.sql to JUnit
Posted by "A B (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12496091 ]
A B commented on DERBY-2642:
----------------------------
I think the optimization in question here is described:
http://db.apache.org/derby/docs/dev/tuning/rtuntransform472.html
In particular, note the following line:
"In this situation, the LIKE predicate is likewise transformed into three predicates: one LIKE predicate,
one predicate using the >= operator, and one predicate using the < operator."
This correlates to what you have noted about the qualifiers, so my guess is that Yes, this is what demonstrates the "dynamic like optimization".
> Convert lang/dynamicLikeOptimization.sql to JUnit
> -------------------------------------------------
>
> Key: DERBY-2642
> URL: https://issues.apache.org/jira/browse/DERBY-2642
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.3.0.0
> Reporter: Knut Anders Hatlen
> Priority: Minor
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Resolved: (DERBY-2642) Convert
lang/dynamicLikeOptimization.sql to JUnit
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2642?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen resolved DERBY-2642.
---------------------------------------
Resolution: Fixed
Fix Version/s: 10.3.0.0
Derby Info: (was: [Patch Available])
Committed revision 540856.
> Convert lang/dynamicLikeOptimization.sql to JUnit
> -------------------------------------------------
>
> Key: DERBY-2642
> URL: https://issues.apache.org/jira/browse/DERBY-2642
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.3.0.0
> Reporter: Knut Anders Hatlen
> Assigned To: Knut Anders Hatlen
> Priority: Minor
> Fix For: 10.3.0.0
>
> Attachments: derby-2642-1.diff, derby-2642-1.stat, derby-2642-2.diff, derby-2642-2.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2642) Convert
lang/dynamicLikeOptimization.sql to JUnit
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2642?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-2642:
--------------------------------------
Attachment: derby-2642-1.stat
derby-2642-1.diff
Attaching the first version of DynamicLikeOptimizationTest. It covers most of what dynamicLikeOptimization.sql tests, except that it's still missing a couple of queries which cast the pattern or escape character to VARCHAR, and it doesn't check the query plans. I will add tests for that later.
DynamicLikeOptimizationTest runs in both embedded mode and client/server mode (the old test only runs in embedded).
> Convert lang/dynamicLikeOptimization.sql to JUnit
> -------------------------------------------------
>
> Key: DERBY-2642
> URL: https://issues.apache.org/jira/browse/DERBY-2642
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.3.0.0
> Reporter: Knut Anders Hatlen
> Assigned To: Knut Anders Hatlen
> Priority: Minor
> Attachments: derby-2642-1.diff, derby-2642-1.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2642) Convert
lang/dynamicLikeOptimization.sql to JUnit
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12495524 ]
Knut Anders Hatlen commented on DERBY-2642:
-------------------------------------------
This test contains one call to SYSCS_GET_RUNTIMESTATISTICS with the following comment:
-- verify that like optimization being performed
execute p2 using 'values ''%'' ';
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
Does anyone know which part of the query plan that shows that like optimization has been performed?
This is what the query plan in question looks like:
Statement Name:
null
Statement Text:
select id from test where vc10 like ?
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text:
Project-Restrict ResultSet (3):
Number of opens = 1
Rows seen = 7
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 7
Rows filtered = 0
restriction = true
projection = false
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
Source result set:
Table Scan ResultSet for TEST at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 7
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 2}
Number of columns fetched=2
Number of pages visited=1
Number of rows qualified=7
Number of rows visited=8
Scan type=heap
start position:
null stop position:
null qualifiers:
Column[0][0] Id: 2
Operator: <
Ordered nulls: false
Unknown return value: true
Negate comparison result: true
Column[0][1] Id: 2
Operator: <
Order&
> Convert lang/dynamicLikeOptimization.sql to JUnit
> -------------------------------------------------
>
> Key: DERBY-2642
> URL: https://issues.apache.org/jira/browse/DERBY-2642
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.3.0.0
> Reporter: Knut Anders Hatlen
> Priority: Minor
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2642) Convert
lang/dynamicLikeOptimization.sql to JUnit
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2642?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-2642:
--------------------------------------
Attachment: derby-2642-2.stat
derby-2642-2.diff
Attaching patch which adds a test which casts the pattern or escape character, and a test which checks the query plan. The patch also removes the old test.
> Convert lang/dynamicLikeOptimization.sql to JUnit
> -------------------------------------------------
>
> Key: DERBY-2642
> URL: https://issues.apache.org/jira/browse/DERBY-2642
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.3.0.0
> Reporter: Knut Anders Hatlen
> Assigned To: Knut Anders Hatlen
> Priority: Minor
> Attachments: derby-2642-1.diff, derby-2642-1.stat, derby-2642-2.diff, derby-2642-2.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2642) Convert
lang/dynamicLikeOptimization.sql to JUnit
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2642?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen updated DERBY-2642:
--------------------------------------
Derby Info: [Patch Available]
> Convert lang/dynamicLikeOptimization.sql to JUnit
> -------------------------------------------------
>
> Key: DERBY-2642
> URL: https://issues.apache.org/jira/browse/DERBY-2642
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.3.0.0
> Reporter: Knut Anders Hatlen
> Assigned To: Knut Anders Hatlen
> Priority: Minor
> Attachments: derby-2642-1.diff, derby-2642-1.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-2642) Convert
lang/dynamicLikeOptimization.sql to JUnit
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2642?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen closed DERBY-2642.
-------------------------------------
> Convert lang/dynamicLikeOptimization.sql to JUnit
> -------------------------------------------------
>
> Key: DERBY-2642
> URL: https://issues.apache.org/jira/browse/DERBY-2642
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.3.0.0
> Reporter: Knut Anders Hatlen
> Assignee: Knut Anders Hatlen
> Priority: Minor
> Fix For: 10.3.0.0
>
> Attachments: derby-2642-1.diff, derby-2642-1.stat, derby-2642-2.diff, derby-2642-2.stat
>
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-2642) Convert
lang/dynamicLikeOptimization.sql to JUnit
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2642?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12496072 ]
Knut Anders Hatlen commented on DERBY-2642:
-------------------------------------------
The last part of the query plan (truncated in the output above because the test sets maximumdisplaywidth too low) looks like this:
qualifiers:
Column[0][0] Id: 2
Operator: <
Ordered nulls: false
Unknown return value: true
Negate comparison result: true
Column[0][1] Id: 2
Operator: <
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
I assume this means that the scan has two qualifiers >= (that is, < negated) and <. Is this what identifies the query plan as optimized for like?
> Convert lang/dynamicLikeOptimization.sql to JUnit
> -------------------------------------------------
>
> Key: DERBY-2642
> URL: https://issues.apache.org/jira/browse/DERBY-2642
> Project: Derby
> Issue Type: Improvement
> Components: Test
> Affects Versions: 10.3.0.0
> Reporter: Knut Anders Hatlen
> Priority: Minor
>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.