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.
--
-- this also tests multiple indexes share one conglomerate if they essentially
-- are the same
autocommit off;
ij> create table tab1 (c1 int, c2 smallint, c3 double precision, c4 varchar(30),
		   c5 varchar(1024));
0 rows inserted/updated/deleted
ij> insert into tab1 values (8, 12, 5.6, 'dfg', 'ghji');
1 row inserted/updated/deleted
ij> insert into tab1 values (76, 2, -9.86, 'yudf', '45gd');
1 row inserted/updated/deleted
ij> insert into tab1 values (-78, 45, -5.6, 'jakdsfh', 'df89g');
1 row inserted/updated/deleted
ij> insert into tab1 values (56, -3, 6.7, 'dfgs', 'fds');
1 row inserted/updated/deleted
ij> create index i1 on tab1 (c1, c3, c4);
0 rows inserted/updated/deleted
ij> create index i2 on tab1 (c1 desc, c3 desc, c4 desc);
0 rows inserted/updated/deleted
ij> create index i3 on tab1 (c1 desc, c3 asc, c4 desc);
0 rows inserted/updated/deleted
ij> create index i4 on tab1 (c2 desc, c3, c1);
0 rows inserted/updated/deleted
ij> create index i5 on tab1 (c1, c2 desc);
0 rows inserted/updated/deleted
ij> insert into tab1 values (34, 67, 5.3, 'rtgd', 'hds');
1 row inserted/updated/deleted
ij> insert into tab1 values (100, 11, 9.0, '34sfg', 'ayupo');
1 row inserted/updated/deleted
ij> insert into tab1 values (-100, 93, 9.1, 'egfh', 's6j');
1 row inserted/updated/deleted
ij> insert into tab1 values (55, 44, -9.85, 'yudd', 'df89f');
1 row inserted/updated/deleted
ij> insert into tab1 values (34, 68, 2.7, 'srg', 'iur');
1 row inserted/updated/deleted
ij> insert into tab1 values (34, 66, 1.2, 'yty', 'wer');
1 row inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 20000;
ij> -- should use index i4
select c1, c3 from tab1 where c2 > 40 and c3 <= 5.3;
C1         |C3                      
------------------------------------
34         |2.7                     
34         |5.3                     
34         |1.2                     
-78        |-5.6                    
55         |-9.85                   
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- should use index i4
select c1, c3 from tab1 where c2 > 40 and c3 <= 5.3
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 = 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:
	Project-Restrict ResultSet (2):
	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:
		Index Scan ResultSet for TAB1 using index I4 at read committed isolation level using instantaneous share row locking chosen by the optimizer
		Number of opens = 1
		Rows seen = 5
		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=5
			Number of rows visited=7
			Scan type=btree
			Tree height=1
			start position:
				None
			stop position:
				>= on first 1 column(s).
				Ordered null semantics on the following columns: 
			qualifiers:
				Column[0][0] Id: 1
				Operator: <=
				Ordered nulls: false
				Unknown return value: false
				Negate comparison result: false
ij> -- should use index i5
select c2, c1 from tab1 where c2 <= 44 and c1 > 55;
C2    |C1         
------------------
-3    |56         
2     |76         
11    |100        
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- should use index i5
select c2, c1 from tab1 where c2 <= 44 and c1 > 55
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 (2):
Number of opens = 1
Rows seen = 3
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 TAB1 using index I5 at read committed isolation level using instantaneous share row locking chosen by the optimizer
	Number of opens = 1
	Rows seen = 3
	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=3
		Number of rows visited=3
		Scan type=btree
		Tree height=1
		start position:
			> on first 1 column(s).
			Ordered null semantics on the following columns: 
		stop position:
			None
		qualifiers:
			Column[0][0] Id: 1
			Operator: <=
			Ordered nulls: false
			Unknown return value: false
			Negate comparison result: false
ij> -- should use i1
select c1, c3, c4 from tab1 order by c1, c3;
C1         |C3                      |C4                            
-------------------------------------------------------------------
-100       |9.1                     |egfh                          
-78        |-5.6                    |jakdsfh                       
8          |5.6                     |dfg                           
34         |1.2                     |yty                           
34         |2.7                     |srg                           
34         |5.3                     |rtgd                          
55         |-9.85                   |yudd                          
56         |6.7                     |dfgs                          
76         |-9.86                   |yudf                          
100        |9.0                     |34sfg                         
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- should use i1
select c1, c3, c4 from tab1 order by c1, 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: 
Index Scan ResultSet for TAB1 using index I1 at read committed isolation level using instantaneous share row 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, 2}
	Number of columns fetched=3
	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> -- should use i2
select c1, c3, c4 from tab1 order by c1 desc, c3 desc, c4 desc;
C1         |C3                      |C4                            
-------------------------------------------------------------------
100        |9.0                     |34sfg                         
76         |-9.86                   |yudf                          
56         |6.7                     |dfgs                          
55         |-9.85                   |yudd                          
34         |5.3                     |rtgd                          
34         |2.7                     |srg                           
34         |1.2                     |yty                           
8          |5.6                     |dfg                           
-78        |-5.6                    |jakdsfh                       
-100       |9.1                     |egfh                          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- should use i2
select c1, c3, c4 from tab1 order by c1 desc, c3 desc, c4 desc
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 TAB1 using index I2 at read committed isolation level using instantaneous share row 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, 2}
	Number of columns fetched=3
	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> -- should use i3
select c1, c3, c4 from tab1 order by c1 desc, c3 asc, c4 desc;
C1         |C3                      |C4                            
-------------------------------------------------------------------
100        |9.0                     |34sfg                         
76         |-9.86                   |yudf                          
56         |6.7                     |dfgs                          
55         |-9.85                   |yudd                          
34         |1.2                     |yty                           
34         |2.7                     |srg                           
34         |5.3                     |rtgd                          
8          |5.6                     |dfg                           
-78        |-5.6                    |jakdsfh                       
-100       |9.1                     |egfh                          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- should use i3
select c1, c3, c4 from tab1 order by c1 desc, c3 asc, c4 desc
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 TAB1 using index I3 at read committed isolation level using instantaneous share row 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, 2}
	Number of columns fetched=3
	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> -- should use i4
select c1, c2, c3 from tab1 order by c2 desc, c3 asc;
C1         |C2    |C3                      
-------------------------------------------
-100       |93    |9.1                     
34         |68    |2.7                     
34         |67    |5.3                     
34         |66    |1.2                     
-78        |45    |-5.6                    
55         |44    |-9.85                   
8          |12    |5.6                     
100        |11    |9.0                     
76         |2     |-9.86                   
56         |-3    |6.7                     
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- should use i4
select c1, c2, c3 from tab1 order by c2 desc, c3 asc
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 (2):
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 TAB1 using index I4 at read committed isolation level using instantaneous share row 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, 2}
		Number of columns fetched=3
		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> -- should use i5
select c1, c2 from tab1 order by c1, c2 desc;
C1         |C2    
------------------
-100       |93    
-78        |45    
8          |12    
34         |68    
34         |67    
34         |66    
55         |44    
56         |-3    
76         |2     
100        |11    
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- should use i5
select c1, c2 from tab1 order by c1, c2 desc
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 TAB1 using index I5 at read committed isolation level using instantaneous share row 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> -- should use i4
select max(c2) from tab1;
1     
------
93    
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- should use i4
select max(c2) from tab1
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 = 1
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:
	Scalar Aggregate ResultSet:
	Number of opens = 1
	Rows input = 1
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
	Index Key Optimization = true
	Source result set:
		Project-Restrict ResultSet (3):
		Number of opens = 1
		Rows seen = 1
		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 TAB1 using index I4 at read committed 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={0}
				Number of columns fetched=1
				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:
					None
				stop position:
					None
				qualifiers:
					None
ij> -- should use i4
select min(c2) from tab1;
1     
------
-3    
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- should use i4
select min(c2) from tab1
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 = 1
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:
	Scalar Aggregate ResultSet:
	Number of opens = 1
	Rows input = 1
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
	Index Key Optimization = true
	Source result set:
		Project-Restrict ResultSet (3):
		Number of opens = 1
		Rows seen = 1
		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:
			Last Key Index Scan ResultSet for TAB1 using index I4at read committed isolation level using share row locking chosen by the optimizer
			Number of opens = 1
			Rows seen = 1
				constructor time (milliseconds) = 0
				open time (milliseconds) = 0
				next time (milliseconds) = 0
				close time (milliseconds) = 0
				next time in milliseconds/row = 0
ij> -- should use i5
select min(c2) from tab1 where c1 = 34;
1     
------
66    
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- should use i5
select min(c2) from tab1 where c1 = 34
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 = 1
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:
	Scalar Aggregate ResultSet:
	Number of opens = 1
	Rows input = 3
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
	Index Key Optimization = false
	Source result set:
		Project-Restrict ResultSet (3):
		Number of opens = 1
		Rows seen = 3
		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 TAB1 using index I5 at read committed isolation level using instantaneous share row locking chosen by the optimizer
			Number of opens = 1
			Rows seen = 3
			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=3
				Number of rows visited=4
				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> -- should use i5
select max(c2) from tab1 where c1 = 34;
1     
------
68    
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- should use i5
select max(c2) from tab1 where c1 = 34
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 = 1
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:
	Scalar Aggregate ResultSet:
	Number of opens = 1
	Rows input = 1
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
	Index Key Optimization = true
	Source result set:
		Project-Restrict ResultSet (3):
		Number of opens = 1
		Rows seen = 1
		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 TAB1 using index I5 at read committed 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={0, 1}
				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
ij> -- test if bulk insert rebuilds desc index right
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB1', 0);
0 rows inserted/updated/deleted
ij> select * from tab1 order by c1 desc;
C1         |C2    |C3                      |C4                            |C5                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
100        |11    |9.0                     |34sfg                         |ayupo                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
76         |2     |-9.86                   |yudf                          |45gd                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
56         |-3    |6.7                     |dfgs                          |fds                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
55         |44    |-9.85                   |yudd                          |df89f                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
34         |66    |1.2                     |yty                           |wer                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
34         |68    |2.7                     |srg                           |iur                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
34         |67    |5.3                     |rtgd                          |hds                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
8          |12    |5.6                     |dfg                           |ghji                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
-78        |45    |-5.6                    |jakdsfh                       |df89g                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
-100       |93    |9.1                     |egfh                          |s6j                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             
ij> -- this tests multiple indexes share one conglomerate if they essentially
-- are the same
create table tab2 (c1 int not null primary key, c2 int, c3 int);
0 rows inserted/updated/deleted
ij> -- not unique index, shouldn't share with primary key's index
create index i21 on tab2(c1);
0 rows inserted/updated/deleted
WARNING 01504: 'I21' index not created because it is a duplicate of an existing index: 'xxxxGENERATED-IDxxxx'.
ij> -- desc index, shouldn't share with primary key's index
create index i22 on tab2(c1 desc);
0 rows inserted/updated/deleted
ij> -- this should share with primary key's index, and give a warning
create unique index i23 on tab2(c1);
0 rows inserted/updated/deleted
WARNING 01504: 'I23' index not created because it is a duplicate of an existing index: 'xxxxGENERATED-IDxxxx'.
ij> create index i24 on tab2(c1, c3 desc);
0 rows inserted/updated/deleted
ij> -- this should share with i24's conglomerate
create index i25 on tab2(c1, c3 desc);
0 rows inserted/updated/deleted
WARNING 01504: 'I25' index not created because it is a duplicate of an existing index: 'I24'.
ij> -- no share
create index i26 on tab2(c1, c3);
0 rows inserted/updated/deleted
ij> insert into tab2 values (6, 2, 8), (2, 8, 5), (28, 5, 9), (3, 12, 543);
4 rows inserted/updated/deleted
ij> create index i27 on tab2 (c1, c2 desc, c3);
0 rows inserted/updated/deleted
ij> -- no share
create index i28 on tab2 (c1, c2 desc, c3 desc);
0 rows inserted/updated/deleted
ij> -- share with i27
create index i29 on tab2 (c1, c2 desc, c3);
0 rows inserted/updated/deleted
WARNING 01504: 'I29' index not created because it is a duplicate of an existing index: 'I27'.
ij> create index i20 on tab2 (c1, c2 desc, c3);
0 rows inserted/updated/deleted
WARNING 01504: 'I20' index not created because it is a duplicate of an existing index: 'I27'.
ij> insert into tab2 values (56, 2, 7), (31, 5, 7), (-12, 5, 2);
3 rows inserted/updated/deleted
ij> select count(distinct conglomeratenumber) from sys.sysconglomerates
	where tableid = (select tableid from sys.systables
						where tablename = 'TAB2');
1          
-----------
7          
ij> select * from tab2;
C1         |C2         |C3         
-----------------------------------
-12        |5          |2          
2          |8          |5          
3          |12         |543        
6          |2          |8          
28         |5          |9          
31         |5          |7          
56         |2          |7          
ij> values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');
1          
-----------
1          
ij> -- see if rebuild indexes correctly
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB2', 0);
0 rows inserted/updated/deleted
ij> select count(distinct conglomeratenumber) from sys.sysconglomerates
	where tableid = (select tableid from sys.systables
						where tablename = 'TAB2');
1          
-----------
7          
ij> select * from tab2;
C1         |C2         |C3         
-----------------------------------
-12        |5          |2          
2          |8          |5          
3          |12         |543        
6          |2          |8          
28         |5          |9          
31         |5          |7          
56         |2          |7          
ij> update tab2 set c2 = 11 where c3 = 7;
2 rows inserted/updated/deleted
ij> select * from tab2;
C1         |C2         |C3         
-----------------------------------
-12        |5          |2          
2          |8          |5          
3          |12         |543        
6          |2          |8          
28         |5          |9          
31         |11         |7          
56         |11         |7          
ij> delete from tab2 where c2 > 10 and c2 < 12;
2 rows inserted/updated/deleted
ij> select * from tab2;
C1         |C2         |C3         
-----------------------------------
-12        |5          |2          
2          |8          |5          
3          |12         |543        
6          |2          |8          
28         |5          |9          
ij> values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');
1          
-----------
1          
ij> -- drop indexes
drop index i22;
0 rows inserted/updated/deleted
ij> drop index i24;
0 rows inserted/updated/deleted
ij> drop index i26;
0 rows inserted/updated/deleted
ij> drop index i28;
0 rows inserted/updated/deleted
ij> drop index i20;
ERROR 42X65: Index 'I20' does not exist.
ij> select count(distinct conglomeratenumber) from sys.sysconglomerates
	where tableid = (select tableid from sys.systables
						where tablename = 'TAB2');
1          
-----------
3          
ij> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'TAB2', 0);
0 rows inserted/updated/deleted
ij> select * from tab2;
C1         |C2         |C3         
-----------------------------------
-12        |5          |2          
2          |8          |5          
3          |12         |543        
6          |2          |8          
28         |5          |9          
ij> values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');
1          
-----------
1          
ij> drop index i21;
ERROR 42X65: Index 'I21' does not exist.
ij> drop index i23;
ERROR 42X65: Index 'I23' does not exist.
ij> drop index i25;
ERROR 42X65: Index 'I25' does not exist.
ij> drop index i27;
0 rows inserted/updated/deleted
ij> drop index i29;
ERROR 42X65: Index 'I29' does not exist.
ij> select count(distinct conglomeratenumber) from sys.sysconglomerates
	where tableid = (select tableid from sys.systables
						where tablename = 'TAB2');
1          
-----------
2          
ij> values SYSCS_UTIL.SYSCS_CHECK_TABLE('APP', 'TAB2');
1          
-----------
1          
ij> -- beetle 4974
create table b4974 (a BIGINT, b BIGINT, c INT, d CHAR(16), e BIGINT);
0 rows inserted/updated/deleted
ij> create index i4974 on b4974(a, d, c, e);
0 rows inserted/updated/deleted
ij> SELECT b from b4974 t1
where (T1.a = 10127 or T1.a = 0)
	and (T1.d = 'ProductBean' or T1.d = 'CatalogEntryBean')
	and (T1.e =0 or T1.e = 0);
B                   
--------------------
ij> rollback;
ij>