I checked it by running the queries. Both the standard SUBSTRING (s FROM start [FOR length]) and the variant SUBSTRING(s,start[,length]) work, and return identical rows. I would call it a documentation bug, or if you like, an undocumented feature :-) This is in 10.2, I don't know what 10S reports as a version string.

Sounds like git's core.autocrlf=input. Makes much more sense. I'm always getting tripped by this when using new Windows dev machine :/

Hence "self-inflicted" although it's worth pointing out the GitHub mirror I'm using is official.

I'm actually working on a sqlite extension in separate git repo but decided to setup sqlite dev environment to improve debugging experience and help me understand codebase better.

Please excuse this interjection if it is too blindingly obvious.

There is no Git repository for which the SQLite developers take responsibility. The official project repository page has a link to Fossil, the DSCM tool which is used for the project (and was developed for it.) I think that if you can trace your difficulty to something in the Git settings for that tracking project, its maintainers might be interested in fixing what they have done or in documenting the appropriate Git user settings for their repo.

You might also consider using Fossil for the SQLite project. You will spend less time trail-blazing that way, and you may well find Fossil a pleasure to use.

not sure what's the fossil behavior around EOL on Windows

Fossil doesn't have an "auto CRLF" setting: the data is the data.

Fossil will attempt to recognize files with CRLF line endings and offer to convert them to LF-only for you, but you can mark files as known to have CRLF line endings so Fossil won't bug you about them.

My obviously self-imposed problem can be fixed by disabling EOL conversions in git (not sure what's the fossil behavior around EOL on Windows but I guess it does work).

git config --get core.autocrlf

# fixup existing data
git rm --cached -r .
git reset
git checkout .

Hmm... could be related to my git settings:

the only one I have is:

core.autocrlf=true

I will try with different settings

Apologies for lack of detail. I'm unable to edit original message as I've posted anonymously (would be nice if Fossil left a cookie to let me do that or claim the post).

Environment

• Windows 10 20H2
• Active code page (chcp): 437
• magicsplat-tcl-tk 1.10.1.20201009
• git 2.29.2.2
• Visual Studio Community 2019 Version 16.8.2

Steps

# Developer Command Prompt for VS 2019
vsdevcmd
git clone https://github.com/sqlite/sqlite.git
cd sqlite
mkdir Debug
cd Debug
nmake /f ..\Makefile.msc TOP=..

At this point shell.c has mix of different EOL styles including a lot of the \r\r\n.

For actual debugging I'm importing project from existing code in VS under name 'sqlite3' and following build commands in sqlite3.vcxproj:

  <PropertyGroup Condition="'$(Configuration)|$(Platform)'=='Debug|Win32'">
    <NMakeFlags>/f ..\Makefile.msc TOP=.. DEBUG=3 USE_AMALGAMATION=0 DYNAMIC_SHELL=0</NMakeFlags>
    <NMakeBuildCommandLine>cd $(OutDir) &amp;&amp; nmake $(NMakeFlags)</NMakeBuildCommandLine>
    <NMakeCleanCommandLine>cd $(OutDir) &amp;&amp; nmake $(NMakeFlags) clean</NMakeCleanCommandLine>
    <NMakeReBuildCommandLine>cd $(OutDir) &amp;&amp; nmake $(NMakeFlags) clean core</NMakeReBuildCommandLine>
    <NMakePreprocessorDefinitions>WIN32;_DEBUG;SQLITE_ENABLE_DESERIALIZE;$(NMakePreprocessorDefinitions)</NMakePreprocessorDefinitions>
    <NMakeIncludeSearchPath>$(OutDir);$(NMakeIncludeSearchPath)</NMakeIncludeSearchPath>
  </PropertyGroup>

and then simple F5 does the trick

Some of the features described in section 4.2.7 of PostgreSQL documentation are implemented in SQLite, such as the FILTER clause; my request is to also implement the ORDER BY clause for aggregate functions too (this would be useful with the built-in group_concat function, as well as for many user-defined aggregate functions).

It will make no difference at all.

Transactions have to do all the same stuff regardless of whether they are started with BEGIN, BEGIN IMMEDIATE or BEGIN EXCLUSIVE. All that changes is exactly when they invoke the system calls to take the various required file locks.

Looking at the last few published versions, release and prerelease, I see that lines are bounded in shell.c as is normal for *Nix systems, with a single ASCII linefeed character. (or \n in your parlance)

Looking at the shell.c produced on my Windows machine using the Fossil repository for SQLite, I see that its lines are bounded by a CR LF sequence (\r\n) as is normal on the platform.

This makes me wonder what strange combination of repository movement and build platform has led to your strange line bounds. Perhaps you should say, just in case this is actually a fixable problem rather than something quirky and unlikely to repeat under normal conditions.

[Added via later edit: ]

Because, obviously, you are causing shell.c to be built, I think it behooves you, as a developer considerate of those who might deal with this issue, to: State what platform you are using to build shell.c and the amalgamation; State what platform the source repository was copied from if not created on the build platform; State what version and build of TCL you are using; and what, if any, editing you have done to the various sources that are combined to form shell.c

I suggest also that you see whether your problem is restricted to just portions of your shell.c, arising from use of your own editor, or have somehow affected all line endings in shell.c .

I think that, until these issues are resolved, it is quite premature to put the word "BUG" in the thread title. If I had done that, I would remove it via the forum's edit feature.

It looks like shell.c ends up with \r\r\n for line endings which breaks debugging (lines don't match).

Unfortunately I don't know enough about Tcl to fix mkshellc.tcl.

sed -e 's/\r\r/\r/' does the trick as a temporary workaround.

Collation sequences only apply to text. NULL is not valid text. A "null string" (that is, a string that does not contain anything) is actually a string and not NULL.

So it really matters what you mean by NULL.

A "null string" is a string (text) that does not contain anything. It is a string/text value, and that value is "empty" of content. Conversely NULL is a specific value that is of the NULL type. Collating sequences only apply to TEXT values.

Outstanding. Thank you.

Can I positively assert that no NULL values will ever be passed to the collation sequence?

Yes. See line 1003 of main.c

In the old Nabble archive post, DRH states that NULLS always sort first, which is not specified in the SQL92 standard, but is left up to implementations.

I'm not asking how to collate NULL values, I'm asking if there is an explicit guarantee in the API that SQLite will always take care of sorting the NULLS first, and that a collation sequence will not have make sure to do so. That certainly appears to be the case.