Major fail of the day!

Toad for Oracle
1

Sigh.

A vendor’s release supplied the following code:

ALTER

ADD CONSTRAINT
CHECK (some_column in (‘Y’,‘N’,NULL));

It looks good, it executes (well, it would if I let it) without error
but it is severely broken.

Without cheating and trying it out, answer the following questions:

  1. Is the value ‘Y’ allowed?
  2. Is the value ‘N’ allowed?
  3. Is the non-value NULL allowed?
  4. Is the value ‘X’ allowed?
  5. How about single characters in lower case, are they allowed?

Explain your answers!

No prizes I’m afraid, just a heads up, warning, whatever. You have to
keep an eye on your vendors!

–

Cheers,
Norm. [TeamT]

2

Seriously?

3

Without cheating

You mean… like refreshing oneself on the structure and use of constraints
:wink:

Answer to all: Depends on what your specifications have defined. On top of that
(since this is third-party supplied) it depends on what your users have been
allowed to previously enter.

If the end-users have been allowed to enter lower case values – as just
one example – then said vendor would hopefully have a data correction sql
in place before they add the constraint.

Of course… I may be viewing your question of the day from a different
perspective so could be way out to lunch with regards the questions you meant to
ask :slight_smile:

Roger S.

4

All I know for sure is that you whipped up a nice polite email with
examples and sent that off to the vendor. :slight_smile:

NULLs don't play nice with real values.

Ed
[TeamT]

On 11/2/2010 12:42 PM, Norman Dunbar wrote:

Sigh.

A vendor's release supplied the following code:

ALTER

ADD CONSTRAINT

CHECK (some_column in ('Y','N',NULL));

It looks good, it executes (well, it would if I let it) without error
but it is severely broken.

Without cheating and trying it out, answer the following questions:

  1. Is the value 'Y' allowed?
  2. Is the value 'N' allowed?
  3. Is the non-value NULL allowed?
  4. Is the value 'X' allowed?
  5. How about single characters in lower case, are they allowed?

Explain your answers!

No prizes I'm afraid, just a heads up, warning, whatever. You have to
keep an eye on your vendors!

5

Evening Jeff,

Seriously?
What?

Seriously a vendor released the code I showed an example of? Oh yes. The
vendor in question shall remain nameless, but they are not an Oracle
shop, the application is "database independent" and the code quality is
below that I would expect from a 3 year old. (3 Year old dog that is!)

Other releases have had "error logging" PL/SQL not in an autonomous
transaction so on rollback of the error, goodbye logging messages!

Even worse, in the event of an error in one particular function, the
errors were logged and then the exception handler fired and did a
commit. Yes, a commit after an error! The comment in the exception code
said something like "we need to commit to keep the logged messages from
vanishing." Go figure!

So, yes, I'm serious.

Cheers,
Norm. [TeamExasperated]

6

look for that billable line item in the state contract with the description “woe
and intrigue”
i guess you consider yourself “fortunate” the error was displayed in english

Martin Gainty

Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.

Ce message est confidentiel et peut être privilégié. Si vous n’êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l’expéditeur. N’importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l’information seulement et n’aura pas n’importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

7

[ Attachment(s) from GTDG included below]

Message from: GTDG

[cid:image001.png@01CB7AA4.BEF93760]

Historical Messages

Author: GTDG
Date: Tue Nov 02 13:05:57 PDT 2010
[cid:image001.png@01CB7AA4.BEF93760]
__

Author: Norman Dunbar
Date: Tue Nov 02 12:23:54 PDT 2010
Evening Jeff, > Seriously? What? Seriously a vendor released the code I showed
an example of? Oh yes. The vendor in question shall remain nameless, but they
are not an Oracle shop, the application is “database independent” and the code
quality is below that I would expect from a 3 year old. (3 Year old dog that
is!) Other releases have had “error logging” PL/SQL not in an autonomous
transaction so on rollback of the error, goodbye logging messages! Even worse,
in the event of an error in one particular function, the errors were logged and
then the exception handler fired and did a commit. Yes, a commit after an error!
The comment in the exception code said something like “we need to commit to keep
the logged messages from vanishing.” Go figure! So, yes, I’m serious. Cheers,
Norm. [TeamExasperated]
__

Author: Jeff Smith
Date: Tue Nov 02 09:47:06 PDT 2010
Seriously?
__

Author: Norman Dunbar
Date: Tue Nov 02 09:42:32 PDT 2010
Sigh. A vendor’s release supplied the following code: ALTER ADD CONSTRAINT CHECK
(some_column in (‘Y’,‘N’,NULL)); It looks good, it executes (well, it would if I
let it) without error but it is severely broken. Without cheating and trying it
out, answer the following questions: 1. Is the value ‘Y’ allowed? 2. Is the
value ‘N’ allowed? 3. Is the non-value NULL allowed? 4. Is the value ‘X’
allowed? 5. How about single characters in lower case, are they allowed? Explain
your answers! No prizes I’m afraid, just a heads up, warning, whatever. You have
to keep an eye on your vendors! – Cheers, Norm. [TeamT]
__

Attachment(s) from GTDG

1 of 1 Photo(s)

image001.png

8

Evening GTDG,

Message from: GTDG
[cid:image001.png@01CB7AA4.BEF93760]
Whoa! Spooky! That cat has the facial features of Winston Churchill.

At least, it does to me!

Cheers,
Norm. [TeamT]

9

[cid:image001.png@01CB7AA4.BEF93760]
image001.png

10

Hi Norman,

Sorry, I don’t get the problem. Ok, it is not what I would program using
constraints to validate and three way logic, but I got the impression that you
see very big problems coming, but why escapes me a little bit.

Groetjes,
Wim

On Tue, Nov 2, 2010 at 17:42, Norman Dunbar wrote:

Sigh.

A vendor's release supplied the following code:

ALTER

ADD CONSTRAINT
CHECK (some_column in (‘Y’,‘N’,NULL));

It looks good, it executes (well, it would if I let it) without error
but it is severely broken.

Without cheating and trying it out, answer the following questions:

1. Is the value 'Y' allowed?
2. Is the value 'N' allowed?
3. Is the non-value NULL allowed?
4. Is the value 'X' allowed?
5. How about single characters in lower case, are they allowed?

Explain your answers!

No prizes I'm afraid, just a heads up, warning, whatever. You have to
keep an eye on your vendors!

--

Cheers,
Norm. [TeamT]
11

Morning all,

Hmmmm, no-one answered the questions! :slight_smile:

As Ed noted, NULL doesn't play nice with values, other than the empty
string of course '' - but that's one of those "three foot tall judges"
of the Oracle world. (Little things, sent to try us!). All you can do
with a NULL is "IS NOT NULL" or "IS NULL" - that is it, no more, nada,
nothing at all. Don't even think about it! :wink:

Here we go:

ALTER

ADD CONSTRAINT

CHECK (some_column in ('Y','N',NULL));

  1. Is the value 'Y' allowed?
    Yes.

  2. Is the value 'N' allowed?
    Yes.

  3. Is the non-value NULL allowed?
    Yes.

  4. Is the value 'X' allowed?
    Yes.

  5. How about single characters in lower case, are they allowed?
    Yes.

And a bonus (unasked question):
6. What is rejected by the constraint?
NOTHING AT ALL.

Explain your answers!
Ok, here's the deal. When an invalid constraint expression like the
above is executed, the constraint value appears to be NULL. That is not
a true or false (as in "can I put this value in here?") so the
constraint doesn't fail and the value in question is allowed through.
Example:

(Sorry Bert, I'm using SQL*Plus here!!!)

SQL> create table oops(a varchar2(1));
Table created.

SQL> alter table oops add constraint major_fail
2 check (a in ('Y','N', NULL));
Table altered.

SQL> insert into oops values (null);
1 row created.

SQL> insert into oops values ('Y');
1 row created.

SQL> insert into oops values ('N');
1 row created.

SQL> insert into oops values ('X');
1 row created.

SQL> insert into oops values ('a');
1 row created.

SQL> select a, case when a in ('Y','N') then 'DATA OK' else 'DATA
CORRUPT!' end as result
2 from oops
3 where a is not null;

A RESULT

12

Morning Martin,

i guess you consider yourself "fortunate" the error was
displayed in english
The error was caught by me doing a QA on the code before I let it near
our test systems. This is a system I introduced many years ago to try
and stop problems happening on the databases before they become real
problems. Reviewing all scripts in a release takes time, but we have
caught a number of potentially serious problems.

This release won't be applied.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

13

Hmmmm, no-one answered the questions! :slight_smile:

That's because it's easier to appear smart by expressing shock than it is to admit not knowing the precise details. : )

14

Hi Mark,

Hmmmm, no-one answered the questions! :slight_smile:
That's because it's easier to appear smart by expressing
shock than it is to admit not knowing the precise details. : )

Good plan! I'll remember that.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

15

It’s a lot of hard work to appear smart with this group!

16

NULLs don't play nice with real values

sigh I’m way too focused on other issues and never even caught that. Of
course, with the statement the obviousness hits me like a wet noodle.

Roger S.