Issue 21718: sqlite3 cursor.description seems to rely on incomplete statement parsing for detection

Per DBAPI and pysqlite docs, .description must be available for any SELECT statement regardless of whether or not rows are returned.  However, this fails for SELECT statements that aren't simple "SELECT"s, such as those that use CTEs and therefore start out with "WITH:":

import sqlite3
conn = sqlite3.connect(":memory:")

cursor = conn.cursor()
cursor.execute("""
    create table foo (id integer primary key, data varchar(20))
""")

cursor.execute("""
    insert into foo (id, data) values (10, 'ten')
""")

cursor.execute("""
    with bar as (select * from foo)
    select * from bar where id = 10
""")

assert cursor.description is not None


cursor.execute("""
    with bar as (select * from foo)
    select * from bar where id = 11
""")

assert cursor.description is not None


the second statement returns no rows and cursor.description is None.   Libraries like SQLAlchemy which rely on this to determine that the statement supports fetchone() and similar are blocked.

It is true that the sqlite interface does not support WITH currently.  It is an interesting question whether or not we could change this as a bug fix or not...I suppose it depends on whether or not it changes any behavior other than making .description work.

this patch fixes the bug.
It parses the with WITH statement.

ping
I'd appreciate a review of my patch.

It's more a matter of my finding time to fully research the problem and solution than an issue of reviewing the patch itself.  There is also potential cross-talk with other patches involving sqllite statement parsing.  Since the sqlite wrapper doesn't currently have an active maintainer, I'm afraid it may be a bit before any review of these issues happen.  If you can get other community members (doesn't have to be committers) to do reviews (of the concepts as well as the patch itself) that would be helpful.  (python-list might be a place to recruit some interest.)

(Personally I find the fact the the wrapper has to do statement parsing at all to be problematic, but there may be no good solution to that problem when you consider backward compatibility issues.)

I have now committed a fix in the pysqlite project at github. https://github.com/ghaering/pysqlite/commit/f67fa9c898a4713850e16934046f0fe2cba8c44c

I'll eventually merge it into the Python tree.

Are you going to merge it into 2.7.10?

It looks to me like a patch that could be merged as a bug fix.

is this going to be fixed in 2.7.10?

@Gerhard would you like that ported to cPython for you?

@Tom - I think that if the patch applies to 2.7.x we should apply it there since its very unlikely to break non-buggy code.

Attached is the patch ported to python2.7.  However, the test doesn't fail for me before I apply (compile) the fix.

I adapted the reproducer in msg220263 and added more tests.

New changeset 45ef062734d6 by Berker Peksag in branch '3.5':
Issue #21718: cursor.description is now available for queries using CTEs
https://hg.python.org/cpython/rev/45ef062734d6

New changeset cf18375732ae by Berker Peksag in branch 'default':
Issue #21718: Merge from 3.5
https://hg.python.org/cpython/rev/cf18375732ae

will the fix be backported to 2.7?

messages: + msg262791

stage: test needed -> patch review

messages: + msg248639

messages: + msg248066
versions: + Python 3.6

messages: + msg233385
nosy: + ghaering

nosy: + tanner
messages: + msg229537

keywords: + patch