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!

Start by adding columns to use as an IDs .... It makes many problems go away. `Just do it!` **Do it now!** ;) — Disillusioned, Dec 20 '16 at 09:34
@CraigYoung well... okay.. thats works fine. Thanks.. If you want to earn some points, add this as the answer... just do it ;) — Tobias, Dec 20 '16 at 09:48
:) Well it didn't really answer your question; glad it nudged you on the way though. Unfortunately, I'm logging off now; so no can do. — Disillusioned, Dec 20 '16 at 09:53

score 0 · Answer 1 · answered Dec 21 '16 at 22:24

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.

score 0 · Answer 2 · answered Jan 25 '17 at 14:59

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.

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

2 Answers2