Can't get the LIKE operator to work. Nor IS NULL

GSQL
1

Another case where I’m trying to take advantage of my previous SQL knowledge but it fails when I try to apply to GSQL.

The LIKE documentation doesn’t have any relevant (to me) examples (using SELECT and WHERE), but it seem straightforward. It describes LIKE in a similar way to other SQL languages. (It’s also very hard to google for LIKE, since it’s a common English word, so maybe I missed an explanation on the internet.)

In fact, this page has the hopeful tip:

Tip: The operators listed in this section are designed to behave like the operators in MySQL.

This simple comparison expression works:

SELECT *  FROM person WHERE name =< "Bob" limit 3

but this unexpectedly fails:

SELECT *  FROM person WHERE name LIKE "Bob%"
Encountered " "like" "LIKE "" at line 1, column 34.
Was expecting one of:
    "<" ...
    ">" ...
    "." ...
    "!=" ...
    "==" ...
    "<=" ...
    ">=" ...

(I also am confused by " "like" "LIKE "" in the error message, since I’m using LIKE only once.)

What further confuses me, is that this list of expected operators doesn’t include IS, IS NOT, or IN even those are included in the documentation for Expressions. (I did see somebody posted elsewhere that IN doesn’t work.)

So, IS NULL fails in the same way:

SELECT *  FROM person WHERE name IS NULL

I suspect that maybe I’m misapplying the documentation Operators, Functions, and Expressions to the WHERE clause, but that just seems odd, since other SQL languages do support that.

I’m very unclear what I’m getting wrong.

2

I use LIKE all the time using installed queries. Keep in mind that what you are able to do from the interactive command line is very restrictive compared to what you can do using a CREATE QUERY followed by INSTALL QUERY.

1 Like
3

Hmmm… I think that is what is not emphasized enough in the documentation and videos. I think this is what is confusing the heck out of me.

So, now I understand that GSQL is unlike most SQL languages (that I know) in that there are these two modes:

  • Interactive mode (with “Built-In”) that is very incomplete and gives obscure error messages when you try to use the full power of the language.
  • Query Function mode which gives the full power, but you have to go through the hassle of creating the function, installing it, and then running it. I think the module 4 video tries to make the Query Function seems like a feature by pointing out how you can pass parameters to the Query Function.

Is this correct or am I missing something?

4

True - and while it may seem like a hassle to create the queries, its actually the best mode. I have many years of using SQL, and yes you have a bit of a paradigm shift, but its not completely unlike prepared statements and query optimization. Here you just have to get used to creating and installing queries for anything other than the most simple examples.

But you get the benefits of all the power of the GSQL language, and the queries are immediately callable as REST APIs , can be created as DISTRIBUTED and will have the best performance. So much so that I now use installed queries for 99% of my work.

1 Like