P1 Fixes Quick Reference

P1 Fixes Quick Reference

Quick reference guide for the three implemented P1 TODO fixes.

P1-5: Branch Merge with Auto-Delete

SQL Syntax

-- Delete source branch after successful merge
MERGE BRANCH feature_branch INTO main
WITH (
    conflict_resolution = 'branch_wins',
    delete_branch_after = true
)

Code Location

/home/claude/HeliosDB Nano/src/sql/executor/phase3.rs

• Lines 183-191: Deletion logic
• Lines 507-518: Helper function

Behavior

• ✅ Deletes source branch only after successful merge
• ✅ Skips deletion if merge has conflicts
• ✅ Logs deletion for audit trail
• ✅ Respects delete_branch_after boolean value

---

P1-6: CREATE INDEX WITH Options

SQL Syntax

-- HNSW vector index with full configuration
CREATE INDEX embedding_idx ON documents USING hnsw (embedding)
WITH (
    -- HNSW parameters
    m = 16,
    ef_construction = 200,

    -- Quantization
    quantization = 'product',
    pq_subquantizers = 8,
    pq_centroids = 256,

    -- Sharding
    sharding_strategy = 'hash',
    shard_count = 16
)

Supported Options

Option	Type	Example	Description
quantization	string	'product'	Quantization type: none, scalar, product, auto
pq_subquantizers	number	8	Product quantization subquantizers
pq_centroids	number	256	Product quantization centroids
m	number	16	HNSW M parameter (connections per node)
ef_construction	number	200	HNSW construction effort
sharding_strategy	string	'hash'	Sharding strategy
shard_count	number	16	Number of shards

Code Location

/home/claude/HeliosDB Nano/src/sql/planner.rs

• Line 119: Integration point
• Lines 851-950: Parser implementation

Examples

Minimal Configuration:

CREATE INDEX idx ON table USING hnsw (col) WITH (m = 16)

Product Quantization:

CREATE INDEX idx ON table USING hnsw (col)
WITH (
    quantization = 'product',
    pq_subquantizers = 8,
    pq_centroids = 256
)

Sharded Index:

CREATE INDEX idx ON table USING hnsw (col)
WITH (
    sharding_strategy = 'hash',
    shard_count = 16
)

---

P1-7: AS OF Time-Travel Queries

SQL Syntax

Timestamp-Based:

SELECT * FROM orders AS OF TIMESTAMP '2025-11-15 06:00:00'
WHERE id = 1

Transaction-Based:

SELECT * FROM orders AS OF TRANSACTION 987654

System Change Number:

SELECT * FROM orders AS OF SCN 123456789

Current Time:

SELECT * FROM orders AS OF NOW

Code Location

/home/claude/HeliosDB Nano/src/sql/planner.rs

• Lines 19-20, 40-43: Planner SQL storage
• Line 341: Integration point
• Lines 354-392: AS OF parser

Usage Pattern

use heliosdb_nano::sql::{Parser, Planner};

let sql = "SELECT * FROM orders AS OF TIMESTAMP '2025-11-15 06:00:00'";
let parser = Parser::new();
let statement = parser.parse_one(sql)?;

// Pass original SQL to planner for AS OF parsing
let planner = Planner::new()
    .with_catalog(&catalog)
    .with_sql(sql.to_string());

let plan = planner.statement_to_plan(statement)?;
// Plan now includes AS OF clause in Scan node

Supported AS OF Types

Type	Format	Example
Timestamp	TIMESTAMP 'YYYY-MM-DD HH:MM:SS'	AS OF TIMESTAMP '2025-11-15 06:00:00'
Transaction	TRANSACTION <id>	AS OF TRANSACTION 987654
SCN	SCN <number>	AS OF SCN 123456789
Now	NOW	AS OF NOW

---

Testing

Run All Tests

cargo test p1_todo_fixes_tests --lib

Run Individual Tests

# P1-5: Branch deletion
cargo test test_p1_5_branch_delete_after_merge --lib

# P1-6: Index options
cargo test test_p1_6_create_index_with_options --lib

# P1-7: AS OF parsing
cargo test test_p1_7_as_of_clause_parsing --lib

# Integration test
cargo test test_p1_integration_all_fixes --lib

Run with Output

cargo test p1_todo_fixes_tests --lib -- --nocapture

---

Common Issues & Solutions

P1-5: Branch Not Deleted

Symptom: Source branch still exists after merge

Possible Causes:

1. delete_branch_after not set to true
2. Merge had conflicts (deletion only on successful merge)
3. Storage engine error

Solution:

-- Ensure option is set correctly
MERGE BRANCH source INTO target
WITH (delete_branch_after = true)  -- Must be true

-- Check merge succeeded without conflicts
-- If conflicts exist, resolve them first

P1-6: Index Options Not Applied

Symptom: Index created but options seem ignored

Possible Causes:

1. SQL syntax error in WITH clause
2. Invalid option name or value
3. Option not supported by index type

Solution:

-- Check option names (case-insensitive, use underscores)
WITH (m = 16)  -- Correct
WITH (M = 16)  -- Also correct
WITH ("m" = 16)  -- Wrong - don't quote option names

-- Check value types
WITH (m = 16)  -- Correct (number)
WITH (m = '16')  -- Wrong (string)
WITH (quantization = 'product')  -- Correct (string)
WITH (quantization = product)  -- Wrong (unquoted)

P1-7: AS OF Not Working

Symptom: Query doesn’t return historical data

Possible Causes:

1. Planner not initialized with SQL string
2. AS OF syntax incorrect
3. Timestamp format invalid

Solution:

// Must call with_sql() for AS OF parsing
let planner = Planner::new()
    .with_catalog(&catalog)
    .with_sql(sql.to_string());  // Required!

// Check SQL syntax
"AS OF TIMESTAMP '2025-11-15 06:00:00'"  // Correct
"AS OF TIMESTAMP 2025-11-15 06:00:00"    // Wrong - needs quotes
"ASOF TIMESTAMP '2025-11-15 06:00:00'"   // Wrong - needs space

---

Integration Examples

Complete Example: Time-Travel Query with Indexed Search

-- Step 1: Create table
CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    embedding VECTOR(1536),
    content TEXT
)

-- Step 2: Create optimized vector index
CREATE INDEX embedding_idx ON documents USING hnsw (embedding)
WITH (
    m = 16,
    ef_construction = 200,
    quantization = 'product',
    pq_subquantizers = 8
)

-- Step 3: Query historical data with vector search
SELECT id, content, embedding <-> '[1.0, 2.0, ...]'::vector AS distance
FROM documents AS OF TIMESTAMP '2025-11-15 06:00:00'
ORDER BY distance
LIMIT 10

Branch Workflow with Auto-Cleanup

-- Create feature branch
CREATE BRANCH feature_x FROM main AS OF NOW

-- Switch to feature branch
USE BRANCH feature_x

-- Make changes...
INSERT INTO documents (content, embedding) VALUES (...)

-- Merge back to main with auto-cleanup
MERGE BRANCH feature_x INTO main
WITH (
    conflict_resolution = 'branch_wins',
    delete_branch_after = true  -- Auto-delete after merge
)

---

Performance Tips

P1-5: Branch Deletion

• Branch deletion is fast (single metadata operation)
• No impact on merge performance
• Safe to enable for automated workflows

P1-6: Index Options

• Choose quantization based on dataset size:
  • none: < 10K vectors
  • scalar: 10K-100K vectors
  • product: > 100K vectors
• Tune HNSW parameters:
  • Higher m: Better recall, more memory
  • Higher ef_construction: Better quality, slower build

P1-7: AS OF Queries

• AS OF parsing has negligible overhead
• Time-travel queries may be slower (historical data access)
• Use indexes on historical queries for best performance
• Consider caching frequently accessed timestamps

---

Error Messages

P1-5 Errors

"Merge failed: N conflicts detected. Use conflict_resolution='branch_wins', 'target_wins', or remove the option for auto resolution."

Fix: Resolve conflicts or specify conflict_resolution strategy

P1-6 Errors

"Invalid quantization type: X. Expected 'none', 'scalar', 'product', or 'auto'"

Fix: Use valid quantization type

"Unknown CREATE INDEX option: X"

Fix: Check option name spelling

P1-7 Errors

"Invalid AS OF clause: X. Expected NOW, TIMESTAMP, TRANSACTION, or SCN"

Fix: Use correct AS OF syntax

---

See Also

• Full implementation details: P1_FIXES_IMPLEMENTATION_SUMMARY.md
• Test suite: tests/p1_todo_fixes_tests.rs
• Phase 3 features: docs/implementation/PHASE_3_*
• Time-travel docs: TIME_TRAVEL_IMPLEMENTATION_SUMMARY.md
• Branch storage docs: docs/BRANCH_STORAGE_GUIDE.md