CTE not working

[anthezium]

This should work, right? (emails.csv is there, works with other queries)
q -d, -H "WITH alias AS (SELECT * FROM ./emails.csv) SELECT * FROM alias"

Instead, I get:
No files matching 'alias' have been found

Do I need to do something special to let it know that the alias is an alias?

[anthezium]

q version 1.5.0
Copyright (C) 2012-2014 Harel Ben-Attia (harelba@gmail.com, @harelba on twitter)
http://harelba.github.io/q/

[jungle-boogie]

Does sqlite3 depend on q having support for CTE? If so, maybe q was made prior to CTE's in sqlite3.

[harelba]

Hi, sorry for the late reply, i'm on a long vacation...

Most sqlite capabilities are exposed through q without any special needs. However, CTE is not one of them. I'll take a deeper look and see if it's possible to add it.

Thanks
Harel

[anthezium]

Thanks for getting back on this!

The query works in sqlite3, so if you decide not to add CTEs (at least in the near term), you could just change this sentence on the site:
"All sqlite3 SQL constructs are supported, including joins across files (use an alias for each table)."

And add stuff to the "Limitations" section.

[harelba]

Of course, i'll definitely add this to the limitations if I can't come up with something good.

[mahiki]

this is a pretty big missing functionality. something like this makes it easier to generate histograms, for instance. I've been using q regularly, such a perfect use-case. But now I want to do more, and my workflow is pushing toward copying file to local postgres db.

If q can't run CTE, and I have to migrate workflow to postgres local db, there is not much sense in continuing to use q because I would already have invested effort in managing a postgres workflow for local csv files.

[bitti]

I don't really know about CTEs but it seems their usecase which can not be achieved with subqueries is in recursive queries. If you don't need recursive queries you should be fine with using subqueries, which are not supported by q in the from clause but there is a workaround: #102

[mahiki]

yes you are right that a subquery satisfies CTE functionality, and I don't need recursive CTE for generic use-cases in my work. I guess the note about sqlite getting CTE functionality gave me hope.

I'm aware of the subquery limitation and the workaround, but based on your reminder I took another look at #102. It works great, although I have to lookup the quoting syntax pretty much every time.

On balance, this is easier than the steps of creating a schema in postgres, and copying the file in.

Still -- if its possible to gain the new sqlite CTE functionality, it would be excellent.

[vi]

Can q recognize that things after AS are not to be interpreted as filenames and just left as is?

[harelba]

Can q recognize that things after AS are not to be interpreted as filenames and just left as is?

Yes, AS is supported, and actually needed when performing JOINs

[vi]

$ python bin/q.py  'with eee as ( select 9 ) select 4'
4

$ python3 bin/q.py  'with eee as ( select 9 ) select * from eee'
No files matching 'eee' have been found

[vi]

Maybe special table names like nofile_eee can be used to signal q not to try to read any files?

diff --git a/bin/q.py b/bin/q.py
index b41451f..de773bf 100755
--- a/bin/q.py
+++ b/bin/q.py
@@ -406,6 +406,9 @@ class Sql(object):
                     qtable_name = qtable_name[:qtable_name.index(')')]
                     self.sql_parts[idx + 1] = qtable_name

+                if qtable_name.startswith("nofile_"):
+                    idx += 1
+                    continue
                 self.qtable_names.add(qtable_name)

                 if qtable_name not in self.qtable_name_positions.keys():

Or maybe there can be implemented a mode where each file-to-table mapping is provided separately and individually?

[harelba]

@vi The issue is not with reading literal SELECTs (e.g. q "select 9" works well in q). CTE syntax itself is not supported because of complications in analyzing the query strings to detect table names.

Perhaps there's a new package for analyzing the SQL in python which can help with this. I'll do some checking.

[vi]

With the diff above some my use cases which required saving temporary CSV files start working without temporary files.

[harelba]

@vi the idea of q is to provide seamless use of SQL, without any need for special casing with other parameters (e.g. user knowledge).

If the CTE use case will get more upvotes, i'd consider it a higher priority for trying to find a proper solution for this.