Hi all,

Consider the following query:

sqlite> select null or (null or 'xxx1');

According to the doc:

For example, the values NULL, 0.0, 0, 'english' and '0' are all considered to > be false. Values 1, 1.0, 0.1, -0.1 and '1english' are considered to be true.

For this case, 'xxx1' should be evaluated to 1 or True, and the whole query should return 1.

However, it returned the following:

sqlite> select null or (null or 'xxx1'); <NULL>

Similarly, we have:

sqlite> select 'xxx1' is 1; 0

Furthermore, I got:

sqlite> select '1english' is 1;
0

-- implicit conversion?
sqlite> select '1english' or null;
1

sqlite> select '1english' != 1;
1

sqlite> select '1english' = FALSE;
0

sqlite> select '1english' = TRUE;
0

sqlite>  select '1english' != TRUE;
1

-- ? what is it except for true and false?
sqlite>  select '1english' != FALSE;
1

-- correct
sqlite> select '1english' is TRUE;
1

-- correct
sqlite> select '1english' is FALSE;
0

This is inconsistent with the documentation.

First, for the documentation, it doesn't explicitly state the string should start with a number. It only gives the example which starts with a number. I think it could be adjusted to state the string must start with a number to be evaluated to true.

Secondly, it doesn't explain why '1english' is neither a FASLE or a TRUE. It is neither a NULL. I expected it should implicitly convert the string to a number/Boolean when I compare it to a number/Boolean.

Intuitively, when comparing with =, the engine should do the conversion itself.

If I do select '1xxx'=TRUE;, it is intuitively expected to convert the left hand side to a bool. Vice versa, it could also convert the right hand side to a string and compare. In the case of my examples, if it is meaningless to compare a string with a Boolean, it should raise a warning or forbid the user from using the comparison, which leads to never true result. Similarly, what is the meaning of = in '1xxx'=TRUE is not explained.

If it is not comparing the value, is it comparing the type? But because the result is always false, I couldn't tell whether I get a correct result.

Think I'm not arguing about the compare results or the hack to do the compare. I'm confused with the operator =. What is the meaning of = in SQLite?

What is the meaning of = in SQLite?

In SQL, = means the same as == does in most programming languages, and sqlite allows == as an alias for =. The IS keyword, however, works differently:

NULL == NULL // ==> NULL
NULL IS NULL // ==> 1 (true)

That's an SQL thing, not specific to sqlite.

In SQL, = means the same as == does in most programming languages

Not "most", not by a long shot. Off the top of my head, not Pascal, Fortran, Cobol, Lisp, or APL. Not to mention that most iconic of Unix languages, the Bourne shell.

You could be right about "most" languages consciously derived from C, but then that might be tautological: any language using "==" might, by that very choice, be consciously derived from C.

SQL was invented at IBM, and some of its conventions are traceable to IBM languages of the era. For example, the use of single quotes to denote string literals, and the practice of escaping an embedded quote by 'doubling ''em up'.

To convert the results of an SQL expression to a boolean value, SQLite first casts the result to a NUMERIC value in the same way as a CAST expression. A numeric zero value (integer value 0 or real value 0.0) is considered to be false. A NULL value is still NULL. All other values are considered true.

And the relevant CAST section:

When casting a TEXT value to INTEGER, the longest possible prefix of the value that can be interpreted as an integer number is extracted from the TEXT value and the remainder ignored. Any leading spaces in the TEXT value when converting from TEXT to INTEGER are ignored. If there is no prefix that can be interpreted as an integer number, the result of the conversion is 0.

So because ‘xxx1’ has no prefix that can be interpreted as an int, it converts to 0.

Thanks for the explanation. I'm more confused with the example queries when comparing a string to a Boolean.

Please don't compare a string to a boolean. It doesn't mean anything and anyone reading your code won't understand it. The fact you can do it at all is a nasty hack. Figure out what you really mean, and write code to do that.

In terms of what SQLite actually does, it tries to turn the string you give it into a number, working character by character from the left, and it stops when it reaches any character which can't be part of that number. So, starting with the value 0 ...

• '123' multiplies by 10 and adds 1, multiplies by 10 and adds 2, multiplies by 10 and adds 3.
• '1x' multiplies by 10 and adds 1, then stops because 'x' can't be part of a number
• 'x1' stops because 'x' can't be part of a number

Once SQLite has the number it then evaluates it as boolean, either FALSE or TRUE.

There is a difference between ISing and ==ing.

TRUE == 1 and FALSE == 0 but any number not 0 or NULL IS TRUE while 0 IS FALSE, and NULL IS neither TRUE nor FALSE but rather is NULL whether by ISing or ==ing.

SQLite version 3.32.0 2020-05-24 12:57:56
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select 5 == true;
0
sqlite> select 5 is true;
1

There is a big difference between BEING (IS) something and being EQUAL (==) to something.

You pointed out the thing I don't know how to describe.

If in the sense of = true meaning being true, I think '1xxx' is also 'being' true.

Perhaps you want "IS TRUE" instead of "==TRUE".

    SELECT '1xxx' IS TRUE, '1xxx' == TRUE;

Returns two columns with values 1 and 0.

Nod nod.

Then what is compared when using =? Because I cannot tell in the case of '1xxx'=True. Is it comparing for the value?

SQLite3 does not have a boolean type. It has NULL, BLOB, TEXT, INTEGER, and REAL (INTEGER and REAL can be called NUMERIC, which is a Schrodinger's type -- you don't know if it is INTEGER or REAL until after you have looked in the box).

For the purpose of comparison (greater than, less than, equals, not equals) or assignment, then TRUE is the INTEGER 1 and FALSE is the INTEGER 0.

For the purpose of IS (a test of being), IS TRUE means "not null and not zero" and IS FALSE means "not null and one of 0, 0.0 or -0.0".

TRUE and FALSE are "magical words" unless there is a column in one of the tables in the query named TRUE or FALSE, in which case it is that column and not the magical value.

Something which evaluates to a non-zero and non-null numeric value IS TRUE, and something which evaluates to a zero value IS FALSE. If you want to know if something IS TRUE or IS FALSE then it must be converted to numeric first -- you have no choice in the matter.

However testing whether something equals 1 or 0 does not require any conversions whatsoever unless the affinity conversion rules apply. And constants do not have affinity. A text constant is never equal to an integer constant.

Fully understand the context now! Thank all of you!

SQLite3 does not have a boolean type. It has NULL, BLOB, TEXT, INTEGER, and REAL

Hmm, is NULL a type? It's a value in a ternary logic system. It's a marker for missing information. But it's not type.

It's definitely not a value, despite the common use of "NULL value". It's a nonvalue, the absence of a value. It's a property of a place where a value would otherwise be. That's why columns are declared with a type and NULL or NOT NULL: two distinct attributes of the attribute.

You can't cast NULL to or from anything. You can try, and the syntax permits it. But nullity trumps all:

sqlite> select typeof(cast(NULL as int)) as hello;
hello     
----------
null      

SQLite's output here is shorthand, the product of inexpressibility. The first parameter to CAST is a value; because values may be missing, CAST accepts NULL in place of a value. (You cannot, for example, CAST(REAL as TEXT) because "REAL" is a type, and CAST wants a value.) The output of TYPEOF is a type, or would be, if type there was. But there is not; the missing value has no type.

What TYPEOF is really saying is that the output of CAST in this case, when combined with something else, will be treated as NULL. Forced by the syntax to provide a one-word answer, it couldn't (more honestly) say missing integer. But that's what it means.

NULL has a specific type in SQLite, that only has one value, which is NULL. When you ask for the type of a column, if it has the value NULL, it will have the type NULL.

This is obviously a semantics or usage argument, so there cannot be any obvious winner. However, you have overlooked James' point that nullness is orthogonal to type. We might (even in SQLite) speak of a column's type, where it would be useless and practically meaningless for that to be NULL, but its values in particular rows may be NULL or not, and when not they have a conventional type. It would be more accurate to say that possible nullity is a feature of all types (where not excluded by a column constraint) rather than a type by itself.

Remember, Columns in SQLite do NOT have a 'Type', but an affinity. Individual cells have any type. NULL is one of the types that a cell can have, which does generally imply omitted data, but it doesn't describe what type of data was omitted. There aren't separate values for omitted integer, omitted float, omitted test, and omitted blob data, just a single value of a special type call NULL.

Maybe in other SQL dialects, NULL can be thought of as a value orthogonal to the type (and it sort of needs to be since the column DOES have a type), but this is different in SQLite.

This is obviously a semantics or usage argument

Perhaps technically, but if typeof(NULL) is anything but NULL, then NULL is incorrectly implemented: null is the value that is equal to nothing else in SQL, not even to itself:

sqlite> select 1 where null = null;
sqlite> 

It would be more accurate to say that possible nullity is a feature of all types

If you cannot compare NULL to NULL, then there is no useful meaning to that wish:

sqlite> create table a(b int);
sqlite> insert into a values (1),(null);
sqlite> select rowid, b from a;
1|1
2|
sqlite> select rowid from a where b=null;
sqlite> select rowid from a where b IS null;
2

In other words, there is no row in a where b equals NULL. Thus the IS NULL SQL operator.

Here, b on row ID 2 is not an integer with value NULL, it simply IS NULL. It is not an integer at all. It is also not a TEXT or a Boolean, or a REAL, or...