Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Calculating Tupper's Self-Referential Formula With SQL

DZone's Guide to

Calculating Tupper's Self-Referential Formula With SQL

I was reading up on the cool Tupper’s Self-Referential Formula and thought, Can this be done in SQL? SQL is Turing-complete, so the answer must be yes.

· Big Data Zone ·
Free Resource

Hortonworks Sandbox for HDP and HDF is your chance to get started on learning, developing, testing and trying out new features. Each download comes preconfigured with interactive tutorials, sample data and developments from the Apache community.

A really geeky way to start a Monday morning is to be nerd-sniped by the cool Fermat’s Library Twitter account...

Image title

…reading up on the cool Tupper’s Self-Referential Formula and thinking, Can this be done in SQL?

As we all know from a previous article, SQL is Turing-complete, so the answer must be yes. And in fact, as it turns out, this is actually super easy compared to some other problems I’ve been solving with SQL on my blog in the past.

The Formula

The formula is really simple:

Or, in a more programmer-y way:

1/2 < floor(mod(floor(y/17)*2^(-17*floor(x)-mod(floor(y), 17)),2))

Luckily, this syntax also happens to be SQL syntax, so we’re almost done. So, let’s try plotting this formula for the area of x BETWEEN 0 AND 105 and y BETWEEN k AND k + 16, where k is just some random large number — let’s say:

96093937991895888497167296212785275471500433966012930665
15055192717028023952664246896428421743507181212671537827
70623355993237280874144307891325963941337723487857735749
82392662971551717371699516523289053822161240323885586618
40132355851360488286933379024914542292886670810961844960
91705183454067827731551705405381627380967602565625016981
48208341878316384911559022561000365235137034387446184837
87372381982248498634650331594100549747005931383392264972
49461751545728366702369745461014655997933798537483143786
841806593422227898388722980000748404719

Unfortunately, most SQL databases cannot handle such large numbers without any additional libraries, except for the awesome PostgreSQL, whose decimal/numeric types can handle up to 131,072 digits before the decimal point and up to 16,383 digits after the decimal point.

Yet again, unfortunately, even PostgreSQL by default can’t handle such precision/scale, so we’re using a trick to expand the precision beyond what’s available by default. Here’s the SQL query:

WITH
  t1(k, z) AS (
    SELECT
      ('96093937991895888497167296212785275471500433966012930665'
    || '15055192717028023952664246896428421743507181212671537827'
    || '70623355993237280874144307891325963941337723487857735749'
    || '82392662971551717371699516523289053822161240323885586618'
    || '40132355851360488286933379024914542292886670810961844960'
    || '91705183454067827731551705405381627380967602565625016981'
    || '48208341878316384911559022561000365235137034387446184837'
    || '87372381982248498634650331594100549747005931383392264972'
    || '49461751545728366702369745461014655997933798537483143786'
    || '841806593422227898388722980000748404719')::numeric,
      (repeat('0', 2000) || '.'
    || repeat('0', 1000) || '1')::numeric
  ),
  tupper(x, y, b) AS (
    SELECT
      x, y,
      0.5 < floor(mod(floor(y / 17) 
              * 2 ^ (-17 * x - mod(y, 17)), 2))
    FROM
      t1, 
      LATERAL (
        SELECT z + x AS x 
        FROM generate_series(0, 105) t2(x)) t2,
      LATERAL (
        SELECT z + k + y AS y 
        FROM generate_series(0, 16) t3(y)) t3
  )
SELECT string_agg(
  CASE WHEN b THEN '@@' ELSE '  ' END, ''
  ORDER BY x DESC)
FROM tupper
GROUP BY y
ORDER BY y ASC;

What’s the result of the above?

string_agg                                                                                                                                                                                                           |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
                @@                                      @@                                @@  @@@@  @@          @@                                @@    @@  @@          @@        @@  @@@@  @@            @@         |
                @@                                      @@  @@            @@              @@    @@  @@          @@                                @@    @@  @@          @@        @@    @@  @@            @@      @@ |
@@@@            @@                                    @@    @@            @@        @@@@  @@    @@  @@  @@  @@  @@  @@@@  @@@@@@@@    @@@@@@  @@@@@@  @@    @@  @@  @@  @@        @@    @@    @@            @@    @@ |
  @@            @@                                    @@    @@    @@  @@  @@              @@  @@    @@    @@    @@        @@  @@  @@  @@  @@  @@  @@  @@    @@  @@  @@  @@        @@  @@      @@            @@    @@ |
  @@            @@                                    @@    @@    @@  @@  @@              @@  @@    @@  @@  @@  @@        @@  @@  @@  @@@@@@  @@@@@@  @@    @@    @@    @@        @@  @@      @@            @@    @@ |
  @@            @@                              @@  @@      @@      @@    @@    @@@@                @@          @@                                    @@    @@  @@      @@    @@              @@      @@@@    @@  @@ |
@@@@@@      @@  @@                              @@  @@      @@    @@      @@  @@    @@              @@          @@                                      @@  @@          @@    @@            @@      @@    @@  @@  @@ |
          @@    @@  @@@@  @@      @@@@      @@@@@@  @@      @@            @@      @@                @@@@@@  @@@@@@                                      @@  @@@@@@  @@@@@@  @@              @@          @@    @@  @@ |
@@@@@@  @@      @@  @@  @@  @@  @@    @@  @@    @@  @@      @@  @@@@@@@@  @@    @@                                                                                                                    @@      @@  @@ |
          @@    @@  @@  @@  @@  @@    @@  @@    @@  @@      @@            @@  @@                                                                                                                    @@        @@  @@ |
@@@@        @@  @@  @@  @@  @@    @@@@      @@@@@@  @@      @@  @@  @@@@  @@  @@@@@@@@                                                                                                              @@@@@@@@  @@  @@ |
    @@          @@                                  @@      @@  @@    @@  @@                                                                                                                    @@            @@  @@ |
  @@            @@                                    @@    @@  @@    @@  @@                                                                                                                    @@          @@    @@ |
@@              @@                                    @@    @@  @@  @@    @@                                                                                                                  @@            @@    @@ |
@@@@@@          @@                                    @@    @@  @@  @@    @@                                                                                                                                @@    @@ |
                @@                                      @@  @@            @@                                                                                                                              @@      @@ |
                @@@@@@                                  @@  @@@@@@    @@@@@@                                                                                                                              @@  @@@@@@ |

It is a formula that can plot itself on a 17-bit wide bitmap. Cool, eh?

Play around with this formula yourself!

Hortonworks Community Connection (HCC) is an online collaboration destination for developers, DevOps, customers and partners to get answers to questions, collaborate on technical articles and share code examples from GitHub.  Join the discussion.

Topics:
big data ,mathematics ,sql ,self-referential formula

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}