@@ -392,22 +392,111 @@ register("sql.diff", async (params) => {
392392// ---------------------------------------------------------------------------
393393// sql.rewrite
394394// ---------------------------------------------------------------------------
395+
396+ /**
397+ * Detect and rewrite non-sargable function-wrapped predicates in TypeScript.
398+ * Covers YEAR(), MONTH(), DAY(), DATE() wrapped columns in WHERE clauses.
399+ */
400+ function detectNonSargableRewrites ( sql : string ) : Array < {
401+ rule : string
402+ original_fragment : string
403+ rewritten_fragment : string
404+ rewritten_sql : string
405+ explanation : string
406+ confidence : number
407+ } > {
408+ const rewrites : Array < {
409+ rule : string
410+ original_fragment : string
411+ rewritten_fragment : string
412+ rewritten_sql : string
413+ explanation : string
414+ confidence : number
415+ } > = [ ]
416+
417+ // Match YEAR(col) = NNNN pattern
418+ const yearPattern = / \b Y E A R \s * \( \s * ( [ a - z A - Z _ ] [ \w . ] * ) \s * \) \s * = \s * ( \d { 4 } ) \b / gi
419+ let match : RegExpExecArray | null
420+ let rewrittenSql = sql
421+ while ( ( match = yearPattern . exec ( sql ) ) !== null ) {
422+ const col = match [ 1 ]
423+ const year = parseInt ( match [ 2 ] , 10 )
424+ const original = match [ 0 ]
425+ const replacement = `${ col } >= '${ year } -01-01' AND ${ col } < '${ year + 1 } -01-01'`
426+ rewrittenSql = rewrittenSql . replace ( original , replacement )
427+ rewrites . push ( {
428+ rule : "NON_SARGABLE_PREDICATE" ,
429+ original_fragment : original ,
430+ rewritten_fragment : replacement ,
431+ rewritten_sql : "" ,
432+ explanation : `Predicate pushdown: YEAR(${ col } ) = ${ year } → range predicate. Allows index/partition pruning.` ,
433+ confidence : 0.9 ,
434+ } )
435+ }
436+
437+ // Match MONTH(col) = N pattern
438+ const monthPattern = / \b M O N T H \s * \( \s * ( [ a - z A - Z _ ] [ \w . ] * ) \s * \) \s * = \s * ( \d { 1 , 2 } ) \b / gi
439+ while ( ( match = monthPattern . exec ( sql ) ) !== null ) {
440+ const col = match [ 1 ]
441+ const month = parseInt ( match [ 2 ] , 10 )
442+ const original = match [ 0 ]
443+ const paddedMonth = String ( month ) . padStart ( 2 , "0" )
444+ const nextMonth = month === 12 ? "01" : String ( month + 1 ) . padStart ( 2 , "0" )
445+ const yearOffset = month === 12 ? "+1 year" : ""
446+ const replacement = `EXTRACT(MONTH FROM ${ col } ) = ${ month } /* consider range predicate for partition pruning */`
447+ rewrittenSql = rewrittenSql . replace ( original , replacement )
448+ rewrites . push ( {
449+ rule : "NON_SARGABLE_PREDICATE" ,
450+ original_fragment : original ,
451+ rewritten_fragment : replacement ,
452+ rewritten_sql : "" ,
453+ explanation : `Function-wrapped predicate MONTH(${ col } ) prevents index usage. Consider a range predicate if combined with a year filter.` ,
454+ confidence : 0.7 ,
455+ } )
456+ }
457+
458+ // Set the full rewritten SQL on all entries
459+ for ( const r of rewrites ) {
460+ r . rewritten_sql = rewrittenSql
461+ }
462+
463+ return rewrites
464+ }
465+
395466register ( "sql.rewrite" , async ( params ) => {
396467 try {
397468 const schema = schemaOrEmpty ( params . schema_path , params . schema_context )
398469 const raw = core . rewrite ( params . sql , schema )
399470 const result = JSON . parse ( JSON . stringify ( raw ) )
400- return {
401- success : true ,
402- original_sql : params . sql ,
403- rewritten_sql : result . suggestions ?. [ 0 ] ?. rewritten_sql ?? null ,
404- rewrites_applied : result . suggestions ?. map ( ( s : any ) => ( {
471+
472+ // Merge Rust suggestions with TypeScript-level rewrites
473+ const coreSuggestions = result . suggestions ?? [ ]
474+ const tsRewrites = detectNonSargableRewrites ( params . sql )
475+
476+ const allSuggestions = [
477+ ...coreSuggestions . map ( ( s : any ) => ( {
405478 rule : s . rule ,
406479 original_fragment : params . sql ,
407480 rewritten_fragment : s . rewritten_sql ?? params . sql ,
408481 explanation : s . explanation ?? s . improvement ?? "" ,
409482 can_auto_apply : ( s . confidence ?? 0 ) >= 0.7 ,
410- } ) ) ?? [ ] ,
483+ } ) ) ,
484+ ...tsRewrites . map ( ( r ) => ( {
485+ rule : r . rule ,
486+ original_fragment : r . original_fragment ,
487+ rewritten_fragment : r . rewritten_fragment ,
488+ explanation : r . explanation ,
489+ can_auto_apply : r . confidence >= 0.7 ,
490+ } ) ) ,
491+ ]
492+
493+ const bestRewrite = coreSuggestions [ 0 ] ?. rewritten_sql ?? tsRewrites [ 0 ] ?. rewritten_sql ?? null
494+
495+ return {
496+ success : true ,
497+ original_sql : params . sql ,
498+ rewritten_sql : bestRewrite ,
499+ rewrites_applied : allSuggestions ,
411500 }
412501 } catch ( e ) {
413502 return { success : false , original_sql : params . sql , rewritten_sql : null , rewrites_applied : [ ] , error : String ( e ) }
0 commit comments