bigquery-emulator: Bigquery Query Like ORDER BY GROUP BY FORMAT_DATE count and subquery not working on bigquery emulator.

Hey @goccy

I have written one query regarding BIG QUERY Like the below for inserting data and fetching data Whenever insert data it will work fine but when I tried to fetch data using group by or count or order by or using subquery it will not give me a proper record and it will give me error.

This is below code I tried.

type MockUsageEvent struct {
	Key       string `json:"key" binding:"required" conform:"trim"`
	Product   string `json:"product" conform:"trim"`
	Version   string `json:"version" conform:"trim"`
	Platform  string `json:"platform" conform:"trim"`
	Event     string `json:"event" binding:"required,oneofCI=activation" conform:"trim,lower"`
	InstallId string `json:"installid" conform:"trim"`
	Created   string `json:"created"`
}

func (i *MockUsageEvent) Save() (map[string]bigquery.Value, string, error) {
	return map[string]bigquery.Value{
		"key":       i.Key,
		"product":   i.Product,
		"version":   i.Version,
		"platform":  i.Platform,
		"event":     i.Event,
		"installid": i.InstallId,
		"created":   i.Created,
	}, bigquery.NoDedupeID, nil
}

ctx := context.Background()
	meta := &bigquery.DatasetMetadata{
		Location: "US", // See https://cloud.google.com/bigquery/docs/locations
	}
	if err := bigQueryClient.Dataset("dataset").Create(ctx, meta); err != nil {
		return
	}

	sampleSchema := bigquery.Schema{
		{Name: "key", Type: bigquery.StringFieldType},
		{Name: "product", Type: bigquery.StringFieldType},
		{Name: "version", Type: bigquery.StringFieldType},
		{Name: "platform", Type: bigquery.StringFieldType},
		{Name: "event", Type: bigquery.StringFieldType},
		{Name: "installid", Type: bigquery.StringFieldType},
		{Name: "created", Type: bigquery.StringFieldType},
	}

	metaData := &bigquery.TableMetadata{
		Schema:         sampleSchema,
		ExpirationTime: time.Now().Add(time.Duration(1*60) * time.Second), // Table will be automatically deleted in 1 day.
	}
	tableRef := bigQueryClient.Dataset("dataset").Table("table")

	if err := tableRef.Create(ctx, metaData); err != nil {
		return
	}

	inserter := bigQueryClient.Dataset("dataset").Table("table").Inserter()

	items := []*MockUsageEvent{
		{Key: "1bjCljWkq6tinyAMuu0tEAhB80", Product: "Phrased Flintstones0", Version: "Phrased Flintstones0", Platform: "Phrased Flintstones0", Event: "license-request", InstallId: "1", Created: "2022-10-11T17:31:39"},
		{Key: "1bjCljWkq6tinyAMuu0tEAhB81", Product: "Phrased Flintstones1", Version: "Phrased Flintstones1", Platform: "Phrased Flintstones1", Event: "camera", InstallId: "1", Created: "2022-10-11T17:31:39"},
		{Key: "1bjCljWkq6tinyAMuu0tEAhB82", Product: "Phrased Flintstones2", Version: "Phrased Flintstones2", Platform: "Phrased Flintstones2", Event: "activation", InstallId: "1", Created: "2022-10-11T17:31:39"},
		{Key: "1bjCljWkq6tinyAMuu0tEAhB83", Product: "Phrased Flintstones3", Version: "Phrased Flintstones3", Platform: "Phrased Flintstones3", Event: "textsearch", InstallId: "1", Created: "2022-10-11T17:31:39"},
		{Key: "1bjCljWkq6tinyAMuu0tEAhB84", Product: "Phrased Flintstones4", Version: "Phrased Flintstones4", Platform: "Phrased Flintstones4", Event: "barcode", InstallId: "1", Created: "2022-10-11T17:31:39"},
		{Key: "1bjCljWkq6tinyAMuu0tEAhB85", Product: "Phrased Flintstones5", Version: "Phrased Flintstones5", Platform: "Phrased Flintstones5", Event: "ocr", InstallId: "1", Created: "2022-10-11T17:31:39"},
		{Key: "1bjCljWkq6tinyAMuu0tEAhB86", Product: "Phrased Flintstones6", Version: "Phrased Flintstones6", Platform: "Phrased Flintstones6", Event: "fooddetection", InstallId: "1", Created: "2022-10-11T17:31:39"},
		{Key: "1bjCljWkq6tinyAMuu0tEAhB87", Product: "Phrased Flintstones7", Version: "Phrased Flintstones7", Platform: "Phrased Flintstones7", Event: "classifications", InstallId: "1", Created: "2022-10-11T17:31:39"},
		{Key: "1bjCljWkq6tinyAMuu0tEAhB88", Product: "Phrased Flintstones8", Version: "Phrased Flintstones8", Platform: "Phrased Flintstones8", Event: "customdetection", InstallId: "1", Created: "2022-10-11T17:31:39"},
		{Key: "1bjCljWkq6tinyAMuu0tEAhB89", Product: "Phrased Flintstones9", Version: "Phrased Flintstones9", Platform: "Phrased Flintstones9", Event: "detectinimage", InstallId: "1", Created: "2022-10-11T17:31:39"},
		{Key: "1bjCljWkq6tinyAMuu0tEAhB89", Product: "Phrased Flintstones9", Version: "Phrased Flintstones10", Platform: "Phrased Flintstones10", Event: "packagedfood", InstallId: "1", Created: "2022-10-11T17:31:39"},
	}

	if err := inserter.Put(ctx, items); err != nil {
		fmt.Println("err=>", err)
		return
	}

While I try to fetch the record using the below query it is not working

HERE THIS QUERY WILL NOT WORKING

SELECT yearMonth, count, key
			FROM (
					SELECT key, FORMAT_DATE('%Y-%m', PARSE_DATETIME('%Y-%m-%d %H:%M:%S', STRING(created))) AS yearMonth, count(distinct installid) as count
					FROM test.dataset.table
					WHERE created BETWEEN '2022-09-14' AND DATE_ADD(DATE '2022-10-14', INTERVAL 1 DAY)
						 AND installid IS NOT NULL AND length(installid) > 0 
					GROUP BY key, yearMonth
					ORDER BY yearMonth
				) AS counts
		 WHERE key IN ("1bjCljWkq6tinyAMuu0tEAhB80","1bjCljWkq6tinyAMuu0tEAhB82","1bjCljWkq6tinyAMuu0tEAhB86") ORDER BY key 

It will give me the error panic: runtime error: invalid memory address or nil pointer dereference [recovered]

Whenever I run this SIMPLE query it will give me 11 records and its works fine but whenever I run the above query it would not work yet.

rows := bigQueryClient.Query(`
			SELECT *
			FROM ` + "`test.dataset.table`" + `
	`)

	it, err := rows.Read(ctx)
	fmt.Println("TOTAL ROWS COUNT", it.TotalRows) 

// OUTPUT : TOTAL ROWS COUNT 11

Please suggest me proper solution for the above query Thank you @goccy