如果我在Vertica中的复杂选择后提交()，为什么我会在pyodbc hy000错误

Question

我一直在追逐这个错误一周：

pyodbc.Error: ('HY000', '[HY000] [Vertica][VerticaDSII] (20) An error occurred during query execution:  (20) (SQLFetch)')

经过大量的反复试验，关键是此特定SELECT查询（简单的问题）的组合，加上curs.execute()后commit()的调用。我真的很想知道您的想法。下面（接下来是配置信息）就是一个示例。了解为什么发生这种情况的任何帮助将不胜感激。我很乐意提供更多细节。

版本：

• python：3.3.2 [GCC 4.4.6 20120305（Red Hat 4.4.6-4）]在Linux上
• unixODBC：2.2.14
• Vertica：Vertica Analytic数据库V7.0.0-0
• OS：Linux版本2.6.32-279.14.1.el6.x86_64（[e-mail]）（GCC版本4.4.6 20120305（Red Hat 4.4.6-4）（GCC）（GCC））

来源：

import pyodbc
query = '''
SELECT relVarTable0.id AS id, relVarTable1.val, relVarTable2.val, relVarTable3.val, relVarTable4.val
FROM (SELECT id FROM B) relVarTable0
LEFT JOIN
(SELECT B1.id AS id, AVG(A1.X) AS val
FROM B B1, AB AB1, A A1
WHERE B1.id = AB1.B_id AND A1.id = AB1.A_id AND A1.X IS NOT NULL
GROUP BY B1.id) relVarTable1
    ON relVarTable0.id = relVarTable1.id
LEFT JOIN
(SELECT B1.id AS id, B1.Y AS val
FROM B B1
WHERE B1.Y IS NOT NULL ) relVarTable2
    ON relVarTable0.id = relVarTable2.id
LEFT JOIN
(SELECT B1.id AS id, AVG(A1.W) AS val
FROM B B1, AB AB1, A A1
WHERE B1.id = AB1.B_id AND A1.id = AB1.A_id AND A1.W IS NOT NULL
GROUP BY B1.id) relVarTable3
    ON relVarTable0.id = relVarTable3.id
LEFT JOIN
(SELECT B1.id AS id, AVG(C1.Z) AS val
FROM B B1, BC BC1, C C1
WHERE B1.id = BC1.B_id AND C1.id = BC1.C_id AND C1.Z IS NOT NULL
GROUP BY B1.id) relVarTable4
    ON relVarTable0.id = relVarTable4.id;
'''
conn = pyodbc.connect('DSN=vertica_kdl_dsn;UserName=__;Password=__)
curs = conn.cursor()
curs.execute('SET search_path TO "$user", rcd_test, public, v_catalog, v_monitor, v_internal;')
curs.execute(query)
curs.commit()           # comment out to get no error
rows = curs.fetchall()  # if above commit called: pyodbc.Error: ('HY000', '[HY000] [Vertica][VerticaDSII] (20) An error occurred during query execution:  (20) (SQLFetch)')
curs.close()

Answer 1

fyi我在Vertica社区论坛上找到了一个简单的解决方案：

在dsn [vertica_kdl_dsn]下的odbc.ini中添加resultbuffersize = 0

（HY000不是一个特别有用的错误消息！）