internal/postgres: prefer longest module path

The go command determines the latest minor version by picking the
longest module path that exists, then the latest version of that.

For example, given two "latest" possibilities for
cloud.google.com/go/pubsublite:

- cloud.google.com/go@v0.69.0/pubsublite
- cloud.google.com/go/pubsublite@v0.4.0

"go get cloud.google.com/go/pubsublite" will choose
cloud.google.com/go/pubsublite@v0.4.0.

Previously, pkg.go.dev/cloud.google.com/pubsublite would show
cloud.google.com/go@v0.69.0/pubsublite. This change updates the site to
display cloud.google.com/go/pubsublite@v0.4.0.

Note that given these two "latest" possibilities:

- cloud.google.com/go@v0.74.0/compute/metadata
- cloud.google.com/go/compute/metadata@v0.0.0-20181115181204-d50f0e9b2506

"go get cloud.google.com/go/compute/metadata" will return
cloud.google.com/go@v0.74.0/compute/metadata.

The reason is because "go get cloud.google.com/go/compute/metadata" will
look for tagged versions of the module
cloud.google.com/go/compute/metadata. Not finding any, it will look for
the pseudo-version for the current tip of the default branch. Since
today, there's no go.mod there, it proceeds as if this subdirectory was
never a nested module. It will then try "cloud.google.com/go/compute"
and so on until it eventually finds that
cloud.google.com/go@v0.74.0/compute/metadata has this package.

Pkg.go.dev/cloud.google.com/go/compute/metadata already shows
cloud.google.com/go@v0.74.0/compute/metadata. A test is added to verify
this logic.

Fixes golang/go#43166

Change-Id: I748fd2693ad7be761e49df9da285902c6dd9101d
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/277512
Run-TryBot: Julie Qiu <julie@golang.org>
Trust: Julie Qiu <julie@golang.org>
Reviewed-by: Jamal Carvalho <jamal@golang.org>
Reviewed-by: Jonathan Amsterdam <jba@google.com>
diff --git a/internal/postgres/path_test.go b/internal/postgres/path_test.go
index 9d80fe2..fa308d9 100644
--- a/internal/postgres/path_test.go
+++ b/internal/postgres/path_test.go
@@ -35,6 +35,10 @@
 		{"m.com", "v2.0.0+incompatible", "a", false},
 		{"m.com/a", "v1.1.0", "b", false},
 		{"m.com/b", "v2.0.0+incompatible", "a", true},
+		{"cloud.google.com/go", "v0.69.0", "pubsublite", false},
+		{"cloud.google.com/go/pubsublite", "v0.4.0", "", false},
+		{"cloud.google.com/go", "v0.74.0", "compute/metadata", false},
+		{"cloud.google.com/go/compute/metadata", "v0.0.0-20181115181204-d50f0e9b2506", "", false},
 	} {
 		m := sample.Module(testModule.module, testModule.version, testModule.packageSuffix)
 		if err := testDB.InsertModule(ctx, m); err != nil {
@@ -165,6 +169,26 @@
 				IsRedistributable: true,
 			},
 		},
+		{
+			name: "prefer pubsublite nested module",
+			path: "cloud.google.com/go/pubsublite",
+			want: &internal.UnitMeta{
+				ModulePath:        "cloud.google.com/go/pubsublite",
+				Name:              "pubsublite",
+				Version:           "v0.4.0",
+				IsRedistributable: true,
+			},
+		},
+		{
+			name: "prefer compute metadata in main module",
+			path: "cloud.google.com/go/compute/metadata",
+			want: &internal.UnitMeta{
+				ModulePath:        "cloud.google.com/go",
+				Name:              "metadata",
+				Version:           "v0.74.0",
+				IsRedistributable: true,
+			},
+		},
 	} {
 		t.Run(test.name, func(t *testing.T) {
 			if test.module == "" {
diff --git a/internal/postgres/unit.go b/internal/postgres/unit.go
index 18e64c9..b432fc6 100644
--- a/internal/postgres/unit.go
+++ b/internal/postgres/unit.go
@@ -111,8 +111,8 @@
 			WHEN m.version_type = 'prerelease' THEN 4
 			ELSE 5
 		END`,
-		"m.sort_version DESC",
 		"m.module_path DESC",
+		"m.sort_version DESC",
 	).PlaceholderFormat(squirrel.Dollar)
 }