fix(parser): ILIKE + PIVOT/UNPIVOT for Snowflake (#483)

[ajitpratap0]

Summary

Two Snowflake correctness fixes from the QA sweep (#483).

• ILIKE: was rejected in every non-PostgreSQL dialect with the error "ILIKE is a PostgreSQL-specific operator". Snowflake and ClickHouse natively support it. Allow it in those dialects; update the rejection message for the remaining ones.
• PIVOT/UNPIVOT: implemented in feat(parser): add SQL Server PIVOT/UNPIVOT clause parsing #477 and gated to SQL Server / Oracle. Snowflake supports the same syntax — extend the gate.

Test plan

• New tests in pkg/sql/parser/snowflake_ilike_pivot_test.go
• go test -race ./pkg/sql/parser/ ./pkg/gosqlx/ green

Part of #483.

🤖 Generated with Claude Code

[vercel]

The latest updates on your projects. Learn more about Vercel for GitHub.

Project	Deployment	Actions	Updated (UTC)
gosqlx-website	Ready	Preview, Comment	Apr 7, 2026 8:04am

[github-actions]

Claude finished @ajitpratap0's task —— View job

---

Code Review Complete ✅

Reviewing PR for Snowflake ILIKE and PIVOT/UNPIVOT support:

Review Tasks

• Read and analyze changed files
• Review ILIKE operator implementation
• Review PIVOT/UNPIVOT changes
• Examine test coverage
• Assess code quality and best practices
• Check for potential bugs/issues
• Evaluate performance considerations
• Review security implications
• Provide final feedback

---

Review Feedback

✅ Overall Assessment: LGTM with Minor Suggestions

This is a well-implemented fix that correctly addresses the Snowflake dialect support issues. The changes are minimal, focused, and follow the existing codebase patterns.

Code Quality & Implementation

🟢 ILIKE Implementation (expressions_operators.go:95-110)

• Correct approach: Extended the dialect allowlist to include Snowflake and ClickHouse alongside PostgreSQL
• Good error messaging: Updated error message from "PostgreSQL-specific" to a more accurate "not supported in %s" format
• Proper placement: Logic is correctly placed within the existing LIKE/ILIKE parsing flow
• Follows patterns: Consistent with existing dialect-specific feature gating

🟢 PIVOT/UNPIVOT Implementation (pivot.go:71-75)

• Minimal change: Simply added Snowflake to the existing allowlist
• Maintains compatibility: No breaking changes to existing SQL Server/Oracle support
• Correct logic: Uses the same OR-based dialect check pattern used elsewhere

Test Coverage

🟢 Comprehensive test suite (snowflake_ilike_pivot_test.go):

• ✅ Tests both positive cases (Snowflake/ClickHouse ILIKE acceptance)
• ✅ Tests negative cases (MySQL ILIKE rejection)
• ✅ Covers NOT ILIKE variant
• ✅ Tests both PIVOT and UNPIVOT for Snowflake
• ✅ Uses realistic SQL examples with proper syntax
• ✅ Good error message validation

Security & Performance

🟢 No concerns identified:

• Changes only affect parsing logic, not execution
• No new attack vectors introduced
• Existing validation and error handling preserved
• No performance impact (same code paths, just expanded dialect checks)

Minor Suggestions for Future Consideration

1. Documentation: Consider updating any dialect compatibility documentation to reflect ILIKE support in Snowflake/ClickHouse

2. Error message consistency: The ILIKE error message could be made more helpful by suggesting alternatives:

   "ILIKE is not supported in %s; use LIKE with UPPER/LOWER functions for case-insensitive matching"

3. Test organization: Consider if these tests belong in a dedicated dialect-specific test directory structure for easier maintenance

Code Correctness

• ✅ Proper handling of NOT ILIKE prefix in expressions_operators.go:45
• ✅ Correct dialect string comparisons using keywords.Dialect constants
• ✅ Maintains existing error handling patterns
• ✅ No breaking changes to public APIs

Technical Accuracy

Verified that:

• ✅ Snowflake does indeed support ILIKE natively (documented)
• ✅ ClickHouse supports ILIKE (documented)
• ✅ Snowflake supports PIVOT/UNPIVOT with same syntax as SQL Server/Oracle

---

Recommendation: Approve and Merge 🚀

This PR successfully fixes the reported issues with minimal, surgical changes. The implementation is correct, well-tested, and maintains backward compatibility.

---

[github-actions]

Claude finished @ajitpratap0's task —— View job

---

Code Review Complete ✅

Reviewing PR for Snowflake ILIKE and PIVOT/UNPIVOT support:

Review Tasks

• Read and analyze changed files
• Review ILIKE operator implementation
• Review PIVOT/UNPIVOT changes
• Examine test coverage
• Assess code quality and best practices
• Check for potential bugs/issues
• Evaluate performance considerations
• Review security implications
• Provide final feedback

---

Review Feedback

✅ Overall Assessment: LGTM with Minor Suggestions

This is a well-implemented fix that correctly addresses the Snowflake dialect support issues. The changes are minimal, focused, and follow the existing codebase patterns.

Code Quality & Implementation

🟢 ILIKE Implementation (expressions_operators.go:95-110)

• Correct approach: Extended the dialect allowlist to include Snowflake and ClickHouse alongside PostgreSQL
• Good error messaging: Updated error message from "PostgreSQL-specific" to a more accurate "not supported in %s" format
• Proper placement: Logic is correctly placed within the existing LIKE/ILIKE parsing flow
• Follows patterns: Consistent with existing dialect-specific feature gating

🟢 PIVOT/UNPIVOT Implementation (pivot.go:71-75)

• Minimal change: Simply added Snowflake to the existing allowlist
• Maintains compatibility: No breaking changes to existing SQL Server/Oracle support
• Correct logic: Uses the same OR-based dialect check pattern used elsewhere

Test Coverage

🟢 Comprehensive test suite (snowflake_ilike_pivot_test.go):

• ✅ Tests both positive cases (Snowflake/ClickHouse ILIKE acceptance)
• ✅ Tests negative cases (MySQL ILIKE rejection)
• ✅ Covers NOT ILIKE variant
• ✅ Tests both PIVOT and UNPIVOT for Snowflake
• ✅ Uses realistic SQL examples with proper syntax
• ✅ Good error message validation

Security & Performance

🟢 No concerns identified:

• Changes only affect parsing logic, not execution
• No new attack vectors introduced
• Existing validation and error handling preserved
• No performance impact (same code paths, just expanded dialect checks)

Minor Suggestions for Future Consideration

1. Documentation: Consider updating any dialect compatibility documentation to reflect ILIKE support in Snowflake/ClickHouse

2. Error message consistency: The ILIKE error message could be made more helpful by suggesting alternatives:

   "ILIKE is not supported in %s; use LIKE with UPPER/LOWER functions for case-insensitive matching"

3. Test organization: Consider if these tests belong in a dedicated dialect-specific test directory structure for easier maintenance

Code Correctness

• ✅ Proper handling of NOT ILIKE prefix in expressions_operators.go:45
• ✅ Correct dialect string comparisons using keywords.Dialect constants
• ✅ Maintains existing error handling patterns
• ✅ No breaking changes to public APIs

Technical Accuracy

Verified that:

• ✅ Snowflake does indeed support ILIKE natively (documented)
• ✅ ClickHouse supports ILIKE (documented)
• ✅ Snowflake supports PIVOT/UNPIVOT with same syntax as SQL Server/Oracle

---

Recommendation: Approve and Merge 🚀

This PR successfully fixes the reported issues with minimal, surgical changes. The implementation is correct, well-tested, and maintains backward compatibility.

---