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

[zzzeek]

BPO	21718
Nosy	@rbtcollins, @bitdancer, @berkerpeksag
Files	with2.diff: fix f67fa9c898a4713850e16934046f0fe2cba8c44c.patch issue21718_tests.diff

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = None
closed_at = <Date 2016-08-21.16:29:53.083>
created_at = <Date 2014-06-11.13:11:09.718>
labels = ['type-bug', 'library']
title = 'sqlite3 cursor.description seems to rely on incomplete statement parsing for detection'
updated_at = <Date 2016-08-21.16:32:28.512>
user = 'https://bugs.python.org/zzzeek'

bugs.python.org fields:

activity = <Date 2016-08-21.16:32:28.512>
actor = 'tanner'
assignee = 'ghaering'
closed = True
closed_date = <Date 2016-08-21.16:29:53.083>
closer = 'berker.peksag'
components = ['Library (Lib)']
creation = <Date 2014-06-11.13:11:09.718>
creator = 'zzzeek'
dependencies = []
files = ['36952', '40184', '42354']
hgrepos = []
issue_num = 21718
keywords = ['patch']
message_count = 14.0
messages = ['220263', '220268', '229537', '231424', '231439', '233385', '240124', '240135', '243933', '248066', '248639', '262791', '273301', '273303']
nosy_count = 7.0
nosy_names = ['ghaering', 'rbcollins', 'r.david.murray', 'zzzeek', 'python-dev', 'berker.peksag', 'tanner']
pr_nums = []
priority = 'normal'
resolution = 'fixed'
stage = 'resolved'
status = 'closed'
superseder = None
type = 'behavior'
url = 'https://bugs.python.org/issue21718'
versions = ['Python 2.7', 'Python 3.5', 'Python 3.6']

[zzzeek]

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.

[bitdancer]

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.

[tanner]

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

[tanner]

ping
I'd appreciate a review of my patch.

[bitdancer]

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.)

[ghaering]

I have now committed a fix in the pysqlite project at github. ghaering/pysqlite@f67fa9c

I'll eventually merge it into the Python tree.

[tanner]

Are you going to merge it into 2.7.10?

[bitdancer]

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

[tanner]

is this going to be fixed in 2.7.10?

[rbtcollins]

@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.

[bitdancer]

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

[berkerpeksag]

I adapted the reproducer in msg220263 and added more tests.

[python-dev]

New changeset 45ef062734d6 by Berker Peksag in branch '3.5':
Issue bpo-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 bpo-21718: Merge from 3.5
https://hg.python.org/cpython/rev/cf18375732ae

[tanner]

will the fix be backported to 2.7?