Support field references

[iafuture]

e.g. UPDATE X SET Field1 = Field2 + 1 WHERE pkf1=1 AND pkf2=2;

[jerryhfeng]

Investigation on design/breakdown first.

[johnfangAFW]

postgres=# explain Update DISTRICT SET D_NEXT_O_ID = D_NEXT_O_ID + 1 WHERE D_W_ID = 1 AND D_ID = 2; QUERY PLAN

Update on district (cost=0.00..4.12 rows=1 width=346) -> Result (cost=0.00..4.12 rows=1 width=346)

(gdb) break ybcExpr.c:YBCNewEvalExprCall Breakpoint 1 at 0x5626834a86f9: file /build/src/k2/postgres/src/backend/executor/ybcExpr.c, line 62.

(gdb) l 57
58 YBCPgExpr YBCNewEvalExprCall(YBCPgStatement ybc_stmt, 59 Expr pg_expr, 60 int32_t attno, 61 int32_t typid, 62 int32_t typmod) { 63 YBCPgExpr ybc_expr = NULL; 64 const YBCPgTypeEntity type_ent = YBCDataTypeFromOidMod(InvalidAttrNumber, typid); 65 YBCPgNewOperator(ybc_stmt, "eval_expr_call", type_ent, &ybc_expr); 66
(gdb) p pg_expr $1 = (Expr ) 0x562685b9aee8 (gdb) p pg_expr $2 = {type = T_OpExpr} (gdb) p attno $3 = 5 (gdb) p typid $4 = 23 (gdb) p typmod $5 = -1 (gdb) n 65 YBCPgNewOperator(ybc_stmt, "eval_expr_call", type_ent, &ybc_expr); (gdb) s k2pg::gate::YBCPgNewOperator (stmt=stmt@entry=0x562685b9c4c0, opname=opname@entry=0x562683d9f4a3 "eval_expr_call", type_entity=0x562684264618 <YBCTypeEntityTable+280>, op_handle=op_handle@entry=0x7ffdaa84b640) at /build/src/k2/connector/pggate/pg_gate_api.cc:878 878 YBCPgExpr *op_handle){ (gdb) n 880 return ToYBCStatus(api_impl->NewOperator(stmt, opname, type_entity, op_handle)); (gdb) s k2pg::gate::PgGateApiImpl::NewOperator (this=0x5626859451d0, stmt=stmt@entry=0x562685b9c4c0, opname=opname@entry=0x562683d9f4a3 "eval_expr_call", type_entity=0x562684264618 <YBCTypeEntityTable+280>, op_handle=op_handle@entry=0x7ffdaa84b640) at /build/src/k2/connector/pggate/pg_gate_impl.cc:813 813 PgExpr **op_handle) {

(gdb) p str $5 = { data = 0x562685b98d08 "{OPEXPR :opno 551 :opfuncid 177 :opresulttype 23 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 5 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattn"..., len = 380, maxlen = 1024, cursor = 0}

(gdb) p (PgConstant )arg $4 = { = {vptr.PgExpr = 0x7f5d9cb2d490 <vtable for k2pg::sql::PgConstant+16>, opcode = k2pg::sql::PgExpr::Opcode::PG_EXPR_CONSTANT, typeentity = 0x558231925ba8 <YBCTypeEntityTable+5800>, typeattrs = {typmod = 0}}, value = {type = k2pg::sql::SqlValue::SLICE, data_ = {{boolval = false, intval = 0, floatval = 0, doubleval = 0}, sliceval = "{OPEXPR :opno 551 :opfuncid 177 :opresulttype 23 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 5 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattn"...}, nullvalue = false}}

(gdb) s _outOpExpr (str=str@entry=0x7ffd7a06f700, node=node@entry=0x55823277ec78) at /build/src/k2/postgres/src/backend/nodes/outfuncs.c:1304 1304 WRITE_NODE_TYPE("OPEXPR"); (gdb) p str $8 = { data = 0x55823277bd58 "{OPEXPR :opno 551 :opfuncid 177 :opresulttype 23 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 5 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnoold 1 :varoattn"..., len = 380, maxlen = 1024, cursor = 0}

[johnfangAFW]

YBCPgExpr YBCNewEvalExprCall(YBCPgStatement ybc_stmt, Expr pg_expr, int32_t attno, int32_t typid, int32_t typmod) { YBCPgExpr ybc_expr = NULL; const YBCPgTypeEntity type_ent = YBCDataTypeFromOidMod(InvalidAttrNumber, typid); YBCPgNewOperator(ybc_stmt, "eval_expr_call", type_ent, &ybc_expr);

Datum expr_datum = CStringGetDatum(nodeToString(pg_expr));
YBCPgExpr expr = YBCNewConstant(ybc_stmt, CSTRINGOID, expr_datum , /* IsNull */ false);
YBCPgOperatorAppendArg(ybc_expr, expr);

/*
 * Adding the column type id and mod to the message since we only have the YQL types in the
 * DocDB Schema.
 * TODO(mihnea): Eventually DocDB should know the full YSQL/PG types and we can remove this.
 */
YBCPgExpr attno_expr = YBCNewConstant(ybc_stmt, INT4OID, (Datum) attno, /* IsNull */ false);
YBCPgOperatorAppendArg(ybc_expr, attno_expr);
YBCPgExpr typid_expr = YBCNewConstant(ybc_stmt, INT4OID, (Datum) typid, /* IsNull */ false);
YBCPgOperatorAppendArg(ybc_expr, typid_expr);
YBCPgExpr typmod_expr = YBCNewConstant(ybc_stmt, INT4OID, (Datum) typmod, /* IsNull */ false);
YBCPgOperatorAppendArg(ybc_expr, typmod_expr);

return ybc_expr;

}

[johnfangAFW]

bool YBCExecuteUpdate(Relation rel, TupleTableSlot slot, HeapTuple tuple, EState estate, ModifyTableState mtstate, Bitmapset updatedCols) { TupleDesc tupleDesc = slot->tts_tupleDescriptor; Oid dboid = YBCGetDatabaseOid(rel); Oid relid = RelationGetRelid(rel); YBCPgStatement update_stmt = NULL; bool isSingleRow = mtstate->yb_mt_is_single_row_update_or_delete; Datum ybctid = 0;

/* Create update statement. */
HandleYBStatus(YBCPgNewUpdate(dboid,
                              relid,
                              estate->es_yb_is_single_row_modify_txn,
                              &update_stmt));

/*
 * Look for ybctid. Raise error if ybctid is not found.
 *
 * If single row update, generate ybctid from tuple values, otherwise
 * retrieve it from the slot.
 */
if (isSingleRow)
{
    ybctid = YBCGetYBTupleIdFromTuple(update_stmt,
                                      rel,
                                      tuple,
                                      slot->tts_tupleDescriptor);
}
else
{
    ybctid = YBCGetYBTupleIdFromSlot(slot);
}

if (ybctid == 0)
{
    ereport(ERROR,
            (errcode(ERRCODE_UNDEFINED_COLUMN), errmsg(
                "Missing column ybctid in UPDATE request to YugaByte database")));
}

/* Bind ybctid to identify the current row. */
YBCPgExpr ybctid_expr = YBCNewConstant(update_stmt, BYTEAOID, ybctid,
                                       false /* is_null */);
HandleYBStatus(YBCPgDmlBindColumn(update_stmt, YBTupleIdAttributeNumber, ybctid_expr));

/* Assign new values to the updated columns for the current row. */
tupleDesc = RelationGetDescr(rel);
bool whole_row = bms_is_member(InvalidAttrNumber, updatedCols);

ModifyTable *mt_plan = (ModifyTable *) mtstate->ps.plan;
ListCell* pushdown_lc = list_head(mt_plan->ybPushdownTlist);

for (int idx = 0; idx < tupleDesc->natts; idx++)
{
    FormData_pg_attribute *att_desc = TupleDescAttr(tupleDesc, idx);

    AttrNumber attnum = att_desc->attnum;
    int32_t type_id = att_desc->atttypid;
    int32_t type_mod = att_desc->atttypmod;

    /* Skip virtual (system) and dropped columns */
    if (!IsRealYBColumn(rel, attnum))
        continue;

    /*
     * Skip unmodified columns if possible.
     * Note: we only do this for the single-row case, as otherwise there
     * might be triggers that modify the heap tuple to set (other) columns
     * (e.g. using the SPI module functions).
     */
    int bms_idx = attnum - YBGetFirstLowInvalidAttributeNumber(rel);
    if (isSingleRow && !whole_row && !bms_is_member(bms_idx, updatedCols))
        continue;

    /* Assign this attr's value, handle expression pushdown if needed. */
    if (pushdown_lc != NULL &&
        ((TargetEntry *) lfirst(pushdown_lc))->resno == attnum)
    {
        TargetEntry *tle = (TargetEntry *) lfirst(pushdown_lc);
        Expr *expr = copyObject(tle->expr);
        YBCExprInstantiateParams(expr, estate->es_param_list_info);

        YBCPgExpr ybc_expr = YBCNewEvalExprCall(update_stmt, expr, attnum, type_id, type_mod);

        HandleYBStatus(YBCPgDmlAssignColumn(update_stmt, attnum, ybc_expr));

        pushdown_lc = lnext(pushdown_lc);
    }
    else
    {
        bool is_null = false;
        Datum d = heap_getattr(tuple, attnum, tupleDesc, &is_null);
        YBCPgExpr ybc_expr = YBCNewConstant(update_stmt, type_id,
                                            d, is_null);

        HandleYBStatus(YBCPgDmlAssignColumn(update_stmt, attnum, ybc_expr));
    }
}

/* Execute the statement. */
int rows_affected_count = 0;
YBCExecWriteStmt(update_stmt, rel, isSingleRow ? &rows_affected_count : NULL);

/* Cleanup. */
update_stmt = NULL;

/*
 * If the relation has indexes, save the ybctid to insert the updated row into the indexes.
 */
if (YBRelHasSecondaryIndices(rel))
{
    tuple->t_ybctid = ybctid;
}

return !isSingleRow || rows_affected_count > 0;

}

[johnfangAFW]

1) PG internal function calls 2) how YB generated the pushdown list 3) Whether YB supported custom function calls

[johnfangAFW]

1) The list of buildint functions inside PG are listed in fmgrtab.c

*

• fmgrtab.c
• The function manager's table of internal functions.
  • It has been GENERATED by src/backend/utils/Gen_fmgrtab.pl

For example for the function in the described SQL: opfuncid 177, it was

{ 177, "int4pl", 2, true, false, int4pl },

which was defined in int.c

• int.c
• Functions for the built-in integer types (except int8).
  • IDENTIFICATION
• src/backend/utils/adt/int.c

as follows

Datum int4pl(PG_FUNCTION_ARGS) { int32 arg1 = PG_GETARG_INT32(0); int32 arg2 = PG_GETARG_INT32(1); int32 result;

if (unlikely(pg_add_s32_overflow(arg1, arg2, &result)))
    ereport(ERROR,
            (errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
             errmsg("integer out of range")));
PG_RETURN_INT32(result);

}

[johnfangAFW]

The logic for YB to decide whether it should pushdown functions are as follows as in ybcplan.c

/*

• Check if the function/procedure can be executed by DocDB (i.e. if we can
• pushdown its execution).
• The main current limitation is that DocDB's execution layer does not have

• syscatalog access (cache lookup) so only specific builtins are supported.q */ static bool YBCIsSupportedDocDBFunctionId(Oid funcid, Form_pg_proc pg_proc) { if (!is_builtin_func(funcid)) { return false; }

  /*

  • Polymorhipc pseduo-types (e.g. anyarray) may require additional
  • processing (requiring syscatalog access) to fully resolve to a concrete
  • type. Therefore they are not supported by DocDB. */ if (IsPolymorphicType(pg_proc->prorettype)) { return false; }

  for (int i = 0; i < pg_proc->pronargs; i++) { if (IsPolymorphicType(pg_proc->proargtypes.values[i])) { return false; } }

  return true; }

as we could see, it checks whether the function is a built-in one and then it is not a Polymorhipc pseduo-types (e.g. anyarray), which may require additional processing (requiring syscatalog access) to fully resolve to a concrete type.

The built-in function check was in fmgr.h/.c, which defines the Postgres function manager and function-call interface.

bool is_builtin_func(Oid id) { return fmgr_isbuiltin(id) != NULL; }

/*

• Lookup routines for builtin-function table. We can search by either Oid
• or name, but search by Oid is much faster. */

static const FmgrBuiltin * fmgr_isbuiltin(Oid id) { uint16 index;

/* fast lookup only possible if original oid still assigned */
if (id >= FirstBootstrapObjectId)
    return NULL;

/*
 * Lookup function data. If there's a miss in that range it's likely a
 * nonexistant function, returning NULL here will trigger an ERROR later.
 */
index = fmgr_builtin_oid_index[id];
if (index == InvalidOidBuiltinMapping)
    return NULL;

return &fmgr_builtins[index];

}

The fmgr_builtin_oid_index was populated by the files such as fmgroids.h, fmgrprotos.h, and fmgrtab.c generated by src/backend/utils/Gen_fmgrtab.pl from pg_proc.dat.

[johnfangAFW]

PG supports Create Function to define a custom function

https://www.postgresql.org/docs/11/sql-createfunction.html

Seems YB also supports that

https://docs.yugabyte.com/latest/api/ysql/the-sql-language/statements/ddl_create_function/

Need to find out how the functions including custom ones are evaluated in YB's DocDB.

[johnfangAFW]

The mechanism for YB's Docdb to eval the function is 1) build a c module called ybgate_api to include the expression evaluation related PG code 2) Docdb reconstruct the PG Partial PG expr node or subtree from the serialized string 3) Walk the expr subtree to either eval a value or call the built-in functions based on PG internal logic.

That is to say, YB's Docdb has dependency on a submode of PG, e.g., ybgate_api.

ybgate_api.h // This file includes C/C++ wrappers around some PG utilities such that it can be be included // into YB C++ codebases. // Specifically, it is currently used by DocDB to evaluate YSQL expresison (pushed down from // the query layer).

include "yb/yql/pggate/ybc_pg_typedefs.h"

ifdef __cplusplus

typedef void* YbgExprContext;

else

typedef struct YbgExprContextData* YbgExprContext;

endif

/*

• Create an expression context to evaluate expressions against. / YbgStatus YbgExprContextCreate(int32_t min_attno, int32_t max_attno, YbgExprContext expr_ctx);

/*

• Add a column value from the table row.
• Used by expression evaluation to resolve scan variables. */ YbgStatus YbgExprContextAddColValue(YbgExprContext expr_ctx, int32_t attno, uint64_t datum, bool is_null);

/*

• Evaluate an expression, using the expression context to resolve scan variables.
• Will filling in datum and is_null with the result. / YbgStatus YbgEvalExpr(char expr_cstring, YbgExprContext expr_ctx, uint64_t datum, bool is_null);

/*

• Given a 'datum' of array type, split datum into individual elements of type 'type' and store
• the result in 'result_datum_array', with number of elements in 'nelems'. This will error out
• if 'type' doesn't match the type of the individual elements in 'datum'. Memory for
• 'result_datum_array' will be allocated in this function itself, pre-allocation is not needed. */ YbgStatus YbgSplitArrayDatum(uint64_t datum, int type, uint64_t *result_datum_array, int nelems);

[johnfangAFW]

ybgate_api.c

include "postgres.h"

include

include "ybgate/ybgate_api.h"

include "catalog/pg_type.h"

include "catalog/pg_type_d.h"

include "catalog/ybctype.h"

include "common/int.h"

include "executor/execExpr.h"

include "executor/executor.h"

include "nodes/execnodes.h"

include "nodes/makefuncs.h"

include "nodes/primnodes.h"

include "utils/memutils.h"

include "utils/numeric.h"

struct YbgExprContextData { // Values from table row. int32_t min_attno; int32_t max_attno; Datum attr_vals; Bitmapset attr_nulls; };

The expr eval logic is in the following tree walk logic.

/*

• Evaluate an expression against an expression context.
• Currently assumes the expression has been checked by the planner to only
• allow immutable functions and the node types handled below.
• TODO: this should use the general YSQL/PG expression evaluation framework, but

• that requires syscaches and other dependencies to be fully initialized. / static Datum evalExpr(YbgExprContext ctx, Expr expr, bool is_null) { switch (expr->type) { case T_FuncExpr: case T_OpExpr: { Oid funcid = InvalidOid; List args = NULL; ListCell *lc = NULL;

      /* Get the (underlying) function info. */
      if (IsA(expr, FuncExpr))
      {
          FuncExpr *func_expr = castNode(FuncExpr, expr);
          args = func_expr->args;
          funcid = func_expr->funcid;
      }
      else if (IsA(expr, OpExpr))
      {
          OpExpr *op_expr = castNode(OpExpr, expr);
          args = op_expr->args;
          funcid = op_expr->opfuncid;
      }
  
      FmgrInfo *flinfo = palloc0(sizeof(FmgrInfo));
      FunctionCallInfo fcinfo = palloc0(SizeForFunctionCallInfo(args->length));
  
      fmgr_info(funcid, flinfo);
      InitFunctionCallInfoData(*fcinfo,
                               flinfo,
                               args->length,
                               InvalidOid,
                               NULL,
                               NULL);
      int i = 0;
      foreach(lc, args)
      {
          Expr *arg = (Expr *) lfirst(lc);
          fcinfo->args[i].value = evalExpr(ctx, arg, &fcinfo->args[i].isnull);
          /*
           * Strict functions are guaranteed to return NULL if any of
           * their arguments are NULL.
           */
          if (flinfo->fn_strict && fcinfo->args[i].isnull) {
              *is_null = true;
              return (Datum) 0;
          }
          i++;
      }
      Datum result = **FunctionCallInvoke**(fcinfo);
      *is_null = fcinfo->isnull;
      return result;
  }
  case T_RelabelType:
  {
      RelabelType *rt = castNode(RelabelType, expr);
      return evalExpr(ctx, rt->arg, is_null);
  }
  case T_Const:
  {
      Const* const_expr = castNode(Const, expr);
      *is_null = const_expr->constisnull;
      return const_expr->constvalue;
  }
  case T_Var:
  {
      Var* var_expr = castNode(Var, expr);
      int32_t att_idx = var_expr->varattno - ctx->min_attno;
      *is_null = bms_is_member(att_idx, ctx->attr_nulls);
      return ctx->attr_vals[att_idx];
  }
  default:
      /* Planner should ensure we never get here. */
      ereport(ERROR,
          (errcode(ERRCODE_INTERNAL_ERROR), errmsg(
          "Unsupported YSQL expression received by DocDB")));
      break;

  } *is_null = true; return (Datum) 0; }

where FunctionCallInvoke was defined in fmgr.h

/*

• This macro invokes a function given a filled-in FunctionCallInfoBaseData
• struct. The macro result is the returned Datum --- but note that
• caller must still check fcinfo->isnull! Also, if function is strict,
• it is caller's responsibility to verify that no null arguments are present
• before calling. */

  define FunctionCallInvoke(fcinfo) ((* (fcinfo)->flinfo->fn_addr) (fcinfo))

[johnfangAFW]

Have discussed how Docdb evals the function/expr, let take a look at how the docdb handles the function/expr calls.

doc_expr.cc

case bfpg::TSOpcode::kPgEvalExprCall: {
  const std::string& expr_str = tscall.operands(0).value().string_value();

  std::vector<DocPgParamDesc> params;
  int num_params = (tscall.operands_size() - 1) / 3;
  params.reserve(num_params);
  for (int i = 0; i < num_params; i++) {
    int32_t attno = tscall.operands(3*i + 1).value().int32_value();
    int32_t typid = tscall.operands(3*i + 2).value().int32_value();
    int32_t typmod = tscall.operands(3*i + 3).value().int32_value();
    params.emplace_back(attno, typid, typmod);
  }

  RETURN_NOT_OK(DocPgEvalExpr(expr_str,
                              params,
                              table_row,
                              schema,
                              result));

  return Status::OK();
}

where bfpg::TSOpcode::kPgEvalExprCall maps to PgExpr opcode Opcode::PG_EXPR_EVAL_EXPR_CALL.

docdb_pgapi.cc

Status DocPgEvalExpr(const std::string& expr_str, std::vector params, const QLTableRow& table_row, const Schema schema, QLValue result) { PG_RETURN_NOT_OK(YbgPrepareMemoryContext());

char expr_cstring = const_cast<char >(expr_str.c_str());

// Create the context expression evaluation. // Since we currently only allow referencing the target col just set min/max attr to col_attno. // TODO Eventually this context should be created once per row and contain all (referenced) // column values. Then the context can be reused for all expressions. YbgExprContext expr_ctx; int32_t min_attno = params[0].attno; int32_t max_attno = params[0].attno;

for (int i = 1; i < params.size(); i++) { min_attno = std::min(min_attno, params[i].attno); max_attno = std::max(max_attno, params[i].attno); }

PG_RETURN_NOT_OK(YbgExprContextCreate(min_attno, max_attno, &expr_ctx));

// Set the column values (used to resolve scan variables in the expression). for (const ColumnId& col_id : schema->column_ids()) { auto column = schema->column_by_id(col_id); SCHECK(column.ok(), InternalError, "Invalid Schema");

// Loop here is ok as params.size() will always be 1 for user tables,
// and 2 for some internal queries (catalog version increment).
// TODO Rethink this if we ever allow more params here.
DCHECK_LT(params.size(), 3);
for (int i = 0; i < params.size(); i++) {
  if (column->order() == params[i].attno) {
    const QLValuePB* val = table_row.GetColumn(col_id.rep());
    bool is_null = false;
    uint64_t datum = 0;
    YbgTypeDesc pg_arg_type = {params[i].typid, params[i].typmod};
    const YBCPgTypeEntity *arg_type = DocPgGetTypeEntity(pg_arg_type);
    YBCPgTypeAttrs arg_type_attrs = { pg_arg_type.type_mod };

    Status s = PgValueFromPB(arg_type, arg_type_attrs, *val, &datum, &is_null);
    if (!s.ok()) {
      PG_RETURN_NOT_OK(YbgResetMemoryContext());
      return s;
    }

    PG_RETURN_NOT_OK(YbgExprContextAddColValue(expr_ctx, column->order(), datum, is_null));
    break;
  }
}

}

// Evaluate the expression and get the result. bool is_null = false; uint64_t datum; PG_RETURN_NOT_OK(YbgEvalExpr(expr_cstring, expr_ctx, &datum, &is_null));

// Assuming first arg is the target column, so using it for the return type. // YSQL layer should guarantee this when producing the params. YbgTypeDesc pg_type = {params[0].typid, params[0].typmod}; const YBCPgTypeEntity *ret_type = DocPgGetTypeEntity(pg_type);

Status s = PgValueToPB(ret_type, datum, is_null, result); PG_RETURN_NOT_OK(YbgResetMemoryContext()); return s; }

[johnfangAFW]

where YbgEvalExp was defined in ybgate_api.c

YbgStatus YbgEvalExpr(char expr_cstring, YbgExprContext expr_ctx, uint64_t datum, bool is_null) { PG_SETUP_ERROR_REPORTING(); Expr expr = (Expr ) stringToNode(expr_cstring); datum = (uint64_t) evalExpr(expr_ctx, expr, is_null); return PG_STATUS_OK; }

Obviously, the PG internal Expr node/subtree was deserialized from the expr string, then evalExpr was called to walk the tree to eval the expr node/subtree. The final result, datum, was returned.

[johnfangAFW]

disabled the function pushdown for now by the following PR

https://github.com/futurewei-cloud/chogori-sql/pull/215

and will add support after k2 platform adds such support. https://github.com/futurewei-cloud/chogori-platform/issues/137