materialize: query plan error when referencing same column multiple times

[derekperkins]

I'm trying to run a materialize command and getting this error: wrong number of fields: got 9 fields for 12 bind locations. I’m selecting 9 unique fields from the source table. 1 of those is referenced 2 times and another 3 times, for 12 total references, which appears to be the error. The raw query runs just fine against vtgate, but fails when executed from Materialize. I'm sure it's much easier to reproduce than I have here, this is just the exact setup where it failed.

vitess/go/vt/sqlparser/parsed_query.go

Lines 88 to 98 in ccdb446

	// AppendFromRow behaves like Append but takes a querypb.Row directly, assuming that
	// the fields in the row are in the same order as the placeholders in this query. The fields might include generated
	// columns which are dropped, by checking against skipFields, before binding the variables
	// note: there can be more fields than bind locations since extra columns might be requested from the source if not all
	// primary keys columns are present in the target table, for example. Also some values in the row may not correspond for
	// values from the database on the source: sum/count for aggregation queries, for example
	func (pq *ParsedQuery) AppendFromRow(buf *bytes2.Buffer, fields []*querypb.Field, row *querypb.Row, skipFields map[string]bool) error {
	if len(fields) < len(pq.bindLocations) {
	return vterrors.Errorf(vtrpcpb.Code_INTERNAL, "wrong number of fields: got %d fields for %d bind locations ",
	len(fields), len(pq.bindLocations))
	}

Raw query

select
   event_id as id,
   user_id as keyspace_id,
   unix_timestamp(created_at)*1e9 as time_next,
   unix_timestamp(created_at)*1e9 as time_scheduled,
   unix_timestamp(created_at)*1e9 as time_created,
   if(workspace_id=0,null,workspace_id) as workspace_id,
   team_id,
   kind,
   kind_id,
   kind_version_id,
   type
from iam_events

Materialize command

vtctlclient -server localhost:15999 Materialize '{
  "workflow": "iam__invite_email__msgs_test",
  "source_keyspace": "iam",
  "target_keyspace": "iam",
  "table_settings": [
    {
      "target_table": "iam__invite_email__msgs_test",
      "source_expression": "select event_id as id, user_id as keyspace_id, unix_timestamp(created_at)*1e9 as time_next, unix_timestamp(created_at)*1e9 as time_scheduled, unix_timestamp(created_at)*1e9 as time_created, if(workspace_id=0,null,workspace_id) as workspace_id, team_id, kind, kind_id, kind_version_id, type from iam_events"
    }
  ],
  "tablet_types": "PRIMARY"
}'

Source table

CREATE TABLE `iam_events` (
  `event_id` bigint NOT NULL,
  `workspace_id` bigint NOT NULL,
  `team_id` bigint DEFAULT NULL,
  `kind` varbinary(15) NOT NULL COMMENT 'string representation of a crud object name: workspace, team, etc.',
  `kind_id` bigint NOT NULL COMMENT 'the id for the kind - used for joins back to the original object',
  `kind_version_id` bigint NOT NULL COMMENT 'the version id for the kind - used for joins back to the original object',
  `type` varbinary(15) NOT NULL COMMENT 'what type of operation occurred: create, update, delete, etc.',
  `user_id` bigint NOT NULL COMMENT 'the user who performed the operation',
  `idempotency_key` varbinary(15) DEFAULT NULL COMMENT 'optionally provided by client to prevent duplicate operations',
  `message` varbinary(100) DEFAULT NULL COMMENT 'optional user facing message to describe the event',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`event_id`),
  KEY `iam_events__user_id` (`user_id`),
  CONSTRAINT `iam_events__user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;

Target table

CREATE TABLE `iam__invite_email__msgs_test` (
  `id` bigint NOT NULL,
  `keyspace_id` bigint NOT NULL,
  `priority` tinyint NOT NULL DEFAULT '50',
  `epoch` smallint NOT NULL DEFAULT '0',
  `time_next` bigint DEFAULT NULL,
  `time_acked` bigint DEFAULT NULL,
  `time_scheduled` bigint NOT NULL,
  `time_created` bigint NOT NULL,
  `attributes` json DEFAULT (json_object(_utf8mb3'tryCount',_utf8mb3'0',_utf8mb3'isDebug',_utf8mb3'false')),
  `workspace_id` bigint DEFAULT NULL,
  `team_id` bigint DEFAULT NULL,
  `kind` varbinary(15) NOT NULL,
  `kind_id` bigint NOT NULL,
  `kind_version_id` bigint NOT NULL,
  `type` varbinary(15) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ack_idx` (`time_acked`),
  KEY `next_idx` (`time_next`,`priority`),
  KEY `iam__invite_email__msgs__users` (`keyspace_id`),
  KEY `iam__invite_email__msgs__iam__workspaces__copy` (`workspace_id`),
  CONSTRAINT `iam__invite_email__msgs__iam__workspaces__copy` FOREIGN KEY (`workspace_id`) REFERENCES `iam__workspaces__copy` (`workspace_id`),
  CONSTRAINT `iam__invite_email__msgs__iam_events` FOREIGN KEY (`id`) REFERENCES `iam_events` (`event_id`),
  CONSTRAINT `iam__invite_email__msgs__users` FOREIGN KEY (`keyspace_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED COMMENT='vitess_message,vt_ack_wait=30,vt_purge_after=86400,vt_batch_size=10,vt_cache_size=10000,vt_poller_interval=30'

Using v12 of all Vitess components

[derekperkins]

Fixed by #9335