Unexpected INSERT ... SET query behavior

Question

I have a table where I need to store two id's from another table. While doing some debugging I noticed some strange behavior of SQL.

Example of wrong sql:

INSERT INTO follower_list set `followerUserId` = '3' AND `followingUserid` = '4' 

The above query was inserting the zero's as value in DB. I studied the query closer and realized I had made a mistake of putting and in place of ,. The real query I need to fulfill my purpose is:

INSERT INTO table SET col1 = '3' , col2 = '4'

Which worked as I expected. My question is related to the first (incorrect) query - since it executed and is syntactically correct, where would queries like it be used?

Answer 1

The reason why the INSERT statement doesn't generates syntax error and it is also working is because MySQL implicitly (one thing I don't like in MySQL :D) parses the statement as boolean expression.

In your INSERT statement, only followerUserId column is updatable because the rest are part of the boolean expression. The query was evaluated as:

INSERT INTO follower_list SET followerUserId = ('3' and (followingUserid='4')) 

• SQLFiddle Demo (SELECT statement)

here:

followerUserId = ('3' and (followingUserid='4')) // assuming followingUserid <> 4
followerUserId = ('3' and (0))
followerUserId = 0

another,

followerUserId = ('3' and (followingUserid='4')) // assuming followingUserid = 4
followerUserId = ('3' and (1))
followerUserId = 1      // will only return zero if one of the values is zero