In For The Win!

Someone on stackoverflow observed that IN is faster than EXISTS on MySQL

select * from `replays` 
where id in (
    select replay_id from `players` 
    where `battletag_name` = 'test') 
order by `id` asc 
limit 100;

select * from `replays` 
where exists (
    select * from `players` 
    where `replays`.`id` = `players`.`replay_id` 
      and `battletag_name` = 'test') 
order by `id` asc 
limit 100;

EXISTS took 70 seconds. IN took 0.4 second.


Aside from IN being faster than EXISTS in most cases, readability of IN is a big win. And also with IN, queries can be made modular, e.g.,

with test_players as 
(
    select replay_id 
    from players 
    where battletag_name = 'test'
) 
select * 
from replays 
where id in (select replay_id from test_players)

Cannot do the above when using EXISTS.

Query Error: Error: ER_NOT_SUPPORTED_YET: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Does not work on MySQL, works on Postgres

select
    t.player, 
    sum(case when t.eventid = 1 then t.points end) as event1,
    sum(case when t.eventid = 2 then t.points end) as event2,
    sum(case when t.eventid = 3 then t.points end) as event3,
    sum(case when t.eventid = 4 then t.points end) as event4,
    
    sum(
        case when t.points >= any(
            select best3.points 
            from tbl best3 
            where best3.player = t.player
            order by best3.points desc 
            limit 3
        ) then 
            t.points
        end
    )             
from tbl t
group by t.player

Output on MySQL 8.0:

Query Error: Error: ER_NOT_SUPPORTED_YET: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Output on Postgres:

| player | event1 | event2 | event3 | event4 | sum |
| ------ | ------ | ------ | ------ | ------ | --- |
| 1      | 25     | 15     | 20     | 20     | 65  |
| 2      | 20     | 13     | 12     | 10     | 45  |

You can even made the code neater on Postgres by using FILTER:

Live test: https://www.db-fiddle.com/f/haMmw4S4f7XMqcBD8CDV7H/1

select
    t.player, 
    sum(t.points) filter(where t.eventid = 1) as event1,
    sum(t.points) filter(where t.eventid = 2) as event2,
    sum(t.points) filter(where t.eventid = 3) as event3,
    sum(t.points) filter(where t.eventid = 4) as event4,
    
    sum(t.points) filter(where
        t.points >= any(
            select best3.points 
            from tbl best3 
            where best3.player = t.player
            order by best3.points desc 
            limit 3
        )
    ) as best3          
from tbl t
group by t.player          

Solution on MySQL 8.0, works on Postgres too:

Live test: https://www.db-fiddle.com/f/4ufuFAXKf7mi5yefNQqoXM/2

with ranking as
(
    select 
        player,       
        rank() over(partition by player order by points desc) as xrank,
        points
    from tbl
)
,pick3 as
(
    select 
        player, 
        sum(points) as best3
    from ranking 
    where xrank <= 3
    group by player
)
select
    t.player, 
    sum(if(t.eventid = 1, t.points,0)) as event1,
    sum(if(t.eventid = 2, t.points,0)) as event2,
    sum(if(t.eventid = 3, t.points,0)) as event3,  
    sum(if(t.eventid = 4, t.points,0)) as event4,
    p.best3            
from tbl t
join pick3 p on t.player = p.player
group by t.player

MySQL 5.7 solution:

Live test: https://www.db-fiddle.com/f/4ufuFAXKf7mi5yefNQqoXM/15

select
    t.player, 
    sum(case when t.eventid = 1 then t.points end) as event1,
    sum(case when t.eventid = 2 then t.points end) as event2,
    sum(case when t.eventid = 3 then t.points end) as event3,
    sum(case when t.eventid = 4 then t.points end) as event4,
    
    sum(
        case when t.points >= (
            select best3.points 
            from tbl best3 
            where best3.player = t.player
            order by best3.points desc 
            limit 1 offset 2
        ) then 
            t.points
        end
    ) as best3            
from tbl t
group by t.player;

Output:

| player | event1 | event2 | event3 | event4 | best3 |
| ------ | ------ | ------ | ------ | ------ | ----- |
| 1      | 25     | 15     | 20     | 20     | 65    |
| 2      | 20     | 13     | 12     | 10     | 45    |

Error: connect ECONNREFUSED 192.168.254.176:3306

In macOS environment there is no explicit file for my.cnf. It's weird, before I introduced .my.cnf in home directory, applications can connect to remote host (e.g., 192.168.254.176) just fine.

The solution is to force the TCP on .my.cnf

[client]
protocol=tcp

Restart:

$ brew services restart mysql

It turns out that the MySQL installation on my machine is using socket. What's odd is I'm not using localhost (e.g., 192.168.254.176) for database connection's host despite server and client are same machine, yet MySQL still resorts to using socket.

On Unix, if you are running the server and the client on the same machine, connect to localhost. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file, unless there are connection parameters specified to ensure that the client makes a TCP/IP connection
-- https://dev.mysql.com/doc/refman/5.5/en/problems-connecting.html

Enabling MySQL's binary logging on macOS

MySQL 5.7.16, installed on macOS Sierra using Homebrew.

With Homebrew installation, it's convenient to access mysql commandline. Whereas using its DMG installer, the mysql commandline can only be accessed using its full path.

mysql --user root --password -e 'SHOW VARIABLES' | grep log_bin

By default, log_bin is disabled

log_bin OFF
log_bin_basename 
log_bin_index 
log_bin_trust_function_creators OFF
log_bin_use_v1_row_events OFF
sql_log_bin ON

Create directory where to put the binary logging files and its index:

sudo mkdir /var/log/kel-mysql-log-bin/
sudo touch /var/log/kel-mysql-log-bin/binlogging.index
sudo chown -R `whoami` /var/log/kel-mysql-log-bin/

Check the binary logging directory:

ls -la /var/log/kel-mysql-log-bin/

Output:

drwxr-xr-x   3 jack  wheel   102 Dec  1 19:55 .
drwxr-xr-x  60 root  wheel  2040 Dec  1 19:55 ..
-rw-r--r--   1 jack  wheel     0 Dec  1 19:55 binlogging.index

Create .my.cnf in user's home path:

vim ~/.my.cnf

.my.cnf content:

[mysqld]
log-bin=/var/log/kel-mysql-log-bin/binlogging
server-id=1

Restart MySQL:

mysql.server restart

Check if the binary logging is enabled:

mysql --user root --password -e 'SHOW VARIABLES' | grep log_bin

Output:

log_bin ON
log_bin_basename /var/log/kel-mysql-log-bin/binlogging
log_bin_index /var/log/kel-mysql-log-bin/binlogging.index
log_bin_trust_function_creators OFF
log_bin_use_v1_row_events OFF
sql_log_bin ON

Check the binary logging directory:

ls -la /var/log/kel-mysql-log-bin/

Output:

drwxr-xr-x   4 jack  wheel   136 Dec  1 19:57 .
drwxr-xr-x  60 root  wheel  2040 Dec  1 19:55 ..
-rw-r-----   1 jack  wheel   154 Dec  1 19:57 binlogging.000001
-rw-r-----   1 jack  wheel    44 Dec  1 19:57 binlogging.index

log_bin variable is not something that is being set directly, it's a read-only variable. To turn the log_bin on, the binary logging directory and index must be specified.


Happy Coding!

Get fluctuating price on MySQL

Given this data:

CREATE TABLE fluctuate
    (Date datetime, Company varchar(10), Price int);

INSERT INTO fluctuate
    (Date, Company, Price)
VALUES
    ('2012-01-04 00:00:00', 'Apple', 458),
    ('2012-01-03 00:00:00', 'Apple', 462),
    ('2012-01-02 00:00:00', 'Apple', 451),
    ('2012-01-01 00:00:00', 'Apple', 450),
    ('2012-01-01 00:00:00', 'Microsoft', 1),
    ('2012-01-03 00:00:00', 'Microsoft', 7),
    ('2012-01-05 00:00:00', 'Microsoft', 5),
    ('2012-01-07 00:00:00', 'Microsoft', 8),
    ('2012-01-08 00:00:00', 'Microsoft', 12);

You want to get the fluctuation in price even the date is non-contiguous:

DATE                       COMPANY             PRICE               DAY_CHANGE
January, 04 2012           Apple               458                 -4
January, 03 2012           Apple               462                 11
January, 02 2012           Apple               451                 1
January, 01 2012           Apple               450                 0
January, 08 2012           Microsoft           12                  4
January, 07 2012           Microsoft           8                   3
January, 05 2012           Microsoft           5                   -2
January, 03 2012           Microsoft           7                   6
January, 01 2012           Microsoft           1                   0

Since there's no windowing function on MySQL yet, we will settle for some MySQL-ism:

select 

date, 
company, 
price, 
day_change

from
(    
  select 

     case when company <> @original_company then
         -- new company detected,
         -- reset the original_price base on the new company
         @original_price := price
     end,
    f.*,
    price - @original_price as day_change,
    (@original_price := price),
    (@original_company := company)


  from fluctuate f

  cross join
  (
    select 
     @original_price := price,
     @original_company := company
     from fluctuate 
     order by company, date limit 1
  )
  as zzz

  order by company, date 

) as yyy
order by company, date desc

How does it work, first we need to get the starting price from the table, we use cross join to get the initial values. Then from there, we subtract the price from the original price and alias it as day_CHANGE, then save the state of the current price to original price, then apply the new original price to next price, and so on.

Now on new company, to reset the state of original price based on that company, on the first expression of our SELECT statement, we detect if the current company is not equal to the original_company; if it isn't, we reset the original_price based on the current company


Happy Coding! :-)

Live test: http://www.sqlfiddle.com/#!2/e16ec/1

Insisting ELSE 0 on COUNT aggregate? You might as well substitute your birthday

Some unenlightened SQL user from stackoverflow wanted to do this, note the ELSE 0 part:

select count(case when 'blah' = 'bleh' then 1 else 0 end) 
from information_schema.tables

Due to his insistence on the need for ELSE 0 part, I recommended to him that he can substitute his birthday instead on that ELSE part, it "works" the same anyway as the ELSE 0 approach:

select 
count(case when 'blah' = 'bleh' then 1 else 'April 20, 1939' end) 
from information_schema.tables

Of course, both queries above are a crime against humanity, and I cannot let this wrong deed goes unpunished uncorrected, here's the correct query:

Idiomatic MySQL (duality between boolean and integer) :

select sum('blah' = 'bleh') 
from information_schema.tables

Idiomatic Postgresql:

select sum(('blah' = 'bleh')::int) 
from information_schema.tables

SQL Server:

select sum(case when 'blah' = 'bleh' then 1 end) 
from information_schema.tables

We can also use these, but the above works well, free of noise and devoid of Cargo Cult Programming, it's better to use that code. SUM ignores 0 and NULLs, why include them?

select sum(case when 'blah' = 'bleh' then 1 else 0 end) 
from information_schema.tables;

select sum(case when 'blah' = 'bleh' then 1 else null end) 
from information_schema.tables;

But for the love of our craft, don't do this (the ELSE 0 on COUNT), this is very wrong :

select count(case when 'blah' = 'bleh' then 1 else 0 end) 
from information_schema.tables

If you insist so, re-read from the top

MySQL has WITH ROLLUP, PostgreSQL users must be fuming with envy

Data sample:

create table ProductInventory(
  ProductCode varchar(10) not null,
  Location varchar(50) not null
);


insert into ProductInventory(ProductCode,Location) values
('CPU','US'),
('CPU','PH'),
('CPU','PH'),
('KB','PH'),
('KB','US'),
('KB','US'),
('MSE','US'),
('MSE','JP');

MySQL query works :

select ProductCode, 
    SUM(Location = 'US') as UsQty,
    SUM(Location = 'PH') as PhilippinesQty
from ProductInventory
group by ProductCode with rollup

Postgres query doesn't work:

select ProductCode, 
    SUM((Location = 'US')::int) as UsQty,
    SUM((Location = 'PH')::int) as PhilippinesQty
from ProductInventory
group by ProductCode with rollup

Output:

ProductCode UsQty PhilippinesQty
CPU             1       2
KB              2       1
MSE             1       0
                4       3

Allay our worries Postgres users, ROLLUP for Postgres is in the pipeline too: http://wiki.postgresql.org/wiki/Grouping_Sets