Strange error: unsupported reference type: <nil>

[andrei-dascalu]

Version

1.22.0

What happened?

I am trying to generate queries in a local project and it results in this error

unsupported reference type: <nil>

The reason for this is unclear in the sense that the exact same structure/queries works fine in Playground. It only appears locally on my Mac, running go 1.21.1

Now, the code is generated - despite the error showing.

I've tried installing sqlc both with go install and brew, made no difference. I can't figure out what's different between what runs in playground and what runs locally. Or maybe the playground simply ignores that output?

The real issue comes when trying to run the function generated => sql: expected 3 arguments, got 2
This is particularly strange because it seems it doesn't recognize arguments with the same name which are part of the same .... scope? I think, because I wrote more complex cases where arguments like calendarIdKey were reused in different parts of the same INSERT and they were fine. However, the timezone argument here is taken separately when part of the CASE test vs when it's used to provide the value in ELSE.

Relevant log output

`unsupported reference type: <nil>` 

With debug: https://gist.github.com/andrei-dascalu/700814035f1998de27a80e0203e9657a

Database schema

CREATE TABLE `Calendar` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Relation` bigint(20) unsigned NOT NULL,
  `CalendarName` longblob NOT NULL,
  `Title` longblob NOT NULL,
  `Description` longblob NOT NULL,
  `Timezone` varchar(50) NOT NULL,
  `UniqueKey` varchar(50) NOT NULL,
  `IdKey` varchar(50) NOT NULL,
  `MainCalendar` enum('true','false') NOT NULL DEFAULT 'false',
  `CreateDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ModifyDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Id`),
  KEY `Relation` (`Relation`),
  KEY `UniqueKey` (`UniqueKey`),
  KEY `IdKey` (`IdKey`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;


CREATE TABLE `Event` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Relation` bigint(20) unsigned NOT NULL,
  `CalendarReference` bigint(20) unsigned NOT NULL,
  `UniqueKey` varchar(50) NOT NULL,
  `EventName` longblob NOT NULL,
  `Description` longblob NOT NULL,
  `Location` varchar(500) NOT NULL,
  `Timezone` varchar(50) NOT NULL,
  `IdKey` varchar(48) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `Relation` (`Relation`),
  KEY `CalendarReference` (`CalendarReference`),
  KEY `UniqueKey` (`UniqueKey`),
  KEY `IdKey` (`IdKey`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;

SQL queries

-- name: AddEvent :execlastid
INSERT INTO `Event` (
    Timezone
) VALUES (
    (CASE WHEN sqlc.arg("Timezone") = "calendar" THEN (SELECT cal.Timezone FROM Calendar cal WHERE cal.IdKey = sqlc.arg("calendarIdKey")) ELSE sqlc.arg("Timezone") END)
);

Configuration

No response

Playground URL

https://play.sqlc.dev/p/296540930cfa87073b096c229df93152bae688b9c997bc70f94b4264928d5464

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

[andrei-dascalu]

if I change ELSE sqlc.arg("Timezone") END to ELSE "test" END then it works. It looks like it won't process the second arg even if it's same as what's used already in the CASE. Still, in Playground the exact same setup works so I'm not sure what I can do to further debug what's going on.

[andrei-dascalu]

Updated desc to reflect issues:
1 - the error that appears when running generate which doesn't seem to break the generation of code
2 - the fact that trying to run the generated function results in an SQL error that complains about expecting 3 arguments and only 2 provided.

[andrei-dascalu]

On a second look, it looks more like arguments are simply not accepted as part of ELSE. It doesn't stop the code generation, but if I change the name of the argument, the argument mentioned in ELSE is not generated as part of the parameter struct.

[kyleconroy]

First, I noticed that your sqlc.json has sql_package set to pgx/v5, which has no effect when the engine is set to mysql. We should probably emit an warning in that case. I don't think this has any effect on underlying error.

The issue is that sqlc isn't generated the correct number of parameters for your query. It should be three, not two

// Incorrect
func (q *Queries) AddEvent(ctx context.Context, arg AddEventParams) error {
	_, err := q.db.Exec(ctx, addEvent, arg.Timezone, arg.CalendarIdKey)
	return err
}

// Correct
func (q *Queries) AddEvent(ctx context.Context, arg AddEventParams) error {
	_, err := q.db.Exec(ctx, addEvent, arg.Timezone, arg.CalendarIdKey, arg.Timezone)
	return err
}

Can you confirm that the manually updated Go code works?

[orisano]

In a case like INSERT > VALUES > CASE WHEN ELSE > paramter, find_params will return a paramRef with parent nil.

sqlc/internal/compiler/find_params.go

Lines 56 to 219 in b2e42f8

	func (p paramSearch) Visit(node ast.Node) astutils.Visitor {
	if len(*p.errs) > 0 {
	return p
	}
	switch n := node.(type) {
	case *ast.A_Expr:
	p.parent = node
	case *ast.BetweenExpr:
	p.parent = node
	case *ast.CallStmt:
	p.parent = n.FuncCall
	case *ast.DeleteStmt:
	if n.LimitCount != nil {
	p.limitCount = n.LimitCount
	}
	case *ast.FuncCall:
	p.parent = node
	case *ast.InsertStmt:
	if s, ok := n.SelectStmt.(*ast.SelectStmt); ok {
	for i, item := range s.TargetList.Items {
	target, ok := item.(*ast.ResTarget)
	if !ok {
	continue
	}
	ref, ok := target.Val.(*ast.ParamRef)
	if !ok {
	continue
	}
	if len(n.Cols.Items) <= i {
	*p.errs = append(*p.errs, fmt.Errorf("INSERT has more expressions than target columns"))
	return p
	}
	*p.refs = append(*p.refs, paramRef{parent: n.Cols.Items[i], ref: ref, rv: n.Relation})
	p.seen[ref.Location] = struct{}{}
	}
	for _, item := range s.ValuesLists.Items {
	vl, ok := item.(*ast.List)
	if !ok {
	continue
	}
	for i, v := range vl.Items {
	ref, ok := v.(*ast.ParamRef)
	if !ok {
	continue
	}
	if len(n.Cols.Items) <= i {
	*p.errs = append(*p.errs, fmt.Errorf("INSERT has more expressions than target columns"))
	return p
	}
	*p.refs = append(*p.refs, paramRef{parent: n.Cols.Items[i], ref: ref, rv: n.Relation})
	p.seen[ref.Location] = struct{}{}
	}
	}
	}
	case *ast.UpdateStmt:
	for _, item := range n.TargetList.Items {
	target, ok := item.(*ast.ResTarget)
	if !ok {
	continue
	}
	ref, ok := target.Val.(*ast.ParamRef)
	if !ok {
	continue
	}
	for _, relation := range n.Relations.Items {
	rv, ok := relation.(*ast.RangeVar)
	if !ok {
	continue
	}
	*p.refs = append(*p.refs, paramRef{parent: target, ref: ref, rv: rv})
	}
	p.seen[ref.Location] = struct{}{}
	}
	if n.LimitCount != nil {
	p.limitCount = n.LimitCount
	}
	case *ast.RangeVar:
	p.rangeVar = n
	case *ast.ResTarget:
	p.parent = node
	case *ast.SelectStmt:
	if n.LimitCount != nil {
	p.limitCount = n.LimitCount
	}
	if n.LimitOffset != nil {
	p.limitOffset = n.LimitOffset
	}
	case *ast.TypeCast:
	p.parent = node
	case *ast.ParamRef:
	parent := p.parent
	if count, ok := p.limitCount.(*ast.ParamRef); ok {
	if n.Number == count.Number {
	parent = &limitCount{}
	}
	}
	if offset, ok := p.limitOffset.(*ast.ParamRef); ok {
	if n.Number == offset.Number {
	parent = &limitOffset{}
	}
	}
	if _, found := p.seen[n.Location]; found {
	break
	}
	// Special, terrible case for *ast.MultiAssignRef
	set := true
	if res, ok := parent.(*ast.ResTarget); ok {
	if multi, ok := res.Val.(*ast.MultiAssignRef); ok {
	set = false
	if row, ok := multi.Source.(*ast.RowExpr); ok {
	for i, arg := range row.Args.Items {
	if ref, ok := arg.(*ast.ParamRef); ok {
	if multi.Colno == i+1 && ref.Number == n.Number {
	set = true
	}
	}
	}
	}
	}
	}
	if set {
	*p.refs = append(*p.refs, paramRef{parent: parent, ref: n, rv: p.rangeVar})
	p.seen[n.Location] = struct{}{}
	}
	return nil
	case *ast.In:
	if n.Sel == nil {
	p.parent = node
	} else {
	if sel, ok := n.Sel.(*ast.SelectStmt); ok && sel.FromClause != nil {
	from := sel.FromClause
	if schema, ok := from.Items[0].(*ast.RangeVar); ok && schema != nil {
	p.rangeVar = &ast.RangeVar{
	Catalogname: schema.Catalogname,
	Schemaname: schema.Schemaname,
	Relname: schema.Relname,
	}
	}
	}
	}
	if _, ok := n.Expr.(*ast.ParamRef); ok {
	p.Visit(n.Expr)
	}
	}
	return p
	}

resolveCatalogRefs ignores paramRefs with nil parent and outputs error messages like the one in the description

sqlc/internal/compiler/resolve.go

Line 603 in b2e42f8

fmt.Printf("unsupported reference type: %T\n", n)

There are two ways to solve this problem.

1. Modify resolveCatalogRefs to treat a paramRef with nil parent as any.
2. Make find_params return paramRef well.

I think it is better to deal with 1 first, and then deal with 2.

This is fundamentally the same problem as #2748.

[orisano]

This is a compiler problem that occurs with all engines, not just MySQL.

[andrei-dascalu]

@kyleconroy indeed, manually adding the same parameter in the list work.

As a workaround for my current situation I used a param with different name that I'm assigning the same value. As a secondary issue, I had to write the workaround SQL as

-- name: AddEvent :execlastid
INSERT INTO `Event` (
    Timezone
) VALUES (
    (CASE WHEN sqlc.arg("TimezoneTest") = "calendar" THEN (SELECT cal.Timezone FROM Calendar cal WHERE cal.IdKey = sqlc.arg("calendarIdKey")) ELSE CONVERT(sqlc.arg("TimezoneValue"), CHAR) END),
);

Without the specific convert, it would complain that it can't figure the type