Pivot column id uniqueness requirement isn't based on SQLite collation and is hard to understand

[saaj]

Steps to reproduce

Dockerfile

FROM ubuntu:focal

ENV DEBIAN_FRONTEND=noninteractive
WORKDIR /tmp/build

RUN set -ex; \
    apt-get update > /dev/null;  \
    apt-get install -y --no-install-recommends \
        sqlite3 libsqlite3-dev gcc wget ca-certificates > /dev/null; \
    wget -q https://github.com/jakethaw/pivot_vtab/raw/6186c43/pivot_vtab.c; \
    gcc -g -O3 -fPIC -shared pivot_vtab.c -o pivot_vtab.so; 

COPY script.sql .

RUN cat script.sql | sqlite3

script.sql

.load ./pivot_vtab
.headers on
.mode column

CREATE TABLE t1 AS
WITH t(r, c, v) AS (
  SELECT *
  FROM (
    VALUES 
      ('a a', 'x x', 1),
      ('c c', 'y y', 2),
      ('b b', 'x x', 3),
      ('a a', 'y y', 4),
      ('c c', 'z z', 5)
  )
)
SELECT * FROM t;

CREATE VIRTUAL TABLE pivot USING pivot_vtab(
  (SELECT r FROM t1 GROUP BY r),
  (SELECT c, c FROM t1 GROUP BY c),   
  (SELECT v FROM t1 WHERE r = ?1 AND c = ?2)
);

SELECT * FROM pivot;

Output of docker build -t pivot_vtab/issue . is as expected:

r           x x         y y         z z       
----------  ----------  ----------  ----------
a a         1           4                     
b b         3                                 
c c                     2           5    

If I change t1 in the script like this:

    VALUES 
      ('a a', 'x X', 1),
      ('c c', 'y y', 2),
      ('b b', 'x x', 3),
      ('a a', 'y y', 4),
      ('c c', 'z z', 5)

It fails with:

Error: near line 19: vtable constructor failed: pivot

Expected behaviour

If the input data (provided by the 3 queries) doesn't fit the requirement, there's an error message that explains it.

Actual behaviour

There's non-informative "vtable constructor failed" error message.

Additional information

The behaviour is the same with BINARY collation. Though the comparison is false by default anyway.

sqlite> SELECT 'x x' = 'x X'
'x x' = 'x X'
-------------
0   

(SELECT id c_id, name FROM c), -- Pivot table column definition query

This may indicate that the extension only expects a sort of star schema, but it would exclude many cases were pivot table is desired. I've answer a couple of SQLite pivot question to estimate that (one, two).