[GRASS-dev] Re: new v.in.geonames: problems with UTF-8 Unicode text

[Glynn Clements]

Ivan Shmakov wrote:

>  > IIRC, the main problem is that sqlite3_column_decltype() only works
>  > for actual columns, not expressions, subselects etc, but the code
>  > in question has to be able to describe the format of rows returned
>  > by arbitrary SELECT statements, not just tables.
> 
>  > So it falls back to sqlite3_column_type(), which returns the type
>  > of the column's data. but that only works if you have a valid
>  > row. If a select doesn't return any rows, you lose. Also,
>  > sqlite3_column_type() only understands null, integer, float, text
>  > and blob types.
> 
> 	I wonder, how it's done for other RDBMS?  E. g., would there be
> 	a table, like:
> 
> CREATE TABLE foo (foo NUMERIC, bar TEXT);
> 
> 	What would be the type of the only column of the query like:
> 
> SELECT foo || bar FROM foo;

Operators such as || which operate on strings normally accept any
operand types. Anything that isn't a string will be converted to one. 
But the result is always a string.

Typically, an operator is selected according to its operand types, and
the operator's result type determines the type of the result column. 
E.g. suppose that you have:

	SELECT a + b FROM foo;

If a and/or b are REAL then the operator would be REAL * REAL -> REAL
and the result would be REAL. If a and b were both INT then the
operator would be INT * INT -> INT and the result would be INT.

>  > Apart from needing to fall-back to the data type for expressions,
>  > another problem is that it coerces the decltype to one of SQLite's
>  > limited set of types, then converts that to one of the DB_SQL_*
>  > types, losing information in the process.
> 
> 	That doesn't feel sound.
> 
>  > However, I don't know if it does this for a specific reason. I
>  > suspect the only way to find out is to try it and see what breaks.

One issue is that SQLite doesn't validate the column types. It uses
some heuristics to determine the affinity type, but ultimately it
allows any sequence of words along with an optional size specifier,
e.g.:

	CREATE TABLE foo ( i INT, t TEXT, b ROAST BEEF(99) );

-- 
Glynn Clements <glynn at gclements.plus.com>