CODEWITHSUNDEEP
phpmysqlunionunion-all
c00000fd
c00000fd

Reputation: 22275

Unexpected behavior of UNION ALL operator

I have the following MySql tables.

Table tblUsg defined as such:

CREATE TABLE `tblUsg` (
`id` INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`ip` VARCHAR(46) NOT NULL,
`dtm` DATETIME NOT NULL,
`huid` BINARY(32) NOT NULL,
`licnm` VARCHAR(20) NOT NULL,
`lichld` VARCHAR(256) NOT NULL,
`flgs` INT NOT NULL,
`agnt` VARCHAR(256),

INDEX `ix_huid` (`huid`),
INDEX `ix_licnm` (`licnm`),
UNIQUE KEY `ix_lichuid` (`huid`, `licnm`)
) AUTO_INCREMENT=0 CHARACTER SET utf8 COLLATE utf8_unicode_ci;

And table tblLics defined as such:

CREATE TABLE `wosLics` (
`id` INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
`licnm` VARCHAR(20) NOT NULL,
`desc` VARCHAR(256) NOT NULL,
`maxcpy` INT NOT NULL,
`dtmFrom` DATETIME,
`dtmTo` DATETIME,
`stat` INT NOT NULL,

UNIQUE KEY `ix_licnm` (`licnm`)
) AUTO_INCREMENT=0 CHARACTER SET utf8 COLLATE utf8_unicode_ci;

I then call the following PHP script when, say, both tables are empty:

$link = @mysql_connect($HOSTNAME, $USERNAME, $PASSWD);
@mysql_select_db($DBNAME);
mysql_set_charset('utf8', $link);

$res = @mysql_query(
    "SELECT `maxcpy`, `stat`, `dtmFrom`, `dtmTo` FROM `tblLics` WHERE `licnm`='zbcdefghijklmnopqrsu'\n".
    "UNION ALL\n".
    "SELECT COUNT(*), NULL, NULL, NULL FROM `tblUsg` WHERE `licnm`='zbcdefghijklmnopqrsu'\n".
    "UNION ALL\n".
    "SELECT COUNT(*), NULL, NULL, NULL FROM `tblUsg` WHERE (`licnm`='zbcdefghijklmnopqrsu' AND `huid`='a871c47a7f48a12b38a994e48a9659fab5d6376f3dbce37559bcb617efe8662d')"
    , $link);
if($res)
{
    $row0 = @mysql_fetch_row($res);
    $row1 = @mysql_fetch_row($res);
    $row2 = @mysql_fetch_row($res);

    echo("<br/>0::<br/>");
    var_dump($row0);
    echo("<br/>1::<br/>");
    var_dump($row1);
    echo("<br/>2::<br/>");
    var_dump($row2);
}

Which outputs this:

0::
array(4) { [0]=> string(1) "0" [1]=> NULL [2]=> NULL [3]=> NULL } 
1::
array(4) { [0]=> string(1) "0" [1]=> NULL [2]=> NULL [3]=> NULL } 
2::
bool(false)

My question is why my $row2 is false when $row1 is the array as I would've expected?

Upvotes: 0

Views: 1259

Answers (1)

axiac
axiac

Reputation: 72226

My question is why my $row2 is false when $row1 is the array as I would've expected?

You expect to get back 3 rows from your query but it only returns 2 rows.

Your query UNIONs three SELECTs. Each of the last two SELECTs always return exactly one row. The first SELECT can return 0 rows or more. Because the table is empty it returns exactly zero rows.

0+1+1. The query returns exactly 2 rows.

Update:

You expect the rows to be returned in a specific order but the query doesn't require any sorting. SQL works with sets of rows and the sets, as mathematical objects, are unsorted collections (and this is how the SQL handles them).

Without the presence of ORDER BY in the query, the rows returned by UNION are not guaranteed to be returned in any order. Not even the order they come from the SELECTs is not preserved.

If you want to get the rows in the order you wrote the SELECT queries then you have to add an additional column that tells the order and use in in the ORDER BY clause:

SELECT `maxcpy`, `stat`, `dtmFrom`, `dtmTo`, 1 AS tableNb
FROM `tblLics`
WHERE `licnm`='zbcdefghijklmnopqrsu'

UNION ALL

SELECT COUNT(*), NULL, NULL, NULL, 2 AS tableNb
FROM `tblUsg`
WHERE `licnm`='zbcdefghijklmnopqrsu'

UNION ALL

SELECT COUNT(*), NULL, NULL, NULL, 3 AS tableNb
FROM `tblUsg`
WHERE `licnm`='zbcdefghijklmnopqrsu'
  AND `huid`='a871c47a7f48a12b38a994e48a9659fab5d6376f3dbce37559bcb617efe8662d'

ORDER BY tableNb

This way you know what part of the query generated each of the returned rows.

Remark

You don't need the row returned by the second query. It basically tells you how many rows are returned by the first query but you can also know that by counting the rows having tableNb == 1 in the result set. Since you want the count after the actual rows, it doesn't need an additional traversal of the result set, it can be done while listing the rows from the first query.

Upvotes: 1

Related Questions