[sqlite] Bug: WINDOW clause within a CTE
The following examples demonstrate a possible bug when using a WINDOW clause within a CTE: SQLite version 3.30.0 2019-09-14 16:44:51 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> CREATE TABLE x AS SELECT 1 a UNION SELECT 2; sqlite> sqlite> -- Unexpected result - expect 1,1 sqlite> WITH y AS ( ...> SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a)) ...> SELECT * FROM y; 1 2 sqlite> -- Unexpected result - expected "Error: no such column: fake_column" sqlite> WITH y AS ( ...> SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY fake_column)) ...> SELECT * FROM y; 1 2 sqlite> -- Possible unexpected result - expected "Error: no such column: fake_column" sqlite> SELECT 1 WINDOW win AS (PARTITION BY fake_column); 1 sqlite> -- Expected result sqlite> SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a); 1 1 sqlite> -- Expected result sqlite> WITH y AS (SELECT Row_Number() OVER (PARTITION BY a) FROM x) ...> SELECT * FROM y; 1 1 sqlite> -- Expected result sqlite> SELECT * FROM ( ...> SELECT Row_Number() OVER (win) FROM x WINDOW win AS (PARTITION BY a)); 1 1 sqlite> -Jake ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Comparison of incompatible types
https://www.sqlite.org/datatype3.html#comparison_expressions has the rules
for how values of different types are ordered and when type conversions
automatically happen.
Numbers are always less than strings, so in that example you should be
using round() instead of printf() as you discovered.
On Sat, Sep 14, 2019, 3:47 AM kapil wrote:
> Hi all,
>
> I was wondering whether sqlite does type checking for fields which are
> referenced in WHERE clause. Because when i tried to compare a field
> generated by printf function and tried to compare it with a float value,
> the comparison didn't work
>
> Eg.
> SELECT count.theme as theme, printf("%.2f",
> (count.num_sets*100.00/sum.total)) as percentage
>
> FROM count,sum
>
> WHERE percentage >= 5.00;
>
> It was not giving expected results.
> When i replaced printf with ROUND function, it worked.
> So i got to think whether this was because printf returns string value.
>
> If so then shouldn't there be type check on fields in comparisons so
> that user can get a meaningful error message or in this case, any error
> message at all
>
> Regards
> Kapil Garg
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Comparison of incompatible types
Hi all,
I was wondering whether sqlite does type checking for fields which are
referenced in WHERE clause. Because when i tried to compare a field
generated by printf function and tried to compare it with a float value,
the comparison didn't work
Eg.
SELECT count.theme as theme, printf("%.2f",
(count.num_sets*100.00/sum.total)) as percentage
FROM count,sum
WHERE percentage >= 5.00;
It was not giving expected results.
When i replaced printf with ROUND function, it worked.
So i got to think whether this was because printf returns string value.
If so then shouldn't there be type check on fields in comparisons so
that user can get a meaningful error message or in this case, any error
message at all
Regards
Kapil Garg
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite-tools-win64-x64-3290000.zip missing
Dear Woman and Man! sqlite-tools-win64-x64-329.zip are missing. How to solve this problem? With kind Greetings! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
