ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- test elimination of sort for order by set isolation to rr; 0 rows inserted/updated/deleted ij> -- test combining of sorts for distinct and order by -- create some tables create table t1(c1 int, c2 int, c3 int, c4 int); 0 rows inserted/updated/deleted ij> insert into t1 values (1, 2, 3, 4); 1 row inserted/updated/deleted ij> insert into t1 values (2, 3, 4, 5); 1 row inserted/updated/deleted ij> insert into t1 values (-1, -2, -3, -4); 1 row inserted/updated/deleted ij> insert into t1 values (-2, -3, -4, -5); 1 row inserted/updated/deleted ij> insert into t1 values (1, 2, 4, 3); 1 row inserted/updated/deleted ij> insert into t1 values (1, 3, 2, 4); 1 row inserted/updated/deleted ij> insert into t1 values (1, 3, 4, 2); 1 row inserted/updated/deleted ij> insert into t1 values (1, 4, 2, 3); 1 row inserted/updated/deleted ij> insert into t1 values (1, 4, 3, 2); 1 row inserted/updated/deleted ij> insert into t1 values (2, 1, 4, 3); 1 row inserted/updated/deleted ij> maximumdisplaywidth 7000; ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij> -- no index on t1 -- full match select distinct c1, c2, c3, c4 from t1 order by 1, 2, 3, 4; C1 |C2 |C3 |C4 ----------------------------------------------- -2 |-3 |-4 |-5 -1 |-2 |-3 |-4 1 |2 |3 |4 1 |2 |4 |3 1 |3 |2 |4 1 |3 |4 |2 1 |4 |2 |3 1 |4 |3 |2 2 |1 |4 |3 2 |3 |4 |5 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- no index on t1 -- full match select distinct c1, c2, c3, c4 from t1 order by 1, 2, 3, 4 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: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 10 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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=All Number of columns fetched=4 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=heap start position: null stop position: null qualifiers: None ij> select distinct c1, c2, c3, c4 from t1 order by c1, c2, c3, c4; C1 |C2 |C3 |C4 ----------------------------------------------- -2 |-3 |-4 |-5 -1 |-2 |-3 |-4 1 |2 |3 |4 1 |2 |4 |3 1 |3 |2 |4 1 |3 |4 |2 1 |4 |2 |3 1 |4 |3 |2 2 |1 |4 |3 2 |3 |4 |5 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct c1, c2, c3, c4 from t1 order by c1, c2, c3, c4 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: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 10 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=10 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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=All Number of columns fetched=4 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=heap start position: null stop position: null qualifiers: None ij> -- in order prefix select distinct c3, c4 from t1 order by 1, 2; C3 |C4 ----------------------- -4 |-5 -3 |-4 2 |3 2 |4 3 |2 3 |4 4 |2 4 |3 4 |5 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- in order prefix select distinct c3, c4 from t1 order by 1, 2 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: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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={2, 3} Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=heap start position: null stop position: null qualifiers: None ij> select distinct c3, c4 from t1 order by c3, c4; C3 |C4 ----------------------- -4 |-5 -3 |-4 2 |3 2 |4 3 |2 3 |4 4 |2 4 |3 4 |5 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct c3, c4 from t1 order by c3, c4 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: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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={2, 3} Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=heap start position: null stop position: null qualifiers: None ij> -- no prefix select distinct c3, c4 from t1 order by 2; C3 |C4 ----------------------- -4 |-5 -3 |-4 3 |2 4 |2 2 |3 4 |3 2 |4 3 |4 4 |5 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- no prefix select distinct c3, c4 from t1 order by 2 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 (4): Number of opens = 1 Rows seen = 9 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: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (3): Number of opens = 1 Rows seen = 10 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: Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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={2, 3} Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=heap start position: null stop position: null qualifiers: None ij> select distinct c3, c4 from t1 order by c4; C3 |C4 ----------------------- -4 |-5 -3 |-4 3 |2 4 |2 2 |3 4 |3 2 |4 3 |4 4 |5 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct c3, c4 from t1 order by c4 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 (4): Number of opens = 1 Rows seen = 9 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: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 9 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=9 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (3): Number of opens = 1 Rows seen = 10 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: Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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={2, 3} Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=heap start position: null stop position: null qualifiers: None ij> -- expression select distinct c3, 1 from t1 order by 1; C3 |2 ----------------------- -4 |1 -3 |1 2 |1 3 |1 4 |1 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- expression select distinct c3, 1 from t1 order by 1 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: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 5 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=5 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (3): Number of opens = 1 Rows seen = 10 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: Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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={2} Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=heap start position: null stop position: null qualifiers: None ij> select distinct c3, 1 from t1 order by 2; C3 |2 ----------------------- -4 |1 -3 |1 2 |1 3 |1 4 |1 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct c3, 1 from t1 order by 2 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 (4): Number of opens = 1 Rows seen = 5 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: Sort ResultSet: Number of opens = 1 Rows input = 10 Rows returned = 5 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=10 Number of rows output=5 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (3): Number of opens = 1 Rows seen = 10 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: Table Scan ResultSet for T1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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={2} Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=heap start position: null stop position: null qualifiers: None ij> -- verify that a sort is still done when a unique index -- exists create unique index i1 on t1(c1, c2, c3, c4); 0 rows inserted/updated/deleted ij> select distinct c4, c3 from t1 where c1 = 1 and c2 = 2 order by c4, c3; C4 |C3 ----------------------- 3 |4 4 |3 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct c4, c3 from t1 where c1 = 1 and c2 = 2 order by c4, c3 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: Sort ResultSet: Number of opens = 1 Rows input = 2 Rows returned = 2 Eliminate duplicates = false In sorted order = false Sort information: Number of rows input=2 Number of rows output=2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (2): Number of opens = 1 Rows seen = 2 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: Index Scan ResultSet for T1 using index I1 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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, 1, 2, 3} Number of columns fetched=4 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=3 Scan type=btree Tree height=1 start position: >= on first 2 column(s). Ordered null semantics on the following columns: stop position: > on first 2 column(s). Ordered null semantics on the following columns: qualifiers: None ij> select distinct c3, c4 from t1 where c1 = 1 and c2 = 2 order by c4; C3 |C4 ----------------------- 4 |3 3 |4 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select distinct c3, c4 from t1 where c1 = 1 and c2 = 2 order by c4 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: Sort ResultSet: Number of opens = 1 Rows input = 2 Rows returned = 2 Eliminate duplicates = false In sorted order = false Sort information: Number of rows input=2 Number of rows output=2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (2): Number of opens = 1 Rows seen = 2 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: Index Scan ResultSet for T1 using index I1 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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, 1, 2, 3} Number of columns fetched=4 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=3 Scan type=btree Tree height=1 start position: >= on first 2 column(s). Ordered null semantics on the following columns: stop position: > on first 2 column(s). Ordered null semantics on the following columns: qualifiers: None ij> -- order by and union select c1 from t1 union select c2 from t1 order by 1; 1 ----------- -3 -2 -1 1 2 3 4 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- order by and union select c1 from t1 union select c2 from t1 order by 1 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: Sort ResultSet: Number of opens = 1 Rows input = 20 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=20 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 10 Rows seen from the right = 10 Rows returned = 20 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Scan ResultSet for T1 using index I1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None Right result set: Index Scan ResultSet for T1 using index I1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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={1} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> select c1 from t1 union select c2 as c1 from t1 order by c1; C1 ----------- -3 -2 -1 1 2 3 4 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select c1 from t1 union select c2 as c1 from t1 order by c1 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: Sort ResultSet: Number of opens = 1 Rows input = 20 Rows returned = 7 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=20 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 10 Rows seen from the right = 10 Rows returned = 20 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Scan ResultSet for T1 using index I1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None Right result set: Index Scan ResultSet for T1 using index I1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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={1} Number of columns fetched=1 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- RESOLVE: next 2 will do 2 sorts (bug 58) select c3, c4 from t1 union select c2, c1 from t1 order by 2; 1 |2 ----------------------- -4 |-5 -3 |-4 -3 |-2 -2 |-1 4 |1 3 |1 2 |1 4 |2 3 |2 1 |2 4 |3 2 |3 3 |4 2 |4 4 |5 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- RESOLVE: next 2 will do 2 sorts (bug 58) select c3, c4 from t1 union select c2, c1 from t1 order by 2 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: Sort ResultSet: Number of opens = 1 Rows input = 15 Rows returned = 15 Eliminate duplicates = false In sorted order = false Sort information: Number of rows input=15 Number of rows output=15 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Sort ResultSet: Number of opens = 1 Rows input = 20 Rows returned = 15 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=20 Number of rows output=15 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 10 Rows seen from the right = 10 Rows returned = 20 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Scan ResultSet for T1 using index I1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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={2, 3} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None Right result set: Project-Restrict ResultSet (5): Number of opens = 1 Rows seen = 10 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: Index Scan ResultSet for T1 using index I1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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, 1} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> select c3, c4 from t1 union select c2, c1 as c4 from t1 order by c4; 1 |C4 ----------------------- -4 |-5 -3 |-4 -3 |-2 -2 |-1 4 |1 3 |1 2 |1 4 |2 3 |2 1 |2 4 |3 2 |3 3 |4 2 |4 4 |5 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select c3, c4 from t1 union select c2, c1 as c4 from t1 order by c4 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: Sort ResultSet: Number of opens = 1 Rows input = 15 Rows returned = 15 Eliminate duplicates = false In sorted order = false Sort information: Number of rows input=15 Number of rows output=15 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Sort ResultSet: Number of opens = 1 Rows input = 20 Rows returned = 15 Eliminate duplicates = true In sorted order = false Sort information: Number of rows input=20 Number of rows output=15 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Union ResultSet: Number of opens = 1 Rows seen from the left = 10 Rows seen from the right = 10 Rows returned = 20 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Scan ResultSet for T1 using index I1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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={2, 3} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None Right result set: Project-Restrict ResultSet (5): Number of opens = 1 Rows seen = 10 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: Index Scan ResultSet for T1 using index I1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 10 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, 1} Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=10 Number of rows visited=10 Scan type=btree Tree height=1 start position: None stop position: None qualifiers: None ij> -- DERBY-2887: investigate affect of NULLS FIRST/LAST on sorting insert into t1 values (1, null, 14, null); 1 row inserted/updated/deleted ij> -- should NOT do a sort: select c1,c2,c3 from t1 where c1 = 1 order by c1,c2; C1 |C2 |C3 ----------------------------------- 1 |2 |3 1 |2 |4 1 |3 |2 1 |3 |4 1 |4 |2 1 |4 |3 1 |NULL |14 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- should NOT do a sort: select c1,c2,c3 from t1 where c1 = 1 order by c1,c2 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: Index Scan ResultSet for T1 using index I1 at serializable isolation level using 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, 1, 2} Number of columns fetched=3 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=7 Number of rows visited=8 Scan type=btree Tree height=-1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None ij> -- Needs to do a sort to get the NULLS FIRST: select c1,c2,c3 from t1 where c1 = 1 order by c1,c2 nulls first; C1 |C2 |C3 ----------------------------------- 1 |NULL |14 1 |2 |4 1 |2 |3 1 |3 |4 1 |3 |2 1 |4 |3 1 |4 |2 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- Needs to do a sort to get the NULLS FIRST: select c1,c2,c3 from t1 where c1 = 1 order by c1,c2 nulls first 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: Sort ResultSet: Number of opens = 1 Rows input = 7 Rows returned = 7 Eliminate duplicates = false In sorted order = false Sort information: Number of rows input=7 Number of rows output=7 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Index Scan ResultSet for T1 using index I1 at serializable isolation level using 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, 1, 2} Number of columns fetched=3 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=7 Number of rows visited=8 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None ij> -- test recognition of single row tables -- even when scanning heap create table u1(c1 int, c2 int); 0 rows inserted/updated/deleted ij> create table u2(c2 int, c3 int); 0 rows inserted/updated/deleted ij> create table u3(c3 int, c4 int); 0 rows inserted/updated/deleted ij> insert into u1 values (1, 1), (2, 2); 2 rows inserted/updated/deleted ij> insert into u2 values (1, 1), (2, 2); 2 rows inserted/updated/deleted ij> insert into u3 values (1, 1), (2, 2); 2 rows inserted/updated/deleted ij> create unique index u1_i1 on u1(c1); 0 rows inserted/updated/deleted ij> create unique index u2_i1 on u2(c2); 0 rows inserted/updated/deleted ij> create unique index u3_i1 on u3(c3); 0 rows inserted/updated/deleted ij> select * from u1, u2, u3 where u1.c1 = 1 and u1.c1 = u2.c2 order by u3.c3; C1 |C2 |C2 |C3 |C3 |C4 ----------------------------------------------------------------------- 1 |1 |1 |1 |1 |1 1 |1 |1 |1 |2 |2 ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from u1, u2, u3 where u1.c1 = 1 and u1.c1 = u2.c2 order by u3.c3 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: Sort ResultSet: Number of opens = 1 Rows input = 2 Rows returned = 2 Eliminate duplicates = false In sorted order = false Sort information: Number of rows input=2 Number of rows output=2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Source result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 2 Rows filtered = 0 Rows returned = 2 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 1 Rows seen from the right = 1 Rows filtered = 0 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Left result set: Index Row to Base Row ResultSet for U1: Number of opens = 1 Rows seen = 1 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for U1 using index U1_I1 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None Right result set: Index Row to Base Row ResultSet for U2: Number of opens = 1 Rows seen = 1 Columns accessed from heap = {1} constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 Index Scan ResultSet for U2 using index U2_I1 at serializable isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 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=All Number of columns fetched=2 Number of deleted rows visited=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=btree Tree height=1 start position: >= on first 1 column(s). Ordered null semantics on the following columns: stop position: > on first 1 column(s). Ordered null semantics on the following columns: qualifiers: None Right result set: Table Scan ResultSet for U3 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 2 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=All Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=2 Number of rows visited=2 Scan type=heap start position: null stop position: null qualifiers: None ij> -- clean up drop table t1; 0 rows inserted/updated/deleted ij> drop table u1; 0 rows inserted/updated/deleted ij> drop table u2; 0 rows inserted/updated/deleted ij> drop table u3; 0 rows inserted/updated/deleted ij> -- DERBY-3997: Elimination of ORDER BY clause because all the columns -- to order by were known to be constant, made extra columns appear in -- the result. create table d3997(x int, y int, z int); 0 rows inserted/updated/deleted ij> -- These queries used to have two result columns, but should only have one select 1 from d3997 where x=1 order by x; 1 ----------- ij> select y from d3997 where x=1 order by x; Y ----------- ij> -- Used to have three columns, should only have two select y,z from d3997 where x=1 order by x; Y |Z ----------------------- ij> -- Used to have three columns, should only have one select x from d3997 where y=1 and z=1 order by y,z; X ----------- ij> -- Dynamic parameters are also constants (expect one column) execute 'select x from d3997 where y=? order by y' using 'values 1'; IJ WARNING: Autocommit may close using result set X ----------- ij> -- Order by columns should not be removed from the result here select * from d3997 where x=1 order by x; X |Y |Z ----------------------------------- ij> select x,y,z from d3997 where x=1 order by x; X |Y |Z ----------------------------------- ij> select x,y,z from d3997 where x=1 and y=1 order by x,y; X |Y |Z ----------------------------------- ij> -- Order by should not be eliminated here (not constant values). Insert some -- data in reverse order to verify that the results are sorted. insert into d3997 values (9,8,7),(6,5,4),(3,2,1); 3 rows inserted/updated/deleted ij> select * from d3997 where y<>2 order by y; X |Y |Z ----------------------------------- 6 |5 |4 9 |8 |7 ij> select z from d3997 where y>2 order by y; Z ----------- 4 7 ij> drop table d3997; 0 rows inserted/updated/deleted ij>