Complex select explanation

Question

As a follow up to this usefull answer which did manage to solve my problem, I post back because, to be honest, I don't fully understand this (for me complex) SELECT.

If someone could light me up about the data calculation (why to split it into a b c) and the CROSS JOIN part I would be gratefull.

This answer did a enourmous performance upgrade in my code, so I do think it is usefull to have this method explained here, for everyone.

Cheers

SELECT dates.Date,
       coalesce(s.daily_price, 0) AS price, s.id_item AS house
FROM
  (SELECT a.Date
   FROM
     ( SELECT '$to' - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Date, '0' AS price
      FROM
        (SELECT 0 AS a
         UNION ALL SELECT 1
         UNION ALL SELECT 2
         UNION ALL SELECT 3
         UNION ALL SELECT 4
         UNION ALL SELECT 5
         UNION ALL SELECT 6
         UNION ALL SELECT 7
         UNION ALL SELECT 8
         UNION ALL SELECT 9) AS a
      CROSS JOIN
        (SELECT 0 AS a
         UNION ALL SELECT 1
         UNION ALL SELECT 2
         UNION ALL SELECT 3
         UNION ALL SELECT 4
         UNION ALL SELECT 5
         UNION ALL SELECT 6
         UNION ALL SELECT 7
         UNION ALL SELECT 8
         UNION ALL SELECT 9) AS b
      CROSS JOIN
        (SELECT 0 AS a
         UNION ALL SELECT 1
         UNION ALL SELECT 2
         UNION ALL SELECT 3
         UNION ALL SELECT 4
         UNION ALL SELECT 5
         UNION ALL SELECT 6
         UNION ALL SELECT 7
         UNION ALL SELECT 8
         UNION ALL SELECT 9) AS c) a
   WHERE a.Date BETWEEN '$from' AND '$to'
   ORDER BY a.Date) dates
LEFT JOIN ".T_ITEM_SEASONS." s ON dates.Date BETWEEN s.season_start AND s.season_end

Edit: In my original question I had added a bug which is discussed under. The buggy code was:
300 * c.a in
( SELECT '$to' - INTERVAL (a.a + (10 * b.a) + (300 * c.a)) DAY AS Date,

Not sure why you changed this part "INTERVAL (a.a + (10 * b.a) + (300 * c.a)". It is not that way in the original answer. — peter.petrov, Jan 04 '14 at 23:13
@peter.petrov, I changed because I needed to go back 5 years in dates. Did try and error and it worked. — Rikard, Jan 04 '14 at 23:19
My gut tells me you introduced a bug this way (because this formula seems to skip some dates). You should have used 3 cross joins and should have added d then (on top of a,b,c). 5 years have about 1825 days so in your d you need to have just the values of 0,1. The original answer went back 999 days. You should have used this: a.a + (10*b.a) + (100*c.a) + 1000 * d.a where d takes values from the set {0,1}. — peter.petrov, Jan 04 '14 at 23:20
@peter.petrov, well to be honest, and that is the reason of this post, I was not sure. So I tried here and looked good: http://sqlfiddle.com/#!2/a2581/1140 — Rikard, Jan 04 '14 at 23:24
"Looks Good" <> "It is Good". Which a,b,c would give you the date which is 1400 days back (calculating from today) ? Answer: none. So you're skipping this date (and not only this one). Some number theory makes this obvious :) — peter.petrov, Jan 04 '14 at 23:28

peter.petrov · Accepted Answer · 2014-01-04T23:36:41.207

The 3 cross joins just generate the Cartesian product

{0, 1,2,..., 9} x {0, 1,2,..., 9} x {0, 1,2,..., 9}
a                 b                 c

This part (let's name it x)

( SELECT curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY AS Date

basically selects the current date and subtracts from it N days for N=0,1,2,...,999
using the a, b, and c values generated in the Cartesian product. Note that each such
N (each number with <= 3 digits) could be written as N = a + 10*b + 100*c for some
particular values of a, b, and c.

Finally this date x is joined to your seasons table apparently
to put each date x into its proper season. The rest should
be more clear to you than to me, I guess.

I think the guy who responded to you could have avoided
these 2 cross joins if he wrote something like:

     (SELECT 0 AS a
     UNION ALL SELECT 1
     UNION ALL SELECT 2
     UNION ALL SELECT 3
     UNION ALL SELECT 4
     UNION ALL SELECT 5
     ..........
     UNION ALL SELECT 997
     UNION ALL SELECT 998
     UNION ALL SELECT 999) AS a

but he wanted to make it more compact (and to avoid writing 1000 lines).
But if he did so, that would have simplified the x which would have become just

( SELECT curdate() - INTERVAL (a.a) DAY AS Date

This is how I read the author's query.

EDIT

Here is a small Java program which demonstrates
that you have gaps/misses the way you did it
i.e. with the a.a + (10 * b.a) + (300 * c.a) formula.

    import java.util.TreeMap;

    public class Test050 {

        public static void main(String[] args) {
            TreeMap<Integer, Boolean> v = new TreeMap<Integer, Boolean>();
            for (int a=0; a<10; a++){
                for (int b=0; b<10; b++){
                    for (int c=0; c<10; c++){
                        int r = a + 10 * b + 300 * c;
                        v.put(r, Boolean.TRUE);
                    }
                }
            }

            for (int key : v.keySet()){
                System.out.println(key);
            }
        }

    }

Interesting! I will add the `d` to reach more days instead of my buggy way. Thanks! — Rikard, Jan 04 '14 at 23:34
Saw your edit. Since I didn't understand the code I did not understand it's logic. I do now and understand my mistaque. Thanks! — Rikard, Jan 04 '14 at 23:38
@Rikard From 100 to 299 (days back) you have gaps. From 1300 to 1499 days back you also have gaps. Maybe there're other gaps too. Just write 3 nested loops as I did (see updated answer) and you will be convinced. — peter.petrov, Jan 04 '14 at 23:38

Complex select explanation

1 Answers1