Source code tarball as of 2024-05-07 17:58Z

For the JS/WASM users, there's a similarly-fresh snapshot of the various JS/WASM builds at wasm:/uv/snapshot.html.

I use Windows 11. I've never tried to compile from code tarball but thought I'd try.

I have SQLite version 3.46.0 2024-05-07 17:58:07 (UTF-16 console I/O) running.

However, I had the following messages:

sqlite3.c(145234): warning C4456: declaration of 'm' hides previous local declaration
sqlite3.c(145189): note: see declaration of 'm'
Generating Code...
LINK : sqlite3.exe not found or not built by the last incremental link; performing full link

I've no idea about implications.

I've no idea about implications.

It's not serious but will be resolved before the release. Thank you for the report.

New tarball uploaded. They hyperlink on my original post has been updated. Please try again and report back success or failure. Thanks.

This was not a bug. It is just a case of the Microsoft compiler being unnecessarily pedantic. Fixed by Stephan's check-in.

This is exactly the kind of annoying little issue that we hope to find with a beta-test like this. So, thank you for going to the trouble of testing!

Should the issue described in this post be working correctly in the 3.46.0 Fiddle?

It was stated it was fixed in src:9ea6bcc8.

I may likely be doing something wrong, but it appears to be as before.

Thank you.

Perhaps I'm missing the point, but I don't see a problem in the current behavior. Can you be more specific about what you think it is doing wrong? The OP gives example and writes as if the problem is self-evident, but it is not evident to me. What do you think specifically that the query is doing wrong? What should the answer actually be, in your opinion, and why?

Please try again and report back success or failure.

I repeated my attempt (this time writing my own notes for posterity).

I still have this message:

LINK : sqlite3.exe not found or not built by the last incremental link; performing full link

but as you've stated that this is not a bug.

1. This time around, the compilation is appreciably slower.
2. I was taken aback for a good few seconds with this:

SQLite version 3.46.0 2024-05-08 17:57:45 (UTF-16 console I/O)

`

It appeared to me that the clock was running backward.

... and then I took note of the date!

Oh, I think I see the problem now. You are concerned that you get different answers depending on whether or not MATERIALIZED or NOT MATERIALIZED is used?

That is not a bug, nor something that needs or ought to be fixed. I have now enhanced the RETURNING documentation in an effort to clarify why. See especially the last two paragraphs of section 2.1. Please review the new documentation and let me know whether or not you think that clears the problem up.

It was at Post 6 that the question was asked, "What happened to the 2?", meaning why was the minimum 3 rather than 2. Posts 8 and 10 elaborated on that with a number of examples, to which Stephan replied in Post 13 that the issue was resolved.

It appeared that the subquery in the returning clause should have had a minimum of 2 and been the same whether from an insert or delete. That's the way I took the closing statement of Post 8:

To my mind the DELETE result is unexpected (i.e. wrong). Do the developers or anyone else have an explaination?

and since Stephan's reply was that it was resolved by that check-in, I expected the results to match now in the Fiddle.

Those examples in Posts 6, 8, and 10 (I see now) differ from the tests in the check-in because the subquery in the returning clause of the examples is not using the table from which the rows were deleted.

More specifically, from Post 6, I was expecting the minimum to be 2 in all rows because the deletions are performed upon table "test" and not "del_id" used in the returning clause.

Whether or not that is correct, I won't pretend to know.

with
del_ids as 
(
 select
    id
 from
    test
 where
    id between 2 and 4
)
delete from test where id in (select id from del_ids)
returning
    id, 
   (select min(id) from del_ids) as min,
   (select max(id) from del_ids) as max;

The result is

┌────┬─────┬─────┐
│ id │ min │ max │
├────┼─────┼─────┤
│ 2  │ 3   │ 4   │
│ 3  │ 3   │ 4   │
│ 4  │ 3   │ 4   │
└────┴─────┴─────┘

I have further revised the documentation to include a new subsection that describes why the current behavior is correct. The text is unchanged from what I added earlier today; the only difference is the new subsection header.

The pedanticness is the compiler moaning about variable name shadowing. There is nothing whatsoever wrong with that. C scoping rules (as in most languages since Algol) are designed to make shadowing a non-problem.

That linker message does not indicate an error. It is purely informative.

Hence if column of RETURNING output contains a subquery that references the table being modified, ...

I think I understand now. Even though the subquery in the RETURNING output in Post 6 doesn't reference the table being modified (test), the fact that it references a table (del_ids) in the WITH that is built from table "test" (and it is not materialized) the 2 is not guaranteed to be returned as the minimum. CTE table "del_ids" is sort of recalculated after the 2 has been deleted from table "test".

Thanks for putting up with my questions. I think I need to check my returning clauses in CTEs to make sure I didn't do this and have been getting away with it for awhile.

Not a bug, but I'm seizing on the opportunity to make a suggestion before the API freezes. Add to json_pretty() a second optional argument: maximum line-length. Lines longer than this get folded with appropriate indentation. I feel that this optional argument should actually precede the indentation string, and would be more used, but that's just personal preference.

Back when I was using pretty printing for LISP, this was greeted with much joy, and removed the need for equivalent code in a lot of programs.

Unfortunately, the programming for this is non-trivial, and has to make assumptions about whitespace and that the length of the indentation string isn't longer than the line-length. I quite understand if the team feels it's not worth the trouble.

I compiled the source in windows 7 simplifed Chinese x64 version. it failed as following

D:\sqlite46>nmake /f Makefile.msc

Microsoft (R) 程序维护实用工具 14.10.25017.0 版
版权所有 (C) Microsoft Corporation。  保留所有权利。


D:\sqlite46>(echo VERSION = ^#define SQLITE_VERSION        "3.46.0"  1>>rcver.vc )
        cl -nologo -W4 -DINCLUDE_MSVC_H=1   -DSQLITE_OS_WIN=1 -I. -I. -fp:precise -MT -DNDEBUG -D_CRT_SECURE_NO_DEPRECAT
E -D_CRT_SECURE_NO_WARNINGS -D_CRT_NONSTDC_NO_DEPRECATE -D_CRT_NONSTDC_NO_WARNINGS -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD
_OVERRIDE_LOCK=-1 -DSQLITE_TEMP_STORE=1  -DSQLITE_MAX_TRIGGER_DEPTH=100  -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_FTS5=1 -
DSQLITE_ENABLE_RTREE=1 -DSQLITE_ENABLE_GEOPOLY=1 -DSQLITE_ENABLE_STMTVTAB=1 -DSQLITE_ENABLE_DBPAGE_VTAB=1 -DSQLITE_ENABL
E_DBSTAT_VTAB=1 -DSQLITE_ENABLE_BYTECODE_VTAB=1 -DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_ENABLE_MATH_FUNCTIONS   -O2 -
Zi -Fosqlite3.lo -Fdsqlite3.pdb  -c sqlite3.c
sqlite3.c
        echo #ifndef SQLITE_RESOURCE_VERSION > sqlite3rc.h
        echo #define SQLITE_RESOURCE_VERSION          3,46,0 >> sqlite3rc.h
        echo #endif >> sqlite3rc.h
        rc -DSQLITE_OS_WIN=1 -I. -I.   -DNDEBUG -D_CRT_SECURE_NO_DEPRECATE -D_CRT_SECURE_NO_WARNINGS -D_CRT_NONSTDC_NO_D
EPRECATE -D_CRT_NONSTDC_NO_WARNINGS -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_TEMP_STORE=1  -DSQLI
TE_MAX_TRIGGER_DEPTH=100  -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_FTS5=1 -DSQLITE_ENABLE_RTREE=1 -DSQLITE_ENABLE_GEOPOLY=
1 -DSQLITE_ENABLE_STMTVTAB=1 -DSQLITE_ENABLE_DBPAGE_VTAB=1 -DSQLITE_ENABLE_DBSTAT_VTAB=1 -DSQLITE_ENABLE_BYTECODE_VTAB=1
 -DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_ENABLE_MATH_FUNCTIONS   -r -fo sqlite3res.lo -DRC_VERONLY .\sqlite3.rc
Microsoft (R) Windows (R) Resource Compiler Version 10.0.10011.16384
Copyright (C) Microsoft Corporation.  All rights reserved.

        csc.exe /target:exe .\Replace.cs
Microsoft(R) Visual C# 编译器 版本 2.0.0.61501
版权所有(C) Microsoft Corporation。保留所有权利。

        echo EXPORTS > sqlite3.def
        dumpbin /all sqlite3.lo  | .\Replace.exe "^\s+/EXPORT:_?(sqlite3(?:session|changeset|changegroup|rebaser|rbu)?_[
^@,]*)(?:@\d+|,DATA)?$" $1 true  | sort >> sqlite3.def
        link.exe /DEBUG  /NOLOGO /MACHINE:x64  /DLL /DEF:sqlite3.def /OUT:sqlite3.dll sqlite3.lo sqlite3res.lo
  正在创建库 sqlite3.lib 和对象 sqlite3.exp
        cl -nologo -W4 -DINCLUDE_MSVC_H=1   -DSQLITE_OS_WIN=1 -I. -I. -fp:precise -MT -DNDEBUG -D_CRT_SECURE_NO_DEPRECAT
E -D_CRT_SECURE_NO_WARNINGS -D_CRT_NONSTDC_NO_DEPRECATE -D_CRT_NONSTDC_NO_WARNINGS -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD
_OVERRIDE_LOCK=-1 -DSQLITE_TEMP_STORE=1  -DSQLITE_MAX_TRIGGER_DEPTH=100  -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_FTS5=1 -
DSQLITE_ENABLE_RTREE=1 -DSQLITE_ENABLE_GEOPOLY=1 -DSQLITE_ENABLE_STMTVTAB=1 -DSQLITE_ENABLE_DBPAGE_VTAB=1 -DSQLITE_ENABL
E_DBSTAT_VTAB=1 -DSQLITE_ENABLE_BYTECODE_VTAB=1 -DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_ENABLE_MATH_FUNCTIONS   -O2 -
Zi -Fesqlite3.exe  -DSQLITE_DQS=0 -DSQLITE_ENABLE_FTS4=1 -DSQLITE_ENABLE_EXPLAIN_COMMENTS=1 -DSQLITE_ENABLE_OFFSET_SQL_F
UNC=1 -DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION=1 -DSQLITE_ENABLE_STMT_SCANSTATUS=1 -DSQLITE_STRICT_SUBTYPE=1 -DHAVE_READLINE
=0 shell.c sqlite3.c  /link /pdb:sqlite3sh.pdb /DEBUG  /NOLOGO /MACHINE:x64   sqlite3res.lo
shell.c
shell.c: warning C4819: 该文件包含不能在当前代码页(936)中表示的字符。请将该文件保存为 Unicode 格式以防止数据丢失
shell.c(27268): warning C4819: 该文件包含不能在当前代码页(936)中表示的字符。请将该文件保存为 Unicode 格式以防止数据丢失
shell.c(27684): error C2001: 常量中有换行符
shell.c(27685): error C2143: 语法错误: 缺少“;”(在“const”的前面)
shell.c(27699): error C2065: “zBom”: 未声明的标识符
shell.c(27699): warning C4047: “=”:“int”与“const char *”的间接级别不同
shell.c(27766): error C2065: “zBom”: 未声明的标识符
shell.c(27766): warning C4047: “函数”:“const char *”与“int”的间接级别不同
shell.c(27766): warning C4024: “oPutsUtf8”: 形参和实参 1 的类型不同
shell.c(27779): error C2065: “zBom”: 未声明的标识符
shell.c(27779): warning C4047: “函数”:“const char *”与“int”的间接级别不同
shell.c(27779): warning C4024: “oPutsUtf8”: 形参和实参 1 的类型不同
sqlite3.c
正在生成代码...
NMAKE : fatal error U1077: “D:\VS2017\VC\Tools\MSVC\14.10.25017\bin\HostX64\x64\cl.EXE”: 返回代码“0x2”
Stop.

after I convert shell.c from utf-8 to unicode, it works, but still has a warning.

D:\sqlite46>nmake /f Makefile.msc

Microsoft (R) 程序维护实用工具 14.10.25017.0 版
版权所有 (C) Microsoft Corporation。  保留所有权利。


D:\sqlite46>(echo VERSION = ^#define SQLITE_VERSION        "3.46.0"  1>>rcver.vc )
        echo #ifndef SQLITE_RESOURCE_VERSION > sqlite3rc.h
        echo #define SQLITE_RESOURCE_VERSION          3,46,0 >> sqlite3rc.h
        echo #endif >> sqlite3rc.h
        rc -DSQLITE_OS_WIN=1 -I. -I.   -DNDEBUG -D_CRT_SECURE_NO_DEPRECATE -D_CRT_SECURE_NO_WARNINGS -D_CRT_NONSTDC_NO_D
EPRECATE -D_CRT_NONSTDC_NO_WARNINGS -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_TEMP_STORE=1  -DSQLI
TE_MAX_TRIGGER_DEPTH=100  -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_FTS5=1 -DSQLITE_ENABLE_RTREE=1 -DSQLITE_ENABLE_GEOPOLY=
1 -DSQLITE_ENABLE_STMTVTAB=1 -DSQLITE_ENABLE_DBPAGE_VTAB=1 -DSQLITE_ENABLE_DBSTAT_VTAB=1 -DSQLITE_ENABLE_BYTECODE_VTAB=1
 -DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_ENABLE_MATH_FUNCTIONS   -r -fo sqlite3res.lo -DRC_VERONLY .\sqlite3.rc
Microsoft (R) Windows (R) Resource Compiler Version 10.0.10011.16384
Copyright (C) Microsoft Corporation.  All rights reserved.

        link.exe /DEBUG  /NOLOGO /MACHINE:x64  /DLL /DEF:sqlite3.def /OUT:sqlite3.dll sqlite3.lo sqlite3res.lo
        cl -nologo -W4 -DINCLUDE_MSVC_H=1   -DSQLITE_OS_WIN=1 -I. -I. -fp:precise -MT -DNDEBUG -D_CRT_SECURE_NO_DEPRECAT
E -D_CRT_SECURE_NO_WARNINGS -D_CRT_NONSTDC_NO_DEPRECATE -D_CRT_NONSTDC_NO_WARNINGS -DSQLITE_THREADSAFE=1 -DSQLITE_THREAD
_OVERRIDE_LOCK=-1 -DSQLITE_TEMP_STORE=1  -DSQLITE_MAX_TRIGGER_DEPTH=100  -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_FTS5=1 -
DSQLITE_ENABLE_RTREE=1 -DSQLITE_ENABLE_GEOPOLY=1 -DSQLITE_ENABLE_STMTVTAB=1 -DSQLITE_ENABLE_DBPAGE_VTAB=1 -DSQLITE_ENABL
E_DBSTAT_VTAB=1 -DSQLITE_ENABLE_BYTECODE_VTAB=1 -DSQLITE_ENABLE_COLUMN_METADATA=1 -DSQLITE_ENABLE_MATH_FUNCTIONS   -O2 -
Zi -Fesqlite3.exe  -DSQLITE_DQS=0 -DSQLITE_ENABLE_FTS4=1 -DSQLITE_ENABLE_EXPLAIN_COMMENTS=1 -DSQLITE_ENABLE_OFFSET_SQL_F
UNC=1 -DSQLITE_ENABLE_UNKNOWN_SQL_FUNCTION=1 -DSQLITE_ENABLE_STMT_SCANSTATUS=1 -DSQLITE_STRICT_SUBTYPE=1 -DHAVE_READLINE
=0 shell.c sqlite3.c  /link /pdb:sqlite3sh.pdb /DEBUG  /NOLOGO /MACHINE:x64   sqlite3res.lo
shell.c
shell.c(27684): warning C4566: 由通用字符名称“\uFEFF”表示的字符不能在当前代码页(936)中表示出来
sqlite3.c
正在生成代码...
LINK : 没有找到 sqlite3.exe 或上一个增量链接没有生成它；正在执行完全链接

D:\sqlite46>sqlite3
SQLite version 3.46.0 2024-05-08 17:57:45 (UTF-16 console I/O)
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select '字符'a;
字符

Google says

shell.c(27684): warning C4566: 由通用字符名称“uFEFF”表示的字符不能在当前代码页(936)中表示出来

means

shell.c(27684): warning C4566: The character represented by the universal character name "\uFEFF" cannot be represented in the current code page (936)

My region & code page were:

SQLite version 3.46.0 2024-05-08 17:57:45 (UTF-16 console I/O)
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .shell chcp
Active code page: 65001
sqlite> .shell PowerShell Get-WinHomeLocation

GeoId HomeLocation
----- ------------
  242 United Kingdom

The only change my test suite has detected is when my callback for creating a virtual table is called and it returns SQLITE_ERROR, then SQLite is now returning SQLITE_MISUSE. Before it returned SQLITE_ERROR.

Thanks for reporting this. Haven't been able to reproduce so far though.

Does the xCreate() method do anything special before it returns SQLITE_ERROR?

Am I able somehow to just run your test to see what is happening?

Dan.

Apologies for not spotting this reply.

SQLITE_MISUSE is coming from sqlite3_declare_vtab.

The zSQL parameter is this isn't remotely valid sql. Before that gave SQL_ERROR and now it gives SQLITE_MISUSE. It is testing code to verify error handling hence the parameter.

The error message is not set, so all I get back is not an error.

Already fixed on trunk

https://sqlite.org/src/info/de8182cf1773ac0d