A small correction to the code above... The request has more to do with the bindParam and bindValue methods. If you use PDO::query like I do above, numbers 123 and 123.45 actually DO get stored in the native data type (INT and REAL in this case), but when retrieved again, they are converted into strings. I've confirmed this using a HEX editor by viewing the database file.

So that's two separate issues there. First, is that if you use bindParam and bindValue to insert data, that data will always be converted into text prior to being sent to the database. Second, if you DON'T use bindParam or bindValue and instead use the traditional method of manually escaping data and concatenating it directly with the SQL query, in this case the values will be stored using native data types, but will not be retrieved as such when a select is performed.

Switching this bug from "Feature/Change Request" to "SQLite related" issues. This seems to be a really big oversight on the developer's part since it eliminates all data-type support from SQLite.

Looks like it's back in "Feature/Change Request" section... Fine, though I don't think supporting proper data-types is an extra "feature".

Ok moving on, I figured out a way to get passed both problems, but it basically eliminates all advantages of PDO, and actually makes things a lot worse. As stated above, if you manually escape the data and don't use variable binding, the data is inserted as proper data types.

When performing a select on that data, PDO_SQLITE will always convert it into a string, however, PDOStatement::getColumnMeta does return the correct native_type field (null, double, string, or integer). This means that if you want data types to be preserved when selecting, you need to loop over every row using PDOStatement::fetch(), use the getColumnMeta for every column in that row, do the conversion, and store the result in a PHP array (2-dimensional). As you can imagine, that is one slow process.

That sounds like an absolutely terrible solution to me, which is why I again ask for at least some sort of a response from one of the developers on this issue (not that I don't enjoy talking to myself).

SQLite by its nature is a typeless database, adding virtual 
type support to it, is by its nature a feature.

Not exactly, it WAS typeless in version 2. As of version 3, it has well defined types as explained in the documentation: http://www.sqlite.org/datatype3.html

If I was to store the number 18446744073709551615, SQLite v3 would store it as an 8-byte integer, but since PHP forces a conversion to string, it would take up 20 bytes. I'm sure you can see the problem, especially when dealing with certain scientific applications which is what I'm trying to write and use SQLite with.

Considering that there is a perfect match between SQLite and PHP data-types, there is really no reason not to implement them...

NULL    -> null
INTEGER -> integer
REAL    -> float
TEXT    -> string
BLOB    -> binary string

After taking a look through the actual PDO_SQLITE code, this kind of change would take half an hour to implement, so I see no real argument against it.

Ok listen, until someone actually starts reading Feature/Change Requests, this is a PDO related issue. It's already been nearly 2 weeks, and I have yet to hear anything constructive from anyone. All I want is for someone to spend 5 minutes and tell me if there is even a chance it will be fixed in the next release.

If so, great, I'll shut up and won't bother you. If not, I have to go back and spend a ton of time writing wrappers for PDO that will emulate what should have been proper behavior.

Since this is the intended behavior, there is no bug and your report should be classified as "feature request".

>If not, I have to go back and spend a ton of time writing
> wrappers for PDO that will emulate what should have been
> proper behavior.
You also can spend some time and cook a patch, that adds the functionality you want, we would gladly review it.

Similar surprises for me.

Thoughts:

(1) Mapping INTEGER to int isn't necessarily ideal, because there's no certainty 
(AFAIK) that sqlite3's integer value will fit within the range offered by int. 
PHP already has a type-promotion paradigm for handling this (by converting to 
float, which supports a larger range of precise integer values), but I don't 
know what it looks like in PHP's source code.

(2) If PHP is converting sqlite3's numeric returns to strings, that means that 
users need to convert strings back to numeric types. That sounds error-prone and 
potentially lossy for both stages of that conversion, potentially resulting in 
data loss because of the unnecessary translation[1]. There might even be a 
vulnerability there, in the event large numbers are being stored, and those 
numbers are damaged during the translations.

(3) If the semantics of the sqlite PDO driver were to change to reflect 
sqlite3's semantics instead of sqlite2's, that would likely break a lot of 
existing code. It stands to reason you wouldn't want the new semantics to be the 
default, but you'd definitely want the old semantics to be deprecated. Perhaps 
in release $next, there's an attribute to switch which versions' semantics are 
used, defaulting to current (sqlite2's) behavior, but allowing sqlite3's to be 
selected. In release $next+1, default to sqlite3's, and deprecate sqlite2's. In 
$next+2, remove sqlite3's, deprecate the attribute. In $next+4's, remove the 
attribute.

As mkhitrov noted, at first glance, it looks faster to fix PHP's code-in-C[2] 
than fix one's own code-in-PHP. Realities of the situation, though, are that I'd 
likely end up waiting through a release or two before sqlite3 semantics are 
applied.

[1] And certainly incorrect behavior, if the user is led to expect sqlite3 
semantics; while I was researching to learn how sqlite behaved, I didn't even 
realize the sqlite2 semantics were in effect anywhere, leading me to write a 
fair amount of code assuming sqlite3 semantics. (I only discovered that the 
sqlite3 semantics weren't as expected while I was rechecking and building up my 
unit test suite for the app I'm on)

[2] I don't hack on PHP source, but I frequently have to refer to it to clarify 
behaviors not clear in the documentation. I can mostly follow what goes on in 
some places, but I haven't tried modifying or adding anything. I don't trust 
myself to write the patch, certainly not yet, and probably not on billable time.

Implemented in https://github.com/php/php-src/commit/438b025a28cda2935613af412fc13702883dd3a2.