Oddities about MySQL

MySQL silently crops data if it’s larger than the specified fieldlength:

mysql> create table t2 (col1 varchar(5));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values ('12345');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values ('123456789');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t2;
+-------+
| col1  |
+-------+
| 12345 |
| 12345 |
+-------+
2 rows in set (0.00 sec)

mysql>

Let’s try this on an Oracle-Database:

SQL>create table t2 (col1 varchar2(5));

Table created.

SQL>insert into t2 values ('12345');

1 row created.

SQL>insert into t2 values ('123456789');
insert into t2 values ('123456789')
                       *
ERROR at line 1:
ORA-01401: inserted value too large for column

SQL>select * from t2;

COL1
-----
12345

SQL>

On MySQL you can’t UPDATE a table when that table is referenced in a subquery in the WHERE-condition of the same statement:

mysql> create table t1 (c1 varchar(10),c2 varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values ('abc','abcs@ssdef');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values ('abc','abcs@ssdef');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values ('abc','abcsssdef');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values ('abc','abcs@ssdef');
Query OK, 1 row affected (0.00 sec)

mysql> update t1 set c2='xyz' where c2 in (select c2 from t1 where instr(c2,'@')>0);
ERROR 1093 (HY000): You can't specify target table 't1' for update in FROM clause
mysql> select * from t1;
+------+------------+
| c1   | c2         |
+------+------------+
| abc  | abcs@ssdef |
| abc  | abcs@ssdef |
| abc  | abcsssdef  |
| abc  | abcs@ssdef |
+------+------------+
4 rows in set (0.00 sec)

mysql>

This behaviour is officially documented here:
http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html
And it is independent of the used storage engine (tested with MyISAM and InnoDB).

What about Oracle? Let’s see:

SQL>create table t1 (c1 varchar2(10),c2 varchar2(10));

Table created.

SQL>insert into t1 values ('abc','abcs@ssdef');

1 row created.

SQL>insert into t1 values ('abc','abcs@ssdef');

1 row created.

SQL>insert into t1 values ('abc','abcsssqw');

1 row created.

SQL>insert into t1 values ('abc','abcs@ssdef');

1 row created.

SQL>select * from t1;

C1         C2
---------- ----------
abc        abcs@ssdef
abc        abcs@ssdef
abc        abcsssqw
abc        abcs@ssdef

SQL>update t1 set c2='xyz' where c2 in (select c2 from t1 where instr(c2,'@')>0);

3 rows updated.

SQL>select * from t1;

C1         C2
---------- ----------
abc        xyz
abc        xyz
abc        abcsssqw
abc        xyz

SQL>

More astonishing oddities about MySQL can be found here:
http://www.databasejournal.com/features/mysql/article.php/3519116/MySQL-Oddities.htm. It’s really worth reading!

Most of this oddities depend on the given “SQL Server Mode” and appear with the default settings of MySQL. By changing the “SQL Server Mode”, one can make MySQL to behave like a “traditional” RDBMS. More info on that here: http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html

Oracle: concatenating strings from multiple rows in one table-field

Developer had the requirement to concat a field from multiple rows as one single string in one row.
Something like this:

    DEPTNO ENAME
---------- ----------
        20 SMITH
        30 ALLEN
        30 WARD
        20 JONES
        30 MARTIN
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        30 TURNER
        20 ADAMS
        30 JAMES
        20 FORD
        10 MILLER

Desired Output:

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Looked like a not so unusually request to me. But digging the docs and the web I found that until 11g there is no official solution for this.

WM_CONCAT

If the WorkspaceManager-schema WMSYS is installed, which normally is if using the DBCA for db-creation in 10g, one has the WMCONCAT()-function that easily does what we want:

SELECT deptno, wm_concat(ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

WM_CONCAT has no built-in functionality to change the separator. But if your data doesn’t prevent it, you could wrap WMCONCAT in a REPLACE like this as a workaround:

select deptno, replace(wm_concat(ename),',',';') as employees 
from emp 
group by deptno;

    DEPTNO EMPLOYEES
---------- ----------------------------------------
        10 KING;CLARK;MILLER
        20 JONES;ADAMS;SCOTT;SMITH;FORD
        30 BLAKE;WARD;MARTIN;JAMES;TURNER;ALLEN

In this example the default-delimiter comma (“,”) is replaced by a semicolon (“;”) after all concatenation is done.

But beware that WM_CONCAT is unofficial and unsupported by Oracle. So no alternative for production-systems.

LISTAGG

In Oracle Database 11g there is the new LISTAGG()-function as the official way to do it:

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

As you see, with LISTAGG it is even possible to define the delimiter. It is the second parameter of the LISTAGG-function (here it is set to comma “,”).

I found an interesting comparison between WMCONCAT and LISTAGG here.

Custom Function

Unfortunately my production-db is neither 11g nor I have the WMSYS-schema installed.
But I found a pretty solution on www.oracle-base.com which you can find here. The approach uses 10g’s COLLECT-function, a table-type and a user-function to get the desired output.
This snippet is from the named article on oracle-base:

    CREATE OR REPLACE TYPE t_varchar2_tab AS TABLE OF VARCHAR2(4000);
    /

    CREATE OR REPLACE FUNCTION tab_to_string (p_varchar2_tab  IN  t_varchar2_tab,
                                              p_delimiter     IN  VARCHAR2 DEFAULT ',') RETURN VARCHAR2 IS
      l_string     VARCHAR2(32767);
    BEGIN
      FOR i IN p_varchar2_tab.FIRST .. p_varchar2_tab.LAST LOOP
        IF i != p_varchar2_tab.FIRST THEN
          l_string := l_string || p_delimiter;
        END IF;
        l_string := l_string || p_varchar2_tab(i);
      END LOOP;
      RETURN l_string;
    END tab_to_string;
    /

The query below shows the COLLECT function in action.

    COLUMN employees FORMAT A50

    SELECT deptno,
           tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab)) AS employees
    FROM   emp
    GROUP BY deptno;
           
        DEPTNO EMPLOYEES
    ---------- --------------------------------------------------
            10 CLARK,KING,MILLER
            20 SMITH,JONES,SCOTT,ADAMS,FORD
            30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES

As obvious from the code above, this custom-made concat-function also offers the possibility to define a delimiter:

    SELECT deptno,
           tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab),':') AS employees
    FROM   emp
    GROUP BY deptno;

        DEPTNO EMPLOYEES
    ---------- --------------------------------------------------
            10 CLAR:KING:MILLER
            20 SMITH:JONES:SCOTT:ADAMS:FORD
            30 ALLEN:WARD:MARTIN:BLAKE:TURNER:JAMES

In this example I set the delimiter to ‘:’ as the second parameter of the tab_to_string-function:

tab_to_string(CAST(COLLECT(ename) AS t_varchar2_tab),’:’))

And if you have the need to get your strings ordered by something, you could “order by” in the COLLECT-function like “COLLECT(ename order by ename)”:

tab_to_string(CAST(COLLECT(ename order by ename) AS t_varchar2_tab),’:’))

How to check if Oracle-DB-edition is “Standard” or “Enterprise”

When logging in via SQL+ you see something like this:

Standard-Edition (SE):

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 2 14:35:46 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production

Enterprise-Edition (EE):

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Aug 2 14:34:52 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options

You could also query v$version:

SE:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

EE:

SQL>  select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production