Memory allocation failed: How to combine four result sets into one table

Question

I have four tables. Every table has just one column with 32768 rows, like:

|calculated|
|2.45644534|
|3.23323567|
[...]

Now I want to combine these four results/tables into one table with four columns, like:

|calc1|calc2|calc3|calc4|
[values]

There are no IDs or something else to identify unique rows. This is my query:

   SELECT A.*, B.*, C.*, D.*
    FROM
        (
            SELECT * FROM :REAL_RESULT
        ) AS A
    JOIN
        (
            SELECT * FROM :PHASE_RESULT
        ) AS B
    ON 1=1
    JOIN
        (
            SELECT * FROM :AMPLITUDE_RESULT
        ) AS C
    ON 1=1 [...]

Now the server is throwing this error:

Error: (dberror) 2048 - column store error: search table error: "TEST"."data::fourier": line 58 col 4 (at pos 1655): [2048] (range 3): column store error: search table error: [9] Memory allocation failed

What can I do now? Are there any other options? Thanks!

Answer 1

what you do in your original code is effectively a cross join on four tables, each containing 2^15 rows. The result size would contain 2^60 rows, quite a few petabyte... That's the reason for the OOM. I used a similar example to show colleagues what can happen when joining big tables with the wrong the join condition. Besides that, SQL is set based and your rows do not have any natural order.

If the tables are column store tables, you could technically join on the internal column $rowid$. But $rowid$ is not officially documented and I can therefore not recommend using it.

A clean solution is the one suggested by Craig. I would probably use an IDENTITY column.

Answer 2

If this cross join was not your original intention, but you wanted join a list of values without any actual join condition you might try UNION:

SELECT COLUMN,0,0,0 from A
union all
SELECT 0,COLUMN,0,0 from B
union all
SELECT 0,0,COLUMN,0 from C
union all
SELECT 0,0,0,COLUMN from D

The output will be the sum of all records for these tables.