SELECT statement returns rows, but SELECT COUNT(*) with same WHERE does not (AWS AuroraDB)

Question

This is the database schema as it relates to User and GpsPosition:

CREATE TABLE GpsPosition 
(
    altitudeInMeters SMALLINT NOT NULL,
    dateCreated      BIGINT NOT NULL,
    dateRegistered   BIGINT NOT NULL,
    deviceId         BINARY(16) NOT NULL,
    emergencyId      BINARY(16) NULL,
    gpsFix           SMALLINT NOT NULL,
    heading          SMALLINT NOT NULL,
    horizontalUncertaintyInMeters SMALLINT NOT NULL,
    id               BINARY(16) NOT NULL,
    latestForDevice  BOOLEAN NOT NULL,
    latestForUser    BOOLEAN NOT NULL,
    latitude         DOUBLE PRECISION NOT NULL,
    longitude        DOUBLE PRECISION NOT NULL,
    numSatellites    SMALLINT NOT NULL,
    speedInKmph      SMALLINT NOT NULL,
    stale            BOOLEAN NOT NULL,
    userId           BINARY(16) NULL,
    verticalUncertaintyInMeters SMALLINT NOT NULL,

    PRIMARY KEY (id)
);

ALTER TABLE GpsPosition 
    ADD CONSTRAINT GpsPosition_deviceId_fkey 
        FOREIGN KEY (deviceId) REFERENCES Device(id) 
            ON UPDATE CASCADE ON DELETE CASCADE;

ALTER TABLE GpsPosition 
    ADD CONSTRAINT GpsPosition_emergencyId_fkey 
        FOREIGN KEY (emergencyId) REFERENCES Emergency(id) 
            ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE GpsPosition 
    ADD CONSTRAINT GpsPosition_userId_fkey 
        FOREIGN KEY (userId) REFERENCES User(id) 
            ON UPDATE CASCADE ON DELETE SET NULL;

ALTER TABLE GpsPosition 
    ADD CONSTRAINT deviceId_dateCreated_must_be_unique 
        UNIQUE (deviceId, dateCreated);

CREATE INDEX i2915035553 ON GpsPosition (deviceId);
CREATE INDEX deviceId_latestForDevice_is_non_unique ON GpsPosition (deviceId, latestForDevice);
CREATE INDEX i3210815937 ON GpsPosition (emergencyId);
CREATE INDEX i1689669068 ON GpsPosition (userId);
CREATE INDEX userId_latestForUser_is_non_unique ON GpsPosition (userId, latestForUser);

This statement returns a great many rows:

select *
from GpsPosition
where exists (select *
              from User
              where User.id = GpsPosition.userId and
                    User.id = UNHEX( '3f4163aab2ac46d6ad15164222aca89e' )
             );

This statement returns a single row (result) with the value of 0:

select count(*)
from GpsPosition
where exists (select *
              from User
              where User.id = GpsPosition.userId and
                    User.id = UNHEX( '3f4163aab2ac46d6ad15164222aca89e' )
             );

What I don't understand is how the SELECT * statement could return many results, whereas the SELECT COUNT(*) statement returns 0. They both have identically the same WHERE statement.

why do you use the correlated subquery instead of join table? — thanh ngo, May 13 '19 at 14:22
On a side note: If your database is consistent, you can simplify this to: `select count(*) from GpsPosition where User.id = UNHEX( '3f4163aab2ac46d6ad15164222aca89e'`. — Thorsten Kettner, May 13 '19 at 14:42
I have no explanation for the behavior you describe. As the queries only differ in `*` vs `count(*)`, the second query must return the number of rows the first query returns. Two possibilities: You made a mistake somewhere or you found a bug in the DBMS. — Thorsten Kettner, May 13 '19 at 14:44
Turns out this is a duplicate of https://stackoverflow.com/questions/55985502/two-sql-statements-should-return-the-same-results-but-they-dont-on-aws-aurora — jasons2645, Jun 27 '19 at 21:10

thanh ngo · Answer 1 · 2019-05-13T14:38:00.093

0

Correlated subqueries are used for row-by-row processing. Each subquery is executed once for every row of the outer query. This diagram helps a lot Because the outer query result has nothing but one row for number of rows in GpsPosition, the result is 0.

I think this should work:

select count(*) from (
    select *
    from GpsPosition
    where exists (select *
                  from User
                  where User.id = GpsPosition.userId and
                        User.id = UNHEX( '3f4163aab2ac46d6ad15164222aca89e' )
                 )
) view1

edited May 13 '19 at 14:38

answered May 13 '19 at 14:28

thanh ngo

834
5
9

I just tried the query below, and it returned 0 as well: select count(*) from ( select * from GpsPosition where exists (select * from User where User.id = GpsPosition.userId and User.id = UNHEX( '3f4163aab2ac46d6ad15164222aca89e' ) ) ) AS T – jasons2645 May 13 '19 at 14:32
Could you prepare some sample data using sqlfiddle please? Then i can reproduce the query and see what is happened. – thanh ngo May 13 '19 at 14:34
Note that I tried the original query you suggested and got an error 1248. Every derived table must have its own alias: https://stackoverflow.com/questions/1888779/what-is-the-error-every-derived-table-must-have-its-own-alias-in-mysql – jasons2645 May 13 '19 at 14:34
And thank YOU Thanh Ngo for taking the time to describe how the EXISTS query execution works :) – jasons2645 May 13 '19 at 14:39

SELECT statement returns rows, but SELECT COUNT(*) with same WHERE does not (AWS AuroraDB)

1 Answers1