36
Amrish
6d

Today, for fun, I wrote prime number generation upto 1000 using pure single MySQL query.

No already created tables, no procedures, no variables. Just pure SQL using derived tables.

So does this mean that pure SQL statements do not have the halting problem?

Putting an EXPLAIN over the query I could see how MySQL guessed that the total number of calculations would be 1000*1000 even before executing the query in itself and this is amazing ♥️

I have attached a screenshot of the query and if you are curious, I have also left below the plain text.

PS this was a SQL problem in Hackerrank.

MySQL query:

select group_concat(primeNumber SEPARATOR '&') from
(select numberTable.number as primeNumber from

(select cast((concat(tens, units, hundreds)+1) as UNSIGNED) as number from

(select 0 as units union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) unitsTable,
(select 0 as tens union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) tensTable,
(select 0 as hundreds union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) hundredsTable order by number) numberTable

inner join

(select cast((concat(tens, units, hundreds)+1) as UNSIGNED) as divisor from

(select 0 as units union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) unitsTable,
(select 0 as tens union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) tensTable,
(select 0 as hundreds union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) hundredsTable order by divisor) divisorTable

on (divisorTable.divisor<=numberTable.number and divisorTable.divisor!=1)
where numberTable.number%divisorTable.divisor=0
group by numberTable.number having count(*)<=1 order by numberTable.number) resultTable;

Comments
  • 4
    Optimizations to query are welcome 😀

    I haven't attached explain's screenshot, in mobile right now. You can execute a explain of this query on any online MySQL try out.
  • 12
    Are you even human?
  • 2
    @dmonkey tbh, I was in the zone and couldn't stop. Also, I can pass the captcha test 😛
  • 4
    @Amrish Passing captchas doesnt prove shit man youre an AI for sure
  • 1
    What the hell.
  • 2
    That’s very creative! Favorited
  • 10
    Meanwhile I give myself a pat on the back if I can properly write a simple JOIN
  • 1
    Good. Now write quick sort.
  • 1
    Not sure if you can use ctes in mysql, but I set myself the challenge to write it for sql server. Does up to 1000, but can be easily extended. No idea how the performance will be for larger ranges though.

    Maybe for a bigger range, performance would be better with a temp table and primary key
  • 0
    @amrish can you optimize your query by only testing odd numbers and testing divisors less than half of the number being tested
  • 0
Your Job Suck?
Get a Better Job
Add Comment