Exploring 'WHERE' Clause

Did you observe that the most commonly used ‘WHERE’ clause works differently in different databases?

Let me walk you through my observations..

Let’s consider we have an EMPLOYEE table as below:

CREATE TABLE EMPLOYEE (
  empId NUMBER PRIMARY KEY,
  name VARCHAR2(15) NOT NULL,
  dept VARCHAR2(10) NOT NULL
);        

Let's insert some sample data.

INSERT INTO EMPLOYEE VALUES (1, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (2, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (3, 'Ava', 'Sales');        

Now the table looks like below:

Now, lets see how a simple "WHERE" clause behaves in different databases. For this article, I've considered ORACLE, PostgreSQL and MySQL

ORACLE

In the below query we are trying to find all departments whose value is 1(integer). Note that department in VARCHAR.

select * from employee where dept = 1;

Output:
ERROR at line 1:
ORA-01722: unable to convert string value containing 'S' to a number: DEPT
ORA-03302: (ORA-01722 details) invalid string value: Sales        

The critical observation is ORACLE tries to typecast the values of the required column to the type of the variable in WHERE. As a number is given in the where clause, it tries to typecast the dept column in each row to a number and match the value to 1.

As the first value in dept column is 'Sales', RACLE is not able to typecast it to a number and hence throws the error.

Let's see another example:

In the below query we are tying to find all employees whose value is 'abc'(string). Note that empId is an integer field.

select * from employee where empId = 'abc';

Output:
ERROR at line 1:
ORA-01722: unable to convert string value containing 'a' to a number:
ORA-03302: (ORA-01722 details) invalid string value: abc        

Here, ORACLE tries to convert the string value given in the WHERE to a number.

So, looks like ORACLE tries to convert a string to a number when there is a mismatch in the datatypes of the column in the WHERE clause.

Now, let's see something interesting with PostgreSQL

Let's try to execute the same scenarios as above.

select * from employee where dept = 1;

Output:
psql:commands.sql:17: ERROR:  operator does not exist: text = integer
LINE 1: select * from employee where dept = 1;
                                          ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.        

Interestingly, PostgreSQL asks the user to explicitly type cast whenever needed.

Let's see another example

select * from employee where empId = 'abc';

Output:
psql:commands.sql:19: ERROR:  invalid input syntax for type integer: "abc"
LINE 1: select * from employee where empId = 'abc';        

PostgreSQL explicitly throws errors for the user to correct his query as shown above.

Now, let's see something more interesting with MySQL.

select * from EMPLOYEE where dept = 1;

Output:
Program did not output anything!        

Interestingly, MySQL does not throw any error at all. It just does a value compare and will output if there is a match, else gives no output.

Let's try next scenario.

select * from EMPLOYEE where empId = 'abc';

Output:
Program did not output anything!        

Hmm.. once again MySQL didn't throw any error.

How can this be an issue?

If you are playing with data in ORACLE(also SNOWFLAKE) database, be careful that you might see errors in runtime because your test data might not cover all real time scenarios.

Let me walk you through an issue we faced recently in our Production while migrating data into ORACLE.

There is a column `OUR_REF_NUM(VARCHAR)`.

In one of our procedures we have a WHERE condition where we were comparing with an integer. As the test data has all integers of type '1001', '1002' etc., we didn't face any issue while testing in development environments.

But the production has data like 'REF12','REF45'. This caused our procedure to fail with

ORA-01722: unable to convert string value containing 'R' to a number: OUR_REF_NUM        

Had we used PostgreSQL, we would have caught this error in lower environments. But, due to the inherent behavior of ORACLE where it typecasts, we missed to find this error.

Eventually we fixed the code.

Conclusion

Be mindful and thoughtful about the basic features and behaviors of the tools we use in our work. This will help in finding and fixing most of the issues early in development phase.