Error in WHERE Clause, sqlx w/ postgres

[BlakeBrown]

I am running a query in Golang, using the sqlx library:

rows, dbErr := db.Queryx(`
	SELECT *
	FROM table_a as a
	   INNER JOIN table_b AS b
	      ON a.b_id = b.id
	   WHERE b.id = 'b3c06feb-8bd1-42e1-b516-c1121990ff81';
	`)

The query is syntactically correct, and runs fine in Postico, terminal, etc. However when I try to run this query in Golang I get the error pq: syntax error at or near "  WHERE"

Can anyone explain this strange behaviour?

[jhngrant]

@BlakeBrown I've been unable to replicate:

CREATE TABLE table_a
(
  b_id UUID
);

INSERT INTO table_a (b_id) VALUES ('b3c06feb-8bd1-42e1-b516-c1121990ff81');

CREATE TABLE table_b
(
  id UUID
);

INSERT INTO table_b (id) VALUES ('b3c06feb-8bd1-42e1-b516-c1121990ff81');

package main

import (
	"github.com/jackc/pgx/pgtype"
	_ "github.com/jackc/pgx/stdlib"
	"github.com/jmoiron/sqlx"
	"log"
)

type Result struct {
	BID pgtype.UUID `db:"b_id"`
	ID  pgtype.UUID `db:"id"`
}

func main() {
	dsn := "host=db.example.com user=usr password=pw"
	db, err := sqlx.Connect("pgx", dsn)

	if err != nil {
		log.Fatalln(err)
	}

	rows, err := db.Queryx(`
SELECT b_id, id
FROM table_a as a
   INNER JOIN table_b AS b
	  ON a.b_id = b.id
   WHERE b.id = 'b3c06feb-8bd1-42e1-b516-c1121990ff81';
	`)

	if err != nil {
		log.Fatalln(err)
	}

	for rows.Next() {
		var r Result
		err = rows.StructScan(&r)
		log.Printf("%v %v\n", r.BID, r.ID)
	}
}

[BlakeBrown]

@jhngrant Thanks a lot for testing it out. I think it's possible we're running an old version of sqlx? I believe our commit sha is 69738bd209812c5a381786011aff17944a384554 which is from January 2015.

Is there any reason you guys haven't released an official version update to the library yet?

[jhngrant]

@BlakeBrown Not sure. @jmoiron will hopefully response.

[jmoiron]

Queryx does not modify the query that you execute so sqlx is unlikely to be the culprit. If you want to truly avoid any sqlx code, db.Query would do that (it directly executes the builtin database/sql).

[jmoiron]

As for an official version, I am waiting for more of a consensus for versioning in Go; probably when dep reaches blessed status and has a clear position on it.

[BlakeBrown]

@jmoiron I already tried db.Query, doesn't work 😞

I think this is an issue related to our internal versioning. As @jhngrant verified, the code works fine with the updated version.

Thank you so much for the help guys, feel free to close.

[jmoiron]

Okay, I'd guess it would be more related to versioning of the postgresql client library than sqlx but if not and you ever find more details please let me know :)