MySQL COALESCE Example & Why you should check the CLI in case of errors

Currently I’m working on some sort of shipping-system for a customer. It turned out that i was spending 3 days looking for a problem with my query while there was no problem. Well, apart from phpmyadmin :) But, lemme tell you later about it.

The problematic part with this “shipping-system” is that i’d need to know, which distributors are opened RIGHT NOW and that we have two tables. One table contains the regular openings and one contains the special openings. Because it might be that the distributor has usually opened on wednesday, exception: 06th July. Of course, the easiest approach would be to have that completly in MySQL as a (one) table or something. Thus i’d save a lot of queries in the php code. But how?

Well, we’ll need a few interesting things:

Overview
View
A View is in simple just a query. Means, instead of writing a long query in PHP, you can save that query as view and run your simplified php query onto that view.

COALESCE
COALESCE is selecting the first field, which is not null. That means

field1 = a, field2 = NULL
COALESCE(field1, field2)
» a

LEFT JOIN
A LEFT JOIN will help us to get NULL back in case one table has no result, which is the case here; i.e. if there is an entry in our special table (table2) use that one, if not, use the result of table1.

Tables
Table1:

CREATE TABLE `table1` (
  `id` bigint(20) NOT NULL auto_increment,
  `distributor` bigint(20) NOT NULL,
  `day` varchar(10) NOT NULL,
  `fromtime` time NOT NULL,
  `totime` time NOT NULL,
  `isClosed` enum('y','n') NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

Table2:

CREATE TABLE `table2` (
  `id` bigint(20) NOT NULL auto_increment,
  `distributor` bigint(20) NOT NULL,
  `date` datetime NOT NULL,
  `fromtime` time NOT NULL,
  `totime` time NOT NULL,
  `isClosed` enum('y','n') NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

The Queries (or, how to build a monster query)

Because of the very complex query I’ll do it step by step and fiddle the queries together afterwards. Let’s start at the beginning. To select only those distributors from table1 which have opened right now, we’ll first check the day:

WHERE `day` = DAYNAME(CURDATE())

Now we need to make sure that “fromtime” is smaller than CURTIME (current time). I.e. if distributor has opened from 08:00 to 15:00 and we have 15:00 right now, it’ll match. If we have 16:00 right now, it won’t match

AND `fromtime` <= CURTIME()

Last but not least, we'll use addtime to remove 10 minutes from "totime" (if totime is 15:00 it'll be 14:50) and we check whether that is bigger than the current time (so if they have opened to 20:00 and currenttime is 15:00 this will match. if current time is 21:00 this won't match.

AND ADDTIME(`totime`, '-0 00:10:00.000000') >= CURTIME() 

Quite simple, isn't it? Now the same for table2:

WHERE `date` = CURDATE()
AND `fromtime` <= CURTIME()
AND ADDTIME(`totime`,'-0 00:10:00.000000') >= CURTIME()

The only difference is, that we don't play around with a day, this time it's a date.

So, at this stage we know how to get the currently-opened-distributors. Time to bring the tables together. Every field which should be overriden by table2 (i.e: fromtime, totime, isclosed) needs to use coalesce. just to show how it works i'll add the original fields also, so you can see what happens:

SELECT
    COALESCE(a.fromtime, b.fromtime) as fromtime,
    a.fromtime as afromtime,
    b.fromtime as bfromtime,
    COALESCE(a.totime, b.totime) as totime,
    a.totime as atotime,
    b.totime as btotime,
    COALESCE(a.isClosed, b.isClosed) as isClosed,
    a.isClosed as aClosed,
    b.isClosed as bClosed
FROM
    table1 as a
LEFT JOIN table2 as b
ON a.distributor = b.distributor

As you can see now, phpmyadmin returns "crap" for the coalesce fields. I marked em bold:

fromtime afromtime bfromtime totime atotime btotime isClosed aClosed bClosed
30303a30303a3030 00:00:00 NULL 31323a30303a3030 12:00:00 NULL n n NULL

Why?
Oh well. It seems phpmyadmin doesn't seem to know, how to deal with COALESCE fields. If you take a closer look at the returned fields you'll notice that it looks like a HEX field, i.e. hex() over the time-field. I found that out while creating a testcase, for that i was using the CLI. And in the CLI the results were quite fine.

Solution?
You can add TIME() around the COALESCE - Then it'll work. Like this:

TIME( COALESCE( a.totime, b.totime ) ) AS totime

When doing it this way, phpmyadmin will display it correctly. However, we learn from this: If you get weird results, try in CLI first or prefer to work in the CLI.

However, now we know how to select only opened distributors and we know how to use COALESCE and the LEFT JOIN. By the way: The Left join is useful because it'll return NULL if there is no result. The COALESCE will choose the first not-null value. That means: If we want to overide (exactly that's what we want!) the data in table1 with data from table2 if both tables give a result, we need to give the field of table2 first:

COALESCE(table2.field, table1.field)

Well, now let's put the query together:

SELECT
    a.distributor AS adist,
    b.distributor AS bdist,
    TIME( COALESCE( b.fromtime, a.fromtime ) ) AS fromtime,
    a.fromtime AS afromtime,
    b.fromtime AS bfromtime,
    TIME( COALESCE( b.totime, a.totime ) ) AS totime,
    a.totime AS atotime,
    b.totime AS btotime,
    COALESCE( b.isClosed, a.isClosed ) AS isClosed,
    a.isClosed AS aClosed,
    b.isClosed AS bClosed
FROM table1 AS a
LEFT JOIN table2 AS b
ON
    a.distributor = b.distributor
AND (
    DATE( b.`date` ) = DATE( CURDATE( ) )
    AND b.`fromtime` <= TIME( CURTIME( ) )
    AND ADDTIME( b.`totime` , '-0 00:10:00.000000' ) >= CURTIME( )
)
WHERE (
    a.`day` = DAYNAME( CURDATE( ) )
    AND a.`fromtime` <= CURTIME( )
    AND ADDTIME( a.`totime` , '-0 00:10:00.000000' ) >= CURTIME( )
)

That looks cool, or? Well, but we haven't finished that thing yet. Now let's remove the stuff we just added for debug purposes and let's put it into a view. Because instead of issuing that big query all the time, i'd prefer to have it in a view, so i just need to do a simple query to that view.

CREATE VIEW tableNow AS
    SELECT
        a.distributor,
        TIME( COALESCE( b.fromtime, a.fromtime ) ) AS fromtime,
        TIME( COALESCE( b.totime, a.totime ) ) AS totime,
        COALESCE( b.isClosed, a.isClosed ) AS isClosed
    FROM table1 AS a
    LEFT JOIN table2 AS b
    ON
        a.distributor = b.distributor
    AND (
        DATE( b.`date` ) = DATE( CURDATE( ) ) 
        AND b.`fromtime` <= TIME( CURTIME( ) ) 
        AND ADDTIME( b.`totime` ,  '-0 00:10:00.000000' ) >= CURTIME( )
    )
    WHERE (
        a.`day` = DAYNAME( CURDATE( ) ) 
        AND a.`fromtime` <= CURTIME( ) 
        AND ADDTIME( a.`totime` ,  '-0 00:10:00.000000' ) >= CURTIME( )
    )

Done.

Leave a Reply

Your email address will not be published. Required fields are marked *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>