CODEWITHSUNDEEP
mysql
Harini Rao
Harini Rao

Reputation: 21

Can someone explain this mysql code to me

This is a mysql code to print prime numbers less than 1000, but i couldn't understand it properly, can someone explain what is happening here. 

SELECT GROUP_CONCAT(NUMB SEPARATOR '&')
FROM (
  SELECT @num:=@num+1 as NUMB FROM
  information_schema.tables t1,
  information_schema.tables t2,
  (SELECT @num:=1) tmp
  ) tempNum
WHERE NUMB<=1000 AND NOT EXISTS(
    SELECT * FROM (
        SELECT @nu:=@nu+1 as NUMA FROM
            information_schema.tables t1,
            information_schema.tables t2,
            (SELECT @nu:=1) tmp1
            LIMIT 1000
        ) tatata
    WHERE FLOOR(NUMB/NUMA)=(NUMB/NUMA) AND NUMA<NUMB AND NUMA>1
)

Upvotes: 1

Views: 587

Answers (1)

zedfoxus
zedfoxus

Reputation: 37039

Let's break this down step by step.

How many rows does information_schema.tables have?

select count(*) from information_schema.tables t1
-> 370

How many rows does information_schema.tables, information_schema.tables have?

select count(*) from information_schema.tables t1, information_schema.tables t2
--> 370 * 370 = 136900

What is := and tmp?

(SELECT @num:=1) tmp

SELECT @num:=1 means we are initializing a variable. (SELECT @num:=1) tmp means we are making a table out of that.

What is tempNum table?

SELECT @num:=@num+1 as NUMB
FROM
  information_schema.tables t1,
  information_schema.tables t2,
  (SELECT @num:=1) tmp

This is almost the same as t1, t2 combination (~137K records). However, what column is being pulled? The column is the variable we declared in tmp table and 1 is added to it. Starting number will be 2, then 3, and onwards.

Why filter NUMB?

Just focus on this part:

SELECT GROUP_CONCAT(NUMB SEPARATOR '&')
FROM (
  SELECT @num:=@num+1 as NUMB FROM
  information_schema.tables t1,
  information_schema.tables t2,
  (SELECT @num:=1) tmp
  ) tempNum
WHERE NUMB<=1000

This would take just the first 1000 records from ~137K records starting from 2 and ending at 1000. GROUP_CONCAT makes the result 2&3&4&5...&1000.

What does tatata do?

Just focus on this now.

SELECT * FROM (
    SELECT @nu:=@nu+1 as NUMA
    FROM
        information_schema.tables t1,
        information_schema.tables t2,
        (SELECT @nu:=1) tmp1
    LIMIT 1000
) tatata

This acts similar to the tempNum but with a slight difference. The inner query selects numbers from 2 and takes 1000 items. That means, the last number is 1001. All those numbers are aliased in tatata virtual table.

End game

Focus on the this part:

AND NOT EXISTS(
    SELECT * FROM (
        SELECT @nu:=@nu+1 as NUMA FROM
            information_schema.tables t1,
            information_schema.tables t2,
            (SELECT @nu:=1) tmp1
            LIMIT 1000
        ) tatata
    WHERE FLOOR(NUMB/NUMA)=(NUMB/NUMA) AND NUMA<NUMB AND NUMA>1
)

So, you know that tempNum has 2..1000. The query above says, get everything from tempNum as long as none of tatata's number can wholly divide tempNum.

  • Let's say tempNum is 2
  • Get all numbers from tatata less than 2. tatata doesn't have any number less than 2
  • So, information in that subquery doesn't exist (NOT EXISTS is true)
  • Extract 2

Next number

  • tempNum is 3
  • Get all numbers from tatata less than 3. That'd be just 2
  • One by one, divide 3 with numbers found. We just found 2. So, divide 3 by 2. Was it a clean division? No.
  • So, information in that subquery doesn't exist (NOT EXISTS is true)
  • Extract 2

Next number

  • tempNum is 4
  • Get all numbers from tatata less than 4. That'd be just 2 and 3
  • One by one, divide 4 with numbers found
  • Did either 2 or 3 wholly divide 4? Yeah...2 divides 4 wholly.
  • So, information in that subquery exists. It'd return 2 (NOT EXISTS is false)
  • Do not extract 4

And on...

As you see, prime numbers are being extracted from tempNum. GROUP_CONCAT just takes the resulting prime numbers and connects them with & resulting in 2&3&5&7...

Upvotes: 3

Related Questions