Merge branch 'main' into nv/functional-unique-index

* fix: mark numeric columns as aggregation in scalar query response

* fix: make py-fmt

* fix: comments

ee/sqlschema/postgressqlschema/signozdb_index_test.go

@@ -0,0 +1,101 @@
+package postgressqlschema
+
+import (
+	"context"
+	"os"
+	"testing"
+	"time"
+
+	"github.com/SigNoz/signoz/ee/sqlstore/postgressqlstore"
+	"github.com/SigNoz/signoz/pkg/instrumentation/instrumentationtest"
+	"github.com/SigNoz/signoz/pkg/sqlschema"
+	"github.com/SigNoz/signoz/pkg/sqlstore"
+	"github.com/stretchr/testify/require"
+)
+
+// devenvPostgresDSN is the DSN for the postgres started by `make devenv-postgres`.
+// Override with TEST_POSTGRES_DSN to point at a different instance.
+const devenvPostgresDSN = "postgres://postgres:password@localhost:5432/signoz?sslmode=disable"
+
+// TestSignozDBTagUniqueIndex inspects the real postgres database the enterprise
+// server migrates and verifies the functional unique index added by migration
+// 094 on the "tag" table.
+//
+//   - "MigrationCreatedIndex" is the ground-truth check: it reads the index
+//     definition straight out of pg_indexes and confirms the functional unique
+//     index physically exists. This proves the migration ran and postgres
+//     accepted it.
+//   - "GetIndicesRoundTrip" exercises the engine's GetIndices read-back path and
+//     checks it reconstructs the same index. This is the part your colleague
+//     asked about.
+//
+// It mirrors the sqlite signoz.db test, but talks to the devenv postgres
+// container instead of a local file. The test skips if postgres is unreachable
+// (run `make devenv-postgres` and the enterprise server first).
+func TestSignozDBTagUniqueIndex(t *testing.T) {
+	dsn := os.Getenv("TEST_POSTGRES_DSN")
+	if dsn == "" {
+		dsn = devenvPostgresDSN
+	}
+
+	ctx := context.Background()
+	cfg := sqlstore.Config{
+		Provider:   "postgres",
+		Postgres:   sqlstore.PostgresConfig{DSN: dsn},
+		Connection: sqlstore.ConnectionConfig{MaxOpenConns: 10, MaxConnLifetime: time.Minute},
+	}
+
+	providerSettings := instrumentationtest.New().ToProviderSettings()
+	store, err := postgressqlstore.New(ctx, providerSettings, cfg)
+	if err != nil {
+		t.Skipf("postgres unreachable at %s (run `make devenv-postgres` and the enterprise server): %v", dsn, err)
+	}
+
+	pingCtx, cancel := context.WithTimeout(ctx, 2*time.Second)
+	defer cancel()
+	if err := store.SQLDB().PingContext(pingCtx); err != nil {
+		t.Skipf("postgres unreachable at %s (run `make devenv-postgres` and the enterprise server): %v", dsn, err)
+	}
+	t.Logf("using postgres at %s", dsn)
+
+	schema, err := New(ctx, providerSettings, sqlschema.Config{}, store)
+	require.NoError(t, err)
+
+	expected := &sqlschema.UniqueIndex{
+		TableName:   "tag",
+		ColumnNames: []sqlschema.ColumnName{"org_id", "kind", "key", "value"},
+		Expressions: []string{"org_id", "kind", "LOWER(key)", "LOWER(value)"},
+	}
+
+	t.Run("MigrationCreatedIndex", func(t *testing.T) {
+		var def string
+		err := store.
+			BunDB().
+			NewRaw("SELECT indexdef FROM pg_indexes WHERE tablename = 'tag' AND indexname = ?", expected.Name()).
+			Scan(ctx, &def)
+		require.NoError(t, err, "expected unique index %q to exist in postgres", expected.Name())
+		t.Logf("stored indexdef: %s", def)
+
+		require.Contains(t, def, "UNIQUE")
+		// postgres normalizes function names to lowercase.
+		require.Contains(t, def, "lower(key)")
+		require.Contains(t, def, "lower(value)")
+	})
+
+	t.Run("GetIndicesRoundTrip", func(t *testing.T) {
+		indices, err := schema.GetIndices(ctx, "tag")
+		require.NoError(t, err)
+
+		t.Logf("GetIndices returned %d indices", len(indices))
+		var got sqlschema.Index
+		for _, idx := range indices {
+			t.Logf("  name=%q type=%s columns=%v create=%s", idx.Name(), idx.Type(), idx.Columns(), string(idx.ToCreateSQL(schema.Formatter())))
+			if idx.Name() == expected.Name() {
+				got = idx
+			}
+		}
+
+		require.NotNil(t, got, "GetIndices did not return the functional unique index %q", expected.Name())
+		require.True(t, expected.Equals(got), "round-tripped index should equal the original definition")
+	})
+}

frontend/src/container/InfraMonitoringK8s/Clusters/constants.ts

@@ -796,7 +796,7 @@ export const getClusterMetricsQueryPayload = (
 								key: k8sDeploymentDesiredKey,
 								type: 'Gauge',
 							},
-							aggregateOperator: 'avg',
+							aggregateOperator: 'latest',
 							dataSource: DataSource.METRICS,
 							disabled: false,
 							expression: 'B',
@@ -839,7 +839,7 @@ export const getClusterMetricsQueryPayload = (
 							reduceTo: ReduceOperators.LAST,
 							spaceAggregation: 'sum',
 							stepInterval: 60,
-							timeAggregation: 'avg',
+							timeAggregation: 'latest',
 						},
 					],
 					queryFormulas: [],

frontend/src/container/InfraMonitoringK8s/EntityDetailsUtils/EntityEvents/EntityEvents.tsx

@@ -40,6 +40,7 @@ import { K8S_ENTITY_EVENTS_EXPRESSION_KEY, useEntityEvents } from './hooks';
 import { getEntityEventsQueryPayload, isEventsKeyNotFoundError } from './utils';
 
 import styles from './EntityEvents.module.scss';
+import { useTimezone } from 'providers/Timezone';
 
 interface EventDataType {
 	key: string;
@@ -167,17 +168,25 @@ function EntityEventsContent({
 		[events],
 	);
 
-	const columns: TableColumnsType<EventDataType> = [
-		{ title: 'Severity', dataIndex: 'severity', key: 'severity', width: 100 },
-		{
-			title: 'Timestamp',
-			dataIndex: 'timestamp',
-			width: 240,
-			ellipsis: true,
-			key: 'timestamp',
-		},
-		{ title: 'Body', dataIndex: 'body', key: 'body' },
-	];
+	const { formatTimezoneAdjustedTimestamp } = useTimezone();
+	const columns: TableColumnsType<EventDataType> = useMemo(
+		() => [
+			{ title: 'Severity', dataIndex: 'severity', key: 'severity', width: 100 },
+			{
+				title: 'Timestamp',
+				dataIndex: 'timestamp',
+				width: 240,
+				ellipsis: true,
+				key: 'timestamp',
+				render: (value: string | number): string =>
+					formatTimezoneAdjustedTimestamp(
+						typeof value === 'string' ? value : value / 1e6,
+					),
+			},
+			{ title: 'Body', dataIndex: 'body', key: 'body' },
+		],
+		[formatTimezoneAdjustedTimestamp],
+	);
 
 	const handleExpandRowIcon = ({
 		expanded,

frontend/src/container/InfraMonitoringK8s/EntityDetailsUtils/EntityTraces/EntityTraces.tsx

@@ -41,6 +41,7 @@ import { getTraceListColumns } from './traceListColumns';
 import { getEntityTracesQueryPayload } from './utils';
 
 import styles from './EntityTraces.module.scss';
+import { useTimezone } from 'providers/Timezone';
 
 interface Props {
 	timeRange: {
@@ -136,7 +137,11 @@ function EntityTracesContent({
 		[timeRange.startTime, timeRange.endTime, userExpression],
 	);
 
-	const traceListColumns = getTraceListColumns(selectedEntityTracesColumns);
+	const { formatTimezoneAdjustedTimestamp } = useTimezone();
+	const traceListColumns = getTraceListColumns(
+		selectedEntityTracesColumns,
+		formatTimezoneAdjustedTimestamp,
+	);
 
 	const isKeyNotFound = isKeyNotFoundError(error);
 	const isDataEmpty =

frontend/src/container/InfraMonitoringK8s/EntityDetailsUtils/EntityTraces/traceListColumns.tsx

@@ -1,15 +1,14 @@
 import { TableColumnsType as ColumnsType } from 'antd';
 import { Badge } from '@signozhq/ui/badge';
 import { Typography } from '@signozhq/ui/typography';
-import { DATE_TIME_FORMATS } from 'constants/dateTimeFormats';
 import { getMs } from 'container/Trace/Filters/Panel/PanelBody/Duration/util';
 import {
 	BlockLink,
 	getTraceLink,
 } from 'container/TracesExplorer/ListView/utils';
-import dayjs from 'dayjs';
 import { RowData } from 'lib/query/createTableColumnsFromQuery';
 import { BaseAutocompleteData } from 'types/api/queryBuilder/queryAutocompleteResponse';
+import { FormatTimezoneAdjustedTimestamp } from 'hooks/useTimezoneFormatter/useTimezoneFormatter';
 
 const keyToLabelMap: Record<string, string> = {
 	timestamp: 'Timestamp',
@@ -59,6 +58,7 @@ const getValueForKey = (data: Record<string, any>, key: string): any => {
 
 export const getTraceListColumns = (
 	selectedColumns: BaseAutocompleteData[],
+	formatTimezoneAdjustedTimestamp: FormatTimezoneAdjustedTimestamp,
 ): ColumnsType<RowData> => {
 	const columns: ColumnsType<RowData> =
 		selectedColumns.map(({ dataType, key, type }) => ({
@@ -73,8 +73,8 @@ export const getTraceListColumns = (
 				if (primaryKey === 'timestamp') {
 					const date =
 						typeof value === 'string'
-							? dayjs(value).format(DATE_TIME_FORMATS.ISO_DATETIME_MS)
-							: dayjs(value / 1e6).format(DATE_TIME_FORMATS.ISO_DATETIME_MS);
+							? formatTimezoneAdjustedTimestamp(value)
+							: formatTimezoneAdjustedTimestamp(value / 1e6);
 
 					return (
 						<BlockLink to={getTraceLink(itemData)} openInNewTab>

frontend/src/container/InfraMonitoringK8s/Pods/constants.ts

@@ -1366,7 +1366,7 @@ export const getPodMetricsQueryPayload = (
 							orderBy: [],
 							queryName: 'B',
 							reduceTo: ReduceOperators.AVG,
-							spaceAggregation: 'avg',
+							spaceAggregation: 'sum',
 							stepInterval: 60,
 							timeAggregation: 'avg',
 						},

frontend/src/hooks/useTimezoneFormatter/useTimezoneFormatter.ts

@@ -22,11 +22,13 @@ interface CacheEntry {
 const CACHE_SIZE_LIMIT = 1000;
 const CACHE_CLEANUP_PERCENTAGE = 0.5; // Remove 50% when limit is reached
 
+export type FormatTimezoneAdjustedTimestamp = (
+	input: TimestampInput,
+	format?: string,
+) => string;
+
 function useTimezoneFormatter({ userTimezone }: { userTimezone: Timezone }): {
-	formatTimezoneAdjustedTimestamp: (
-		input: TimestampInput,
-		format?: string,
-	) => string;
+	formatTimezoneAdjustedTimestamp: FormatTimezoneAdjustedTimestamp;
 } {
 	// Initialize cache using useMemo to persist between renders
 	const cache = useMemo(() => new Map<string, CacheEntry>(), []);

frontend/src/providers/Timezone.tsx

@@ -19,17 +19,14 @@ import {
 } from 'components/CustomTimePicker/timezoneUtils';
 import { LOCALSTORAGE } from 'constants/localStorage';
 import useTimezoneFormatter, {
-	TimestampInput,
+	FormatTimezoneAdjustedTimestamp,
 } from 'hooks/useTimezoneFormatter/useTimezoneFormatter';
 
 export interface TimezoneContextType {
 	timezone: Timezone;
 	browserTimezone: Timezone;
 	updateTimezone: (timezone: Timezone) => void;
-	formatTimezoneAdjustedTimestamp: (
-		input: TimestampInput,
-		format?: string,
-	) => string;
+	formatTimezoneAdjustedTimestamp: FormatTimezoneAdjustedTimestamp;
 	isAdaptationEnabled: boolean;
 	setIsAdaptationEnabled: Dispatch<SetStateAction<boolean>>;
 }

pkg/querier/consume.go

@@ -62,7 +62,7 @@ func readAsTimeSeries(rows driver.Rows, queryWindow *qbtypes.TimeRange, step qbt
 	numericColsCount := 0
 	for i, ct := range colTypes {
 		slots[i] = reflect.New(ct.ScanType()).Interface()
-		if numericKind(ct.ScanType().Kind()) {
+		if isNumericKind(ct.ScanType()) {
 			numericColsCount++
 		}
 	}
@@ -270,8 +270,14 @@ func readAsTimeSeries(rows driver.Rows, queryWindow *qbtypes.TimeRange, step qbt
 	}, nil
 }
 
-func numericKind(k reflect.Kind) bool {
-	switch k {
+func isNumericKind(t reflect.Type) bool {
+	if t == nil {
+		return false
+	}
+	for t.Kind() == reflect.Ptr || t.Kind() == reflect.UnsafePointer {
+		t = t.Elem()
+	}
+	switch t.Kind() {
 	case reflect.Float32, reflect.Float64,
 		reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64,
 		reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
@@ -290,7 +296,13 @@ func readAsScalar(rows driver.Rows, queryName string) (*qbtypes.ScalarData, erro
 	var aggIndex int64
 	for i, name := range colNames {
 		colType := qbtypes.ColumnTypeGroup
-		if aggRe.MatchString(name) {
+		// Builder queries aliases aggregation columns as __result_N (always numeric) and wraps group-by keys with toString (always string);
+		// Raw ClickHouse queries may use any aliases.
+		// Handling Builder queries, If name like __result_N -> aggregation, otherwise group-by column
+		// Handling Raw ClickHouse queries, If type is numeric -> aggregation, otherwise group-by column
+		// NOTE: For clickhouse queries, its wrong to assume that numeric columns are always aggregations, user might be grouping by on integer status_code.
+		// However, we are fine with this for now. If need arises, simplest way would be to solve this on the frontend side by asking user a mapping of column names to column types.
+		if aggRe.MatchString(name) || isNumericKind(colTypes[i].ScanType()) {
 			colType = qbtypes.ColumnTypeAggregation
 		}
 		cd[i] = &qbtypes.ColumnDescriptor{

pkg/signoz/provider.go

@@ -213,6 +213,7 @@ func NewSQLMigrationProviderFactories(
 		sqlmigration.NewCloudIntegrationRemoveCascadeDeleteFactory(sqlschema),
 		sqlmigration.NewAddUserDashboardPreferenceFactory(sqlstore, sqlschema),
 		sqlmigration.NewRecreateUserDashboardPreferenceFactory(sqlstore, sqlschema),
+		sqlmigration.NewAddTagUniqueIndexFactory(sqlstore, sqlschema),
 	)
 }
 

pkg/sqlmigration/082_add_tags.go

@@ -62,8 +62,6 @@ func (migration *addTags) Up(ctx context.Context, db *bun.DB) error {
 	})
 	sqls = append(sqls, tagTableSQLs...)
 
-	// TODO (@namanverma): add a unique index for tags: (org_id, kind, (LOWER(key)), (LOWER(value)))
-
 	tagRelationsTableSQLs := migration.sqlschema.Operator().CreateTable(&sqlschema.Table{
 		Name: "tag_relation",
 		Columns: []*sqlschema.Column{

pkg/sqlmigration/094_add_tag_unique_index.go

@@ -0,0 +1,60 @@
+package sqlmigration
+
+import (
+	"context"
+
+	"github.com/SigNoz/signoz/pkg/factory"
+	"github.com/SigNoz/signoz/pkg/sqlschema"
+	"github.com/SigNoz/signoz/pkg/sqlstore"
+	"github.com/uptrace/bun"
+	"github.com/uptrace/bun/migrate"
+)
+
+type addTagUniqueIndex struct {
+	sqlstore  sqlstore.SQLStore
+	sqlschema sqlschema.SQLSchema
+}
+
+func NewAddTagUniqueIndexFactory(sqlstore sqlstore.SQLStore, sqlschema sqlschema.SQLSchema) factory.ProviderFactory[SQLMigration, Config] {
+	return factory.NewProviderFactory(factory.MustNewName("add_tag_unique_index"), func(ctx context.Context, ps factory.ProviderSettings, c Config) (SQLMigration, error) {
+		return &addTagUniqueIndex{
+			sqlstore:  sqlstore,
+			sqlschema: sqlschema,
+		}, nil
+	})
+}
+
+func (migration *addTagUniqueIndex) Register(migrations *migrate.Migrations) error {
+	return migrations.Register(migration.Up, migration.Down)
+}
+
+func (migration *addTagUniqueIndex) Up(ctx context.Context, db *bun.DB) error {
+	tx, err := db.BeginTx(ctx, nil)
+	if err != nil {
+		return err
+	}
+
+	defer func() {
+		_ = tx.Rollback()
+	}()
+
+	sqls := migration.sqlschema.Operator().CreateIndex(
+		&sqlschema.UniqueIndex{
+			TableName:   "tag",
+			ColumnNames: []sqlschema.ColumnName{"org_id", "kind", "key", "value"},
+			Expressions: []string{"org_id", "kind", "LOWER(key)", "LOWER(value)"},
+		},
+	)
+
+	for _, sql := range sqls {
+		if _, err := tx.ExecContext(ctx, string(sql)); err != nil {
+			return err
+		}
+	}
+
+	return tx.Commit()
+}
+
+func (migration *addTagUniqueIndex) Down(_ context.Context, _ *bun.DB) error {
+	return nil
+}

pkg/sqlschema/index.go

@@ -1,6 +1,8 @@
 package sqlschema
 
 import (
+	"fmt"
+	"hash/fnv"
 	"slices"
 	"strings"
 
@@ -49,9 +51,23 @@ type Index interface {
 	ToDropSQL(fmter SQLFormatter) []byte
 }
 
+// UniqueIndex models a unique index on a table.
+//
+// In the common case the index keys on plain columns: set only ColumnNames and
+// the SQL is emitted with each column identifier-quoted by the formatter
+// (`CREATE UNIQUE INDEX uq_t_a_b ON t (a, b)`).
+//
+// For functional indexes (e.g. case-insensitive uniqueness on `LOWER(col)`),
+// set Expressions to the raw SQL parts and use ColumnNames as metadata for
+// "which columns does this index touch". When Expressions is non-empty, it
+// overrides ColumnNames for SQL emission — each entry is written verbatim, so
+// the caller owns well-formedness — and the auto-generated name uses a hash
+// suffix instead of a readable column join because expressions aren't valid
+// identifier fragments.
 type UniqueIndex struct {
 	TableName   TableName
 	ColumnNames []ColumnName
+	Expressions []string
 	name        string
 }
 
@@ -71,16 +87,28 @@ func (index *UniqueIndex) Name() string {
 		}
 		b.WriteString(string(column))
 	}
+
+	if len(index.Expressions) > 0 {
+		if len(index.ColumnNames) > 0 {
+			b.WriteString("_")
+		}
+		hasher := fnv.New32a()
+		_, _ = hasher.Write([]byte(strings.Join(index.Expressions, "\x00")))
+		fmt.Fprintf(&b, "%08x", hasher.Sum32())
+	}
 	return b.String()
 }
 
 func (index *UniqueIndex) Named(name string) Index {
 	copyOfColumnNames := make([]ColumnName, len(index.ColumnNames))
 	copy(copyOfColumnNames, index.ColumnNames)
+	copyOfExpressions := make([]string, len(index.Expressions))
+	copy(copyOfExpressions, index.Expressions)
 
 	return &UniqueIndex{
 		TableName:   index.TableName,
 		ColumnNames: copyOfColumnNames,
+		Expressions: copyOfExpressions,
 		name:        name,
 	}
 }
@@ -101,7 +129,18 @@ func (index *UniqueIndex) Equals(other Index) bool {
 	if other.Type() != IndexTypeUnique {
 		return false
 	}
-
+	otherUnique, ok := other.(*UniqueIndex)
+	if !ok {
+		return false
+	}
+	// Plain and functional indexes produce different SQL even if their column
+	// sets overlap; require both shapes to match.
+	if (len(index.Expressions) == 0) != (len(otherUnique.Expressions) == 0) {
+		return false
+	}
+	if len(index.Expressions) > 0 && !slices.Equal(index.Expressions, otherUnique.Expressions) {
+		return false
+	}
 	return index.Name() == other.Name() && slices.Equal(index.Columns(), other.Columns())
 }
 
@@ -114,12 +153,20 @@ func (index *UniqueIndex) ToCreateSQL(fmter SQLFormatter) []byte {
 	sql = fmter.AppendIdent(sql, string(index.TableName))
 	sql = append(sql, " ("...)
 
-	for i, column := range index.ColumnNames {
-		if i > 0 {
-			sql = append(sql, ", "...)
+	if len(index.Expressions) > 0 {
+		for i, expr := range index.Expressions {
+			if i > 0 {
+				sql = append(sql, ", "...)
+			}
+			sql = append(sql, expr...)
+		}
+	} else {
+		for i, column := range index.ColumnNames {
+			if i > 0 {
+				sql = append(sql, ", "...)
+			}
+			sql = fmter.AppendIdent(sql, string(column))
 		}
-
-		sql = fmter.AppendIdent(sql, string(column))
 	}
 
 	sql = append(sql, ")"...)

pkg/sqlschema/index_test.go

@@ -38,6 +38,43 @@ func TestIndexToCreateSQL(t *testing.T) {
 			},
 			sql: `CREATE UNIQUE INDEX IF NOT EXISTS "my_index" ON "users" ("id", "name", "email")`,
 		},
+		{
+			name: "Unique_Functional_SingleExpression",
+			index: &UniqueIndex{
+				TableName:   "users",
+				ColumnNames: []ColumnName{"email"},
+				Expressions: []string{"LOWER(email)"},
+			},
+			sql: `CREATE UNIQUE INDEX IF NOT EXISTS "uq_users_email_1e5a87f1" ON "users" (LOWER(email))`,
+		},
+		{
+			name: "Unique_Functional_MixedColumnsAndExpressions",
+			index: &UniqueIndex{
+				TableName:   "tag",
+				ColumnNames: []ColumnName{"org_id", "kind", "key", "value"},
+				Expressions: []string{"org_id", "kind", "LOWER(key)", "LOWER(value)"},
+			},
+			sql: `CREATE UNIQUE INDEX IF NOT EXISTS "uq_tag_org_id_kind_key_value_57e8f81f" ON "tag" (org_id, kind, LOWER(key), LOWER(value))`,
+		},
+		{
+			name: "Unique_Functional_ComplexExpression",
+			index: &UniqueIndex{
+				TableName:   "users",
+				ColumnNames: []ColumnName{"first_name", "last_name"},
+				Expressions: []string{"LOWER(TRIM(first_name) || ' ' || TRIM(last_name))"},
+			},
+			sql: `CREATE UNIQUE INDEX IF NOT EXISTS "uq_users_first_name_last_name_adb1ff53" ON "users" (LOWER(TRIM(first_name) || ' ' || TRIM(last_name)))`,
+		},
+		{
+			name: "Unique_Functional_Named",
+			index: &UniqueIndex{
+				TableName:   "tag",
+				ColumnNames: []ColumnName{"org_id", "kind", "key", "value"},
+				Expressions: []string{"org_id", "kind", "LOWER(key)", "LOWER(value)"},
+				name:        "uq_tag_org_kind_lower_key_lower_value",
+			},
+			sql: `CREATE UNIQUE INDEX IF NOT EXISTS "uq_tag_org_kind_lower_key_lower_value" ON "tag" (org_id, kind, LOWER(key), LOWER(value))`,
+		},
 		{
 			name: "PartialUnique_1Column",
 			index: &PartialUniqueIndex{
@@ -229,6 +266,47 @@ func TestIndexEquals(t *testing.T) {
 			},
 			equals: false,
 		},
+		{
+			name: "Unique_Functional_Same",
+			a: &UniqueIndex{
+				TableName:   "users",
+				ColumnNames: []ColumnName{"email"},
+				Expressions: []string{"LOWER(email)"},
+			},
+			b: &UniqueIndex{
+				TableName:   "users",
+				ColumnNames: []ColumnName{"email"},
+				Expressions: []string{"LOWER(email)"},
+			},
+			equals: true,
+		},
+		{
+			name: "Unique_Functional_DifferentExpressions",
+			a: &UniqueIndex{
+				TableName:   "users",
+				ColumnNames: []ColumnName{"email"},
+				Expressions: []string{"LOWER(email)"},
+			},
+			b: &UniqueIndex{
+				TableName:   "users",
+				ColumnNames: []ColumnName{"email"},
+				Expressions: []string{"UPPER(email)"},
+			},
+			equals: false,
+		},
+		{
+			name: "Unique_Functional_NotEqualToPlainSameColumns",
+			a: &UniqueIndex{
+				TableName:   "users",
+				ColumnNames: []ColumnName{"email"},
+				Expressions: []string{"LOWER(email)"},
+			},
+			b: &UniqueIndex{
+				TableName:   "users",
+				ColumnNames: []ColumnName{"email"},
+			},
+			equals: false,
+		},
 	}
 
 	for _, testCase := range testCases {
@@ -238,6 +316,75 @@ func TestIndexEquals(t *testing.T) {
 	}
 }
 
+func TestUniqueIndexFunctionalName(t *testing.T) {
+	t.Run("autogen uses uq_<table>_<hash>", func(t *testing.T) {
+		idx := &UniqueIndex{
+			TableName:   "tag",
+			ColumnNames: []ColumnName{"org_id", "kind", "key", "value"},
+			Expressions: []string{"org_id", "kind", "LOWER(key)", "LOWER(value)"},
+		}
+		assert.Equal(t, "uq_tag_org_id_kind_key_value_57e8f81f", idx.Name())
+	})
+
+	t.Run("same expressions produce the same name", func(t *testing.T) {
+		a := &UniqueIndex{
+			TableName:   "users",
+			Expressions: []string{"LOWER(email)"},
+		}
+		b := &UniqueIndex{
+			TableName:   "users",
+			Expressions: []string{"LOWER(email)"},
+		}
+		assert.Equal(t, a.Name(), b.Name())
+	})
+
+	t.Run("different expressions produce different names", func(t *testing.T) {
+		a := &UniqueIndex{
+			TableName:   "users",
+			Expressions: []string{"LOWER(email)"},
+		}
+		b := &UniqueIndex{
+			TableName:   "users",
+			Expressions: []string{"UPPER(email)"},
+		}
+		assert.NotEqual(t, a.Name(), b.Name())
+	})
+
+	t.Run("expressions in different order produce different names", func(t *testing.T) {
+		a := &UniqueIndex{
+			TableName:   "tag",
+			Expressions: []string{"org_id", "LOWER(key)"},
+		}
+		b := &UniqueIndex{
+			TableName:   "tag",
+			Expressions: []string{"LOWER(key)", "org_id"},
+		}
+		assert.NotEqual(t, a.Name(), b.Name())
+	})
+
+	t.Run("functional autogen differs from plain autogen for same columns", func(t *testing.T) {
+		plain := &UniqueIndex{
+			TableName:   "users",
+			ColumnNames: []ColumnName{"email"},
+		}
+		functional := &UniqueIndex{
+			TableName:   "users",
+			ColumnNames: []ColumnName{"email"},
+			Expressions: []string{"LOWER(email)"},
+		}
+		assert.Equal(t, "uq_users_email", plain.Name())
+		assert.NotEqual(t, plain.Name(), functional.Name())
+	})
+
+	t.Run("Named() override wins over hash", func(t *testing.T) {
+		idx := (&UniqueIndex{
+			TableName:   "tag",
+			Expressions: []string{"org_id", "LOWER(key)"},
+		}).Named("my_functional_index")
+		assert.Equal(t, "my_functional_index", idx.Name())
+	})
+}
+
 func TestPartialUniqueIndexName(t *testing.T) {
 	a := &PartialUniqueIndex{
 		TableName:   "users",

pkg/sqlschema/sqlitesqlschema/signozdb_index_test.go

@@ -0,0 +1,111 @@
+package sqlitesqlschema
+
+import (
+	"context"
+	"os"
+	"path/filepath"
+	"testing"
+	"time"
+
+	"github.com/SigNoz/signoz/pkg/instrumentation/instrumentationtest"
+	"github.com/SigNoz/signoz/pkg/sqlschema"
+	"github.com/SigNoz/signoz/pkg/sqlstore"
+	"github.com/SigNoz/signoz/pkg/sqlstore/sqlitesqlstore"
+	"github.com/stretchr/testify/require"
+)
+
+// findSignozDB walks up from the test's working directory looking for a
+// signoz.db file (the one the community server creates at the repo root).
+func findSignozDB(t *testing.T) string {
+	t.Helper()
+
+	dir, err := os.Getwd()
+	require.NoError(t, err)
+
+	for {
+		candidate := filepath.Join(dir, "signoz.db")
+		if _, err := os.Stat(candidate); err == nil {
+			return candidate
+		}
+
+		parent := filepath.Dir(dir)
+		if parent == dir {
+			return ""
+		}
+		dir = parent
+	}
+}
+
+// TestSignozDBTagUniqueIndex inspects the real signoz.db produced by running the
+// community server and verifies the functional unique index added by migration
+// 094 on the "tag" table.
+//
+//   - "MigrationCreatedIndex" is the ground-truth check: it reads the index DDL
+//     straight out of sqlite_master and confirms the functional unique index
+//     physically exists. This proves the migration ran and sqlite accepted it.
+//   - "GetIndicesRoundTrip" exercises the engine's GetIndices read-back path and
+//     checks it reconstructs the same index. This is the part your colleague
+//     asked about.
+func TestSignozDBTagUniqueIndex(t *testing.T) {
+	dbPath := findSignozDB(t)
+	if dbPath == "" {
+		t.Skip("signoz.db not found; start the community server first so it creates the file and runs migrations")
+	}
+	t.Logf("using signoz.db at %s", dbPath)
+
+	ctx := context.Background()
+	cfg := sqlstore.Config{
+		Provider: "sqlite",
+		Sqlite: sqlstore.SqliteConfig{
+			Path:            dbPath,
+			Mode:            "wal",
+			BusyTimeout:     10 * time.Second,
+			TransactionMode: "deferred",
+		},
+		Connection: sqlstore.ConnectionConfig{MaxOpenConns: 10},
+	}
+
+	providerSettings := instrumentationtest.New().ToProviderSettings()
+	store, err := sqlitesqlstore.New(ctx, providerSettings, cfg)
+	require.NoError(t, err)
+
+	schema, err := New(ctx, providerSettings, sqlschema.Config{}, store)
+	require.NoError(t, err)
+
+	expected := &sqlschema.UniqueIndex{
+		TableName:   "tag",
+		ColumnNames: []sqlschema.ColumnName{"org_id", "kind", "key", "value"},
+		Expressions: []string{"org_id", "kind", "LOWER(key)", "LOWER(value)"},
+	}
+
+	t.Run("MigrationCreatedIndex", func(t *testing.T) {
+		var ddl string
+		err := store.
+			BunDB().
+			NewRaw("SELECT sql FROM sqlite_master WHERE type = 'index' AND tbl_name = 'tag' AND name = ?", expected.Name()).
+			Scan(ctx, &ddl)
+		require.NoError(t, err, "expected unique index %q to exist in signoz.db", expected.Name())
+		t.Logf("stored DDL: %s", ddl)
+
+		require.Contains(t, ddl, "UNIQUE")
+		require.Contains(t, ddl, "LOWER(key)")
+		require.Contains(t, ddl, "LOWER(value)")
+	})
+
+	t.Run("GetIndicesRoundTrip", func(t *testing.T) {
+		indices, err := schema.GetIndices(ctx, "tag")
+		require.NoError(t, err)
+
+		t.Logf("GetIndices returned %d indices", len(indices))
+		var got sqlschema.Index
+		for _, idx := range indices {
+			t.Logf("  name=%q type=%s columns=%v create=%s", idx.Name(), idx.Type(), idx.Columns(), string(idx.ToCreateSQL(schema.Formatter())))
+			if idx.Name() == expected.Name() {
+				got = idx
+			}
+		}
+
+		require.NotNil(t, got, "GetIndices did not return the functional unique index %q", expected.Name())
+		require.True(t, expected.Equals(got), "round-tripped index should equal the original definition")
+	})
+}

tests/integration/tests/querier/17_clickhouse_queries.py

@@ -0,0 +1,74 @@
+"""
+Integration tests for raw ClickHouse SQL queries in the querier.
+"""
+
+from collections.abc import Callable
+from datetime import UTC, datetime, timedelta
+from http import HTTPStatus
+
+from fixtures import querier, types
+from fixtures.auth import USER_ADMIN_EMAIL, USER_ADMIN_PASSWORD
+
+
+def test_clickhouse_scalar_numeric_result_alias_classified_as_aggregation(
+    signoz: types.SigNoz,
+    create_user_admin: None,  # pylint: disable=unused-argument
+    get_token: Callable[[str, str], str],
+) -> None:
+    """A numeric column aliased ``__result_0`` is classified as an aggregation."""
+    now = datetime.now(tz=UTC).replace(second=0, microsecond=0)
+    token = get_token(USER_ADMIN_EMAIL, USER_ADMIN_PASSWORD)
+
+    response = querier.make_query_request(
+        signoz,
+        token,
+        int((now - timedelta(hours=1)).timestamp() * 1000),
+        int(now.timestamp() * 1000),
+        [
+            {
+                "type": "clickhouse_sql",
+                "spec": {
+                    "name": "A",
+                    "query": "SELECT toFloat64(1.5) AS `__result_0`",
+                    "disabled": False,
+                },
+            }
+        ],
+        request_type=querier.RequestType.SCALAR,
+    )
+
+    assert response.status_code == HTTPStatus.OK
+    assert response.json()["status"] == "success"
+
+    columns = querier.get_scalar_columns(response.json())
+    assert len(columns) == 1
+    assert columns[0]["name"] == "__result_0"
+    assert columns[0]["columnType"] == "aggregation"
+    assert columns[0]["aggregationIndex"] == 0
+
+    response = querier.make_query_request(
+        signoz,
+        token,
+        int((now - timedelta(hours=1)).timestamp() * 1000),
+        int(now.timestamp() * 1000),
+        [
+            {
+                "type": "clickhouse_sql",
+                "spec": {
+                    "name": "A",
+                    "query": "SELECT toNullable(toFloat64(1.5)) AS value",
+                    "disabled": False,
+                },
+            }
+        ],
+        request_type=querier.RequestType.SCALAR,
+    )
+
+    assert response.status_code == HTTPStatus.OK
+    assert response.json()["status"] == "success"
+
+    columns = querier.get_scalar_columns(response.json())
+    assert len(columns) == 1
+    assert columns[0]["name"] == "value"
+    assert columns[0]["columnType"] == "aggregation"
+    assert columns[0]["aggregationIndex"] == 0