#289 DELETE query with non-pushable clauses does not work

1.0 Dev Q
closed
None
5
2012-04-27
2012-04-06
No

1. Run the attached SampleDb.sql
2. create or replace function f(id int) returns int as
$$begin return 3;end $$ language plpgsql;

3. The delete queries as shown below will return the same error.
DELETE FROM EMPLOYEE E WHERE EDLEVEL > f(2);
DELETE FROM EMPLOYEE E WHERE EDLEVEL > ( SELECT AVG(EDLEVEL) FROM EMPLOYEE WHERE WORKDEPT = E.WORKDEPT );
DELETE FROM EMPLOYEE E WHERE SALARY > ( SELECT AVG(SALARY) FROM EMPLOYEE WHERE SUBSTRING(E.WORKDEPT,1,1) = SUBSTRING(WORKDEPT, 1,1) );

All of the above return : ERROR: attribute number 12 exceeds number of columns 2

Basically, when the WHERE clause is not-pushable, we get this error.

Also, the fix for this should test correctness in case of replicated tables.

Discussion

  • Amit Khandekar

    Amit Khandekar - 2012-04-06

    This script creates the tables required to reproduce.

     
  • Amit Khandekar

    Amit Khandekar - 2012-04-27
    • status: open --> closed
     
  • Amit Khandekar

    Amit Khandekar - 2012-04-27

    Issue #1. For a delete statement, the SELECT statement generated has only those columns that are required, not all columns from the table, and that too, the order of the target list might be different than the table columns. For e.g., for a table tab (id1 int, id2 int, id3 int), if the delete stmt is:
    DELETE from tab where id3 > volatile_func(3);
    Say, the select clause would have something like this:
    select id3, ctid, xc_nodeid from tab.
    Now the WHERE clause (id3 > volatile(func(3)) has var with varattno = 3 because this is the physical table attribute number for id3. But when the qual is applied, it applies it with the third attribute in the result slot, and that third attribute is xc_nodeid, not id3.
    For a normal select statement, the select target list always matches with the table attributes, so the WHERE clause gets correctly applied. So this happens only for delete and update.

    Fix:

    At the time of applying the quals, re-arranged the slot values into an intermediate slot, so as to make the values ordered in line with the order of the table attributes, and then applied the qual.

    Basically, in ExecRemoteQuery(), for a single relation scan, the qual attnos refer to the attribute no. of the actual table descriptor. So when they are applied to a tuple slot, the tuple slot descriptor also should match the table desc. But the result slot corresponds to the order of target list which need not coincide with the order of table attributes. So we need to rearrange the values from result slot into an intermediate slot (new field RemoteQueryState->fulltupleslot) and then apply the quals on fulltupleslot. And to rearrange the values, we need to save the targetlist vars to be stored (in new field RemoteQueryState->tlist_vars).

    If it is not a single table scan, it should be a reduced scan, for e.g. reduced remote join. In this case, the qual references are already adjusted (set_remote_references) according to their position in targetlist, so they can be safely applied to the resultslot without the need of an intermediate slot.

    Issue #2. The target list generated in rewriteTargetListUD() uses pull_var_clause to extract the attributes used by quals so that those are essentially added up in the target list in the select statement in RemoteQuery scan. To extract these attributes from quals, we use pull_var_clause.
    But it was found that pull_var_clause() does not extract vars from subquery:
    DELETE FROM EMPLOYEE E WHERE EDLEVEL > ( SELECT AVG(EDLEVEL) FROM EMPLOYEE WHERE WORKDEPT = E.WORKDEPT );

    Fix:

    Unfortunately we do not have a walker that exactly fulfills our requirements here, so a similar walker had to be written that also pulls vars from subquery.

     

Log in to post a comment.

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks