knadh/listmonk · error

error getting tables from query: %v

Error message

error getting tables from query: %v

What it means

validateQueryTables runs EXPLAIN-style plan extraction on a custom subscriber query to enumerate every relation it touches. If getTablesFromQueryPlan cannot parse or extract table names from the returned query plan, this wrapped error is returned so QuerySubscribers/ExportSubscribers never run an unvalidatable query.

Source

Thrown at internal/core/subscribers.go:620

// validateQueryTables checks if the query accesses only allowed tables.
func validateQueryTables(db *sqlx.DB, query string, allowedTables map[string]struct{}, args ...any) error {
	// Get the EXPLAIN (FORMAT JSON) output.
	tx, err := db.BeginTxx(context.Background(), &sql.TxOptions{ReadOnly: true})
	if err != nil {
		return err
	}
	defer tx.Rollback()

	var plan string
	if err = tx.QueryRow("EXPLAIN (FORMAT JSON) "+query, args...).Scan(&plan); err != nil {
		return err
	}

	// Extract all relation names from the JSON plan.
	tables, err := getTablesFromQueryPlan(plan)
	if err != nil {
		return fmt.Errorf("error getting tables from query: %v", err)
	}

	// Validate against allowed tables.
	for _, table := range tables {
		if _, ok := allowedTables[table]; !ok {
			return fmt.Errorf("table '%s' is not allowed", table)
		}
	}

	return nil
}

// getTablesFromQueryPlan parses the EXPLAIN JSON to find all "Relation Name" entries.
func getTablesFromQueryPlan(explainJSON string) ([]string, error) {
	var plans []map[string]any
	if err := json.Unmarshal([]byte(explainJSON), &plans); err != nil {
		return nil, err
	}

View on GitHub (pinned to 670c01717d)

Solutions

1. Simplify the query: avoid CTEs (WITH ...), unions, or exotic syntax; use a plain SELECT ... FROM subscribers with joins to known tables
2. First fix the underlying SQL so it executes cleanly (test it directly against the DB) — unparseable plans often stem from invalid SQL
3. Check whether a DB engine version change altered plan output and update getTablesFromQueryPlan's parsing
4. Verify the query only references allowed tables (subscribers, lists, subscriber_lists) since even a parsed query must pass validation next

Example fix

// before
WITH active AS (SELECT id FROM subscribers WHERE status='enabled') SELECT * FROM active
// after
SELECT s.* FROM subscribers s WHERE s.status = 'enabled'

// Validate the SQL before submitting it as a segment/export query
if strings.Contains(strings.ToUpper(q), "WITH ") || strings.Contains(strings.ToUpper(q), "UNION") {
    return errors.New("CTEs and UNIONs are not supported in subscriber queries")
}

if err := validateQueryTables(query); err != nil {
    if strings.HasPrefix(err.Error(), "error getting tables from query") {
        log.Printf("unparseable query plan, simplify SQL: %v", err)
    }
}

When it happens

Trigger: Calling QuerySubscribers or ExportSubscribers with a SQL query whose plan cannot be parsed by getTablesFromQueryPlan — e.g. malformed SQL that still produced a plan object, unsupported SQL constructs (CTEs, subqueries in unusual positions, dialect-specific syntax) the parser doesn't understand.

Common situations: Users pasting complex analytic SQL with CTEs or unions into a custom segment/export query, database upgrades changing EXPLAIN output format so the plan parser no longer matches, or typos in SQL.

Related errors

• table '%s' is not allowed
• invalid signature
• invalid SNS certificate URL: %v
• error fetching campaign subscribers (%s): %v

AI-assisted analysis of knadh/listmonk@670c01717d (2026-09-01). Data as JSON: /api/errors/18368dbe54bacd57. Report an issue: GitHub.