SQL/MX 3.x Query Guide (H06.22+, J06.11+)

Compiling and Executing a Query
HP NonStop SQL/MX Query Guide—640323-001
1-14
Improving Query Performance
Order of Evaluation of Predicates
The SQL/MX compiler can select any order for evaluating the predicates based on the
factors such as cost, cardinality, join type, access path, and so on. As a result,
SQL/MX might not be able to retain the order in which the predicates appear in a query
while executing them. SQL/MX might return different results depending on the order of
evaluation. The following example explains how the order of evaluation of predicates
can affect the result set.
>>CREATE TABLE T_DIVBYZERO (A INT, B INT);
--- SQL operation complete.
>>INSERT INTO T_DIVBYZERO VALUES (1,1),(0,0),(2,2);
--- 3 row(s) inserted.
>>SELECT * FROM T_DIVBYZERO WHERE A <> 0 AND 10/B > 1;
A B
----------- -----------
1 1
2 2
--- 2 row(s) selected.
>>SELECT * FROM T_DIVBYZERO WHERE 10/B > 1 AND A <> 0;
A B
----------- -----------
1 1
*** ERROR[8419] An arithmetic expression attempted a
division by zero.
--- 1 row(s) selected.
>>
You might notice similar behavior with complex queries, when SQL/MX selects other
order for evaluating the predicates than the given order. Another example is given
below.
>>CREATE TABLE STAFF_UC (
EMPNUM CHAR (3) CHARACTER SET UCS2 NOT NULL UNIQUE,
EMPNAME NCHAR VARYING (20));
--- SQL operation complete.
>>
>>CREATE TABLE WORKS_UC (
EMPNUM CHAR (3) CHARACTER SET UCS2 NOT NULL,
PNUM CHAR (3) CHARACTER SET UCS2 NOT NULL);
--- SQL operation complete.
>>
>>CREATE TABLE PROJ_UC (
PNUM CHAR (3) CHARACTER SET UCS2 NOT NULL UNIQUE,
CITY VARCHAR (15) CHARACTER SET UCS2);
--- SQL operation complete.
>>