SQL query bug for basic column operations - BUG 'rename' method.

[oualib]

Discussed in #1140

^{Originally posted by mail4umar January 31, 2024}
The following lines of code produces an error:

vdf = vp.vDataFrame({"a":[1,2,3]})
vdf["a"].rename("b")
vdf["c"] = "1"
vdf

The Error is:

MissingColumn: Severity: ERROR, Message: Column "a" does not exist, Sqlstate: 42703, Routine: transformSQLColumnRef, File: /data/jenkins/workspace/RE-ReleaseBuilds/RE-Laserbeam_2/server/vertica/Optimizer/OptUtil/ExprUtil.cpp, Line: 7389, Error Code: 2624, SQL: 'SELECT                      "b" AS "b", "c" AS "c"                  FROM (SELECT "a" AS "b", "c" FROM (                 SELECT                      1 AS "c"                  FROM ((SELECT 1 AS "a") UNION ALL (SELECT 2 AS "a") UNION ALL (SELECT 3 AS "a")) VERTICAPY_SUBTABLE) VERTICAPY_SUBTABLE) VERTICAPY_SUBTABLE                                   LIMIT 100 OFFSET 0'

Upon investigation and looking at the SQL I can see that there is one line incorrect:

SELECT
  "b" AS "b",
  "c" AS "c"
FROM
  (
    SELECT
      "a" AS "b",
      "c"
    FROM
      (
        SELECT
          1 AS "c",
        FROM
          (
            (
              SELECT
                1 AS "a") UNION ALL (
              SELECT
                2 AS "a") UNION ALL (
              SELECT
                3 AS "a"
            )
          ) AS VERTICAPY_SUBTABLE_1
      ) AS VERTICAPY_SUBTABLE_2
  ) AS VERTICAPY_SUBTABLE_3
LIMIT 100
OFFSET 0;

In the third nested SELECT, there is no mention of column "a".

The correct query should be:

SELECT
  "b" AS "b",
  "c" AS "c"
FROM
  (
    SELECT
      "a" AS "b",
      "c"
    FROM
      (
        SELECT
          1 AS "c",
          "a" AS "a"
        FROM
          (
            (
              SELECT
                1 AS "a") UNION ALL (
              SELECT
                2 AS "a") UNION ALL (
              SELECT
                3 AS "a"
            )
          ) AS VERTICAPY_SUBTABLE_1
      ) AS VERTICAPY_SUBTABLE_2
  ) AS VERTICAPY_SUBTABLE_3
LIMIT 100
OFFSET 0;

Note the above only happens when a new column is created using SQL string.