Repository: AlecStrong/sql-psi (kotlin)

Issue:
Aliased column name is not recognized in HAVING clause in MySQL
Dialect: MySQL

failing sql:

```sql
-- Table definition edited down to the relevant columns:
CREATE TABLE `models` (
  `model_id` int(11) NOT NULL AUTO_INCREMENT,
  `model_descriptor_id` int(11) NOT NULL,
  `build_date` datetime NOT NULL,
  PRIMARY KEY (`model_id`)
) DEFAULT CHARSET=latin1;

searchTest:
SELECT TO_SECONDS(build_date) AS build_date_seconds
FROM models
GROUP BY model_descriptor_id
HAVING build_date_seconds > 0 LIMIT 1;
```

The query runs fine in the MySQL shell, but I get this error:

```
/path/to/db/ModelSearch.sq line 7:7 - No column found with name build_date_seconds
4    SELECT TO_SECONDS(build_date) AS build_date_seconds
5    FROM models
6    GROUP BY model_descriptor_id
7    HAVING build_date_seconds > 0 LIMIT 1
            ^^^^^^^^^^^^^^^^^^
```

Pull request:
Closes https://github.com/AlecStrong/sql-psi/issues/192

Patch:
diff --git a/core/src/main/kotlin/com/alecstrong/sql/psi/core/psi/mixins/SelectStmtMixin.kt b/core/src/main/kotlin/com/alecstrong/sql/psi/core/psi/mixins/SelectStmtMixin.kt
index 89d216db..e628e536 100644
--- a/core/src/main/kotlin/com/alecstrong/sql/psi/core/psi/mixins/SelectStmtMixin.kt
+++ b/core/src/main/kotlin/com/alecstrong/sql/psi/core/psi/mixins/SelectStmtMixin.kt
@@ -20,6 +20,7 @@ import com.alecstrong.sql.psi.core.psi.SqlTypes
 import com.alecstrong.sql.psi.core.psi.asColumns
 import com.intellij.lang.ASTNode
 import com.intellij.psi.PsiElement
+import com.intellij.psi.PsiNamedElement
 import com.intellij.psi.util.PsiTreeUtil
 
 internal abstract class SelectStmtMixin(
@@ -27,6 +28,12 @@ internal abstract class SelectStmtMixin(
 ) : SqlCompositeElementImpl(node),
   SqlSelectStmt,
   FromQuery {
+  /**
+   * During some resolution steps we don't care about the parent's projection and can safely ignore
+   * it to avoid recursing too far.
+   */
+  private var ignoreParentProjection = false
+
   private val queryExposed = ModifiableFileLazy {
     if (valuesExpressionList.isNotEmpty()) {
       return@ModifiableFileLazy listOf(QueryResult(null, valuesExpressionList.first().exprList.asColumns()))
@@ -47,7 +54,27 @@ internal abstract class SelectStmtMixin(
   }
 
   override fun queryAvailable(child: PsiElement): Collection<QueryResult> {
-    if (child in exprList || child in resultColumnList) {
+    if (child in exprList) {
+      val available = fromQuery().map { it.copy(adjacent = true) } +
+        super.queryAvailable(this).map { it.copy(adjacent = false) }
+      if (ignoreParentProjection) return available
+
+      val projection = (parent as CompoundSelectStmtMixin).queryExposed().map { selectStmt ->
+        selectStmt.copy(
+          adjacent = false,
+          columns = selectStmt.columns.filter { projectionColumn ->
+            // Avoid including any projection columns that would create name collisions.
+            (projectionColumn.element as? PsiNamedElement)?.name !in
+              available.flatMap {
+                it.columns.mapNotNull { (it.element as? PsiNamedElement)?.name }
+              }
+          }
+        )
+      }
+
+      return available + projection
+    }
+    if (child in resultColumnList) {
       return fromQuery().map { it.copy(adjacent = true) } +
         super.queryAvailable(this).map { it.copy(adjacent = false) }
     }
@@ -83,11 +110,16 @@ internal abstract class SelectStmtMixin(
   }
 
   private fun SqlColumnName.isSameAs(other: SqlColumnName): Boolean {
-    if (this == other) return true
-    val thisRef = reference?.resolve() ?: return false
-    if (thisRef == other) return true
-    val otherRef = other.reference?.resolve() ?: return false
-    return thisRef == otherRef
+    try {
+      ignoreParentProjection = true
+      if (this == other) return true
+      val thisRef = reference?.resolve() ?: return false
+      if (thisRef == other) return true
+      val otherRef = other.reference?.resolve() ?: return false
+      return thisRef == otherRef
+    } finally {
+      ignoreParentProjection = false
+    }
   }
 
   override fun annotate(annotationHolder: SqlAnnotationHolder) {


Test patch:
diff --git a/test-fixtures/src/main/resources/fixtures/alias-in-having/Test.s b/test-fixtures/src/main/resources/fixtures/alias-in-having/Test.s
new file mode 100644
index 00000000..85b68b6b
--- /dev/null
+++ b/test-fixtures/src/main/resources/fixtures/alias-in-having/Test.s
@@ -0,0 +1,12 @@
+-- Table definition edited down to the relevant columns:
+CREATE TABLE `models` (
+  `model_id` TEXT NOT NULL AUTOINCREMENT,
+  `model_descriptor_id` TEXT NOT NULL,
+  `build_date` TEXT NOT NULL,
+  PRIMARY KEY (`model_id`)
+);
+
+SELECT max(build_date) AS build_date_seconds
+FROM models
+GROUP BY model_descriptor_id
+HAVING build_date_seconds > 0 LIMIT 1;
\ No newline at end of file