postgresqltable-functions

Unexpected behaviour using table functions over multivalued columns with same length

Using table functions I thought that you get new rows associated with the current row, each new row may have more than one column, and each column contains a single value of the values contained in the expression referenced (with it is evaluated against the current row).

Well maybe to long (and not quite well explained), you can run this example an see how the arrays' elements are combinded in the resulting row.

I mean, the id

 select 
    id,    
    unnest(numbers),
    unnest(letters)
  from
  (
    values
    (1, ARRAY[1,2], ARRAY['a','b', 'c']),
    (2, ARRAY[3,4], ARRAY['d','e', 'f'])
  ) as aTable(id,numbers,letters)

Yelling this:

1 Well there is kind of cartesian product taking the arrays (row by row) as like they were sets.

So, now you can run this.

 select 
    id,    
    unnest(numbers),
    unnest(letters)
  from
  (
    values
    (1, ARRAY[1,2], ARRAY['a','b']),
    (2, ARRAY[3,4], ARRAY['d','e'])
  ) as aTable(id,numbers,letters)

And this is the output:

2

I expected the same here, the kind of cartasian product, but I see that another combination criteria is being used. As a wise coworker and friend note the arrays length are the same and that probably causes another combination criteria. So I keep wondering what is happening here and ask here if anyone know or understand what is happeing here, and off course there will be a good "thanks for sacarme de esta amigo" if someone know how to "force" the cartasian products when the lenght of the arrays are the same (like this case) . I read some of the docs but find not clue.

UPDATE (After Laurenz's answer)

The way to make all combinations is to move the table functions into the from clause, using lateral joins can be a way:

   select id,number,letter
      from
      (
        values
        (1, ARRAY[1,2], ARRAY['a','b']),
        (2, ARRAY[3,4], ARRAY['d','e'])
      ) as aTable(id,numbers,letters)
      cross join lateral unnest(numbers) as number
      cross join lateral unnest(letters) as letter
Solution

  • You are using table functions in the SELECT list, which is supported, but strange. You should use them in the FROM clause of your query instead.

    Besides, the behavior of your query changed in PostgreSQL version 10 (and you must be using an older version). The release notes from v10 describe that:

    • Change the implementation of set-returning functions appearing in a query's SELECT list (Andres Freund)

      Set-returning functions are now evaluated before evaluation of scalar expressions in the SELECT list, much as though they had been placed in a LATERAL FROM-clause item. This allows saner semantics for cases where multiple set-returning functions are present. If they return different numbers of rows, the shorter results are extended to match the longest result by adding nulls. Previously the results were cycled until they all terminated at the same time, producing a number of rows equal to the least common multiple of the functions' periods. In addition, set-returning functions are now disallowed within CASE and COALESCE constructs. For more information see Section 37.4.8.

    Your query exhibits the old "cycling" behavior: if both functions return a table of two rows, you will get two result rows. If one returns m rows and the other n, the number of result rows will be the least common multiple of m and n.

    You should upgrade to a supported version of PostgreSQL.