【Caused by】Vertica error - too complex to analyze

2018.11.30

前言

某工程进行Vertica表间数据拼接，大致SQL语句如下：

insert into tableForInsert as select * from tableA union all select * from tableB ...

union all¹的表非常多，导致SQL语句过长，报错如下：

java.sql.SQLNonTransientException: [Vertica][VJDBC](4963) ERROR: The query contains a SET operation tree that is too complex to analyze

追根溯源

从Vertica的官方文档²得知，Vertica对于SQL语句有两条限制：

1. The first limit is based on the stack available to the expression. Vertica requires at least 100kb of free stack：第一条是关于SQL语句的大小，要求小于栈大小。这取决于Vertica的配置；
2. The second limit is the number of recursions possible in an analytic expression. The limit is 2000…cannot be increased：第二条是语句可能的递归次数不得超过2000，而且2000是上限，不可增加。

并且根据Vertica官方某博客³可推断，union all是一条递归语句：

Other implementations include ANSI SQL’s recursive SQL syntax using WITH and UNION ALL, special graph based algorithms and enumerated path technique

恍然大悟

综上，该语句报错的原因就是SQL语句的union all太多而导致超过2000的上限。最佳解决方案是修改SQL语句，弃用union all进行拼接，根本上避免了会超过上限的可能性；次之的方法，可以考虑将SQL语句缩短。

---

1. Vertica Union Clause ↩︎

2. Vertica Limits to SQL Expressions ↩︎

3. Can Vertica Climb a Tree? ↩︎