WITH Clause in SELECT with empty result set

Binh_1

Hi,

I am trying to use the WITH clause in a SELECT.  Documentation is at https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/ProgrammersGuide/Queries/WITHClausesInSELECT.htm .

My query looks like this.

WITH
    with_clause_1 AS (SELECT  ...    ),
    with_clause_2 AS (SELECT  ...    )

SELECT *
FROM with_clause_1, with_clause_2

 

If both *with_clauses* return values, then the query works fine.  However, if one of them do not have any rows (e.g. with_clause_1 returns 0 row), then the result of the whole query returns zero row.

 

Does anyone know how I can write the query such that rows will always be returned if at least 1 of the clauses return a row?  For the clauses that return 0 row, it's ok to return a static value too, so something like NULL is fine to indicate 0 row returned.

Is this a bug with this WITH clause then?

Binh_1

I just found a workaround by doing a UNION with DUAL, but it requires defining a specific value for each column in the query.  It would still be nice to not have to do this.

Binh_1

Actually, the UNION with DUAL will only address the case where one of the *with_clauses* return an empy result set.  But if the with_clause has values, I'm not gettingn the same result set as running the whole query vs. running the "select for the with_clause UNION DUAL".

Binh_1

To summarize, the original problem is still there and not resolved.  Introducing the UNION with DUAL just ensures that the result set returned is never empty.

id10t

Review SQL syntax

FROM with_clause_1, with_clause_2

comma means join(and don't invent a new syntax - UNION DUAL). Try next:

WITH set1 AS(SELECT * FROM Customer),
     set2 AS(SELECT * FROM Customer)
SELECT * FROM set1
     UNION ALL
SELECT * FROM set2;

daniel=> \e
 id | name |  birthday  
----+------+------------
  1 | Dan  | 1983-01-08
  2 | Ian  | 1984-01-09
  3 | Lee  | 1985-02-10
  1 | Dan  | 1983-01-08
  2 | Ian  | 1984-01-09
  3 | Lee  | 1985-02-10
(6 rows)

Binh_1

Since you're using a union, it requires the columns for set1 and set2 to be of the same data type.  This means you can't have set1 return a column of varchar but set2 have an integer column.  With what I did originally, it works fine even if the data types are different, but the problem is these with_clauses must have data!

Also, I don't understand why the WITH clause is used in this case then if you're just doing a union.  Alternative, you can just have a union for all those selects in the with_clauses.  The only "advantage" I see is that you get to use these named queries in the union.

Binh_1

I am now able to accomplish this using a FULL OUTER JOIN on set1 and set2, by adding a common column across these "sets" that is used for the join. 

I will update if there are any other issues.

Thanks id10t for pointing me in the right direction.