[sqlite] Shadowing a table name with a common table expression

[William Chargin]

If we create a table and then declare a common table expression of the
same name, `SELECT`s seem to refer to the table, while `INSERT` targets
refer to the CTE. For example:

```
CREATE TABLE foo (x);
INSERT INTO foo SELECT 1;
WITH foo (x) AS (SELECT 10) INSERT INTO foo SELECT x + 1 FROM foo;
SELECT x FROM foo;
```

On my SQLite (tested version 3.11.0 and also v3.33.1 from Fossil head),
this prints 1 and 11. If the `foo` in `SELECT x + 1 FROM foo` had
referred to the underlying table, it would have printed 1 and 2 instead.

Clearly this is a bit confusing, and I should probably avoid doing it.
But is the behavior defined either by SQL or SQLite? I didn't see
anything relevant in .
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Non-keyword quoted identifiers parsed as string literals

[William Chargin]

> This is documented behaviour. Use single quotes for literal strings.
> SQLite will assume you meant 'literlal' if your write "literal" and
> there is no column of that name. There is no need to quote names in
> SQLite unless the name contains non-alpha characters.

Thanks, yes. I was quoting the names because they _did_ contain
non-alpha characters. The actual example was that I had a table

CREATE TABLE primitives_Commit(
id TEXT NOT NULL PRIMARY KEY,
oid,
author,
"author.date"
);

and another table with a column "author.user", and had mistakenly tried
to SELECT things FROM primitives_Commit WHERE "author.user" IS NOT NULL.

The reason for the funny names is that the table schema was dynamically
generated by an abstraction layer. I have since rewritten that layer
such that this is no longer necessary, and gotten rid of double quotes
entirely, which seems to be for the best. :-)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Non-keyword quoted identifiers parsed as string literals

[William Chargin]

Thank you both for your quick and helpful replies! The `quirks.html`
page certainly clears things up. Glad to see that there are new options
to disable this; I reached out to the maintainers of the language
bindings that I use to see if we can get that enabled [1].

[1]: https://github.com/JoshuaWise/better-sqlite3/issues/301

Best,
WC
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Non-keyword quoted identifiers parsed as string literals

[William Chargin]

I tracked down a perplexing issue to the following behavior:

sqlite> CREATE TABLE tab (col);
sqlite> SELECT nope FROM tab;  -- fails; good
Error: no such column: nope
sqlite> SELECT "nope" FROM tab;  -- works?
sqlite> INSERT INTO tab (col) VALUES (77);
sqlite> SELECT col FROM tab WHERE nope IS NOT NULL;  -- fails; good
Error: no such column: nope
sqlite> SELECT col FROM tab WHERE "nope" IS NOT NULL;  -- works?
77

It seems that "nope" is being interpreted as a string literal here,
while quoted names of valid columns are not:

sqlite> SELECT "nope", "col" FROM tab;
nope|77

I see that this is discussed briefly in the documentation, though the
exception as written only applies to quoted keywords, which "nope" is
not: 

But it seems especially surprising that the parse tree should depend on
the actual identifier values and table schemata, making the grammar not
context-free.

Is this working as intended? Are there plans to make SQLite reject such
examples as malformed queries instead of implicitly coercing?

My `sqlite3 --version`:

3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Glob documentation clarity

[William Chargin]

I think that you can infer it from the statement that "The GLOB operator
is similar to LIKE". The documentation for the "LIKE" operator notes
explicitly that the right-hand operand is the pattern (needle) and the
left-hand operand is the string to match against the pattern (haystack).

That said, it wouldn't hurt to reiterate this in the docs for "GLOB" and
the other functions/operators in this category.

Best,
WC
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] docs: application_id is signed, not unsigned

[William Chargin]

Update: drh fixed this in 3580ba4b5bd75ec6. Thanks!


Best,
WC
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] docs: application_id is signed, not unsigned

[William Chargin]

The docs for "PRAGMA application_id" read:

> The application_id PRAGMA is used to query or set the 32-bit unsigned
> big-endian "Application ID" integer located at [...].

However, it appears that the argument to this pragma is interpreted as a
_signed_ integer, not an unsigned integer. In particular, values outside
the ranged of a 32-bit signed integer are treated as zero, and negative
values within the range of a 32-bit signed integer are accepted but
interpreted as unsigned by file(1).

Meanwhile, magic.txt says that application_id _is_ a signed integer, and
pragma.c seems to imply it (it says that schema_version and user_version
are signed, and application_id goes through the same code path as far as
I can tell).

Is this an error in the documentation?

The following script reproduces the behavior:

#!/bin/sh
set -eu

printf '$ %s\n' 'sqlite3 --version'
sqlite3 --version

tmpdir="$(mktemp -d)"
tmpfile="${tmpdir}/test.db"

run() {
printf '\n'
rm -f "${tmpfile}"
printf 'application_id: %s\n' "$1"
printf 'SQLite says: '
printf '
PRAGMA application_id = 777;
PRAGMA application_id = %s;
PRAGMA application_id;
' "$1" | sqlite3 "${tmpfile}"
printf 'file(1) says: '
file -b "${tmpfile}"
printf 'raw bytes are: '
xxd -i -s 68 -l 4 <"${tmpfile}"
}

# Values between 0 and 2^31 - 1, inclusive, work fine.
run 0
run 3
run 2147483647

# Values larger than 2^31 - 1 are treated as zero.
run 2147483648
run 2147483649
run 4294967295
run 4294967296
run 4294967297

# Values between -2^31 and -1, inclusive, are treated inconsistently
# between SQLite and file(1).
run -2147483648
run -2147483647
run -3

# Values smaller than -2^31 are treated as zero.
run -2147483649
run -4294967295
run -4294967296

rm "${tmpfile}"
rmdir "${tmpdir}"

(End of script.)

A copy of this script and its output on my machine are available at:

https://gist.github.com/wchargin/ac2a339f002a9604018bbc3b3be59ffb

I have the following version of sqlite3...

3.11.0 2016-02-15 17:29:24 3d862f207e3adc00f78066799ac5a8c282430a5f

...running on GNU/Linux (Linux Mint 18.2).

I have searched the issue tracker and found no tickets matching
"application_id". I see one mailing list thread from 2014-02-06 that
mentions this in the context of another question, with no replies.

Best,
WC
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users