| ij(CONNECTION1)> -- |
| -- 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. |
| -- |
| autocommit off; |
| ij(CONNECTION1)> create table foo (a int, b char(250), c char(250), d int); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (1, '1', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (11, '11', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (12, '12', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (13, '13', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (14, '14', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (15, '15', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (16, '16', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (17, '17', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (18, '18', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (11, '111', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (12, '121', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (13, '131', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (14, '141', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (15, '151', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (16, '161', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (17, '171', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (18, '181', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (2, '2', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (3, '3', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (4, '4', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (5, '5', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (6, '6', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (7, '7', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (8, '8', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (9, '9', '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> create index foox on foo (b); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> -- normal max optimization, last row in index is not deleted. |
| select max(b) from foo; |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| 9 & |
| ij(CONNECTION1)> -- new max optimization, last row in index is deleted but others on page aren't. |
| delete from foo where a = 9; |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> select max(b) from foo; |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| 8 & |
| ij(CONNECTION1)> -- new max optimization, last row in index is deleted but others on page aren't. |
| delete from foo where a = 8; |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> select max(b) from foo; |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| 7 & |
| ij(CONNECTION1)> -- new max optimization, last row in index is null, real max on last page. |
| insert into foo values (9, null, '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> select max(b) from foo; |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| 7 & |
| ij(CONNECTION1)> -- new max optimization, last is null and deleted, real max on last page. |
| delete from foo where a > 2; |
| 22 rows inserted/updated/deleted |
| ij(CONNECTION1)> select max(b) from foo; |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| 2 & |
| ij(CONNECTION1)> -- max optimization does not work - fail over to scan, all rows on last page are |
| -- deleted, except for non-deleted null row on last page. max row on 1st page. |
| delete from foo where a > 1; |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into foo values (9, null, '1', 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> select max(b) from foo; |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| 1 & |
| ij(CONNECTION1)> -- max optimization does not work - fail over to scan, all rows on last page are |
| -- deleted. non-deleted null row on last page. max row is on 1st page. |
| delete from foo where a > 1; |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> select max(b) from foo; |
| 1 |
| -------------------------------------------------------------------------------------------------------------------------------- |
| 1 & |
| ij(CONNECTION1)> create table b5772 (a int, b int); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> create index b1 on b5772(b); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> -- 0 row case |
| select max(b) from b5772; |
| 1 |
| ----------- |
| NULL |
| ij(CONNECTION1)> select min(b) from b5772; |
| 1 |
| ----------- |
| NULL |
| ij(CONNECTION1)> -- 1 row case |
| insert into b5772 values (1, 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> select max(b) from b5772; |
| 1 |
| ----------- |
| 1 |
| ij(CONNECTION1)> select min(b) from b5772; |
| 1 |
| ----------- |
| 1 |
| ij(CONNECTION1)> -- 1 null row case |
| drop table b5772; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> create table b5772 (a int, b int); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> create index b1 on b5772(b); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> insert into b5772 values (2, null); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> select max(b) from b5772; |
| 1 |
| ----------- |
| NULL |
| ij(CONNECTION1)> select min(b) from b5772; |
| 1 |
| ----------- |
| NULL |
| WARNING 01003: Null values were eliminated from the argument of a column function. |
| ij(CONNECTION1)> -- 1 row plus, one null row. |
| insert into b5772 values (1, 1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> -- cleanup |
| drop table b5772; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> drop table foo; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> |
