I use group by 99,99% with aggregate functions.
Just this one special case I have when I must
get min, max values and also I have group_concat
where I must filter records. Result is then
inserted into table where I have not null constraint
and I get error because min, max returned null.
I solved
Radovan Antloga wrote:
> select min(A)
> from TEST
> where B is null
> and A > 3;
>
> if you replace min(A) with * you get empty result set as expected
> but with min or max or avg you get one record
This is just how aggregate functions in SQL work.
When you're using GROUP BY, you get exactly
Hi,
I discovered a bug using sqlite 3.15.2. It is simple
to reproduce. Try this SQL-s:
CREATE TABLE TEST(
A integer primary key,
B integer);
-- insert some test data
insert into TEST (A,B) values (1, 1);
insert into TEST (A,B) values (2, null);
-- check count(*)
select count(*)
from TEST
This is the way SQL (not only SQLite) interprets these queries.
Basically you ask information about an empty set:
- count(*) = 0, as expected
- min(A) is undefined, which is translated in SQL by 'null' value; since
'null' is not 'nothing', you get a 1-line result comprising 'null' !
Regards
Sorry to post this to quick. I just checked this
with Firebird and I get same result. I did not
expect that.
Sorry once again !!
Radovan Antloga je 06.02.2017 ob 18:34 napisal:
Hi,
I discovered a bug using sqlite 3.15.2. It is simple
to reproduce. Try this SQL-s:
CREATE TABLE TEST(
A
Hi,
I discovered a bug using sqlite 3.15.2. It is simple
to reproduce. Try this SQL-s:
CREATE TABLE TEST(
A integer primary key,
B integer);
-- insert some test data
insert into TEST (A,B) values (1, 1);
insert into TEST (A,B) values (2, null);
-- check count(*)
select count(*)
from TEST
6 matches
Mail list logo
