SQLite Forum

Feature request: add index info to WITH clause table
# Short version

I'm with you on adding INDEXes to CTE but I hate your syntax proposal. :)

# Long version

## I feel your pain

Actually, I've written a lot of large queries where having such a feature would have been a time saver. It would have also prevented me from rewriting a "clean" CTE-query into a complex write-only equivalent (I was unable to understand it after a few hours even with extensive comments.)

## No prior art

There is no other SQL implementation that has such a feature (or at least no implementation that I am aware of.)

So the syntax has to be "invented". My reasoning is that it should also fit with the SQL way of expressing things: I mean it has to avoid mixing a heavy DDL syntax (CREATE INDEX) in a relatively pure query syntax (the CTE.)

## The proposal

My suggestion is twofold:

 * Extend [`table-contraint`](https://www.sqlite.org/syntax/table-constraint.html) with an INDEX branch. As a bonus, it will make creating INDEXes possible from inside CREATE TABLE. [Note that it is already possible to do so in MySQL](https://dev.mysql.com/doc/refman/8.0/en/create-table.html).   The suggested extension will reuse parts I highlighted in the current CREATE INDEX syntax:

     linerad = 10px
     linewid *= 0.5
     $h = 0.21

     circle radius 10%
A0:  arrow 2*arrowht
CR:  oval "CREATE" fit
UQ:  oval "UNIQUE" fit with .w at (linewid right of CR.e,.8*$h below CR)
IX:  oval "INDEX" fit with .w at (linewid right of UQ.e,CR)
     arrow from CR.e right even with UQ; arrow to IX.w
     arrow from CR.e right linerad then down even with UQ then to UQ.w
     line from UQ.e right linerad then up even with IX then to arrowht left of IX.w
     arrow from IX.e right
     oval "IF" fit
     arrow right 2*arrowht
     oval "NOT" fit
     arrow 2*arrowht
ETS: oval "EXISTS" fit

     # IF NOT EXISTS bypass
Y1:  .5*$h below UQ.s  # vertical position of back-arrow
     arrow from IX.e right linerad then down even with Y1  then left even with UQ
     arrow from ETS.e right then down even with Y1 then left even with ETS.w
     line left even with IX.w

     # second row
     arrow left even with first circle then down $h*1.25 then right 2*arrowht
SN:  oval "schema-name" fit
     arrow 2*arrowht
DOT: oval "." bold fit
     oval "index-name" fit
     arrow 2*arrowht
     oval "ON" fit
     arrow 2*arrowht
     oval "table-name" fit
     arrow 2*arrowht

LP:  oval "(" bold fill red fit
IXC: box "indexed-column" fill red fit
RP:  oval ")"  bold fit    fill red     

     # Loop over indexed-column
CMA: oval "," bold fit at $h*1.25 below IXC fill red
     arrow from IXC.e right linerad then down even with CMA then to CMA.e
     line from CMA.w left even with 2*arrowht left of IXC.w \
       then up even with IXC then right linerad

     # schema-name bypass
     arrow from (first circle,SN.n) down even with $h below SN \
       then right even with SN
     line right even with arrowht right of DOT.e then up even with DOT \
        then right linerad

     # WHERE clause and end circle
WH:  oval "WHERE" fit with .w at IXC.w+(linewid,-$h*3.5) fill red
     arrow right 2*arrowht
WEX: box "expr" fit fill red
     arrow right even with linewid*1.5 right of RP.e
     circle same fill red

     # Linkage from the last ")" to the WHERE clause or or circle
     arrow from RP.e right linerad then down even with CMA
J1:  previous.end
     line down even with last circle then to arrowht left of last circle.w
     arrow from J1 down even with $h below CMA then left even with CMA
     arrow left even with 2*arrowht left of WH.w then down even with WH \
       then to WH.w

 * Extend CTE declaration to allow `table-contraint`s to be used inside the column names declaration (just as what one can already do in CREATE TABLE).

## Your example using my suggested syntax

LookupTable( TimeStamp, LookupContent, PRIMARY KEY( TimeStamp ) ) as MATERIALIZE (
   select substr(Content,1,24)        as TimeStamp
        , trim(substr(Content,26,40)) as LookupContent 
     from VirtualLookupTextTable
    where instr(Content,'keyword')
select FromTimeStamp, ToTimeStamp, M.Content, L.Content
  from VirtualMainTextTable   M
  left outer join LookupTable L
               on M.ToTimeStamp>M.FromTimeStamp and L.TimeStamp > M.FromTimeStamp and L.TimeStamp <= M.ToTimeStamp
                                                 or L.TimeStamp == M.ToTimeStamp

## Another example using INDEX inside the columns definition of the CTE

LookupTable( a, b, INDEX (a, b), INDEX (b) WHERE a < 10 ) as MATERIALIZE (...)