MySQL error 1064: search query not working as expected

I am attempting to write a query for searching on a table, but I am getting the generic 1064 MySQL syntax error. I don't see anything wrong with my query... in fact, I have another query just like it that works fine.

The queries are using an SQL driver in Go, all of the ? will be converted to values before the query is ran.

This is my error:

> Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT tax.*
FROM tax
JOIN user_tax
ON user_tax.tax_id = tax.id
WHERE u' at line 3

For instance, this is the working query:

SELECT tax.*
FROM tax
JOIN user_tax
	ON user_tax.tax_id = tax.id
WHERE user_tax.user_id = ?
	AND tax.deleted_at IS NULL
	AND user_tax.deleted_at IS NULL

The query above will find all tax records for a given user.

Now I am trying to make the same exact query, except now it is a "search" query. It takes in a search string and looks for it on the name and rate column of the tax table. This is it:

SET @searchString = '%?%';

SELECT tax.*
FROM tax
JOIN user_tax
	ON user_tax.tax_id = tax.id
WHERE user_tax.user_id = ?
	AND tax.deleted_at IS NULL
	AND user_tax.deleted_at IS NULL
	AND tax.name LIKE @searchString
		OR tax.rate LIKE @searchString

This is the query in code:

var taxes []Tax
err := r.db.Select(&taxes, `
	SET @query = '%?%';

	SELECT tax.*
	FROM tax
	JOIN user_tax
		ON user_tax.tax_id = tax.id
	WHERE user_tax.user_id = ?
		AND tax.deleted_at IS NULL
		AND user_tax.deleted_at IS NULL
		AND tax.name LIKE @query
			OR tax.rate LIKE @query
`, q, userId)

If you want to avoid binding the same value multiple times, you can bind it in a cross joined subquery once and reuse the value:

SELECT tax.*
FROM tax
JOIN user_tax
    ON user_tax.tax_id = tax.id
CROSS JOIN (SELECT ? as q) sub
WHERE user_tax.user_id = ?
    AND tax.deleted_at IS NULL
    AND user_tax.deleted_at IS NULL
    AND (tax.name LIKE sub.q
         OR tax.rate LIKE sub.q)

Now just bind %s% as the first parameter.

I don't understand why to do SET @query and pass it inside of it when You can do it by using bind parameter using ?.

So here is solution (I'm not familiar with go so please sorry if something wrong with go code):

var taxes []Tax
query := "%something%youre%looking%for%"
err := r.db.Select(&taxes, `

    SELECT tax.*
    FROM tax
    JOIN user_tax
        ON user_tax.tax_id = tax.id
    WHERE user_tax.user_id = ?
        AND tax.deleted_at IS NULL
        AND user_tax.deleted_at IS NULL
        AND (
              tax.name LIKE ? 
              OR 
              tax.rate LIKE ?
            )

`, q, userId, query, query)

OR try this (I'm not sure if it will work) :

var taxes []Tax
query := "something"
err := r.db.Select(&taxes, `
    SET @query = CONCAT('%', ?, '%');

    SELECT tax.*
    FROM tax
    JOIN user_tax
        ON user_tax.tax_id = tax.id
    WHERE user_tax.user_id = ?
        AND tax.deleted_at IS NULL
        AND user_tax.deleted_at IS NULL
        AND (
              tax.name LIKE @query
              OR 
              tax.rate LIKE @query
            )

`, q, query, userId)

The syntax errors reported by MySQL contain the magic words "check the manual that corresponds to your MySQL server version for the right syntax to use near '" followed by the trailing part of the query that doesn't fit with the leading part that was already parsed and found as correct.

When the part of the query reported in the error message doesn't look incorrect, something is usually missing just before it.

In your case it seems nothing is missing and this leads to a single conclusion: you try to run two queries (correctly separated by ;) but the library you use to send queries to the server doesn't know that and thinks it is only one query. The same happens on the other side of the connection and the MySQL server simply reports as "Syntax error" everything it founds after the first query (and its terminating ;).

Don't send two queries in a single call or use a call that allows multiple queries in the same string (if such a call is provided by the library).