why are select, order by, and group by $ escaped

[dvic]

Is there any particular reason for this? Is there a workaround to use sub-expressions with positional arguments ($1, $2, etc. obtained using .Var()) in these positions?

[huandu]

This package uses $0 .. $n to represent placeholders. If the $ is not escaped in Select/OrderBy/etc, there will be some unexpected behavior when building SQL.

Why do you need to use $1/$2 explicitly? Please let me know your scenario. I can help you.

[dvic]

I lost an hour or so on this issue because .Build() was missing arguments supplied in sb.Var (I used sub-expressions that used positional arguments in the select and order by). This is not what you expect, you expect when you call .Var() on a builder that the variable is always returned in .Build() not matter what, right?

[huandu]

Can you please show me some code? I still don't get your point.

[dvic]

So for example, I cannot create these kind of queries:

	sqlbuilder.DefaultFlavor = sqlbuilder.PostgreSQL
	sb := sqlbuilder.NewSelectBuilder()

	sb.Select("id", fmt.Sprintf("someFunc(some_col, %s)", sb.Var("bar")))
	sb.From("user")
	sb.Where("status = " + sb.Var("foo"))

	sql, args := sb.Build()
	fmt.Println(sql)
	fmt.Println(args)

The output is:

SELECT id, someFunc(some_col, $0) FROM user WHERE status = $1
[foo]

As you can see, bar is missing from the arguments and $0 is present in the query, which makes this query invalid in Postgres (positional arguments start at $1).

[dvic]

Or a more realistic example, where I want to find users with 'foo' in their name but sort the users whose name starts with 'foo' first:

	sqlbuilder.DefaultFlavor = sqlbuilder.PostgreSQL
	sb := sqlbuilder.NewSelectBuilder()

	foo := sb.Var("foo")
	sb.Select("id")
	sb.From("user")
	sb.Where("name ~* " + foo)
	sb.OrderBy(fmt.Sprintf("name ~* '^' || %s DESC", foo)) // DESC here because BOOL is sorted FALSE, TRUE

	sql, args := sb.Build()
	fmt.Println(sql)
	fmt.Println(args)

Prints

SELECT id FROM user WHERE name ~* $1 ORDER BY name ~* '^' || $0 DESC
[foo]

I understand now why this happens, but it's not desired I think, not sure how to fix this without introducing breaking changes.

[huandu]

OK. I get it. I wasn't aware of your use cases. I can fix it soon.