CODEWITHSUNDEEP
mysqlsqlmariadb
Caffeinated
Caffeinated

Reputation: 12484

What is the reason for this mysterous SQL output ( using MariaDB )?

This is a query I inputted , admittedly a random tweak , into a test box in the website SQLZoo :

SELECT title || 'fo'
FROM movie
WHERE title IS NOT NULL
AND title like '%he%';

And it returns unusual output that looks like this :

title || fo
--------------------
0
1
1
1
1
1
1
/* etc etc just 0's and 1's */

Whereas if I just use the following query :

SELECT title 
FROM movie
WHERE title IS NOT NULL
AND title like '%he%';

then I will ge tthe very reasonable output I expected :

title
--------------------
Gone with the End
Dr. doLittle
Con Air.
/* et etc more movi */

So I think the back-end on this website uses something called MariaDB ( a branch of MySQL , perhaps ? ).

why would I get the output of 0's and 1's only , just by conccatenating the title with a string ?

To be precise, it's the 6th one - What is the id of the film 'Casablanca' . thanks

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269963

By default, the || is the logical or operation -- not string concatenation. So, your select is really:

select (title OR fo)

which just checks that one or the other is "true" (non-zero).

The list of operators is here.

You can change this behavior by setting the mode PIPES_AS_CONCAT (see here).

Upvotes: 3

Related Questions