RoutingRules: "Unknown column" errors with gen3 and gen4 planners

[artemvovk]

Overview of the Issue

I am running into hard-to-understand errors from VTGate when parsing different SQL queries. Specifically, the errors refer to unknown column. The column/table/schema all exist - the main issue is figuring out what kind of SQL will actually work in each given case.

seems related: #8054

Reproduction Steps

All queries have been tested using v3 and gen4fallback planners.

Steps to reproduce this issue, example:

1. All VSchemas are unsharded
2. Deploy the following schema:
   external keyspace:

   | subscriptions | CREATE TABLE `subscriptions` (
  `id` bigint(20) unsigned NOT NULL,
  `site_id` bigint(20) unsigned NOT NULL,
  `account_id` bigint(20) unsigned NOT NULL,
  `plan_id` bigint(20) unsigned NOT NULL,
  `plan_version_id` bigint(20) unsigned DEFAULT NULL,
  `shipping_method_id` bigint(20) unsigned DEFAULT NULL,
  `billing_info_id` bigint(20) unsigned DEFAULT NULL,
  `state` enum('active','canceled','expired','failed','future','modified','paused','pending') NOT NULL DEFAULT 'pending',
  `is_in_trial` tinyint(1) NOT NULL DEFAULT '0',
  `is_live` tinyint(1) NOT NULL DEFAULT '0',
  `is_past_due` tinyint(1) NOT NULL DEFAULT '0',
  `auto_renew` tinyint(1) DEFAULT NULL,
  `auto_collecting` tinyint(1) NOT NULL DEFAULT '1',
  `net_terms` smallint(6) DEFAULT NULL,
  `tax_inclusive` tinyint(1) NOT NULL DEFAULT '0',
  `unit_amount_in_cents` int(11) NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT '1',
  `shipping_amount_in_cents` int(11) NOT NULL DEFAULT '0',
  `monthly_recurring_amount` int(11) NOT NULL DEFAULT '0',
  `current_period_started_at` datetime NOT NULL,
  `current_period_ends_at` datetime NOT NULL,
  `trial_started_at` datetime DEFAULT NULL,
  `trial_ends_at` datetime DEFAULT NULL,
  `activated_at` datetime DEFAULT NULL,
  `canceled_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `converted_at` datetime DEFAULT NULL,
  `paused_at` datetime DEFAULT NULL,
  `remaining_pause_cycles` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `expiration_reason` varchar(30) DEFAULT NULL,
  `active_invoice_id` bigint(20) unsigned DEFAULT NULL,
  `currency` varchar(3) NOT NULL,
  `total_billing_cycles` int(11) DEFAULT NULL,
  `remaining_billing_cycles` int(11) DEFAULT NULL,
  `uuid` varchar(32) NOT NULL,
  `billing_variables` varchar(255) NOT NULL DEFAULT '{}',
  `first_renewal_date` datetime DEFAULT NULL,
  `po_number` varchar(50) DEFAULT NULL,
  `customer_notes` text,
  `terms_and_conditions` text,
  `vat_reverse_charge_notes` text,
  `purchase_id` bigint(20) unsigned DEFAULT NULL,
  `revenue_schedule_type_cd` tinyint(2) unsigned DEFAULT NULL,
  `started_with_gift` tinyint(1) NOT NULL DEFAULT '0',
  `no_billing_info_reason_cd` tinyint(2) unsigned DEFAULT NULL,
  `imported_trial` tinyint(1) NOT NULL DEFAULT '0',
  `current_term_started_at` datetime DEFAULT NULL,
  `current_term_ends_at` datetime DEFAULT NULL,
  `renewal_billing_cycles` int(11) DEFAULT NULL,
  `payment_gateway_id` bigint(20) unsigned DEFAULT NULL,
  `introductory_discount_active` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `index_subscriptions_on_account_id_and_state` (`account_id`,`state`),
  KEY `index_subscriptions_on_current_period_ends_at` (`current_period_ends_at`),
  KEY `index_subscriptions_on_plan_id_and_plan_version_id` (`plan_id`,`plan_version_id`),
  KEY `index_subscriptions_on_site_id_and_activated_at` (`site_id`,`activated_at`),
  KEY `index_subscriptions_on_site_id_and_id` (`site_id`,`id`),
  KEY `index_subscriptions_on_site_id_and_state` (`site_id`,`state`),
  KEY `index_subscriptions_on_site_id_and_updated_at` (`site_id`,`updated_at`),
  KEY `index_subscriptions_on_site_id_and_uuid` (`site_id`,`uuid`) ENGINE=InnoDB DEFAULT CHARSET=utf8

`catalog` keyspace:

CREATE TABLE `plans` (
  `plan_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `site_id` bigint(20) unsigned NOT NULL,
  `dunning_campaign_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `merchant_plan_id` varchar(50) NOT NULL,
  `description` text,
  `status` enum('ACTIVE','INACTIVE','REQUESTED','BANNED','DELETED') NOT NULL DEFAULT 'ACTIVE',
  `created_at` datetime NOT NULL,
  `modified_at` datetime NOT NULL,
  `deleted_at` datetime NOT NULL DEFAULT '1000-01-01 00:00:00',
  `trial_period_id` bigint(20) unsigned DEFAULT NULL,
  `trial_requires_billing_info` tinyint(1) NOT NULL DEFAULT '1',
  `total_billing_cycles` int(11) DEFAULT NULL,
  `auto_renew` tinyint(1) DEFAULT '1',
  `send_renewal_reminders` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `send_trial_reminders` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `notify_days_before_trial` int(11) unsigned NOT NULL DEFAULT '3',
  `bypass_hosted_confirmation` tinyint(1) NOT NULL DEFAULT '0',
  `display_donation_amounts` tinyint(1) NOT NULL DEFAULT '0',
  `success_url` varchar(255) DEFAULT NULL,
  `cancel_url` varchar(255) DEFAULT NULL,
  `unit_name` varchar(50) NOT NULL DEFAULT 'unit',
  `target_version_id_for_existing_subscriptions` bigint(20) unsigned DEFAULT NULL,
  `display_phone_number` tinyint(1) NOT NULL DEFAULT '0',
  `payment_page_tos_link` varchar(255) DEFAULT NULL,
  `backfilled_for_terms` tinyint(1) NOT NULL DEFAULT '0',
  `allow_any_item_on_subscriptions` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`plan_id`),
  UNIQUE KEY `index_plans_on_site_id_merchant_plan_id_status_deleted_at` (`site_id`,`merchant_plan_id`,`status`,`deleted_at`),
  KEY `index_plans_on_modified_at` (`modified_at`),
  KEY `index_plans_on_site_id_and_status_and_created_at` (`site_id`,`status`,`created_at`),
  KEY `index_plans_on_site_id_and_status_and_modified_at` (`site_id`,`status`,`modified_at`),
  KEY `index_plans_on_trial_period_id_and_status` (`trial_period_id`,`status`),
  KEY `index_plans_on_dunning_campaign_id` (`dunning_campaign_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3398989776339835630 DEFAULT CHARSET=utf8

another keyspace I have is called recurly which has MoveTables workflows replicating the above tables into it

1. There are associated RoutingRules for these tables, as they are being migrated from external and catalog keyspaces into recurly. The routing rules currently move only replica and rdonly traffic into recurly keyspace

  #sample from GetRoutingRules
 4311     {
  4312     ¦ fromTable: external.subscriptions@rdonly,
  4313     ¦ toTables: [
  4314     ¦   recurly.subscriptions
  4315     ¦ ]
  4316     },

1. Select statements that error:

mysql -A -h 127.0.0.1 -P 3306 -u root catalog -e 'select * from catalog.plans where catalog.plans.site_id = 988673674913 limit 2'
ERROR 1054 (42S22) at line 1: target: catalog.-.master: vttablet: rpc error: code = NotFound desc = Unknown column 'catalog.plans.site_id' in 'where clause' (errno 1054) (sqlstate 42S22) (CallerID: root): Sql: "select plans.plan_id as plan_id, plans.site_id as site_id, plans.dunning_campaign_id as dunning_campaign_id, plans.`name` as `name`, plans.merchant_plan_id as merchant_plan_id, plans.description as description, plans.`status` as `status`, plans.created_at as created_at, plans.modified_at as modified_at, plans.deleted_at as delet

and

Execute: symbol plans.merchant_plan_id not found, request: map[BindVariables:map[vtg1:type:INT64 value:"3398944155452550579" vtg2:type:VARBINARY value:"mtm-arrears" vtg3:type:INT64 value:"1"] Session:autocommit:true target_string:"external" options:{included_fields:ALL client_found_rows:true workload:OLTP} found_rows:1 row_count:-1 DDLStrategy:"direct" SessionUUID:"e7023dee-3cba-11ec-ac30-a228c8bc1979" Sql:SELECT `subscriptions`.* FROM `subscriptions` INNER JOIN `catalog`.`plans` ON `catalog`.`plans`.`plan_id` = `subscriptions`.`plan_id` WHERE `subscriptions`.`account_id` = 3398944155452550579 AND `subscriptions`.`is_live` = TRUE AND `plans`.`merchant_plan_id` != 'mtm-arrears' ORDER BY `subscriptions`.`id` ASC LIMIT 1]

1. Errors seem to depend on if each column is fully qualified <keyspace>.<table>.<column>; but the first example query will work if only <table>.<column> are specified in the WHERE clause (which doesn't quite make sense)

Binary version

Example:

vitess@vitess-cluster-uscentral1b0-vtgate-c8b546d6-7dc7f9dfb9-pdtvq:/$ vtgate --version
ERROR: logging before flag.Parse: E1103 18:09:47.025244      20 syslogger.go:149] can't connect to syslog
Version: 11.0.0 (Git revision aa798b854a branch 'HEAD') built on Wed Oct 27 19:05:24 UTC 2021 by vitess@48e958bfcb18 using go1.15.6 linux/amd64

Operating system and Environment details

OS, Architecture, and any other information you can provide
about the environment.

cat /etc/os-release
PRETTY_NAME="Debian GNU/Linux 10 (buster)"
NAME="Debian GNU/Linux"
VERSION_ID="10"
VERSION="10 (buster)"
VERSION_CODENAME=buster
ID=debian
HOME_URL="https://www.debian.org/"
SUPPORT_URL="https://www.debian.org/support"
BUG_REPORT_URL="https://bugs.debian.org/"

uname -srm
Linux 5.4.120+ x86_64

Log Fragments

VTGate doesn't seem to log these query errors with gen4 planner.