A fascinating SQL problem

Can you solve this problem in SQL – i.e. without resorting to a procedural solution?

SQL combine multiple identifiers to create a group id for duplicate records

“I have a data extract with three different identifiers: A, B, C
Each identifier may appear in more than one row, and each row may have one or more of these three identifiers (i.e the column is populated or null).
I want to be able to group all records that have any combination of either A, B or C in common and assign them the same group id.
Extract table showing what the eventual groups should be:

A    | B    | C    | Group
====   ====   ====   =====
p      NULL   NULL   1
p      r      NULL   1
q      NULL   NULL   2
NULL   r      NULL   1
NULL   NULL   s      2
q      NULL   s      2

So, the input data is a table with three columns (A, B, and C), some of which are NULL. The output is a third column, “Group”, which will be assigned a number which classifies the row into a “group”. Each group will be distinct in that none of its members will have a value in A, B or C that appears in any row in any other group.

This question is fascinating because it cannot be solved, I believe, without some form of iteration. If I get the row (p) along with (q), they are in two different groups; but, if I add the row (p,q), all of a sudden my original rows are now in the same group along with the new row.

The solution will probably have to examine each row in consideration with the entire record set – an operation of O(n^2), if my understanding of CS theory is correct. I suspect a solution using at least a CTE and/or the MODEL clause will be required.

UPDATE:
An elegant solution, using a hierarchical query and Oracle’s CONNECT_BY_ROOT function, has been posted by Vincent Malgrat.


Negative, Captain

Seen in the wild:

... WHERE substr(amount,0, 1) != '-'

If you wanted to query a table of monetary transactions for any refunds (i.e. where the transaction amount is negative), how would you do it? Perhaps you’d think about avoiding problems that might occur if the default number format were to change, hm?

(before you say it: no, there is no index on amount, so it wasn’t a misguided attempt to avoid an index access path…)


If at first you don’t succeed… it’s impossible.

How many times have you tried something, got either an error or unexpected results, and decided what you were trying to do was not possible? Have you later on discovered someone quietly doing the impossible?

I think this phenomenon is a form of the “correlation-implies-causation” fallacy.

Unfortunately, this seems to happen too often, if the kind of questions I see quite often are any guide. A recent example is: “Why cannot I select from more than one table in Oracle?”. Here, the author seems to have followed the following thought process:

  1. “SELECT * FROM table1” returns some rows.
  2. “SELECT * FROM table1, table2” returns no rows.
  3. Therefore, you can’t query more than one table in one SQL statement in Oracle.

In this case, the writer had not realised that table2 had no rows in it; what complicated things somewhat was that in one session, the second query was returning rows – because he’d inserted some rows into table2 in that session but hadn’t issued a COMMIT, so those rows were not visible by other sessions.

For a person inexperienced in SQL or Oracle, this sort of mistake is forgivable; but I suspect we all make this sort of mistake quite often. I know I have!

When trying something new, it takes diligent research and testing to determine whether one’s approach is simply wrong, or if unrelated factors (e.g. getting the syntax wrong, or the environment is not set up correctly) are causing failure. This gets more tiresome and frustrating (a “gumption trap”, in Persig‘s parlance) when one was halfway through solving some other problem, and this unexpected problem gets in the way.

Sometimes you just have to go to bed and see if it becomes clearer the next day. If the problem persists, ask a question on StackOverflow!

P.S. if a Google search reveals “doing X is impossible”, ask “Why?”



My favourite 11g thing

For too long I’ve muddled my way through using the built-in “explain plan” feature of the IDE-that-shall-remain-nameless provided by the client; but now that they’ve successfully upgraded from 9i to 11g, I’m now in a much better place. No longer will I wonder which table each step in a plan refers to, or what filters are being applied where.

DBMS_XPLAN is the best!

--get plan for a query
set pagesize 0 linesize 999 heading off feedback off echo on
explain plan for
SELECT bla FROM mytable WHERE bla;
set echo off
select * from table(dbms_xplan.display(format=>'ALL'));
--get plan for cursor in cache
set pagesize 0 linesize 999 heading off feedback off
select * from table(dbms_xplan.display_cursor(
sql_id=>'fnumywukcm2ng', cursor_child_no=> 0, format=>'ALL'));

ORA-01481 Invalid number format model

My code worked perfectly fine in Oracle 9i, but after our upgrade to 11g all of a sudden it was failing with ORA-01481 “Invalid number format model”.

The cause? A mixed-up number format model – I’d accidentally mixed locale-specific (“.“) and locale-safe (“G“) codes in the number format. I don’t know if this is due to a bug in 9i or an enhancement in 11g.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
- 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
SQL> select to_char(12345.678,'99G990.0') from dual;
select to_char(12345.678,'99G990.0') from dual
*
ERROR at line 1:
ORA-01481: invalid number format model
SQL> select to_char(12345.678,'99,990.0') from dual;
TO_CHAR(1
---------
12,345.7
SQL> select to_char(12345.678,'99G990D0') from dual;
TO_CHAR(1
---------
12,345.7

“Action: Consult the manual… in retrospect, not of much help in this instance, unfortunately – however, these sorts of problems may usually be worked out by experimentation.


Weird SQL Results

Chris Taylor raised a good question.

Here is the interesting part (to me) – Sometimes it returns 1 row, sometimes it returns more than 1 row, and sometimes it returns no rows. To my mind, “trunc(dbms_random.value(1,500)” should return a random value between 1 & 500 every time and just one value.  Is this weird, or is it just me???

Basically he’s wondering why querying a table on a randomly-chosen unique identifier does not always return exactly 1 row.

At first glance it seems nonsensical and looks like a bug, but really, it’s not – in fact, Oracle is doing exactly the right thing (anecdotally, a colleague has told me that SQL Server gets this particular behaviour wrong).

A simple test case: the inner query generates exactly 100 rows, with the numbers 1 to 100. The outer query then queries this result set, with the intention of picking one of those rows only:

select * from (
  select rownum r from dual connect by level <= 100
)
where r = trunc(dbms_random.value(1,100));

However, what happens here is that Oracle knows that dbms_random.value is not deterministic; and the predicates (WHERE clause) must be evaluated once for each row returned from the FROM clause. Since dbms_random.value is not deterministic, Oracle knows it must re-evaluate it for each row, separately – which means it is comparing a different number for each row returned.

So, for example, Oracle looks at row 1, with the value 1. It generates a random number, e.g. 12, and so the predicate evaluates to FALSE, and the row is not returned. Oracle then looks at row 2, which has the value 2. It generates a random number, e.g. 2, and so the predicate evaluates to TRUE, and the row is returned. It does this for each row until the 100th is evaluated and then the query stops.

If none of the 100 random numbers happen to coincide with the values returned from the table, then the query will return no rows.

Quiz question: what is the theoretical maximum number of rows the above query could ever return?

To fix it so that Oracle only evaluates the selection criteria once, we just need to wrap it in another subquery:

select * from (
  select rownum r from dual connect by level <= 100
)
where r = (select trunc(dbms_random.value(1,100)) from dual);

A Good Bad Example

I learnt something new today (thanks to Steven) about TRIM – in the past I’ve used RTRIM and LTRIM if I only want to trim from the left or right ends of a string, but I was not aware (or had forgotten) that SQL’s TRIM function allows this to be specified.

Oracle’s documentation of TRIM, however, has a somewhat confusing example:

“This example trims leading zeros from the hire date of the employees in the hr schema:

SELECT employee_id,
TO_CHAR(TRIM(LEADING 0 FROM hire_date))
FROM employees
WHERE department_id = 60
ORDER BY employee_id;

EMPLOYEE_ID TO_CHAR(T
----------- ---------
103 3-JAN-90
104 21-MAY-91
105 25-JUN-97
106 5-FEB-98
107 7-FEB-99

As an example of using the TRIM function the above code is adequate; however, why does it call the TO_CHAR function at that point? After all, TO_CHAR is only useful if the datatype of its argument is not a character string – but in this case, it’s a string already – TRIM always returns a string.

Not only does the example perform a useless datatype conversion, it involves an implicit conversion – from date to string. The expression would be much better like this:

TRIM(LEADING 0 FROM TO_CHAR(hire_date))

Better – but still not perfect. Sure, we should probably specify the date format so the code is a bit more robust (what if the session has a default format of ‘MM/DD/YY’?), but we can see yet another implicit data type conversion: “LEADING 0“. The only purpose for TRIM is to remove characters. Sure, you can put in zero if you want, but it’s only going to be converted to a character, ‘0’ anyway – so why not make it obvious to the next coder who comes along:

TRIM(LEADING '0' FROM TO_CHAR(hire_date,'DD/MM/YY'))

There, much better! I think the above expression is much clearer about what it will actually do – i.e. it converts a date to a string of characters, and then removes any leading ‘0’ characters.


“Not possible” is rarely correct

A good example of how not to answer a “Is it possible to…” question:

Is it possible to write a query which returns a date for every day between two specified days?

It’s ok to admit you don’t know how to do something.

It’s ok to say you don’t think a simple or feasible solution exists for a problem.

It may even be ok to say that something is impossible – if you constrain your answer to current technology. To say this you need to really know the technology, you need to have read about the problem widely enough, and you need to have enough personal experience to be able to say confidently, “no, what you are asking is impossible”. Even then, you might still be wrong, or become wrong sooner or later.

An answer saying outright, “No, that is impossible”, is just inviting a sharp rebuttal. Especially when in the very next sentence you admit that you aren’t an “Oracle specialist” 🙂


Truncated Mean in Oracle

A colleague needed to get the average from a set of data, but disregarding the top 25% and the bottom 25%. We didn’t know of any builtin Oracle function that would do this, but a review of the wikipedia page for Average yielded the technical term: truncated (or trimmed) mean. So we searched the Oracle docs and Google for ways to implement this function and didn’t come up with anything very useful. There were some SQL Server scripts which would have required two or three passes over the dataset.

After browsing through the aggregate functions documentation, I hit upon the NTILE function which I’ve used before, and realised that was the answer. The NTILE function takes a set of ordered data and divides it evenly (or as evenly as possible) between a number of buckets. In our case, we wanted to discard the top 25% and bottom 25%, so we simply divide the set into 4 buckets and discard the 1st and the 4th buckets; then take the standard average of the remainder:

SELECT AVG(mystat)
FROM (
      SELECT mystat,
             NTILE(4) OVER (ORDER BY mystat) n
      FROM (SELECT mystat
            FROM mytable)
     )
WHERE n IN (2,3);

The benefit of this query is that it only does one pass over the data, and was easily modified to partition the data set into groups; a count was also added so that the average could be taken over the entire data set for any groups that had less than 4 items.

To get the truncated mean in groups, except for groups with <4 items (for which we’ll report the average over the entire group):

SELECT mycat, AVG(mystat)
FROM (
      SELECT mycat, mystat,
             NTILE(4) OVER (PARTITION BY mycat
                            ORDER BY mystat) n,
             COUNT(1) OVER (PARTITION BY mycat) c 
      FROM (SELECT mycat, mystat
            FROM mytable)
     )
WHERE n IN (2,3) OR c < 4
GROUP BY mycat
ORDER BY mycat;