search

jakethaw / pivot_vtab

SQLite3 pivot virtual table
MIT License
29 stars 1 forks source link

Broken vtable 2nd and 3rd queries are silently ignored #3

Closed saaj closed 3 years ago

saaj commented 3 years ago

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 -bail

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),
  (FOO BAR BAZ),   
  (FOO BAR BAZ)
);

SELECT * FROM pivot;

Expected result

Error message explaining broken 2nd and 3d pivot queries.

Actual result

r         
----------
a a       
b b       
c c