|
Problem IC63966
|
Status: Closed |
OR PREDICATE WITH SUBTERMS BEING QUANTIFIED = PREDICATE OR SINGLE COLUMN IN SUBQUERY PREDICATE MAY CAUSE A SQL1224 ERROR |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Certain queries can result in a SQL1224 error or instance
shutdown when the query contains an OR predicate with multiple
subquery operations. In particular the query must contain the
following constructs:
1) An OR predicate is present in the WHERE or ON clause of any
sub-select within the query.
2) At least one operand of the OR is a quantified = predicate
using the ANY/SOME qualifier or a single column IN subquery
predicate such as '<expression> IN <subquery>', '<expression> =
ANY(<subquery>)', or '<expression> = SOME(<subquery>)'.
3) The <expression> referenced as left hand operand of the
quantified = predicate or IN subquery predicate from condition
(2) is a complex expression containing a quantified, IN subquery
or EXISTS subquery predicate.
Two example queries are show below with an explanation of what
parts of the query can be used to identify this issue.
Example 1
1. SELECT *
2. FROM A
3. WHERE a.C1 = 1
4. OR
5. CASE
6. WHEN A.C1 IN
7. (SELECT DISTINCT B.C1
8. FROM B
9. )
10. THEN A.C2
11. ELSE 2
12. END
13. IN
14. (SELECT DISTINCT C.C1
15. FROM C
16. );
Lines 3-16 define an OR predicate in the WHERE clause satisfying
condition 1. Lines 13-16 are a single column IN subquery
predicate within the OR predicate satisfying condition 2. Lines
5 through 11 are a complex expression with an IN subquery
predicate satisfying condition 3.
Example 2
1. SELECT *
2. FROM A
3. WHERE a.C1 = 1
4. OR
5. CASE
6. WHEN EXISTS
7. (SELECT 1
8. FROM B
9. WHERE A.c1=b.c1
10. )
11. THEN A.C2
12. ELSE 2
13. END
14. = ANY
15. (SELECT DISTINCT C.C1
16. FROM C
17. );
Lines 3-17 define an OR predicate in the WHERE clause satisfying
condition 1. Lines 14-17 show a quantified = predicate within
the OR predicate satisfying condition 2. Lines 5-12 are a
complex expression with an EXISTS subquery predicate satisfying
condition 3. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* Certain queries can result in a SQL1224 error or instance *
* *
* *
* *
* shutdown when the query contains an OR predicate with *
* *
* multiple *
* *
* subquery operations. In particular the query must contain *
* *
* the *
* *
* following constructs: *
* *
* 1) An OR predicate is present in the WHERE or ON clause of *
* *
* any *
* *
* sub-select within the query. *
* *
* 2) At least one operand of the OR is a quantified = *
* *
* predicate *
* *
* using the ANY/SOME qualifier or a single column IN subquery *
* *
* *
* *
* predicate such as '<expression> IN <subquery>', *
* *
* '<expression> = *
* *
* ANY(<subquery>)', or '<expression> = SOME(<subquery>)'. *
* *
* *
* *
* 3) The <expression> referenced as left hand operand of the *
* *
* *
* *
* quantified = predicate or IN subquery predicate from *
* *
* condition *
* *
* (2) is a complex expression containing a quantified, IN *
* *
* subqueryor EXISTS subquery predicate. *
* *
* *
* *
* Two example queries are show below with an explanation *
* *
* of *
* *
* what parts of the query can be used to identify this issue. *
* *
* *
* *
* Example 1 *
* *
* *
* *
* 1. SELECT * *
* *
* 2. FROM A *
* *
* 3. WHERE a.C1 = 1 *
* *
* 4. OR *
* *
* 5. CASE *
* *
* 6. WHEN A.C1 IN *
* *
* 7. (SELECT DISTINCT B.C1 *
* *
* 8. FROM B *
* *
* 9. ) *
* *
* 10. THEN A.C2 *
* *
* 11. ELSE 2 *
* *
* 12. END *
* *
* 13. IN *
* *
* 14. (SELECT DISTINCT C.C1 *
* *
* 15. FROM C *
* *
* 16. ); *
* *
* *
* *
* Lines 3-16 define an OR predicate in the WHERE clause *
* *
* satisfyingcondition 1. Lines 13-16 are a single column IN *
* *
* subquery predicate within the OR predicate satisfying *
* *
* condition 2. Lines 5 through 11 are a complex expression *
* *
* with an *
* *
* IN subquery predicate satisfying condition 3. *
* *
* *
* *
* Example 2 *
* *
* *
* *
* 1. SELECT * *
* *
* 2. FROM A *
* *
* 3. WHERE a.C1 = 1 *
* *
* 4. OR *
* *
* 5. CASE *
* *
* 6. WHEN EXISTS *
* *
* 7. (SELECT 1 *
* *
* 8. FROM B *
* *
* 9. WHERE A.c1=b.c1 *
* *
* 10. ) *
* *
* 11. THEN A.C2 *
* *
* 12. ELSE 2 *
* *
* 13. END *
* *
* 14. = ANY *
* *
* 15. (SELECT DISTINCT C.C1 *
* *
* 16. FROM C *
* *
* 17. ); *
* *
* *
* *
* *
* *
* Lines 3-17 define an OR predicate in the WHERE clause *
* *
* satisfyingcondition 1. Lines 14-17 show a quantified = *
* *
* predicate *
* *
* within the OR predicate satisfying condition 2. Lines 5-12 *
* *
* are *
* *
* a complex expression with an EXISTS subquery predicate *
* *
* satisfying condition 3. *
****************************************************************
* RECOMMENDATION: *
* 1) Convert the single column IN subquery predicate into a *
* *
* *
* *
* multiple column IN subquery predicate. As in example 1 above *
* *
* we *
* *
* add lines 5, 14 and 17 shown below *
* *
* *
* *
* 1. SELECT * *
* *
* 2. FROM A *
* *
* 3. WHERE a.C1 = 1 *
* *
* 4. OR *
* *
* 5. ( *
* *
* 6. CASE *
* *
* 7. WHEN A.C1 IN *
* *
* 8. (SELECT DISTINCT B.C1 *
* *
* 9. FROM B *
* *
* 10. ) *
* *
* 11. THEN A.C2 *
* *
* 12. ELSE 2 *
* *
* 13. END *
* *
* 14. ,1) *
* *
* 15. IN *
* *
* 16. (SELECT DISTINCT C.C1 *
* *
* 17. ,1 *
* *
* 18. FROM C *
* *
* 19. ); *
* *
* 2) *
* *
* For a quantified = predicate using the ANY or SOME *
* *
* quantification this is a synonym for a single column IN *
* *
* *
* *
* subquery. Convert the quantified = predicate to a *
* *
* multicolumn *
* *
* INsubquery predicate as shown above. *
**************************************************************** |
| Local Fix: |
1) Convert the single column IN subquery predicate into a
multiple column IN subquery predicate. As in example 1 above we
add lines 5, 14 and 17 shown below
1. SELECT *
2. FROM A
3. WHERE a.C1 = 1
4. OR
5. (
6. CASE
7. WHEN A.C1 IN
8. (SELECT DISTINCT B.C1
9. FROM B
10. )
11. THEN A.C2
12. ELSE 2
13. END
14. ,1)
15. IN
16. (SELECT DISTINCT C.C1
17. ,1
18. FROM C
19. );
2) For a quantified = predicate using the ANY or SOME
quantification this is a synonym for a single column IN
subquery. Convert the quantified = predicate to a multicolumn IN
subquery predicate as shown above. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 1 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in Version 9.7 Fix Pack 1 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 22.10.2009
16.12.2009
16.12.2009 |
| Problem solved at the following versions (IBM BugInfos) |
9.0.,
9.7.FP1
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.1
|
 |
