SQLite Operation Result type

I understand the concept of type affinity and have read the "Datatypes In SQLite Version 3". What I don't understand is how two fields, declared as NUM (tried float开发者_开发百科, double etc) in a CREATE statement and multiplied with each other will generate a result that is type NULL. Is there a way to CAST or coerce or bribe the SQLite to produce a NUM when multyplying two declared NUMs, i.e: NUM * NUM = NUM ?

For example:

CREATE TABLE A (id varchar(3) primary key not null, x real not null, y real not null)
CREATE TABLE B AS SELECT x * y as z from A.

The corresponding statement is

CREATE TABLE B(
  id TEXT,
  x REAL,
  y REAL,
  z,

Any way I can get a REAL next to the z above?

You need to cast the result:

CREATE TABLE B AS SELECT CAST(x * y AS REAL) as z from A.