vibesql_executor/index_ddl/
create_index.rs

1//! CREATE INDEX statement execution
2//!
3//! This module provides the main executor for CREATE INDEX statements,
4//! dispatching to specialized modules based on index type:
5//!
6//! - [`btree_index`] - B-tree indexes (standard and unique)
7//! - [`spatial_index`] - R-tree spatial indexes
8//! - [`vector_index`] - IVFFlat and HNSW vector indexes
9//! - [`expression_index`] - Expression-based functional indexes
10//! - [`validation`] - Pre-creation validation
11
12use vibesql_ast::CreateIndexStmt;
13use vibesql_storage::Database;
14
15use super::{
16    btree_index::create_btree_index,
17    spatial_index::create_spatial_index,
18    validation::{index_already_exists, validate_create_index},
19    vector_index::{create_hnsw_index, create_ivfflat_index},
20};
21use crate::errors::ExecutorError;
22
23/// Executor for CREATE INDEX statements
24pub struct CreateIndexExecutor;
25
26impl CreateIndexExecutor {
27    /// Execute a CREATE INDEX statement
28    ///
29    /// # Arguments
30    ///
31    /// * `stmt` - The CREATE INDEX statement AST node
32    /// * `database` - The database to create the index in
33    ///
34    /// # Returns
35    ///
36    /// Success message or error
37    ///
38    /// # Examples
39    ///
40    /// ```
41    /// use vibesql_ast::{CreateIndexStmt, IndexColumn, OrderDirection};
42    /// use vibesql_executor::CreateIndexExecutor;
43    /// use vibesql_storage::Database;
44    ///
45    /// let mut db = Database::new();
46    /// // First create a table
47    /// // ... (table creation code) ...
48    ///
49    /// let stmt = CreateIndexStmt {
50    ///     index_name: "idx_users_email".to_string(),
51    ///     if_not_exists: false,
52    ///     table_name: "users".to_string(),
53    ///     index_type: vibesql_ast::IndexType::BTree { unique: false },
54    ///     columns: vec![IndexColumn::Column {
55    ///         column_name: "email".to_string(),
56    ///         direction: OrderDirection::Asc,
57    ///         prefix_length: None,
58    ///     }],
59    ///     where_clause: None,
60    /// };
61    ///
62    /// let result = CreateIndexExecutor::execute(&stmt, &mut db);
63    /// // assert!(result.is_ok());
64    /// ```
65    pub fn execute(
66        stmt: &CreateIndexStmt,
67        database: &mut Database,
68    ) -> Result<String, ExecutorError> {
69        // Handle IF NOT EXISTS early (before validation which also checks this)
70        if stmt.if_not_exists && index_already_exists(stmt, database) {
71            return Ok(format!("Index '{}' already exists (skipped)", stmt.index_name));
72        }
73
74        // Validate the CREATE INDEX statement
75        let validation = validate_create_index(stmt, database)?;
76
77        // Partial indexes (CREATE INDEX … WHERE …): the predicate has already
78        // been validated for semantic legality (e.g. no window functions). The
79        // catalog records the predicate via `IndexMetadata::where_clause` so
80        // the FK-mismatch checker and the index-selection planner can
81        // recognise and skip partial indexes. The B-tree index path
82        // (`create_btree_index`) now also evaluates the predicate against
83        // every existing row at build time so the initial index body only
84        // contains matching rows. Subsequent INSERT/UPDATE/DELETE maintenance
85        // is handled by `partial_index_maintenance` on each DML path. See
86        // issue #5214.
87
88        // Dispatch to appropriate index creation based on type
89        match &stmt.index_type {
90            vibesql_ast::IndexType::BTree { unique } => create_btree_index(
91                database,
92                stmt,
93                &validation.table_name,
94                &validation.qualified_table_name,
95                &validation.table_schema,
96                *unique,
97            ),
98
99            vibesql_ast::IndexType::Fulltext => Err(ExecutorError::UnsupportedFeature(
100                "FULLTEXT indexes are not yet implemented".to_string(),
101            )),
102
103            vibesql_ast::IndexType::Spatial => create_spatial_index(
104                database,
105                stmt,
106                &validation.table_name,
107                &validation.qualified_table_name,
108                &validation.table_schema,
109            ),
110
111            vibesql_ast::IndexType::IVFFlat { metric, lists } => create_ivfflat_index(
112                database,
113                stmt,
114                &validation.table_name,
115                &validation.qualified_table_name,
116                &validation.table_schema,
117                *metric,
118                *lists,
119            ),
120
121            vibesql_ast::IndexType::Hnsw { metric, m, ef_construction } => create_hnsw_index(
122                database,
123                stmt,
124                &validation.table_name,
125                &validation.qualified_table_name,
126                &validation.table_schema,
127                *metric,
128                *m,
129                *ef_construction,
130            ),
131        }
132    }
133}
134
135#[cfg(test)]
136mod tests {
137    use vibesql_ast::{ColumnDef, CreateTableStmt, IndexColumn, OrderDirection};
138    use vibesql_storage::Row;
139    use vibesql_types::{DataType, SqlValue};
140
141    use super::*;
142    use crate::CreateTableExecutor;
143
144    fn create_test_table(db: &mut Database) {
145        let stmt = CreateTableStmt {
146            temporary: false,
147            if_not_exists: false,
148            table_name: "users".to_string(),
149            columns: vec![
150                ColumnDef {
151                    name: "id".to_string(),
152                    data_type: DataType::Integer,
153                    nullable: false,
154                    constraints: vec![],
155                    default_value: None,
156                    comment: None,
157                    generated_expr: None,
158                    is_exact_integer_type: false,
159                },
160                ColumnDef {
161                    name: "email".to_string(),
162                    data_type: DataType::Varchar { max_length: Some(255) },
163                    nullable: false,
164                    constraints: vec![],
165                    default_value: None,
166                    comment: None,
167                    generated_expr: None,
168                    is_exact_integer_type: false,
169                },
170                ColumnDef {
171                    name: "name".to_string(),
172                    data_type: DataType::Varchar { max_length: Some(100) },
173                    nullable: true,
174                    constraints: vec![],
175                    default_value: None,
176                    comment: None,
177                    generated_expr: None,
178                    is_exact_integer_type: false,
179                },
180            ],
181            table_constraints: vec![],
182            table_options: vec![],
183            quoted: false,
184            name_source: None,
185            as_query: None,
186            without_rowid: false,
187        };
188
189        CreateTableExecutor::execute(&stmt, db).unwrap();
190    }
191
192    #[test]
193    fn test_create_simple_index() {
194        let mut db = Database::new();
195        create_test_table(&mut db);
196
197        let stmt = CreateIndexStmt {
198            index_name: "idx_users_email".to_string(),
199            if_not_exists: false,
200            table_name: "users".to_string(),
201            index_type: vibesql_ast::IndexType::BTree { unique: false },
202            columns: vec![IndexColumn::Column {
203                column_name: "email".to_string(),
204                direction: OrderDirection::Asc,
205                prefix_length: None,
206            }],
207            where_clause: None,
208        };
209
210        let result = CreateIndexExecutor::execute(&stmt, &mut db);
211        assert!(result.is_ok());
212        assert_eq!(
213            result.unwrap(),
214            "Index 'idx_users_email' created successfully on table 'main.users'"
215        );
216
217        // Verify index exists
218        assert!(db.index_exists("idx_users_email"));
219    }
220
221    #[test]
222    fn test_create_unique_index() {
223        let mut db = Database::new();
224        create_test_table(&mut db);
225
226        let stmt = CreateIndexStmt {
227            index_name: "idx_users_email_unique".to_string(),
228            if_not_exists: false,
229            table_name: "users".to_string(),
230            index_type: vibesql_ast::IndexType::BTree { unique: true },
231            columns: vec![IndexColumn::Column {
232                column_name: "email".to_string(),
233                direction: OrderDirection::Asc,
234                prefix_length: None,
235            }],
236            where_clause: None,
237        };
238
239        let result = CreateIndexExecutor::execute(&stmt, &mut db);
240        assert!(result.is_ok());
241        assert!(db.index_exists("idx_users_email_unique"));
242    }
243
244    #[test]
245    fn test_create_multi_column_index() {
246        let mut db = Database::new();
247        create_test_table(&mut db);
248
249        let stmt = CreateIndexStmt {
250            index_name: "idx_users_email_name".to_string(),
251            if_not_exists: false,
252            table_name: "users".to_string(),
253            index_type: vibesql_ast::IndexType::BTree { unique: false },
254            columns: vec![
255                IndexColumn::Column {
256                    column_name: "email".to_string(),
257                    direction: OrderDirection::Asc,
258                    prefix_length: None,
259                },
260                IndexColumn::Column {
261                    column_name: "name".to_string(),
262                    direction: OrderDirection::Desc,
263                    prefix_length: None,
264                },
265            ],
266            where_clause: None,
267        };
268
269        let result = CreateIndexExecutor::execute(&stmt, &mut db);
270        assert!(result.is_ok());
271    }
272
273    #[test]
274    fn test_create_index_duplicate_name() {
275        let mut db = Database::new();
276        create_test_table(&mut db);
277
278        let stmt = CreateIndexStmt {
279            index_name: "idx_users_email".to_string(),
280            if_not_exists: false,
281            table_name: "users".to_string(),
282            index_type: vibesql_ast::IndexType::BTree { unique: false },
283            columns: vec![IndexColumn::Column {
284                column_name: "email".to_string(),
285                direction: OrderDirection::Asc,
286                prefix_length: None,
287            }],
288            where_clause: None,
289        };
290
291        // First creation succeeds
292        let result = CreateIndexExecutor::execute(&stmt, &mut db);
293        assert!(result.is_ok());
294
295        // Second creation fails
296        let result = CreateIndexExecutor::execute(&stmt, &mut db);
297        assert!(result.is_err());
298        assert!(matches!(result, Err(ExecutorError::IndexAlreadyExists(_))));
299    }
300
301    #[test]
302    fn test_create_index_on_nonexistent_table() {
303        let mut db = Database::new();
304
305        let stmt = CreateIndexStmt {
306            index_name: "idx_nonexistent".to_string(),
307            if_not_exists: false,
308            table_name: "nonexistent_table".to_string(),
309            index_type: vibesql_ast::IndexType::BTree { unique: false },
310            columns: vec![IndexColumn::Column {
311                column_name: "id".to_string(),
312                direction: OrderDirection::Asc,
313                prefix_length: None,
314            }],
315            where_clause: None,
316        };
317
318        let result = CreateIndexExecutor::execute(&stmt, &mut db);
319        assert!(result.is_err());
320        assert!(matches!(result, Err(ExecutorError::TableNotFound(_))));
321    }
322
323    #[test]
324    fn test_create_index_on_nonexistent_column() {
325        let mut db = Database::new();
326        create_test_table(&mut db);
327
328        let stmt = CreateIndexStmt {
329            index_name: "idx_users_nonexistent".to_string(),
330            if_not_exists: false,
331            table_name: "users".to_string(),
332            index_type: vibesql_ast::IndexType::BTree { unique: false },
333            columns: vec![IndexColumn::Column {
334                column_name: "nonexistent_column".to_string(),
335                direction: OrderDirection::Asc,
336                prefix_length: None,
337            }],
338            where_clause: None,
339        };
340
341        let result = CreateIndexExecutor::execute(&stmt, &mut db);
342        assert!(result.is_err());
343        assert!(matches!(result, Err(ExecutorError::ColumnNotFound { .. })));
344    }
345
346    #[test]
347    fn test_create_index_if_not_exists_when_not_exists() {
348        let mut db = Database::new();
349        create_test_table(&mut db);
350
351        let stmt = CreateIndexStmt {
352            index_name: "idx_users_email".to_string(),
353            if_not_exists: true,
354            table_name: "users".to_string(),
355            index_type: vibesql_ast::IndexType::BTree { unique: false },
356            columns: vec![IndexColumn::Column {
357                column_name: "email".to_string(),
358                direction: OrderDirection::Asc,
359                prefix_length: None,
360            }],
361            where_clause: None,
362        };
363
364        let result = CreateIndexExecutor::execute(&stmt, &mut db);
365        assert!(result.is_ok());
366        assert_eq!(
367            result.unwrap(),
368            "Index 'idx_users_email' created successfully on table 'main.users'"
369        );
370        assert!(db.index_exists("idx_users_email"));
371    }
372
373    #[test]
374    fn test_create_index_if_not_exists_when_exists() {
375        let mut db = Database::new();
376        create_test_table(&mut db);
377
378        // First creation
379        let stmt = CreateIndexStmt {
380            index_name: "idx_users_email".to_string(),
381            if_not_exists: false,
382            table_name: "users".to_string(),
383            index_type: vibesql_ast::IndexType::BTree { unique: false },
384            columns: vec![IndexColumn::Column {
385                column_name: "email".to_string(),
386                direction: OrderDirection::Asc,
387                prefix_length: None,
388            }],
389            where_clause: None,
390        };
391        CreateIndexExecutor::execute(&stmt, &mut db).unwrap();
392
393        // Second creation with IF NOT EXISTS should succeed
394        let stmt_with_if_not_exists = CreateIndexStmt {
395            index_name: "idx_users_email".to_string(),
396            if_not_exists: true,
397            table_name: "users".to_string(),
398            index_type: vibesql_ast::IndexType::BTree { unique: false },
399            columns: vec![IndexColumn::Column {
400                column_name: "email".to_string(),
401                direction: OrderDirection::Asc,
402                prefix_length: None,
403            }],
404            where_clause: None,
405        };
406        let result = CreateIndexExecutor::execute(&stmt_with_if_not_exists, &mut db);
407        assert!(result.is_ok());
408        assert!(db.index_exists("idx_users_email"));
409    }
410
411    #[test]
412    fn test_create_index_with_schema_qualified_table() {
413        let mut db = Database::new();
414        create_test_table(&mut db);
415
416        // Create index using schema-qualified table name (with default main schema)
417        let index_stmt = CreateIndexStmt {
418            index_name: "idx_users_email_qualified".to_string(),
419            if_not_exists: false,
420            table_name: "main.users".to_string(), // Explicitly qualify with main schema
421            index_type: vibesql_ast::IndexType::BTree { unique: false },
422            columns: vec![IndexColumn::Column {
423                column_name: "email".to_string(),
424                direction: OrderDirection::Asc,
425                prefix_length: None,
426            }],
427            where_clause: None,
428        };
429
430        let result = CreateIndexExecutor::execute(&index_stmt, &mut db);
431        assert!(result.is_ok());
432        assert_eq!(
433            result.unwrap(),
434            "Index 'idx_users_email_qualified' created successfully on table 'main.users'"
435        );
436
437        // Verify index exists
438        assert!(db.index_exists("idx_users_email_qualified"));
439    }
440
441    /// Build a `CreateTableStmt` with two unconstrained columns (a, b).
442    /// `temporary` selects between a main table and a TEMP table.
443    fn simple_ab_table(name: &str, temporary: bool) -> CreateTableStmt {
444        CreateTableStmt {
445            temporary,
446            if_not_exists: false,
447            table_name: name.to_string(),
448            columns: vec![
449                ColumnDef {
450                    name: "a".to_string(),
451                    data_type: DataType::Integer,
452                    nullable: true,
453                    constraints: vec![],
454                    default_value: None,
455                    comment: None,
456                    generated_expr: None,
457                    is_exact_integer_type: false,
458                },
459                ColumnDef {
460                    name: "b".to_string(),
461                    data_type: DataType::Integer,
462                    nullable: true,
463                    constraints: vec![],
464                    default_value: None,
465                    comment: None,
466                    generated_expr: None,
467                    is_exact_integer_type: false,
468                },
469            ],
470            table_constraints: vec![],
471            table_options: vec![],
472            quoted: false,
473            name_source: None,
474            as_query: None,
475            without_rowid: false,
476        }
477    }
478
479    fn index_on_b(index_name: &str, table_name: &str) -> CreateIndexStmt {
480        CreateIndexStmt {
481            index_name: index_name.to_string(),
482            if_not_exists: false,
483            table_name: table_name.to_string(),
484            index_type: vibesql_ast::IndexType::BTree { unique: false },
485            columns: vec![IndexColumn::Column {
486                column_name: "b".to_string(),
487                direction: OrderDirection::Asc,
488                prefix_length: None,
489            }],
490            where_clause: None,
491        }
492    }
493
494    /// Regression test for #5505: `CREATE INDEX` on an unqualified TEMP table
495    /// must resolve the table in the session temp schema, not assume `main`.
496    #[test]
497    fn test_create_index_on_temp_table_resolves_temp_schema() {
498        let mut db = Database::new();
499
500        // CREATE TEMP TABLE tbl(a, b)
501        CreateTableExecutor::execute(&simple_ab_table("tbl", true), &mut db).unwrap();
502
503        // CREATE INDEX tbl_idx ON tbl(b) -- unqualified, must find the temp table
504        let result = CreateIndexExecutor::execute(&index_on_b("tbl_idx", "tbl"), &mut db);
505        assert!(
506            result.is_ok(),
507            "CREATE INDEX on a temp table should succeed (got {:?})",
508            result.err()
509        );
510
511        // The index must be reported against the temp schema, matching sqlite3
512        // which records the index in sqlite_temp_master.
513        let msg = result.unwrap();
514        assert!(
515            msg.contains(&format!("{}.tbl", db.catalog.temp_schema_name())),
516            "index should be created in the temp schema, got: {msg}"
517        );
518
519        // Index exists and is honored against live temp-table data.
520        assert!(db.index_exists("tbl_idx"));
521
522        db.insert_row("tbl", Row::new(vec![SqlValue::Integer(1), SqlValue::Integer(2)])).unwrap();
523        db.insert_row("tbl", Row::new(vec![SqlValue::Integer(3), SqlValue::Integer(4)])).unwrap();
524
525        let rows: Vec<_> = db.get_table("tbl").unwrap().scan_live().collect();
526        assert_eq!(rows.len(), 2, "temp table should hold the inserted rows");
527    }
528
529    /// Regression test for #5505: when a table of the same name exists in both
530    /// `main` and the temp schema, an unqualified `CREATE INDEX` resolves to the
531    /// TEMP table (temp shadows main), matching sqlite3 3.51.0.
532    #[test]
533    fn test_create_index_temp_shadows_main() {
534        let mut db = Database::new();
535
536        // main.t and a shadowing temp.t
537        CreateTableExecutor::execute(&simple_ab_table("t", false), &mut db).unwrap();
538        CreateTableExecutor::execute(&simple_ab_table("t", true), &mut db).unwrap();
539
540        let result = CreateIndexExecutor::execute(&index_on_b("ix", "t"), &mut db);
541        assert!(result.is_ok(), "CREATE INDEX should resolve to temp.t (got {:?})", result.err());
542
543        let msg = result.unwrap();
544        assert!(
545            msg.contains(&format!("{}.t", db.catalog.temp_schema_name())),
546            "unqualified index target must shadow to the temp table, got: {msg}"
547        );
548        assert!(db.index_exists("ix"));
549    }
550
551    #[test]
552    fn test_create_index_on_nonexistent_schema_qualified_table() {
553        let mut db = Database::new();
554
555        // Create a custom schema
556        db.catalog.create_schema("test_schema".to_string()).unwrap();
557
558        // Try to create index on non-existent table
559        let index_stmt = CreateIndexStmt {
560            index_name: "idx_nonexistent".to_string(),
561            if_not_exists: false,
562            table_name: "test_schema.nonexistent_table".to_string(),
563            index_type: vibesql_ast::IndexType::BTree { unique: false },
564            columns: vec![IndexColumn::Column {
565                column_name: "id".to_string(),
566                direction: OrderDirection::Asc,
567                prefix_length: None,
568            }],
569            where_clause: None,
570        };
571
572        let result = CreateIndexExecutor::execute(&index_stmt, &mut db);
573        assert!(result.is_err());
574        assert!(matches!(result, Err(ExecutorError::TableNotFound(_))));
575    }
576
577    // ========================================================================
578    // IVFFlat Index Tests
579    // ========================================================================
580
581    fn create_vector_table(db: &mut Database) {
582        let stmt = CreateTableStmt {
583            temporary: false,
584            if_not_exists: false,
585            table_name: "documents".to_string(),
586            columns: vec![
587                ColumnDef {
588                    name: "id".to_string(),
589                    data_type: DataType::Integer,
590                    nullable: false,
591                    constraints: vec![],
592                    default_value: None,
593                    comment: None,
594                    generated_expr: None,
595                    is_exact_integer_type: false,
596                },
597                ColumnDef {
598                    name: "embedding".to_string(),
599                    data_type: DataType::Vector { dimensions: 3 },
600                    nullable: true,
601                    constraints: vec![],
602                    default_value: None,
603                    comment: None,
604                    generated_expr: None,
605                    is_exact_integer_type: false,
606                },
607                ColumnDef {
608                    name: "content".to_string(),
609                    data_type: DataType::Varchar { max_length: Some(1000) },
610                    nullable: true,
611                    constraints: vec![],
612                    default_value: None,
613                    comment: None,
614                    generated_expr: None,
615                    is_exact_integer_type: false,
616                },
617            ],
618            table_constraints: vec![],
619            table_options: vec![],
620            quoted: false,
621            name_source: None,
622            as_query: None,
623            without_rowid: false,
624        };
625
626        CreateTableExecutor::execute(&stmt, db).unwrap();
627    }
628
629    #[test]
630    fn test_create_ivfflat_index_l2() {
631        let mut db = Database::new();
632        create_vector_table(&mut db);
633
634        let stmt = CreateIndexStmt {
635            index_name: "idx_documents_embedding".to_string(),
636            if_not_exists: false,
637            table_name: "documents".to_string(),
638            index_type: vibesql_ast::IndexType::IVFFlat {
639                metric: vibesql_ast::VectorDistanceMetric::L2,
640                lists: 4,
641            },
642            columns: vec![IndexColumn::Column {
643                column_name: "embedding".to_string(),
644                direction: OrderDirection::Asc,
645                prefix_length: None,
646            }],
647            where_clause: None,
648        };
649
650        let result = CreateIndexExecutor::execute(&stmt, &mut db);
651        assert!(result.is_ok(), "IVFFlat index creation failed: {:?}", result.err());
652        assert!(result
653            .unwrap()
654            .contains("IVFFlat index 'idx_documents_embedding' created successfully"));
655        assert!(db.index_exists("idx_documents_embedding"));
656    }
657
658    #[test]
659    fn test_create_ivfflat_index_cosine() {
660        let mut db = Database::new();
661        create_vector_table(&mut db);
662
663        let stmt = CreateIndexStmt {
664            index_name: "idx_documents_cosine".to_string(),
665            if_not_exists: false,
666            table_name: "documents".to_string(),
667            index_type: vibesql_ast::IndexType::IVFFlat {
668                metric: vibesql_ast::VectorDistanceMetric::Cosine,
669                lists: 4,
670            },
671            columns: vec![IndexColumn::Column {
672                column_name: "embedding".to_string(),
673                direction: OrderDirection::Asc,
674                prefix_length: None,
675            }],
676            where_clause: None,
677        };
678
679        let result = CreateIndexExecutor::execute(&stmt, &mut db);
680        assert!(result.is_ok());
681        assert!(db.index_exists("idx_documents_cosine"));
682    }
683
684    #[test]
685    fn test_create_ivfflat_index_inner_product() {
686        let mut db = Database::new();
687        create_vector_table(&mut db);
688
689        let stmt = CreateIndexStmt {
690            index_name: "idx_documents_ip".to_string(),
691            if_not_exists: false,
692            table_name: "documents".to_string(),
693            index_type: vibesql_ast::IndexType::IVFFlat {
694                metric: vibesql_ast::VectorDistanceMetric::InnerProduct,
695                lists: 4,
696            },
697            columns: vec![IndexColumn::Column {
698                column_name: "embedding".to_string(),
699                direction: OrderDirection::Asc,
700                prefix_length: None,
701            }],
702            where_clause: None,
703        };
704
705        let result = CreateIndexExecutor::execute(&stmt, &mut db);
706        assert!(result.is_ok());
707        assert!(db.index_exists("idx_documents_ip"));
708    }
709
710    #[test]
711    fn test_create_ivfflat_index_on_non_vector_column() {
712        let mut db = Database::new();
713        create_vector_table(&mut db);
714
715        // Try to create IVFFlat index on a non-vector column
716        let stmt = CreateIndexStmt {
717            index_name: "idx_documents_content".to_string(),
718            if_not_exists: false,
719            table_name: "documents".to_string(),
720            index_type: vibesql_ast::IndexType::IVFFlat {
721                metric: vibesql_ast::VectorDistanceMetric::L2,
722                lists: 4,
723            },
724            columns: vec![IndexColumn::Column {
725                column_name: "content".to_string(),
726                direction: OrderDirection::Asc,
727                prefix_length: None,
728            }],
729            where_clause: None,
730        };
731
732        let result = CreateIndexExecutor::execute(&stmt, &mut db);
733        assert!(result.is_err());
734        assert!(matches!(result, Err(ExecutorError::InvalidIndexDefinition(_))));
735    }
736
737    #[test]
738    fn test_create_ivfflat_index_multiple_columns_fails() {
739        let mut db = Database::new();
740        create_vector_table(&mut db);
741
742        // IVFFlat indexes must be on exactly one column
743        let stmt = CreateIndexStmt {
744            index_name: "idx_documents_multi".to_string(),
745            if_not_exists: false,
746            table_name: "documents".to_string(),
747            index_type: vibesql_ast::IndexType::IVFFlat {
748                metric: vibesql_ast::VectorDistanceMetric::L2,
749                lists: 4,
750            },
751            columns: vec![
752                IndexColumn::Column {
753                    column_name: "embedding".to_string(),
754                    direction: OrderDirection::Asc,
755                    prefix_length: None,
756                },
757                IndexColumn::Column {
758                    column_name: "id".to_string(),
759                    direction: OrderDirection::Asc,
760                    prefix_length: None,
761                },
762            ],
763            where_clause: None,
764        };
765
766        let result = CreateIndexExecutor::execute(&stmt, &mut db);
767        assert!(result.is_err());
768        assert!(matches!(result, Err(ExecutorError::InvalidIndexDefinition(_))));
769    }
770
771    #[test]
772    fn test_create_ivfflat_index_if_not_exists() {
773        let mut db = Database::new();
774        create_vector_table(&mut db);
775
776        let stmt = CreateIndexStmt {
777            index_name: "idx_documents_embedding".to_string(),
778            if_not_exists: true,
779            table_name: "documents".to_string(),
780            index_type: vibesql_ast::IndexType::IVFFlat {
781                metric: vibesql_ast::VectorDistanceMetric::L2,
782                lists: 4,
783            },
784            columns: vec![IndexColumn::Column {
785                column_name: "embedding".to_string(),
786                direction: OrderDirection::Asc,
787                prefix_length: None,
788            }],
789            where_clause: None,
790        };
791
792        // First creation
793        let result = CreateIndexExecutor::execute(&stmt, &mut db);
794        assert!(result.is_ok());
795
796        // Second creation with IF NOT EXISTS should succeed
797        let result2 = CreateIndexExecutor::execute(&stmt, &mut db);
798        assert!(result2.is_ok());
799        assert!(result2.unwrap().contains("already exists"));
800    }
801
802    #[test]
803    fn test_ivfflat_index_search() {
804        let mut db = Database::new();
805        create_vector_table(&mut db);
806
807        // Insert test vector data
808        // Row 0: [1.0, 0.0, 0.0] - should be closest to query [0.9, 0.1, 0.0]
809        db.insert_row(
810            "documents",
811            Row::new(vec![
812                SqlValue::Integer(1),
813                SqlValue::Vector(vec![1.0, 0.0, 0.0]),
814                SqlValue::Varchar(arcstr::ArcStr::from("doc1")),
815            ]),
816        )
817        .unwrap();
818
819        // Row 1: [0.0, 1.0, 0.0]
820        db.insert_row(
821            "documents",
822            Row::new(vec![
823                SqlValue::Integer(2),
824                SqlValue::Vector(vec![0.0, 1.0, 0.0]),
825                SqlValue::Varchar(arcstr::ArcStr::from("doc2")),
826            ]),
827        )
828        .unwrap();
829
830        // Row 2: [0.0, 0.0, 1.0]
831        db.insert_row(
832            "documents",
833            Row::new(vec![
834                SqlValue::Integer(3),
835                SqlValue::Vector(vec![0.0, 0.0, 1.0]),
836                SqlValue::Varchar(arcstr::ArcStr::from("doc3")),
837            ]),
838        )
839        .unwrap();
840
841        // Row 3: [0.5, 0.5, 0.0] - second closest to query
842        db.insert_row(
843            "documents",
844            Row::new(vec![
845                SqlValue::Integer(4),
846                SqlValue::Vector(vec![0.5, 0.5, 0.0]),
847                SqlValue::Varchar(arcstr::ArcStr::from("doc4")),
848            ]),
849        )
850        .unwrap();
851
852        // Create IVFFlat index (should build on existing data)
853        let stmt = CreateIndexStmt {
854            index_name: "idx_embedding".to_string(),
855            if_not_exists: false,
856            table_name: "documents".to_string(),
857            index_type: vibesql_ast::IndexType::IVFFlat {
858                metric: vibesql_ast::VectorDistanceMetric::L2,
859                lists: 2, // 2 clusters for small test data
860            },
861            columns: vec![IndexColumn::Column {
862                column_name: "embedding".to_string(),
863                direction: OrderDirection::Asc,
864                prefix_length: None,
865            }],
866            where_clause: None,
867        };
868
869        let result = CreateIndexExecutor::execute(&stmt, &mut db);
870        assert!(result.is_ok(), "Index creation failed: {:?}", result.err());
871
872        // Test search API
873        // Query vector near [1.0, 0.0, 0.0]
874        let query_vector = vec![0.9, 0.1, 0.0];
875        let results = db.search_ivfflat_index("idx_embedding", &query_vector, 2);
876        assert!(results.is_ok(), "Search should succeed: {:?}", results.err());
877
878        let neighbors = results.unwrap();
879        // Should find at least the nearest vectors
880        assert!(!neighbors.is_empty(), "Should find at least one neighbor");
881
882        // The closest vector should be [1.0, 0.0, 0.0] (row 0)
883        let (first_row_id, first_distance) = neighbors[0];
884        assert!(first_distance >= 0.0, "Distance should be non-negative");
885        // Since we inserted [1.0, 0.0, 0.0] at row 0, it should be closest
886        assert_eq!(first_row_id, 0, "First result should be the closest vector");
887    }
888
889    #[test]
890    fn test_ivfflat_get_indexes_for_table() {
891        let mut db = Database::new();
892        create_vector_table(&mut db);
893
894        // Create IVFFlat index
895        let stmt = CreateIndexStmt {
896            index_name: "idx_vec".to_string(),
897            if_not_exists: false,
898            table_name: "documents".to_string(),
899            index_type: vibesql_ast::IndexType::IVFFlat {
900                metric: vibesql_ast::VectorDistanceMetric::Cosine,
901                lists: 2,
902            },
903            columns: vec![IndexColumn::Column {
904                column_name: "embedding".to_string(),
905                direction: OrderDirection::Asc,
906                prefix_length: None,
907            }],
908            where_clause: None,
909        };
910
911        let result = CreateIndexExecutor::execute(&stmt, &mut db);
912        assert!(result.is_ok());
913
914        // Test getting IVFFlat indexes for the table
915        let ivfflat_indexes = db.get_ivfflat_indexes_for_table("documents");
916        assert_eq!(ivfflat_indexes.len(), 1, "Should have one IVFFlat index");
917
918        let (metadata, index) = &ivfflat_indexes[0];
919        assert!(metadata.index_name.to_uppercase().contains("IDX_VEC"));
920        assert_eq!(index.metric(), vibesql_ast::VectorDistanceMetric::Cosine);
921    }
922
923    #[test]
924    fn test_ivfflat_set_probes() {
925        let mut db = Database::new();
926        create_vector_table(&mut db);
927
928        // Create IVFFlat index
929        let stmt = CreateIndexStmt {
930            index_name: "idx_probes".to_string(),
931            if_not_exists: false,
932            table_name: "documents".to_string(),
933            index_type: vibesql_ast::IndexType::IVFFlat {
934                metric: vibesql_ast::VectorDistanceMetric::L2,
935                lists: 4,
936            },
937            columns: vec![IndexColumn::Column {
938                column_name: "embedding".to_string(),
939                direction: OrderDirection::Asc,
940                prefix_length: None,
941            }],
942            where_clause: None,
943        };
944
945        let result = CreateIndexExecutor::execute(&stmt, &mut db);
946        assert!(result.is_ok());
947
948        // Set probes to search more clusters (improves recall at cost of speed)
949        let set_probes_result = db.set_ivfflat_probes("idx_probes", 3);
950        assert!(set_probes_result.is_ok());
951
952        // Verify the index can still be searched
953        let query_vector = vec![0.5, 0.5, 0.5];
954        let search_result = db.search_ivfflat_index("idx_probes", &query_vector, 3);
955        assert!(search_result.is_ok());
956    }
957
958    // ========================================================================
959    // Expression Index Tests
960    // ========================================================================
961
962    #[test]
963    fn test_create_expression_index_lower() {
964        let mut db = Database::new();
965        create_test_table(&mut db);
966
967        // Insert some test data
968        db.insert_row(
969            "users",
970            Row::new(vec![
971                SqlValue::Integer(1),
972                SqlValue::Varchar(arcstr::ArcStr::from("Test@Example.COM")),
973                SqlValue::Varchar(arcstr::ArcStr::from("John")),
974            ]),
975        )
976        .unwrap();
977
978        // Create expression index on LOWER(email)
979        let stmt = CreateIndexStmt {
980            index_name: "idx_users_email_lower".to_string(),
981            if_not_exists: false,
982            table_name: "users".to_string(),
983            index_type: vibesql_ast::IndexType::BTree { unique: false },
984            columns: vec![IndexColumn::new_expression(
985                vibesql_ast::Expression::Function {
986                    name: vibesql_ast::FunctionIdentifier::new("lower"),
987                    args: vec![vibesql_ast::Expression::ColumnRef(
988                        vibesql_ast::ColumnIdentifier::simple("email", false),
989                    )],
990                    character_unit: None,
991                },
992                OrderDirection::Asc,
993            )],
994            where_clause: None,
995        };
996
997        let result = CreateIndexExecutor::execute(&stmt, &mut db);
998        assert!(result.is_ok(), "Expression index creation failed: {:?}", result.err());
999        assert!(db.index_exists("idx_users_email_lower"));
1000    }
1001
1002    #[test]
1003    fn test_create_expression_index_arithmetic() {
1004        let mut db = Database::new();
1005
1006        // Create a table with numeric columns
1007        let table_stmt = vibesql_ast::CreateTableStmt {
1008            temporary: false,
1009            if_not_exists: false,
1010            table_name: "products".to_string(),
1011            columns: vec![
1012                vibesql_ast::ColumnDef {
1013                    name: "id".to_string(),
1014                    data_type: DataType::Integer,
1015                    nullable: false,
1016                    constraints: vec![],
1017                    default_value: None,
1018                    comment: None,
1019                    generated_expr: None,
1020                    is_exact_integer_type: false,
1021                },
1022                vibesql_ast::ColumnDef {
1023                    name: "price".to_string(),
1024                    data_type: DataType::Integer,
1025                    nullable: false,
1026                    constraints: vec![],
1027                    default_value: None,
1028                    comment: None,
1029                    generated_expr: None,
1030                    is_exact_integer_type: false,
1031                },
1032                vibesql_ast::ColumnDef {
1033                    name: "discount".to_string(),
1034                    data_type: DataType::Integer,
1035                    nullable: false,
1036                    constraints: vec![],
1037                    default_value: None,
1038                    comment: None,
1039                    generated_expr: None,
1040                    is_exact_integer_type: false,
1041                },
1042            ],
1043            table_constraints: vec![],
1044            table_options: vec![],
1045            quoted: false,
1046            name_source: None,
1047            as_query: None,
1048            without_rowid: false,
1049        };
1050        crate::CreateTableExecutor::execute(&table_stmt, &mut db).unwrap();
1051
1052        // Insert test data
1053        db.insert_row(
1054            "products",
1055            Row::new(vec![SqlValue::Integer(1), SqlValue::Integer(100), SqlValue::Integer(10)]),
1056        )
1057        .unwrap();
1058
1059        // Create expression index on (price - discount)
1060        let stmt = CreateIndexStmt {
1061            index_name: "idx_products_net_price".to_string(),
1062            if_not_exists: false,
1063            table_name: "products".to_string(),
1064            index_type: vibesql_ast::IndexType::BTree { unique: false },
1065            columns: vec![IndexColumn::new_expression(
1066                vibesql_ast::Expression::BinaryOp {
1067                    op: vibesql_ast::BinaryOperator::Minus,
1068                    left: Box::new(vibesql_ast::Expression::ColumnRef(
1069                        vibesql_ast::ColumnIdentifier::simple("price", false),
1070                    )),
1071                    right: Box::new(vibesql_ast::Expression::ColumnRef(
1072                        vibesql_ast::ColumnIdentifier::simple("discount", false),
1073                    )),
1074                },
1075                OrderDirection::Asc,
1076            )],
1077            where_clause: None,
1078        };
1079
1080        let result = CreateIndexExecutor::execute(&stmt, &mut db);
1081        assert!(result.is_ok(), "Arithmetic expression index creation failed: {:?}", result.err());
1082        assert!(db.index_exists("idx_products_net_price"));
1083    }
1084
1085    #[test]
1086    fn test_create_unique_expression_index() {
1087        let mut db = Database::new();
1088        create_test_table(&mut db);
1089
1090        // Insert data with different emails but same lowercase
1091        db.insert_row(
1092            "users",
1093            Row::new(vec![
1094                SqlValue::Integer(1),
1095                SqlValue::Varchar(arcstr::ArcStr::from("user@example.com")),
1096                SqlValue::Varchar(arcstr::ArcStr::from("John")),
1097            ]),
1098        )
1099        .unwrap();
1100
1101        // Create unique expression index on LOWER(email)
1102        let stmt = CreateIndexStmt {
1103            index_name: "idx_users_email_lower_unique".to_string(),
1104            if_not_exists: false,
1105            table_name: "users".to_string(),
1106            index_type: vibesql_ast::IndexType::BTree { unique: true },
1107            columns: vec![IndexColumn::new_expression(
1108                vibesql_ast::Expression::Function {
1109                    name: vibesql_ast::FunctionIdentifier::new("lower"),
1110                    args: vec![vibesql_ast::Expression::ColumnRef(
1111                        vibesql_ast::ColumnIdentifier::simple("email", false),
1112                    )],
1113                    character_unit: None,
1114                },
1115                OrderDirection::Asc,
1116            )],
1117            where_clause: None,
1118        };
1119
1120        let result = CreateIndexExecutor::execute(&stmt, &mut db);
1121        assert!(result.is_ok(), "Unique expression index creation failed: {:?}", result.err());
1122        assert!(db.index_exists("idx_users_email_lower_unique"));
1123    }
1124
1125    #[test]
1126    fn test_create_expression_index_rejects_non_deterministic() {
1127        let mut db = Database::new();
1128        create_test_table(&mut db);
1129
1130        // Try to create expression index on RANDOM() - should fail
1131        let stmt = CreateIndexStmt {
1132            index_name: "idx_users_random".to_string(),
1133            if_not_exists: false,
1134            table_name: "users".to_string(),
1135            index_type: vibesql_ast::IndexType::BTree { unique: false },
1136            columns: vec![IndexColumn::new_expression(
1137                vibesql_ast::Expression::Function {
1138                    name: vibesql_ast::FunctionIdentifier::new("random"),
1139                    args: vec![],
1140                    character_unit: None,
1141                },
1142                OrderDirection::Asc,
1143            )],
1144            where_clause: None,
1145        };
1146
1147        let result = CreateIndexExecutor::execute(&stmt, &mut db);
1148        assert!(result.is_err(), "Non-deterministic expression index should fail");
1149        assert!(matches!(result, Err(ExecutorError::UnsupportedFeature(_))));
1150    }
1151
1152    #[test]
1153    fn test_create_expression_index_validates_column_references() {
1154        let mut db = Database::new();
1155        create_test_table(&mut db);
1156
1157        // Try to create expression index with non-existent column
1158        let stmt = CreateIndexStmt {
1159            index_name: "idx_users_bad_col".to_string(),
1160            if_not_exists: false,
1161            table_name: "users".to_string(),
1162            index_type: vibesql_ast::IndexType::BTree { unique: false },
1163            columns: vec![IndexColumn::new_expression(
1164                vibesql_ast::Expression::Function {
1165                    name: vibesql_ast::FunctionIdentifier::new("lower"),
1166                    args: vec![vibesql_ast::Expression::ColumnRef(
1167                        vibesql_ast::ColumnIdentifier::simple("nonexistent_column", false),
1168                    )],
1169                    character_unit: None,
1170                },
1171                OrderDirection::Asc,
1172            )],
1173            where_clause: None,
1174        };
1175
1176        let result = CreateIndexExecutor::execute(&stmt, &mut db);
1177        assert!(result.is_err(), "Expression index with non-existent column should fail");
1178        assert!(matches!(result, Err(ExecutorError::ColumnNotFound { .. })));
1179    }
1180
1181    #[test]
1182    fn test_create_expression_index_handles_null() {
1183        let mut db = Database::new();
1184        create_test_table(&mut db);
1185
1186        // Insert row with NULL name
1187        db.insert_row(
1188            "users",
1189            Row::new(vec![
1190                SqlValue::Integer(1),
1191                SqlValue::Varchar(arcstr::ArcStr::from("user@example.com")),
1192                SqlValue::Null, // NULL name
1193            ]),
1194        )
1195        .unwrap();
1196
1197        // Create expression index on LOWER(name) - should handle NULL
1198        let stmt = CreateIndexStmt {
1199            index_name: "idx_users_name_lower".to_string(),
1200            if_not_exists: false,
1201            table_name: "users".to_string(),
1202            index_type: vibesql_ast::IndexType::BTree { unique: false },
1203            columns: vec![IndexColumn::new_expression(
1204                vibesql_ast::Expression::Function {
1205                    name: vibesql_ast::FunctionIdentifier::new("lower"),
1206                    args: vec![vibesql_ast::Expression::ColumnRef(
1207                        vibesql_ast::ColumnIdentifier::simple("name", false),
1208                    )],
1209                    character_unit: None,
1210                },
1211                OrderDirection::Asc,
1212            )],
1213            where_clause: None,
1214        };
1215
1216        let result = CreateIndexExecutor::execute(&stmt, &mut db);
1217        assert!(result.is_ok(), "Expression index with NULL should succeed: {:?}", result.err());
1218        assert!(db.index_exists("idx_users_name_lower"));
1219    }
1220}
vibesql_executor/index_ddl/create_index.rs

vibesql_executor/index_ddl/
create_index.rs
