With子句在Oracle数据库中不按预期工作

Question

我正在使用pypika构建一些查询。它过去工作得很好，但是我对oracle数据库上的子查询有一个问题。查询如下所示

fake_query = Query().from_(my_table).where(my_table.ID == "12345").select(my_table.ID)

QN = AliasedQuery("fake_query_with")

query = (
    Query()
    .with_(fake_query, "fake_query_with")
    .from_(QN)
    .select(
        QN.star. # problematic line
    )
)

df_temp = claim_conn.read_dataframe(query.get_sql())

所以当我运行这个脚本时，我得到了一个错误：

DatabaseError: ORA-00904: "fake_query_with": invalid identifier

pypika查询将按预期转换为字符串。

WITH fake_query_with AS 
(
   SELECT "ID" 
   FROM "MYTABLE" 
   WHERE "ID"=12345
) 
SELECT "fake_query_with".* 
FROM fake_query_with

所以这个查询失败了，但是如果我用简单的QN.star替换*，它就能工作，但是我当然失去了别名的有趣用法。我知道这只是一个虚拟片段，但它只是为了证明错误。

Answer 1

正如@astentx在一条评论中所说的，这看起来像PyPika中的一个bug。

您可以(可能)通过在代码中使用大写字母(即两个地方的"FAKE_QUERY_WITH" )来解决这个问题。

QN = AliasedQuery("FAKE_QUERY_WITH")

query = (
    Query()
    .with_(fake_query, "FAKE_QUERY_WITH")
    .from_(QN)
    .select(
        QN.star. # problematic line
    )
)

然后生成的SQL应该如下所示：

WITH FAKE_QUERY_WITH AS 
(
   SELECT "ID" 
   FROM "MYTABLE" 
   WHERE "ID"=12345
) 
SELECT "FAKE_QUERY_WITH".* 
FROM FAKE_QUERY_WITH

而引号和未引号标识符之间的区别将是毫无意义的。

小提琴显示了修改后的SQL有效--在PyPika中未经过测试，但希望这实际上是它将生成的.

Answer 2

我不知道Python和Pypika，但是--从Oracle的角度来看--这就是这里的错误:使用(邪恶！)双引号。

在Oracle中，默认情况下，所有“标识符”(表名、列名、过程、函数.)以大写格式存储在数据字典中。然后，您可以任意引用它们:上、下或MIxeD CaSe --没有区别，一切都会正常工作。

但是，如果将名称括在双引号中，那么每次都必须使用双引号，按照创建对象时使用的字母大小写--否则，它将无法工作。

例如：

SQL> create table mytable as
  2    select 12345 id,
  3           'Littlefoot' "NamE"
  4  from dual;

Table created.

SQL> select table_name from user_tables where table_name = 'MYTABLE';

TABLE_NAME
------------------------------
MYTABLE                            --> stored in UPPERCASE

SQL> select column_name from user_tab_columns where table_name = 'MYTABLE';

COLUMN_NAME
------------------------------ 
ID                                --> stored in UPPERCASE because there were no double quotes
NamE                              --> mixed case, as I enclosed the name into double quotes

SQL> select id from mytable;      --> reference it any way you want

        ID
----------
     12345

SQL> select name from mytable;    --> reference it exactly as it was created
select name from mytable
       *
ERROR at line 1:
ORA-00904: "NAME": invalid identifier


SQL> select "NamE" from mytable;

NamE
----------
Littlefoot

SQL>

就你而言：

SQL> WITH fake_query_with AS         --> CTE named using lower case, but no double quotes
  2  (
  3     SELECT "ID"
  4     FROM "MYTABLE"
  5     WHERE "ID"=12345
  6  )
  7  SELECT "fake_query_with".*      --> double quotes, lower case
  8  FROM fake_query_with;
SELECT "fake_query_with".*      
                         *
ERROR at line 7:
ORA-00904: "fake_query_with": invalid identifier


SQL>

如果你想让它起作用，那么要么a)去掉双引号(如果可以，也许Py*需要双引号，不能说)(这是我真诚的建议)，或者b)到处使用双引号

( a)没有双引号

SQL> WITH
  2     fake_query_with
  3     AS
  4        (SELECT id
  5           FROM mytable
  6          WHERE id = 12345)
  7  SELECT fake_query_with.*
  8    FROM fake_query_with;

        ID
----------
     12345

SQL>

( b)双引号，处处

SQL> WITH
  2     "fake_query_with"
  3     AS
  4        (SELECT "ID"
  5           FROM "MYTABLE"
  6          WHERE "ID" = 12345)
  7  SELECT "fake_query_with".*
  8    FROM "fake_query_with";

        ID
----------
     12345

SQL>

看看能不能帮上忙