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 "Ole Solberg (JIRA)" <ji...@apache.org> on 2007/02/12 12:15:05 UTC
[jira] Created: (DERBY-2321) 'lang/dynamicLikeOptimization.sql'
gets unexpected query plan?
'lang/dynamicLikeOptimization.sql' gets unexpected query plan?
---------------------------------------------------------------
Key: DERBY-2321
URL: https://issues.apache.org/jira/browse/DERBY-2321
Project: Derby
Issue Type: Bug
Affects Versions: 10.3.0.0
Environment: OS: Red Hat Enterprise Linux AS release 4 (Nahant Update 3) 64bits - Linux 2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:56:28 EST 2006 GNU/Linux
JVM: Sun Microsystems Inc. 1.5.0_07-b03
------------------ Java Information ------------------
Java Version: 1.5.0_07
Java Vendor: Sun Microsystems Inc.
Java home: /usr/local/java/jdk1.5.0_07/jre
Java classpath: /home/os136789/Apache/Derby/trunk/jars/insane/derby.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyclient.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbytools.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbynet.jar:/home/os136789/Apache/Derby/db2jcc/lib/db2jcc.jar:/home/os136789/Apache/Derby/db2jcc/lib/db2jcc_license_c.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyTesting.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyrun.jar:/home/os136789/Apache/Derby/trunk/tools/java/jakarta-oro-2.0.8.jar:/home/os136789/Apache/Derby/trunk/tools/java/junit.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_de_DE.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_es.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_fr.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_it.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_ja_JP.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_ko_KR.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_pt_BR.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_zh_CN.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_zh_TW.jar
OS name: Linux
OS architecture: i386
OS version: 2.6.9-34.ELsmp
Java user name: os136789
Java user home: /home/os136789
Java user dir: /export/home/tmp/os136789/testingDerby/Linux-2.6.9-34.ELsmp_x86_64-x86_64/derbyall
java.specification.name: Java Platform API Specification
java.specification.version: 1.5
--------- Derby Information --------
JRE - JDBC: J2SE 5.0 - JDBC 3.0
[/home/os136789/Derby/trunk/jars/insane/derby.jar] 10.3.0.0 alpha - (506088)
[/home/os136789/Derby/trunk/jars/insane/derbytools.jar] 10.3.0.0 alpha - (506088)
[/home/os136789/Derby/trunk/jars/insane/derbynet.jar] 10.3.0.0 alpha - (506088)
[/home/os136789/Derby/trunk/jars/insane/derbyclient.jar] 10.3.0.0 alpha - (506088)
[/home/os136789/Derby/db2jcc/lib/db2jcc.jar] 2.4 - (17)
[/home/os136789/Derby/db2jcc/lib/db2jcc_license_c.jar] 2.4 - (17)
------------------------------------------------------
Reporter: Ole Solberg
Priority: Minor
See http://dbtg.thresher.com/derby/test/trunk15/jvm1.5/testing/testlog/Linux-2.6.9-34.ELsmp_x86_64-x86_64/506088-derbyall_diff.txt
ij> -- pushing generated predicates down
prepare p1 as 'select id from test where c10 like ?';
ij> prepare p2 as 'select id from test where vc10 like ?';
ij> select vc10 from test where vc10 like 'values cast(null as varchar(1))';
.
.
ij> -- verify that like optimization being performed
execute p2 using 'values ''%'' ';
.
.
338 del
< select id from test where vc10 like ?
338a338
> select 1 from t1 where '%foobar' like ? escape ?
<signature>
********* Diff file derbyall/derbylang/dynamicLikeOptimization.diff
*** Start: dynamicLikeOptimization jdk1.5.0_07 derbyall:derbylang 2007-02-12 00:06:26 ***
338 del
< select id from test where vc10 like ?
338a338
> select 1 from t1 where '%foobar' like ? escape ?
351 del
< Number of opens = 1
352 del
< Rows seen = 7
352a351,352
> Number of opens = 0
> Rows seen = 0
364 del
< Number of opens = 1
365 del
< Rows seen = 7
365a364,365
> Number of opens = 0
> Rows seen = 0
367 del
< restriction = true
367a367
> restriction = false
376 del
< Table Scan ResultSet for TEST at read committed isolation level using instantaneous share row locking chosen by the optimizer
377 del
< Number of opens = 1
378 del
< Rows seen = 7
378a376,378
> Table Scan ResultSet for T1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
> Number of opens = 0
> Rows seen = 0
385d384
< next time in milliseconds/row = 0
387,392d385
< 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
396 del
< Column[0][0] Id: 2
397 del
< Operator: <
398 del
< Ordered nulls: false
399 del
< Unknown return value: true
400 del
< Negate comparison result: true
401 del
< Column[0][1] Id: 2
402 del
< Operator: <
403 del
< Order&
403a389
> None
Test Failed.
*** End: dynamicLikeOptimization jdk1.5.0_07 derbyall:derbylang 2007-02-12 00:06:35 ***
</signature>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2321) 'lang/dynamicLikeOptimization.sql'
gets unexpected query plan?
Posted by "Mike Matrigali (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2321?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Mike Matrigali updated DERBY-2321:
----------------------------------
Component/s: Regression Test Failure
> 'lang/dynamicLikeOptimization.sql' gets unexpected query plan?
> ---------------------------------------------------------------
>
> Key: DERBY-2321
> URL: https://issues.apache.org/jira/browse/DERBY-2321
> Project: Derby
> Issue Type: Bug
> Components: Regression Test Failure
> Affects Versions: 10.3.0.0
> Environment: OS: Red Hat Enterprise Linux AS release 4 (Nahant Update 3) 64bits - Linux 2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:56:28 EST 2006 GNU/Linux
> JVM: Sun Microsystems Inc. 1.5.0_07-b03
> ------------------ Java Information ------------------
> Java Version: 1.5.0_07
> Java Vendor: Sun Microsystems Inc.
> Java home: /usr/local/java/jdk1.5.0_07/jre
> Java classpath: /home/os136789/Apache/Derby/trunk/jars/insane/derby.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyclient.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbytools.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbynet.jar:/home/os136789/Apache/Derby/db2jcc/lib/db2jcc.jar:/home/os136789/Apache/Derby/db2jcc/lib/db2jcc_license_c.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyTesting.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyrun.jar:/home/os136789/Apache/Derby/trunk/tools/java/jakarta-oro-2.0.8.jar:/home/os136789/Apache/Derby/trunk/tools/java/junit.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_de_DE.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_es.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_fr.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_it.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_ja_JP.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_ko_KR.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_pt_BR.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_zh_CN.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_zh_TW.jar
> OS name: Linux
> OS architecture: i386
> OS version: 2.6.9-34.ELsmp
> Java user name: os136789
> Java user home: /home/os136789
> Java user dir: /export/home/tmp/os136789/testingDerby/Linux-2.6.9-34.ELsmp_x86_64-x86_64/derbyall
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.5
> --------- Derby Information --------
> JRE - JDBC: J2SE 5.0 - JDBC 3.0
> [/home/os136789/Derby/trunk/jars/insane/derby.jar] 10.3.0.0 alpha - (506088)
> [/home/os136789/Derby/trunk/jars/insane/derbytools.jar] 10.3.0.0 alpha - (506088)
> [/home/os136789/Derby/trunk/jars/insane/derbynet.jar] 10.3.0.0 alpha - (506088)
> [/home/os136789/Derby/trunk/jars/insane/derbyclient.jar] 10.3.0.0 alpha - (506088)
> [/home/os136789/Derby/db2jcc/lib/db2jcc.jar] 2.4 - (17)
> [/home/os136789/Derby/db2jcc/lib/db2jcc_license_c.jar] 2.4 - (17)
> ------------------------------------------------------
> Reporter: Ole Solberg
> Priority: Minor
>
> See http://dbtg.thresher.com/derby/test/trunk15/jvm1.5/testing/testlog/Linux-2.6.9-34.ELsmp_x86_64-x86_64/506088-derbyall_diff.txt
> ij> -- pushing generated predicates down
> prepare p1 as 'select id from test where c10 like ?';
> ij> prepare p2 as 'select id from test where vc10 like ?';
> ij> select vc10 from test where vc10 like 'values cast(null as varchar(1))';
> .
> .
> ij> -- verify that like optimization being performed
> execute p2 using 'values ''%'' ';
> .
> .
> 338 del
> < select id from test where vc10 like ?
> 338a338
> > select 1 from t1 where '%foobar' like ? escape ?
> <signature>
> ********* Diff file derbyall/derbylang/dynamicLikeOptimization.diff
> *** Start: dynamicLikeOptimization jdk1.5.0_07 derbyall:derbylang 2007-02-12 00:06:26 ***
> 338 del
> < select id from test where vc10 like ?
> 338a338
> > select 1 from t1 where '%foobar' like ? escape ?
> 351 del
> < Number of opens = 1
> 352 del
> < Rows seen = 7
> 352a351,352
> > Number of opens = 0
> > Rows seen = 0
> 364 del
> < Number of opens = 1
> 365 del
> < Rows seen = 7
> 365a364,365
> > Number of opens = 0
> > Rows seen = 0
> 367 del
> < restriction = true
> 367a367
> > restriction = false
> 376 del
> < Table Scan ResultSet for TEST at read committed isolation level using instantaneous share row locking chosen by the optimizer
> 377 del
> < Number of opens = 1
> 378 del
> < Rows seen = 7
> 378a376,378
> > Table Scan ResultSet for T1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
> > Number of opens = 0
> > Rows seen = 0
> 385d384
> < next time in milliseconds/row = 0
> 387,392d385
> < 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
> 396 del
> < Column[0][0] Id: 2
> 397 del
> < Operator: <
> 398 del
> < Ordered nulls: false
> 399 del
> < Unknown return value: true
> 400 del
> < Negate comparison result: true
> 401 del
> < Column[0][1] Id: 2
> 402 del
> < Operator: <
> 403 del
> < Order&
> 403a389
> > None
> Test Failed.
> *** End: dynamicLikeOptimization jdk1.5.0_07 derbyall:derbylang 2007-02-12 00:06:35 ***
> </signature>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (DERBY-2321) 'lang/dynamicLikeOptimization.sql'
gets unexpected query plan?
Posted by "Kathey Marsden (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-2321?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Kathey Marsden updated DERBY-2321:
----------------------------------
Derby Info: (was: [Regression])
Unchecking Regession checkbox as this is not a confirmed product regression
> 'lang/dynamicLikeOptimization.sql' gets unexpected query plan?
> ---------------------------------------------------------------
>
> Key: DERBY-2321
> URL: https://issues.apache.org/jira/browse/DERBY-2321
> Project: Derby
> Issue Type: Bug
> Components: Regression Test Failure
> Affects Versions: 10.3.1.4
> Environment: OS: Red Hat Enterprise Linux AS release 4 (Nahant Update 3) 64bits - Linux 2.6.9-34.ELsmp #1 SMP Fri Feb 24 16:56:28 EST 2006 GNU/Linux
> JVM: Sun Microsystems Inc. 1.5.0_07-b03
> ------------------ Java Information ------------------
> Java Version: 1.5.0_07
> Java Vendor: Sun Microsystems Inc.
> Java home: /usr/local/java/jdk1.5.0_07/jre
> Java classpath: /home/os136789/Apache/Derby/trunk/jars/insane/derby.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyclient.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbytools.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbynet.jar:/home/os136789/Apache/Derby/db2jcc/lib/db2jcc.jar:/home/os136789/Apache/Derby/db2jcc/lib/db2jcc_license_c.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyTesting.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyrun.jar:/home/os136789/Apache/Derby/trunk/tools/java/jakarta-oro-2.0.8.jar:/home/os136789/Apache/Derby/trunk/tools/java/junit.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_de_DE.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_es.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_fr.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_it.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_ja_JP.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_ko_KR.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_pt_BR.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_zh_CN.jar:/home/os136789/Apache/Derby/trunk/jars/insane/derbyLocale_zh_TW.jar
> OS name: Linux
> OS architecture: i386
> OS version: 2.6.9-34.ELsmp
> Java user name: os136789
> Java user home: /home/os136789
> Java user dir: /export/home/tmp/os136789/testingDerby/Linux-2.6.9-34.ELsmp_x86_64-x86_64/derbyall
> java.specification.name: Java Platform API Specification
> java.specification.version: 1.5
> --------- Derby Information --------
> JRE - JDBC: J2SE 5.0 - JDBC 3.0
> [/home/os136789/Derby/trunk/jars/insane/derby.jar] 10.3.0.0 alpha - (506088)
> [/home/os136789/Derby/trunk/jars/insane/derbytools.jar] 10.3.0.0 alpha - (506088)
> [/home/os136789/Derby/trunk/jars/insane/derbynet.jar] 10.3.0.0 alpha - (506088)
> [/home/os136789/Derby/trunk/jars/insane/derbyclient.jar] 10.3.0.0 alpha - (506088)
> [/home/os136789/Derby/db2jcc/lib/db2jcc.jar] 2.4 - (17)
> [/home/os136789/Derby/db2jcc/lib/db2jcc_license_c.jar] 2.4 - (17)
> ------------------------------------------------------
> Reporter: Ole Solberg
> Priority: Minor
>
> See http://dbtg.thresher.com/derby/test/trunk15/jvm1.5/testing/testlog/Linux-2.6.9-34.ELsmp_x86_64-x86_64/506088-derbyall_diff.txt
> ij> -- pushing generated predicates down
> prepare p1 as 'select id from test where c10 like ?';
> ij> prepare p2 as 'select id from test where vc10 like ?';
> ij> select vc10 from test where vc10 like 'values cast(null as varchar(1))';
> .
> .
> ij> -- verify that like optimization being performed
> execute p2 using 'values ''%'' ';
> .
> .
> 338 del
> < select id from test where vc10 like ?
> 338a338
> > select 1 from t1 where '%foobar' like ? escape ?
> <signature>
> ********* Diff file derbyall/derbylang/dynamicLikeOptimization.diff
> *** Start: dynamicLikeOptimization jdk1.5.0_07 derbyall:derbylang 2007-02-12 00:06:26 ***
> 338 del
> < select id from test where vc10 like ?
> 338a338
> > select 1 from t1 where '%foobar' like ? escape ?
> 351 del
> < Number of opens = 1
> 352 del
> < Rows seen = 7
> 352a351,352
> > Number of opens = 0
> > Rows seen = 0
> 364 del
> < Number of opens = 1
> 365 del
> < Rows seen = 7
> 365a364,365
> > Number of opens = 0
> > Rows seen = 0
> 367 del
> < restriction = true
> 367a367
> > restriction = false
> 376 del
> < Table Scan ResultSet for TEST at read committed isolation level using instantaneous share row locking chosen by the optimizer
> 377 del
> < Number of opens = 1
> 378 del
> < Rows seen = 7
> 378a376,378
> > Table Scan ResultSet for T1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
> > Number of opens = 0
> > Rows seen = 0
> 385d384
> < next time in milliseconds/row = 0
> 387,392d385
> < 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
> 396 del
> < Column[0][0] Id: 2
> 397 del
> < Operator: <
> 398 del
> < Ordered nulls: false
> 399 del
> < Unknown return value: true
> 400 del
> < Negate comparison result: true
> 401 del
> < Column[0][1] Id: 2
> 402 del
> < Operator: <
> 403 del
> < Order&
> 403a389
> > None
> Test Failed.
> *** End: dynamicLikeOptimization jdk1.5.0_07 derbyall:derbylang 2007-02-12 00:06:35 ***
> </signature>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.