Syntactical problems are easy to catch, you get an error. But what if your conditions are false, you don’t get an error but the results aren’t what were expected. When creating a program or modifying it, part of the testing is to ensure that the returned results are correct so this way we can deal with the initial situation. However, things get more complicated when the world around changes. Let’s take an example.

The following script creates an OrderItem table. It’s not a gem what comes to database design but sufficient to pinpoint the problem we’re discussing of:

CREATE TABLE OrderItem (

OrderNumber int,

Product varchar(100),

RowType int CONSTRAINT ck_orderitem_rowtype

CHECK (RowType IN (1,2)),

-- 1=Included, 2=Excluded

Amount int,

ListPrice decimal

);

Product is the name of the item to be sold, Amount defines the quantity of items to be purchased and ListPrice is the price of a single item. RowType defines if this item is included in the order (value is one) or was it selected but then removed from the order (value of two).

Now let’s add some data

INSERT INTO OrderItem

(OrderNumber, Product, RowType, Amount, ListPrice)

VALUES (1, 'Squared tyre', 1, 4, 100.0);

INSERT INTO OrderItem

(OrderNumber, Product, RowType, Amount, ListPrice)

VALUES (1, 'Oil scent, 100 oz', 2, 1, 50.0);

INSERT INTO OrderItem

(OrderNumber, Product, RowType, Amount, ListPrice)

VALUES (1, 'Haircut', 1, 1, 20.0);

Now let’s imagine that the application fetches a list of items in the order. In order to show the prices correctly, the SELECT statement needs to take the RowType into account, something like the following:

SELECT CASE oi.RowType

WHEN 1 THEN 'Included into order'

WHEN 2 THEN 'Excluded from order'

END As Status,

oi.Product,

oi.Amount,

CASE oi.RowType

WHEN 1 THEN oi.Amount * oi.ListPrice

-- Calculate the price

WHEN 2 THEN 0 -- Excluded so no price

END As Price

FROM OrderItem oi

WHERE oi.OrderNumber = 1

ORDER BY oi.RowType,

oi.Product;

The results would be

Status Product Amount Price

------------------- ----------------- ------ -----

Included into order Haircut 1 20

Included into order Squared tyre 4 400

Excluded from order Oil scent, 100 oz 1 0

Also the application could fetch the items to be delivered, with the following query:

SELECT oi.Product,

oi.Amount,

oi.Amount * oi.ListPrice AS Price

FROM OrderItem oi

WHERE oi.OrderNumber = 1

AND oi.RowType = 1

ORDER BY oi.RowType,

oi.Product;

And the results would be

Product Amount Price

------------ ------ -----

Haircut 1 20

Squared tyre 4 400

So everything is perfect, until…

One day an extra row type is defined and added into the database. It was decided that customers buying lot of items were rewarded with a gift included in the purchase, naturally free of charge. The gift is a bunch of flowers which is also sold separately. Because of this a new RowType 3 was introduced to distinguish free gift items from other items.

The following change was made to the database

ALTER TABLE OrderItem

DROP CONSTRAINT ck_orderitem_rowtype;

ALTER TABLE OrderItem

ADD CONSTRAINT ck_orderitem_rowtype

CHECK (RowType IN (1,2,3));

-- 1=Included, 2=Excluded, 3=Gift

The order 1 we used as an example had such a big value that it would get a gift so let’s add it

INSERT INTO OrderItem

(OrderNumber, Product, RowType, Amount, ListPrice)

VALUES (1, 'Bunch of flowers', 3, 1, 5.0);

Now let’s see what happens with our original queries. The first one would return

Status Product Amount Price

------------------- ----------------- ------ -----

Included into order Haircut 1 20

Included into order Squared tyre 4 400

Excluded from order Oil scent, 100 oz 1 0

NULL Bunch of flowers 1 NULL

And the second one

Product Amount Price

------------ ------ -----

Haircut 1 20

Squared tyre 4 400

The first one is more obvious you get NULL values in the result set in places where they are not expected so if someone looks carefully this may be noticed.

The second one is more dangerous, since the WHERE clause restricted the rows only to known RowTypes the gift isn’t included in the returned set of rows. Just by looking at the data nothing looks suspicious so you need to know the data behind in order to spot the problem.

If you think about other languages they have different kinds of mechanisms to handle known and unknown values. For example a switch structure has a default block that is fired for all values not listed in case statements. This same idea can be applied to some extent to queries. However, in order to ensure that false data doesn’t pass through we won’t do any default actions but deliberately generate errors.

Consider the following statement. It has a small modification in the CASE structure. RowTypes one and two are handled correctly but if an unknown RowType is spotted the ELSE branch is executed and a division by zero error is thrown.

SELECT CASE oi.RowType

WHEN 1 THEN 'Included into order'

WHEN 2 THEN 'Excluded from order'

END As Status,

oi.Product,

oi.Amount,

CASE oi.RowType

WHEN 1 THEN oi.Amount * oi.ListPrice

WHEN 2 THEN 0

ELSE 0/0

END As Price

FROM OrderItem oi

WHERE oi.OrderNumber = 1

ORDER BY oi.RowType,

oi.Product;

When the statement is run you get a message like following

Msg 8134, Level 16, State 1, Line 86

Divide by zero error encountered.

That would prevent a situation where an unknown RowType could cause false data to be returned. But what about the second one, it didn’t have any CASE structure in the SELECT. And even if a similar CASE would be added to the statement it wouldn’t work since the WHERE clause takes care that no other values but 1 is returned.

Well, why not add the CASE into the WHERE clause:

SELECT oi.Product,

oi.Amount,

oi.Amount * oi.ListPrice AS Price

FROM OrderItem oi

WHERE oi.OrderNumber = 1

AND 1 = CASE oi.RowType

WHEN 1 THEN 1

WHEN 2 THEN 0

ELSE 0/0

END

ORDER BY oi.RowType,

oi.Product;

When the statement above is run, again, a division by zero error is returned.

So after adding the new RowType and fixing the statements the final versions could look something like these:

SELECT CASE oi.RowType

WHEN 1 THEN 'Included into order'

WHEN 2 THEN 'Excluded from order'

WHEN 3 THEN 'Gift'

END As Status,

oi.Product,

oi.Amount,

CASE oi.RowType

WHEN 1 THEN oi.Amount * oi.ListPrice

WHEN 2 THEN 0

WHEN 3 THEN 0

ELSE 0/0

END As Price

FROM OrderItem oi

WHERE oi.OrderNumber = 1

ORDER BY oi.RowType,

oi.Product;

And

SELECT oi.Product,

oi.Amount,

CASE oi.RowType

WHEN 1 THEN oi.Amount * oi.ListPrice

WHEN 3 THEN 0

ELSE 0/0

END AS Price

FROM OrderItem oi

WHERE oi.OrderNumber = 1

AND 1 = CASE oi.RowType

WHEN 1 THEN 1

WHEN 2 THEN 0

WHEN 3 THEN 1

ELSE 0/0

END

ORDER BY oi.RowType,

oi.Product;

Next time a new RowType is added both of the statements will react by causing and error and will hopefully get caught already in testing.

So is this the way to always write SELECT statements? No, it isn’t. The modification in the SELECT clause is quite safe and can be used more widely but the second one, a CASE in the WHERE clause is something to be careful with. Complex structures in conditions may lead into a situation where perfectly valid indexes aren’t used because of an expression. So it’s important to always check how the statement is behaving by investigating the execution plan.

Also it’s not feasible to use this kind of structure in every place. But in critical statements it may be justified to add deliberate errors in case of unknown values.

## Ei kommentteja:

## Lähetä kommentti