Skip to content

Database governance built for humans and agents — controlling changes and access across every major database.

7.3 KB
Raw Blame
package pg

import (
	"context"
	"testing"
	"time"

	"github.com/stretchr/testify/require"
	"google.golang.org/protobuf/encoding/protojson"

	"github.com/bytebase/bytebase/backend/common/testcontainer"
	v1pb "github.com/bytebase/bytebase/backend/generated-go/v1"
	"github.com/bytebase/bytebase/backend/plugin/db/util"
)

func TestBuildTimestamptzRowValue(t *testing.T) {
	tests := []struct {
		name       string
		typeName   string
		input      time.Time
		wantString string // non-empty means we expect a string fallback with this value
	}{
		{
			name:     "valid timestamp",
			typeName: "TIMESTAMP",
			input:    time.Date(2026, 5, 11, 12, 34, 56, 0, time.UTC),
		},
		{
			name:     "valid timestamptz",
			typeName: "TIMESTAMPTZ",
			input:    time.Date(2026, 5, 11, 12, 34, 56, 0, time.FixedZone("", 2*60*60)),
		},
		{
			name:       "BC timestamp falls back to PG-formatted string",
			typeName:   "TIMESTAMP",
			input:      time.Date(0, 12, 31, 15, 0, 0, 0, time.UTC), // Go year 0 == 1 BC
			wantString: "0001-12-31 15:00:00 BC",
		},
		{
			name:       "year 10000 timestamp omits zone",
			typeName:   "TIMESTAMP",
			input:      time.Date(10000, 1, 1, 0, 0, 0, 0, time.UTC),
			wantString: "10000-01-01 00:00:00",
		},
		{
			name:       "year 10000 timestamptz keeps offset",
			typeName:   "TIMESTAMPTZ",
			input:      time.Date(10000, 5, 11, 12, 34, 56, 0, time.FixedZone("", 2*60*60)),
			wantString: "10000-05-11 12:34:56+02:00",
		},
	}

	for _, tc := range tests {
		t.Run(tc.name, func(t *testing.T) {
			got := buildTimestamptzRowValue(tc.typeName, tc.input, 6)

			// The whole point of the fix: marshaling must never fail.
			_, err := protojson.Marshal(got)
			require.NoError(t, err)

			if tc.wantString != "" {
				require.Equal(t, tc.wantString, got.GetStringValue())
				return
			}
			if tc.typeName == "TIMESTAMP" {
				require.NotNil(t, got.GetTimestampValue())
			} else {
				require.NotNil(t, got.GetTimestampTzValue())
			}
		})
	}
}

func TestPadZeroes(t *testing.T) {
	tests := []struct {
		name     string
		rawStr   string
		acc      int
		expected string
	}{
		{
			name:     "No decimal part",
			rawStr:   "12:34:56",
			acc:      6,
			expected: "12:34:56",
		},
		{
			name:     "Already correct precision",
			rawStr:   "12:34:56.123456",
			acc:      6,
			expected: "12:34:56.123456",
		},
		{
			name:     "Less precision",
			rawStr:   "12:34:56.123",
			acc:      6,
			expected: "12:34:56.123000",
		},
		{
			name:     "More precision",
			rawStr:   "12:34:56.123456789",
			acc:      6,
			expected: "12:34:56.123456789",
		},
		{
			name:     "With timezone",
			rawStr:   "12:34:56.123+02:00",
			acc:      6,
			expected: "12:34:56.123000+02:00",
		},
		{
			name:     "With negative timezone",
			rawStr:   "12:34:56.123-02:00",
			acc:      6,
			expected: "12:34:56.123000-02:00",
		},
		{
			name:     "Invalid format: timezone before decimal (edge case fix)",
			rawStr:   "12:34-05:56.123",
			acc:      6,
			expected: "12:34-05:56.123000",
		},
		{
			name:     "Negative interval: leading minus should be ignored",
			rawStr:   "-00:04:37.530865",
			acc:      6,
			expected: "-00:04:37.530865",
		},
		{
			name:     "Negative interval with short precision",
			rawStr:   "-00:02:45.25",
			acc:      6,
			expected: "-00:02:45.250000",
		},
		{
			name:     "Negative interval without decimal",
			rawStr:   "-00:05:00",
			acc:      6,
			expected: "-00:05:00",
		},
		{
			name:     "Negative interval with timezone (minus after decimal)",
			rawStr:   "-12:34:56.123-05:00",
			acc:      6,
			expected: "-12:34:56.123-05:00",
		},
	}

	for _, test := range tests {
		got := padZeroes(test.rawStr, test.acc)
		require.Equal(t, test.expected, got)
	}
}

// TestConvertValueTimestampFromDriver reads timestamps through the driver rather than
// from a constructed time.Time, exercising the session time zone the way QueryConn does:
// read once per statement, then applied to every timestamptz in the result. It also
// covers ColumnType.DecimalSize reporting the column's fractional-second scale, which
// Bytebase's pgx fork adds and upstream does not, so a pgx upgrade that loses that patch
// fails here instead of silently changing query results.
func TestConvertValueTimestampFromDriver(t *testing.T) {
	const (
		sessionZone   = "Asia/Shanghai"
		sessionOffset = 8 * 60 * 60
	)

	ctx := context.Background()
	_, db := testcontainer.NewPgDatabase(t)

	// SET applies to one session, so hold a single connection for the query.
	conn, err := db.Conn(ctx)
	require.NoError(t, err)
	defer func() { require.NoError(t, conn.Close()) }()
	_, err = conn.ExecContext(ctx, "SET TimeZone TO '"+sessionZone+"'")
	require.NoError(t, err)

	// QueryConn reads the zone once per statement, after any preceding SET.
	location := util.PgSessionLocation(conn)
	require.Equal(t, sessionZone, location.String())

	rows, err := conn.QueryContext(ctx, `SELECT
		'2026-03-04 05:06:07.891234+00'::timestamptz    AS tz_default,
		'2026-03-04 05:06:07.891234+00'::timestamptz(3) AS tz_scale3,
		'2026-03-04 05:06:07.891234'::timestamp         AS ts_default,
		'10000-01-01 00:00:00+00'::timestamptz          AS tz_out_of_range,
		'10000-01-01 00:00:00'::timestamp               AS ts_out_of_range`)
	require.NoError(t, err)
	defer rows.Close()

	columnTypes, err := rows.ColumnTypes()
	require.NoError(t, err)
	require.Len(t, columnTypes, 5)

	require.True(t, rows.Next())
	dest := make([]any, len(columnTypes))
	for i, columnType := range columnTypes {
		dest[i] = makeValueByTypeName(columnType.DatabaseTypeName(), columnType)
	}
	require.NoError(t, rows.Scan(dest...))
	require.NoError(t, rows.Err())

	values := make(map[string]*v1pb.RowValue, len(columnTypes))
	for i, columnType := range columnTypes {
		values[columnType.Name()] = convertValue(columnType.DatabaseTypeName(), columnType, dest[i], location)
	}

	tzDefault := values["tz_default"].GetTimestampTzValue()
	require.NotNil(t, tzDefault)
	require.Equal(t, "CST", tzDefault.Zone)
	require.EqualValues(t, sessionOffset, tzDefault.Offset)
	require.EqualValues(t, 6, tzDefault.Accuracy)
	require.Equal(t, "2026-03-04T05:06:07.891234Z", tzDefault.GoogleTimestamp.AsTime().Format(time.RFC3339Nano))

	// Postgres rounds the fixture's .891234 to .891 for timestamptz(3).
	tzScale3 := values["tz_scale3"].GetTimestampTzValue()
	require.NotNil(t, tzScale3)
	require.Equal(t, "CST", tzScale3.Zone)
	require.EqualValues(t, sessionOffset, tzScale3.Offset)
	require.EqualValues(t, 3, tzScale3.Accuracy)
	require.Equal(t, "2026-03-04T05:06:07.891Z", tzScale3.GoogleTimestamp.AsTime().Format(time.RFC3339Nano))

	// timestamp without time zone carries no zone, so it is not shifted.
	tsDefault := values["ts_default"].GetTimestampValue()
	require.NotNil(t, tsDefault)
	require.EqualValues(t, 6, tsDefault.Accuracy)
	require.Equal(t, "2026-03-04T05:06:07.891234Z", tsDefault.GoogleTimestamp.AsTime().Format(time.RFC3339Nano))

	// A date past the protobuf timestamp range falls back to a formatted string built
	// from wall-clock fields, which is the one path where converting the location moves
	// the value instead of relabeling it. timestamptz renders in the session zone; a
	// zone-less timestamp keeps the stored wall clock and gains no offset.
	require.Equal(t, "10000-01-01 08:00:00+08:00", values["tz_out_of_range"].GetStringValue())
	require.Equal(t, "10000-01-01 00:00:00", values["ts_out_of_range"].GetStringValue())
}