// Insert inserts rows into the database. // Privileges: INSERT on table. Also requires UPDATE on "ON DUPLICATE KEY UPDATE". // Notes: postgres requires INSERT. No "on duplicate key update" option. // mysql requires INSERT. Also requires UPDATE on "ON DUPLICATE KEY UPDATE". func (p *planner) Insert( n *parser.Insert, desiredTypes []parser.Datum, autoCommit bool, ) (planNode, error) { en, err := p.makeEditNode(n.Table, n.Returning, desiredTypes, autoCommit, privilege.INSERT) if err != nil { return nil, err } if n.OnConflict != nil { if err := p.checkPrivilege(en.tableDesc, privilege.UPDATE); err != nil { return nil, err } // TODO(dan): Support RETURNING in UPSERTs. if n.Returning != nil { return nil, fmt.Errorf("RETURNING is not supported with UPSERT") } } var cols []sqlbase.ColumnDescriptor // Determine which columns we're inserting into. if n.DefaultValues() { cols = en.tableDesc.Columns } else { var err error if cols, err = p.processColumns(en.tableDesc, n.Columns); err != nil { return nil, err } } // Number of columns expecting an input. This doesn't include the // columns receiving a default value. numInputColumns := len(cols) colIDSet := make(map[sqlbase.ColumnID]struct{}, len(cols)) for _, col := range cols { colIDSet[col.ID] = struct{}{} } // Add the column if it has a DEFAULT expression. addIfDefault := func(col sqlbase.ColumnDescriptor) { if col.DefaultExpr != nil { if _, ok := colIDSet[col.ID]; !ok { colIDSet[col.ID] = struct{}{} cols = append(cols, col) } } } // Add any column that has a DEFAULT expression. for _, col := range en.tableDesc.Columns { addIfDefault(col) } // Also add any column in a mutation that is WRITE_ONLY and has // a DEFAULT expression. for _, m := range en.tableDesc.Mutations { if m.State != sqlbase.DescriptorMutation_WRITE_ONLY { continue } if col := m.GetColumn(); col != nil { addIfDefault(*col) } } defaultExprs, err := makeDefaultExprs(cols, &p.parser, p.evalCtx) if err != nil { return nil, err } // Replace any DEFAULT markers with the corresponding default expressions. insertRows, err := p.fillDefaults(defaultExprs, cols, n) if err != nil { return nil, err } // Construct the check expressions. The returned slice will be nil if no // column in the table has a check expression. checkExprs, err := p.makeCheckExprs(cols) if err != nil { return nil, err } // Prepare the check expressions. var qvals qvalMap typedCheckExprs := make([]parser.TypedExpr, 0, len(checkExprs)) if len(checkExprs) > 0 { qvals = make(qvalMap) table := tableInfo{ columns: makeResultColumns(en.tableDesc.Columns), } for i := range checkExprs { expr, err := resolveQNames(checkExprs[i], &table, qvals, &p.qnameVisitor) if err != nil { return nil, err } typedExpr, err := parser.TypeCheck(expr, nil, parser.TypeBool) if err != nil { return nil, err } if typedExpr, err = p.parser.NormalizeExpr(p.evalCtx, typedExpr); err != nil { return nil, err } typedCheckExprs = append(typedCheckExprs, typedExpr) } } // Analyze the expressions for column information and typing. desiredTypesFromSelect := make([]parser.Datum, len(cols)) for i, col := range cols { desiredTypesFromSelect[i] = col.Type.ToDatumType() } rows, err := p.makePlan(insertRows, desiredTypesFromSelect, false) if err != nil { return nil, err } if expressions := len(rows.Columns()); expressions > numInputColumns { return nil, fmt.Errorf("INSERT has more expressions than target columns: %d/%d", expressions, numInputColumns) } // Type check the tuples, if any, to collect placeholder types. if values, ok := n.Rows.Select.(*parser.ValuesClause); ok { for _, tuple := range values.Tuples { for eIdx, val := range tuple.Exprs { if _, ok := val.(parser.DefaultVal); ok { continue } typedExpr, err := parser.TypeCheck(val, p.evalCtx.Args, desiredTypesFromSelect[eIdx]) if err != nil { return nil, err } err = sqlbase.CheckColumnType(cols[eIdx], typedExpr.ReturnType(), p.evalCtx.Args) if err != nil { return nil, err } } } } if err := en.rh.TypeCheck(); err != nil { return nil, err } ri, err := makeRowInserter(en.tableDesc, cols) if err != nil { return nil, err } var tw tableWriter if n.OnConflict == nil { tw = &tableInserter{ri: ri, autoCommit: autoCommit} } else { // TODO(dan): These are both implied by the short form of UPSERT. When the // INSERT INTO ON CONFLICT form is implemented, get these values from // n.OnConfict. upsertConflictIndex := en.tableDesc.PrimaryIndex insertCols := ri.insertCols indexColSet := make(map[sqlbase.ColumnID]struct{}, len(upsertConflictIndex.ColumnIDs)) for _, colID := range upsertConflictIndex.ColumnIDs { indexColSet[colID] = struct{}{} } // updateCols contains the columns that will be updated when a conflict is // found. For the UPSERT short form, it is the set of columns in insertCols // minus any columns in the conflict index. Example: // `UPSERT INTO abc VALUES (1, 2, 3)` is syntactic sugar for // `INSERT INTO abc VALUES (1, 2, 3) ON CONFLICT a DO UPDATE SET b = 2, c = 3`. updateCols := make([]sqlbase.ColumnDescriptor, 0, len(insertCols)) for _, c := range insertCols { if _, ok := indexColSet[c.ID]; !ok { updateCols = append(updateCols, c) } } ru, err := makeRowUpdater(en.tableDesc, updateCols) if err != nil { return nil, err } // TODO(dan): Use ru.fetchCols to compute the fetch selectors. tw = &tableUpserter{ri: ri, ru: ru, autoCommit: autoCommit} } in := &insertNode{ n: n, editNodeBase: en, defaultExprs: defaultExprs, checkExprs: typedCheckExprs, qvals: qvals, insertRows: insertRows, insertCols: ri.insertCols, insertColIDtoRowIndex: ri.insertColIDtoRowIndex, desiredTypes: desiredTypesFromSelect, tw: tw, } return in, nil }
// Insert inserts rows into the database. // Privileges: INSERT on table. Also requires UPDATE on "ON DUPLICATE KEY UPDATE". // Notes: postgres requires INSERT. No "on duplicate key update" option. // mysql requires INSERT. Also requires UPDATE on "ON DUPLICATE KEY UPDATE". func (p *planner) Insert( n *parser.Insert, desiredTypes []parser.Datum, autoCommit bool, ) (planNode, error) { en, err := p.makeEditNode(n.Table, n.Returning, desiredTypes, autoCommit, privilege.INSERT) if err != nil { return nil, err } if n.OnConflict != nil { if !n.OnConflict.DoNothing { if err := p.checkPrivilege(en.tableDesc, privilege.UPDATE); err != nil { return nil, err } } // TODO(dan): Support RETURNING in UPSERTs. if n.Returning != nil { return nil, fmt.Errorf("RETURNING is not supported with UPSERT") } } var cols []sqlbase.ColumnDescriptor // Determine which columns we're inserting into. if n.DefaultValues() { cols = en.tableDesc.Columns } else { var err error if cols, err = p.processColumns(en.tableDesc, n.Columns); err != nil { return nil, err } } // Number of columns expecting an input. This doesn't include the // columns receiving a default value. numInputColumns := len(cols) colIDSet := make(map[sqlbase.ColumnID]struct{}, len(cols)) for _, col := range cols { colIDSet[col.ID] = struct{}{} } // Add the column if it has a DEFAULT expression. addIfDefault := func(col sqlbase.ColumnDescriptor) { if col.DefaultExpr != nil { if _, ok := colIDSet[col.ID]; !ok { colIDSet[col.ID] = struct{}{} cols = append(cols, col) } } } // Add any column that has a DEFAULT expression. for _, col := range en.tableDesc.Columns { addIfDefault(col) } // Also add any column in a mutation that is WRITE_ONLY and has // a DEFAULT expression. for _, m := range en.tableDesc.Mutations { if m.State != sqlbase.DescriptorMutation_WRITE_ONLY { continue } if col := m.GetColumn(); col != nil { addIfDefault(*col) } } defaultExprs, err := makeDefaultExprs(cols, &p.parser, p.evalCtx) if err != nil { return nil, err } // Replace any DEFAULT markers with the corresponding default expressions. insertRows, err := p.fillDefaults(defaultExprs, cols, n) if err != nil { return nil, err } // Analyze the expressions for column information and typing. desiredTypesFromSelect := make([]parser.Datum, len(cols)) for i, col := range cols { desiredTypesFromSelect[i] = col.Type.ToDatumType() } rows, err := p.newPlan(insertRows, desiredTypesFromSelect, false) if err != nil { return nil, err } if expressions := len(rows.Columns()); expressions > numInputColumns { return nil, fmt.Errorf("INSERT has more expressions than target columns: %d/%d", expressions, numInputColumns) } // Type check the tuples, if any, to collect placeholder types. if values, ok := n.Rows.Select.(*parser.ValuesClause); ok { for _, tuple := range values.Tuples { for eIdx, val := range tuple.Exprs { if _, ok := val.(parser.DefaultVal); ok { continue } typedExpr, err := parser.TypeCheck(val, &p.semaCtx, desiredTypesFromSelect[eIdx]) if err != nil { return nil, err } err = sqlbase.CheckColumnType(cols[eIdx], typedExpr.ReturnType(), p.semaCtx.Args) if err != nil { return nil, err } } } } if err := en.rh.TypeCheck(); err != nil { return nil, err } ri, err := makeRowInserter(en.tableDesc, cols) if err != nil { return nil, err } var tw tableWriter if n.OnConflict == nil { tw = &tableInserter{ri: ri, autoCommit: autoCommit} } else { updateExprs, conflictIndex, err := upsertExprsAndIndex(en.tableDesc, *n.OnConflict, ri.insertCols) if err != nil { return nil, err } if n.OnConflict.DoNothing { // TODO(dan): Postgres allows ON CONFLICT DO NOTHING without specifying a // conflict index, which means do nothing on any conflict. Support this if // someone needs it. tw = &tableUpserter{ri: ri, conflictIndex: *conflictIndex} } else { names, err := p.namesForExprs(updateExprs) if err != nil { return nil, err } updateCols, err := p.processColumns(en.tableDesc, names) if err != nil { return nil, err } helper, err := p.makeUpsertHelper(en.tableDesc, ri.insertCols, updateCols, updateExprs, conflictIndex) if err != nil { return nil, err } tw = &tableUpserter{ri: ri, updateCols: updateCols, conflictIndex: *conflictIndex, evaler: helper} } } in := &insertNode{ n: n, editNodeBase: en, defaultExprs: defaultExprs, insertRows: insertRows, insertCols: ri.insertCols, insertColIDtoRowIndex: ri.insertColIDtoRowIndex, tw: tw, } if err := in.checkHelper.init(p, en.tableDesc); err != nil { return nil, err } in.run.initEditNode(rows) return in, nil }
// Update updates columns for a selection of rows from a table. // Privileges: UPDATE and SELECT on table. We currently always use a select statement. // Notes: postgres requires UPDATE. Requires SELECT with WHERE clause with table. // mysql requires UPDATE. Also requires SELECT with WHERE clause with table. // TODO(guanqun): need to support CHECK in UPDATE func (p *planner) Update(n *parser.Update, desiredTypes []parser.Datum, autoCommit bool) (planNode, error) { tracing.AnnotateTrace() en, err := p.makeEditNode(n.Table, n.Returning, desiredTypes, autoCommit, privilege.UPDATE) if err != nil { return nil, err } exprs := make([]*parser.UpdateExpr, len(n.Exprs)) for i, expr := range n.Exprs { // Replace the sub-query nodes. newExpr, err := p.replaceSubqueries(expr.Expr, len(expr.Names)) if err != nil { return nil, err } exprs[i] = &parser.UpdateExpr{Tuple: expr.Tuple, Expr: newExpr, Names: expr.Names} } // Determine which columns we're inserting into. names, err := p.namesForExprs(exprs) if err != nil { return nil, err } updateCols, err := p.processColumns(en.tableDesc, names) if err != nil { return nil, err } defaultExprs, err := makeDefaultExprs(updateCols, &p.parser, p.evalCtx) if err != nil { return nil, err } var requestedCols []sqlbase.ColumnDescriptor if len(en.rh.exprs) > 0 || len(en.tableDesc.Checks) > 0 { // TODO(dan): This could be made tighter, just the rows needed for RETURNING // exprs. requestedCols = en.tableDesc.Columns } ru, err := makeRowUpdater(en.tableDesc, updateCols, requestedCols) if err != nil { return nil, err } tw := tableUpdater{ru: ru, autoCommit: autoCommit} tracing.AnnotateTrace() // Generate the list of select targets. We need to select all of the columns // plus we select all of the update expressions in case those expressions // reference columns (e.g. "UPDATE t SET v = v + 1"). Note that we flatten // expressions for tuple assignments just as we flattened the column names // above. So "UPDATE t SET (a, b) = (1, 2)" translates into select targets of // "*, 1, 2", not "*, (1, 2)". targets := sqlbase.ColumnsSelectors(ru.fetchCols) i := 0 // Remember the index where the targets for exprs start. exprTargetIdx := len(targets) desiredTypesFromSelect := make([]parser.Datum, len(targets), len(targets)+len(exprs)) for _, expr := range exprs { if expr.Tuple { switch t := expr.Expr.(type) { case (*parser.Tuple): for _, e := range t.Exprs { typ := updateCols[i].Type.ToDatumType() e := fillDefault(e, typ, i, defaultExprs) targets = append(targets, parser.SelectExpr{Expr: e}) desiredTypesFromSelect = append(desiredTypesFromSelect, typ) i++ } default: return nil, fmt.Errorf("cannot use this expression to assign multiple columns: %s", expr.Expr) } } else { typ := updateCols[i].Type.ToDatumType() e := fillDefault(expr.Expr, typ, i, defaultExprs) targets = append(targets, parser.SelectExpr{Expr: e}) desiredTypesFromSelect = append(desiredTypesFromSelect, typ) i++ } } rows, err := p.SelectClause(&parser.SelectClause{ Exprs: targets, From: []parser.TableExpr{n.Table}, Where: n.Where, }, nil, nil, desiredTypesFromSelect) if err != nil { return nil, err } // ValArgs have their types populated in the above Select if they are part // of an expression ("SET a = 2 + $1") in the type check step where those // types are inferred. For the simpler case ("SET a = $1"), populate them // using checkColumnType. This step also verifies that the expression // types match the column types. sel := rows.(*selectTopNode).source.(*selectNode) for i, target := range sel.render[exprTargetIdx:] { // DefaultVal doesn't implement TypeCheck if _, ok := target.(parser.DefaultVal); ok { continue } // TODO(nvanbenschoten) isn't this TypeCheck redundant with the call to SelectClause? typedTarget, err := parser.TypeCheck(target, &p.semaCtx, updateCols[i].Type.ToDatumType()) if err != nil { return nil, err } err = sqlbase.CheckColumnType(updateCols[i], typedTarget.ReturnType(), p.semaCtx.Args) if err != nil { return nil, err } } if err := en.rh.TypeCheck(); err != nil { return nil, err } updateColsIdx := make(map[sqlbase.ColumnID]int, len(ru.updateCols)) for i, col := range ru.updateCols { updateColsIdx[col.ID] = i } un := &updateNode{ n: n, editNodeBase: en, updateCols: ru.updateCols, updateColsIdx: updateColsIdx, tw: tw, } if err := un.checkHelper.init(p, en.tableDesc); err != nil { return nil, err } un.run.initEditNode(rows) return un, nil }
// Update updates columns for a selection of rows from a table. // Privileges: UPDATE and SELECT on table. We currently always use a select statement. // Notes: postgres requires UPDATE. Requires SELECT with WHERE clause with table. // mysql requires UPDATE. Also requires SELECT with WHERE clause with table. // TODO(guanqun): need to support CHECK in UPDATE func (p *planner) Update(n *parser.Update, desiredTypes []parser.Datum, autoCommit bool) (planNode, error) { tracing.AnnotateTrace() en, err := p.makeEditNode(n.Table, n.Returning, desiredTypes, autoCommit, privilege.UPDATE) if err != nil { return nil, err } exprs := make([]parser.UpdateExpr, len(n.Exprs)) for i, expr := range n.Exprs { exprs[i] = *expr } // Determine which columns we're inserting into. var names parser.QualifiedNames for _, expr := range n.Exprs { // TODO(knz): We need to (attempt to) expand subqueries here already // so that it retrieves the column names. But then we need to do // it again when the placeholder values are known below. newExpr, eErr := p.expandSubqueries(expr.Expr, len(expr.Names)) if eErr != nil { return nil, eErr } if expr.Tuple { n := 0 switch t := newExpr.(type) { case *parser.Tuple: n = len(t.Exprs) case *parser.DTuple: n = len(*t) default: return nil, util.Errorf("unsupported tuple assignment: %T", newExpr) } if len(expr.Names) != n { return nil, fmt.Errorf("number of columns (%d) does not match number of values (%d)", len(expr.Names), n) } } names = append(names, expr.Names...) } updateCols, err := p.processColumns(en.tableDesc, names) if err != nil { return nil, err } defaultExprs, err := makeDefaultExprs(updateCols, &p.parser, p.evalCtx) if err != nil { return nil, err } ru, err := makeRowUpdater(en.tableDesc, updateCols) if err != nil { return nil, err } // TODO(dan): Use ru.fetchCols to compute the fetch selectors. tw := tableUpdater{ru: ru, autoCommit: autoCommit} tracing.AnnotateTrace() // Generate the list of select targets. We need to select all of the columns // plus we select all of the update expressions in case those expressions // reference columns (e.g. "UPDATE t SET v = v + 1"). Note that we flatten // expressions for tuple assignments just as we flattened the column names // above. So "UPDATE t SET (a, b) = (1, 2)" translates into select targets of // "*, 1, 2", not "*, (1, 2)". // TODO(radu): we only need to select columns necessary to generate primary and // secondary indexes keys, and columns needed by returningHelper. targets := en.tableDesc.AllColumnsSelector() i := 0 // Remember the index where the targets for exprs start. exprTargetIdx := len(targets) desiredTypesFromSelect := make([]parser.Datum, len(targets), len(targets)+len(exprs)) for _, expr := range n.Exprs { if expr.Tuple { if t, ok := expr.Expr.(*parser.Tuple); ok { for _, e := range t.Exprs { typ := updateCols[i].Type.ToDatumType() e := fillDefault(e, typ, i, defaultExprs) targets = append(targets, parser.SelectExpr{Expr: e}) desiredTypesFromSelect = append(desiredTypesFromSelect, typ) i++ } } } else { typ := updateCols[i].Type.ToDatumType() e := fillDefault(expr.Expr, typ, i, defaultExprs) targets = append(targets, parser.SelectExpr{Expr: e}) desiredTypesFromSelect = append(desiredTypesFromSelect, typ) i++ } } // TODO(knz): Until we split the creation of the node from Start() // for the SelectClause too, we cannot cache this. This is because // this node's initSelect() method both does type checking and also // performs index selection. We cannot perform index selection // properly until the placeholder values are known. rows, err := p.SelectClause(&parser.SelectClause{ Exprs: targets, From: []parser.TableExpr{n.Table}, Where: n.Where, }, desiredTypesFromSelect) if err != nil { return nil, err } // ValArgs have their types populated in the above Select if they are part // of an expression ("SET a = 2 + $1") in the type check step where those // types are inferred. For the simpler case ("SET a = $1"), populate them // using checkColumnType. This step also verifies that the expression // types match the column types. for i, target := range rows.(*selectNode).render[exprTargetIdx:] { // DefaultVal doesn't implement TypeCheck if _, ok := target.(parser.DefaultVal); ok { continue } typedTarget, err := parser.TypeCheck(target, p.evalCtx.Args, updateCols[i].Type.ToDatumType()) if err != nil { return nil, err } err = sqlbase.CheckColumnType(updateCols[i], typedTarget.ReturnType(), p.evalCtx.Args) if err != nil { return nil, err } } if err := en.rh.TypeCheck(); err != nil { return nil, err } un := &updateNode{ n: n, editNodeBase: en, desiredTypes: desiredTypesFromSelect, defaultExprs: defaultExprs, updateCols: ru.updateCols, tw: tw, } return un, nil }