I have a test which uses H2 as an in memory database. The following table,
data and query produces a single row in MySQL with the expected values, but
not in H2:
CREATE TABLE redirects (
site_id INTEGER NOT NULL,
company_id INTEGER NOT NULL,
type CHAR(1) NOT NULL,
capture TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO redirects (site_id, company_id, type, capture) VALUES (1, 1, 'm',
CURRENT_TIMESTAMP);
INSERT INTO redirects (site_id, company_id, type, capture) VALUES (1, 2, 'm',
CURRENT_TIMESTAMP);
INSERT INTO redirects (site_id, company_id, type, capture) VALUES (1, 1, 'h',
CURRENT_TIMESTAMP);
INSERT INTO redirects (site_id, company_id, type, capture) VALUES (1, 1, 'h',
CURRENT_TIMESTAMP);
INSERT INTO redirects (site_id, company_id, type, capture) VALUES (1, 2, 'h',
CURRENT_TIMESTAMP);
INSERT INTO redirects (site_id, company_id, type, capture) VALUES (1, 1, 'a',
CURRENT_TIMESTAMP);
INSERT INTO redirects (site_id, company_id, type, capture) VALUES (1, 1, 'a',
CURRENT_TIMESTAMP);
INSERT INTO redirects (site_id, company_id, type, capture) VALUES (1, 1, 'a',
CURRENT_TIMESTAMP);
INSERT INTO redirects (site_id, company_id, type, capture) VALUES (1, 2, 'a',
CURRENT_TIMESTAMP);
SELECT
COALESCE(site_id, 1) site_id,
COALESCE(company_id, -1) company_id,
COUNT(CASE WHEN type = 'm' THEN 1 END) views,
COUNT(CASE WHEN type IN ('h', 'a') THEN 1 END) clicks
FROM
redirects
WHERE
(site_id = 1 AND company_id = 3 AND capture > TIMESTAMP '2017-04-24
00:00:00.0');
The result in MySQL is the following:
+---------+------------+-------+--------+
| site_id | company_id | views | clicks |
+---------+------------+-------+--------+
| 1 | -1 | 0 | 0 |
+---------+------------+-------+--------+
However, in H2 (version 1.4.194) the result is:
SITE_ID COMPANY_ID VIEWS CLICKS
1 2 0 0
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
