WRONG RESULT with range list partitioning
Hi everybody,
Could some one explain me why these 2 queries return a different result? I'm working on 9iR2 (9.2.0.5) so I would be glade if somebody on 10g could execute the script to check if on 10g the result is also wrong.
Thanks
Mike
PHP Code:
DROP TABLE test_range_list_part;
CREATE TABLE test_range_list_part (
part_range_key NUMBER,
subpart_list_key number,
value number)
PARTITION BY RANGE ( part_range_key )
SUBPARTITION BY LIST ( subpart_list_key )
SUBPARTITION TEMPLATE
(SUBPARTITION sp1 VALUES
(1,3),
SUBPARTITION sp2 VALUES
(2,4),
SUBPARTITION other VALUES (DEFAULT))
(PARTITION p1 VALUES LESS THAN (10),
PARTITION p2 VALUES LESS THAN (20),
PARTITION p3 VALUES LESS THAN (30));
CREATE UNIQUE INDEX pk_test_range_list_part
ON test_range_list_part(part_range_key, subpart_list_key)
LOCAL;
INSERT INTO test_range_list_part VALUES (1,1,1);
INSERT INTO test_range_list_part VALUES (1,2,1);
INSERT INTO test_range_list_part VALUES (1,3,1);
INSERT INTO test_range_list_part VALUES (1,4,1);
INSERT INTO test_range_list_part VALUES (1,11,1);
INSERT INTO test_range_list_part VALUES (1,12,1);
COMMIT;
After this I execute the following 2 queries which should return the same result:
PHP Code:
SELECT
value, subpart_list_key, COUNT(*)
FROM test_range_list_part PARTITION (p1)
WHERE
(
( subpart_list_key =2 and value = 1)
OR
( subpart_list_key = 1 )
)
AND value = 1
GROUP BY value, subpart_list_key;
SELECT
value, subpart_list_key, COUNT(*)
FROM test_range_list_part PARTITION (p1)
WHERE
(
( subpart_list_key =2 ) -- REMOVED THE AND CONDITION WHICH SHOULD HAVE NO EFFECT ON THE RESULT
OR
( subpart_list_key = 1 )
)
AND value = 1
GROUP BY value, subpart_list_key;
AND I GET !?!
PHP Code:
SQL> SELECT
2 value, subpart_list_key, COUNT(*)
3 FROM test_range_list_part PARTITION (p1)
4 WHERE
5 (
6 (subpart_list_key =2 and value = 1)
7 OR
8 (subpart_list_key = 1 )
9 )
10 AND value = 1
11 GROUP BY value, subpart_list_key;
VALUE SUBPART_LIST_KEY COUNT(*)
---------- ---------------- ----------
1 2 1
SQL> SELECT
2 value, subpart_list_key, COUNT(*)
3 FROM test_range_list_part PARTITION (p1)
4 WHERE
5 (
6 (subpart_list_key =2 )-- REMOVED THE AND CONDITION WHICH SHOULD HAVE NO EFFECT ON THE RESULT
7 OR
8 (subpart_list_key = 1 )
9 )
10 AND value = 1
11 GROUP BY value, subpart_list_key;
VALUE SUBPART_LIST_KEY COUNT(*)
---------- ---------------- ----------
1 1 1
1 2 1
