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

vibesql_executor/index_ddl/
create_index.rs
