On 7 Oct 2012, at 10:32am, Marcel Wesołowski <[email protected]> wrote:
> In SQLite when strings are concatenated (with ||) and one of them is null,
> the result will be null.
> This is not a required behaviour (not intuitive)
Actually, it is required behaviour, and I find it intuitive given what NULL
means. SQL92 6.13 General Rules 2(a)
"If either S1 or S2 is the null value, then the result of the
<concatenation> is the null value."
Generally, finding NULL unexpectedly is a big problem and you almost never find
a serious program showing a NULL value as ''. Normally you want them to be a
great big 'VALUE MISSING !!!!!1!!' kind of thing to show that something is
wrong. That's what 'ifnull()' is for.
> and most users tend to add
> *ifnull(X, '')* to get proper results.
> This slows down the whole operation, when we have lots of records in the
> database.
In SQL there is a huge difference between a NULL and a zero-length string and
your data should respect that difference. If you want a value to act as
zero-length strings, then you will find it far more convenient to actually
store the zero-length string and not a NULL. That way you can do your concats
and other operations without having to worry about NULLs everywhere. Makes
both your data storage and your code smaller and more efficient.
> Would it be hard to add both: compile time option (in C source) and pragma
> function to make concat yeld different results?
> In MS database there is *CONCAT_NULL_YIELDS_NULL* setting which does exacly
> what it states.
> Why there's no such functionality in SQLite?
>
> Moreover there also should be function called *concat(...)* which would
> take multiple arguments instead of using '||' operator.
SQLite is 'lite'. It's very small and contains only the bare minimum of
functions, all of which make sense and conform to the SQL standard (as best as
practical). If you keep adding functions only a minority of people want you
end up with something which isn't 'lite' any more.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
