kvk30 kvk30 - 4 months ago 8
SQL Question

Same command but different outputs

I am validating Firebird over open access commands. For validating,
I use the same command over Firebird, open access with the same data and schema.

SELECT EMPNO,SAL
FROM EMP E
WHERE 1 = (SELECT MAX(DEPTNO)
FROM DEPT D
WHERE E.ENAME like '%AME%')
OR EMPNO = (SELECT MAX(DEPTNO)
FROM DEPT D
WHERE E.ENAME like (SELECT TOP 1 ENAME
FROM emp E1
WHERE E1.EMPNO=D.DEPTNO))
ORDER BY EMPNO;


Here are the outputs for open access,

EMPNO SAL
-15 3000
-12 2000
-11 100500.55
-10 NULL
-9 1111.11
-8 -3000
-6 NULL
.........
.........
Rows selected = 27


Firebird,

EMPNO SAL

Rows selected = 0


I was confused that firebird, open access use SQL as a query language. Gave different outputs.

DDL of the query,

CREATE TABLE EMP(EMPNO integer , ENAME varchar(32) , JOB varchar(32) , HIREDATE datetime , SAL float ,COMM real ,DEPTNO integer);
CREATE TABLE DEPT(DEPTNO integer, DNAME varchar(40), LOC varchar(40), DIVNO integer);


Data for above DDL,

INSERT INTO EMP VALUES(101, 'NAME1', 'TITLE1', '1990-02-02 02:02:02', 100500.55, 2000.90, 1);
INSERT INTO EMP VALUES (102,'NAME2','TITLE2', '1958-02-02 02:02:02',2000.00, 20000.00, 1);
INSERT INTO EMP VALUES(103, 'NAME3', 'TITLE3', '1997-03-12 08:09:54', 3000.00, 1000.50, 2);
INSERT INTO EMP VALUES(104, 'NAME4', 'TITLE4', '1997-04-02 00:00:00', 1111.11, 2.22, 1);
INSERT INTO EMP VALUES(105, 'NAME5', 'TITLE5', '1997-06-12 08:09:54', 3000.00, 1000.50, 2);
INSERT INTO EMP VALUES(106, 'NAME6', 'TITLE6', '1997-06-12 02:02:02', 1111.11, 2.22, 1);
INSERT INTO DEPT (DEPTNO, DNAME, LOC, DIVNO) Values (1, 'Software', 'San Jose', 10);
INSERT INTO DEPT (DEPTNO, DNAME, LOC, DIVNO) Values (2, 'Sales', 'LA', 20);
INSERT INTO DEPT (DEPTNO, DNAME, LOC, DIVNO) Values (3, 'Marketing', 'LA', 20);

Answer

The reasons that you don't get any information is simple, the data you have doesn't allow for any data to be returned. Lets dissect your query:

SELECT EMPNO,SAL
FROM EMP E 

Select some fields from emp

WHERE 1 = (SELECT MAX(DEPTNO) 
           FROM DEPT D 
           WHERE E.ENAME like '%AME%') 

Where 1 must be equal to the max DEPTNO from DEPT if for the current row from EMP the field ENAME contains AME (note that this is case sensitive).

This means that the comparison is either 1 = null (which is null, not false) or - with your sample data - 1 = 3 (which is false).

OR EMPNO = (SELECT MAX(DEPTNO)
            FROM DEPT D 
            WHERE E.ENAME like  (SELECT TOP 1 ENAME 
                                 FROM emp E1 
                                 WHERE E1.EMPNO=D.DEPTNO)) 

Or EMPNO is equal to the maximum of DEPTNO of DEPT with a convoluted query that makes no sense: you are querying the maximum DEPTNO where the ENAME of the current row of EMP E is equal to any first ENAME for where the EMPNO is equal to the current DEPTNO of DEPT D (which given the sample data is always false).

This query will always yield null because - in the sample data - the values in EMPNO are disjunct from DEPTNO, so the result of the innermost query is no row (yields null), so the first inner query will also yield null. Even if the first inner query had returned a value, the evaluation would still be false as EMPNO and DEPTNO are disjunct.

In other words, the WHERE-clause is equivalent to WHERE FALSE OR NULL, which yields no rows.

I have no idea what open access is (because a quick search doesn't reveal anything called that), but it seems to have very weird evaluation rules for SQL queries.

As I don't understand what this query is trying to retrieve, I also can't offer you a query that works.

Comments