[sqlite] constraint failed message and no clue what went wrong

[Bernd Lehmkuhl]

> Dominique Devienne <ddevienne at gmail.com> hat am 23. Mai 2016 um 13:42 
> geschrieben:
> 
> 
> On Mon, May 23, 2016 at 12:22 PM, Bernd Lehmkuhl <bernd.lehmkuhl at 
> mailbox.org
> > wrote:
> >
> > > Dominique Devienne <ddevienne at gmail.com> hat am 23. Mai 2016 um 11:20
> > geschrieben:
> > > On Mon, May 23, 2016 at 10:39 AM, Bernd Lehmkuhl <
> > bernd.lehmkuhl at mailbox.org
> > > > [...] What might cause a "constraint failed" message following
> > this command: [...]
> > >
> > > Which version of SQLite? More recent ones tell you which constraint
> > failed,
> > > when they are named, which yours are (a good thing IMHO).
> >
> > Most recent one - 3.12.2. Unfortunately without any name.
> > [...]
> 
> 
> /*** t_geometrie_index ***/
> CREATE VIRTUAL TABLE t_geometrie_index USING rtree(
>   id,
> [...]
> 
> sqlite> INSERT INTO t_geometrie_index (id, xmin, xmax, ymin, ymax)
> >    ...> SELECT
> >    ...>   t.auto_id, [...]
> 
>    ...> FROM
> >    ...>   (
> >    ...>     SELECT
> >    ...>       k.id, [...]
> >    ...>     FROM
> >    ...>       t_geometrie_knoten k
> >    ...>
> >    ...>     UNION ALL <<<<<<
> >    ...>
> >    ...>     SELECT
> >    ...>       p.id, [...]
> >    ...>     FROM
> >    ...>       t_geometrie_punkte p
> >    ...>     GROUP BY
> >    ...>       p.id
> >    ...>   ) sub JOIN t_geometrie_typ t
> >    ...>         ON   sub.id = t.id;
> > Error: constraint failed
> > sqlite> rollback;
> > sqlite> .quit
> >
> 
> OK, was worth a shot. I had a feeling it might be related to the RTREE
> vtables.
> 
> Never used RTREE myself, in SQLite, although I know what this is.
> Could it be your you "knoten" and "punkte" tables have values with the same
> IDs?

Gotcha! Thanks. Even though I claimed having checked that auto_id is unique in 
that query, you just proofed me wrong: 

SELECT 
  auto_id, 
  COUNT(*) 
FROM 
  ( 
  SELECT
    t.auto_id,
    sub.xmin,
    sub.xmax,
    sub.ymin,
    sub.ymax
  FROM
    (
      SELECT
      k.id,
      k.x AS xmin,
      k.x AS xmax,
      k.y AS ymin,
      k.y AS ymax
    FROM
      t_geometrie_knoten k

    UNION ALL

    SELECT
      p.id,
      MIN(p.x),
      MAX(p.x),
      MIN(p.y),
      MAX(p.y)
    FROM
      t_geometrie_punkte p
    GROUP BY
      p.id
    ) sub JOIN t_geometrie_typ t
          ON   sub.id = t.id 
) 
GROUP BY 
  auto_id 
HAVING 
  COUNT(*) > 1 

gives me one auto_id with three occurrences. I should check my data more 
thoroughly, I guess.
Thanks for saving my day :-)