`LIMIT N` causes "no query solution" ?

[asg017]

select * from temp.label_reader('test_labels.csv') limit 10;

[opustecnica]

Having the same issue and this is how I am temporarily overcoming it: SELECT rowid, * FROM temp.ip_reader('H:\Temp\detect_ips.csv.zst') WHERE rowid <= 10 Needless to say this would require some ranking if we where to apply other WHERE statements.

PS: Spectacularly fast, particularly when querying zst compressed files. Thank you!

[asg017]

I know the cause of the issue and it'll be fixed in the next release, I'm just lagging a bit on deploying it. Thanks for sharing!

[opustecnica]

Alex,

Do you mind if I attempt to fix it? I'd like to fix both the parsing of the column names with spaces and the 'limit' issue above. before I dive in, do you have any hint or suggestions?

THX! :-)

[asg017]

Hey @opustecnica! I actually think this issue was fixed in a recent version of sqlite-xsv, but I'm not 100% sure. v0.2.1-alpha.9 might contain the fix, but I can verify it later. tldr it's a small bug in the xBestIndex method

Do you mind sharing the column names with spaces problem? Feel free to file a separate issue as well

[opustecnica]

Alex,

Testing on:

#----------------------- xsv_version()

v0.2.1-alpha.9 #----------------------- [this still fails] "SELECT * FROM temp.xsv_5m_Sales_Records_Reader('D:\Temp\5m-Sales-Records.csv.zst') LIMIT 10" "SQL logic error no query solution" #----------------------- [this works, albeit slowly.] "SELECT rowid, * FROM temp.xsv_5m_Sales_Records_Reader('D:\Temp\5m-Sales-Records.csv.zst') WHERE rowid <= 10" rowid Region Country Item_Type

---

1 Australia and Oceania        Palau        Office Supplies
2 Europe                       Poland       Beverages

...

[asg017]

@opustecnica just published v0.2.1-alpha.11, which should allow for LIMIT constraints on xsv_reader tables. Can you see if it fixes your issue?

[opustecnica]

SUCCESS!

xsv_version()
-------------
v0.2.1-alpha.11

CREATE VIRTUAL TABLE IF NOT EXISTS temp.xsv_5m_Sales_Records_Reader USING csv_reader(Region TEXT, Country TEXT, Item_Type TEXT);
SELECT * FROM temp.xsv_5m_Sales_Records_Reader('D:\Temp\5m-Sales-Records.csv.zst') LIMIT 10;

Region                       Country      Item_Type
------                       -------      ---------
Australia and Oceania        Palau        Office Supplies
Europe                       Poland       Beverages
North America                Canada       Cereal
Europe                       Belarus      Snacks
Middle East and North Africa Oman         Cereal
Sub-Saharan Africa           Burkina Faso Office Supplies
Europe                       Montenegro   Personal Care
Middle East and North Africa Azerbaijan   Cosmetics
Sub-Saharan Africa           South Sudan  Clothes
North America                Greenland    Personal Care

I am sure you already know this, but the usefulness of the xsv_reader approach (common schema) on large (chronologically organized) csv.zst archives is awesome!