Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Error running PRQL query with REGEXP #50

Closed
richb-hanover opened this issue Jun 3, 2024 · 10 comments
Closed

Error running PRQL query with REGEXP #50

richb-hanover opened this issue Jun 3, 2024 · 10 comments

Comments

@richb-hanover
Copy link
Contributor

richb-hanover commented Jun 3, 2024

Workaround found: #50 (comment)

Using qStudio 3.03 on macOS 12.7.5 with Java 16.0.1. I can open the Property in Lyme.sqlite database and use short PRQL queries. For example this query returns six substantially identical rows with different dates:

from ScrapedData
select {
    SD_PID,
    SD_Owner,
    SD_Version,
    SD_CollectedOn,
    }
filter SD_PID == 5

But if I try to execute a longer query (for example, Non-conf_Buildings.prql) I get the following error.

image

I will note that all three PRQL reference implementations (PRQL Playground 0.11.3, PRQL VSCode extension 0.11.3, and PRQL DevContainer 0.11.5) do not give this error and generate identical SQL code for this query.

This might be a prqlc problem - but I'm reporting it here. What other information could I provide? Thanks.

Jun 02, 2024 9:18:01 PM com.timestored.docs.OpenDocumentsModel setSelectedDocument
INFO: setSelectedDocument: Non-conf_Buildings.prql
Jun 02, 2024 9:18:09 PM com.timestored.qstudio.CommonActions sendQuery
WARNING: Send Query Error
java.io.IOException: Non-zero exit value for runArgs:Error:
     ╭─[:114:22]
     │
 114 │ derive { StateRoad = isStateRoad sd.SD_Street_Address }
     │                      ─────┬─────
     │                           ╰─────── Ambiguous name
     │
     │ Help: could be any of: czd.`isStateRoad`, sd.`isStateRoad`
─────╯



	at com.timestored.qstudio.CommonActions.compilePRQL(CommonActions.java:614)
	at com.timestored.qstudio.CommonActions.sendQuery(CommonActions.java:564)
	at com.timestored.qstudio.CommonActions.sendQuery(CommonActions.java:532)
	at com.timestored.qstudio.CommonActions.access$000(CommonActions.java:98)
	at com.timestored.qstudio.CommonActions$8.actionPerformed(CommonActions.java:284)
	at java.desktop/javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1972)
	at java.desktop/javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2313)
	at java.desktop/javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:405)
	at java.desktop/javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:262)
	at java.desktop/javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:279)
	at java.desktop/java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:297)
	at java.desktop/java.awt.Component.processMouseEvent(Component.java:6617)
	at java.desktop/javax.swing.JComponent.processMouseEvent(JComponent.java:3342)
	at java.desktop/java.awt.Component.processEvent(Component.java:6382)
	at java.desktop/java.awt.Container.processEvent(Container.java:2264)
	at java.desktop/java.awt.Component.dispatchEventImpl(Component.java:4993)
	at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2322)
	at java.desktop/java.awt.Component.dispatchEvent(Component.java:4825)
	at java.desktop/java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4934)
	at java.desktop/java.awt.LightweightDispatcher.processMouseEvent(Container.java:4563)
	at java.desktop/java.awt.LightweightDispatcher.dispatchEvent(Container.java:4504)
	at java.desktop/java.awt.Container.dispatchEventImpl(Container.java:2308)
	at java.desktop/java.awt.Window.dispatchEventImpl(Window.java:2773)
	at java.desktop/java.awt.Component.dispatchEvent(Component.java:4825)
	at java.desktop/java.awt.EventQueue.dispatchEventImpl(EventQueue.java:772)
	at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:721)
	at java.desktop/java.awt.EventQueue$4.run(EventQueue.java:715)
	at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:95)
	at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:745)
	at java.desktop/java.awt.EventQueue$5.run(EventQueue.java:743)
	at java.base/java.security.AccessController.doPrivileged(AccessController.java:391)
	at java.base/java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(ProtectionDomain.java:85)
	at java.desktop/java.awt.EventQueue.dispatchEvent(EventQueue.java:742)
	at java.desktop/java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:203)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:124)
	at java.desktop/java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:113)
	at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:109)
	at java.desktop/java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
	at java.desktop/java.awt.EventDispatchThread.run(EventDispatchThread.java:90)

Jun 02, 2024 9:18:09 PM com.timestored.jgrowl.FadingGrowler show
SEVERE: Error: Problem sending query to server
Jun 02, 2024 9:18:09 PM com.timestored.qstudio.model.QueryManager sendQRtoListeners
INFO: queryResultReturned: QueryResult{query=let SubDistrict = dist road -> case [
  dist == "RD" && road == 1 => "Rural-State Road",
  dist == "RD" && road == 0 => "Rural-Town Road",
  dist == "BD" => "Commercial",
  dist == "LCD" => "LymeCommon/Ctr",
  dist == "ELD" => "EastLyme",
  dist == "SKIWAY" => "Skiway",
  dist == "MFD" => "MtnForest",
  true => dist,
]
# Largest allowed building footprint as percentage of lot size in a zoning district
let AllowedBldgFprtPct = zd -> case [
  zd == "LymeCommon/Ctr" => 6.0,    # percent
  zd == "Commercial" => 10.0,       # percent
  zd == "Rural-State Road" => 2.0,  # percent
  zd == "Rural-Town Road" => 2.0,   # percent
  zd == "EastLyme" => 1.0,          # percent
  zd == "Skiway" => 1.0,            # percent
  zd == "MtnForest" => 1.0,         # percent
  true => "?",
]
# 90% Conforming Building footprint as percentage of lot size in a zoning district
let AllowedBldgFprtPct90 = zd -> case [
  zd == "LymeCommon/Ctr" => 6.0,    # percent
  zd == "Commercial" => 10.0,       # percent
  zd == "Rural-State Road" => 2.0,  # percent
  zd == "Rural-Town Road" => 2.0,   # percent
  zd == "EastLyme" => 1.0,          # percent
  zd == "Skiway" => 1.0,            # percent
  zd == "MtnForest" => 1.0,         # percent
  true => "?",
]
# Largest allowed Lot Coverage as percentage of lot size in a zoning district
let AllowedLotCoverage = zd -> case [
  zd == "LymeCommon/Ctr" => 12.0,    # percent
  zd == "Commercial" => 20.0,        # percent
  zd == "Rural-State Road" => 12.0,  # percent
  zd == "Rural-Town Road" => 12.0,   # percent
  zd == "EastLyme" => 12.0,          # percent
  zd == "Skiway" => 2.0,             # percent
  zd == "MtnForest" => 2.0,          # percent
  true => "?",
]
# 90% Conforming Lot Coverage as percentage of lot size in a zoning district
let AllowedLotCoverage90 = zd -> case [
  zd == "LymeCommon/Ctr" => 12.0,    # percent
  zd == "Commercial" => 20.0,        # percent
  zd == "Rural-State Road" => 12.0,  # percent
  zd == "Rural-Town Road" => 12.0,   # percent
  zd == "EastLyme" => 12.0,          # percent
  zd == "Skiway" => 2.0,             # percent
  zd == "MtnForest" => 2.0,          # percent
  true => "?",
]
# Max Gross Floor Area for a zoning district
let AllowedGFA = zd -> case [
  zd == "LymeCommon/Ctr" => 6000.0,
  zd == "Commercial" => 14000.0,
  zd == "Rural-State Road" => 14000.0,
  zd == "Rural-Town Road" => 14000.0,
  zd == "EastLyme" => 14000.0,
  zd == "Skiway" => 14000.0,
  zd == "MtnForest" => 14000.0,
  true => "?",
]
# 90% Conforming Gross Floor Area for a zoning district
let AllowedGFA90 = zd -> case [
  zd == "LymeCommon/Ctr" => 6000.0,
  zd == "Commercial" => 14000.0,
  zd == "Rural-State Road" => 14000.0,
  zd == "Rural-Town Road" => 14000.0,
  zd == "EastLyme" => 14000.0,
  zd == "Skiway" => 14000.0,
  zd == "MtnForest" => 14000.0,
  true => "?",
]

# ============ begin  query ==============
from buildings
# filter BL_BuildingNum == 1
select {
  BL_PID,
  BL_Gross_Floor_Area,
  BL_Stories,
  BL_Year_Built,
  # ComputedFootprint=BL_Gross_Floor_Area / BL_Stories * 1.0,
  # # Allowance for 15 x 30 ft driveway
  # BldgPlusDriveway = ComputedFootprint + 450,
  # Era = case [
  #   BL_Year_Built < 1900 => 1,
  #   BL_Year_Built < 1983 => 2,
  #   BL_Year_Built < 2000 => 3,
  #   true => 4,
  # ]
}
# Sum up the computed footprint for each building on a PID
group { BL_PID } (
  aggregate {
    NumBldgs = count this,
    BiggestBldgFprt = max (BL_Gross_Floor_Area / BL_Stories),
    TotalBldgFprt = sum (BL_Gross_Floor_Area / BL_Stories),
    TotalGFA = sum BL_Gross_Floor_Area,
  }
)
# join with the parcel data from Vision
join sd=ScrapedData (this.BL_PID == that.SD_PID)
filter SD_Version == 14 # latest data
filter SD_Lot_Size != 0               # ignore zero lot size
filter !(SD_Description ~= "CONDO")
filter BiggestBldgFprt != null

join side:left czd = CorrectedZoningDistrict (sd.SD_PID == czd.CZD_PID)

derive { StateRoad = isStateRoad sd.SD_Street_Address }

# Clean up districts
derive { District = CleanZoningDistrict sd.SD_Zoning_District }
derive { RealDistrict = District ?? czd.CZD_Actual }

# ZDistrict distinguishes between on-state-road and not
derive { ZDistrict = SubDistrict RealDistrict StateRoad }

# Compute a placeholder for driveway lot coverage in LCD
derive DrivewayFprt = case [
  ZDistrict == "LymeCommon/Ctr" => 450,
  true => 0
]

# ===== Start collecting the columns needed
select {
  PID = sd.SD_PID,
  `Street Address` = sd.SD_Street_Address,
  ZoningDistr = ZDistrict,
  LotSize = sd.SD_Lot_Size,
  NumBldgs,

   # Examine Building Footprints
  MaxAllowedFprtPct = (AllowedBldgFprtPct ZDistrict),
  BiggestBldgFprt,
  MaxAllowedBldg = sd.SD_Lot_Size * 43560 * MaxAllowedFprtPct / 100.0,
  BldgNonConforming = BiggestBldgFprt > MaxAllowedBldg,

  # Examine Lot Coverage
  TotalBldgFprt,
  DrivewayFprt,
  LotCoverageSF = TotalBldgFprt+DrivewayFprt,
  LotCoveragePct = LotCoverageSF * 100.0 / (43560 * sd.SD_Lot_Size),
  MaxAllowedLotCoverage = (AllowedLotCoverage ZDistrict),
  CoverageNonConforming = LotCoveragePct > MaxAllowedLotCoverage,

  # Examine Total Gross Floor Area
  TotalGFA,
  MaxAllowedGFA = (AllowedGFA ZDistrict),
  GFANonConforming = TotalGFA > MaxAllowedGFA
}
sort { PID }

# ===== Format the columns for display =====
# select {
#   PID,
#   `Street Address`,
#   ZoningDistr,
#   LotSize = sig_fig LotSize 2,
#   NumBldgs,

#   # Examine Building Footprints
#   BiggestBuilding=(sig_fig BiggestBldgFprt 0),
#   MaxAllowedSF = (sig_fig MaxAllowedBldg 0),
#   MaxAllowedBldgPct=prtpct MaxAllowedFprtPct,
#   BldgNonConforming,

#   # Examine Lot Coverage
#   BuildingSF = (sig_fig TotalBldgFprt 2),
#   DrivewaySF = (sig_fig DrivewayFprt 2),
#   TotalSF = (sig_fig LotCoverageSF 2),
#   ActCoverage =(prtpct LotCoveragePct),
#   AllowedCoverage = prtpct MaxAllowedLotCoverage,
#   CoverageNonConforming,

#   # Examine Total Gross Floor Area
#   GrossFloorArea = TotalGFA,
#   AllowedFloorArea = MaxAllowedGFA,
#   GFANonConforming = TotalGFA > MaxAllowedGFA
# }
# sort { ZoningDistr, -BldgNonConforming, -CoverageNonConforming, -GFANonConforming }

# ===== Groupings =====

# ===== Group (too) large buildings by zoning district =====
#
# group { ZoningDistr } (
#   aggregate {
#     `MaxBldgFprt%` = min MaxAllowedFprtPct,
#     NonConfBuildings = sum BldgNonConforming,
#     Total = count this,
#   }
# )
# # Compute the percent of small parcels vs all parcels
# derive PctNonConforming = (prtpct 100.0*NonConfBuildings/Total)
# # Sort by zoning district
# sort { ZoningDistr }

# ===== Group Lot Coverage by zoning district =====
#
# group { ZoningDistr } (
#   aggregate {
#     `MaxLotCoverage` = prtpct (min MaxAllowedLotCoverage),
#     NonConformingLots = sum CoverageNonConforming,
#     Total = count this,
#   }
# )
# # Compute the percent of small parcels vs all parcels
# derive PctNonConforming = (prtpct 100.0*NonConformingLots/Total)
# # Sort by zoning district
# sort { ZoningDistr }

# ===== Group Gross Floor Area by zoning district =====
#
# group { ZoningDistr } (
#   aggregate {
#     `Allowed Gross Floor Area` = min MaxAllowedGFA,
#     NonConforming = sum GFANonConforming,
#     Total = count this,
#   }
# )
# # Compute the percent of small parcels vs all parcels
# derive PctNonConforming = (prtpct 100.0*NonConforming/Total)
# # Sort by zoning district
# sort { ZoningDistr }

# ===== Results shown in separate spreadsheet
, wasResult=false, wasException=true}
Jun 02, 2024 9:18:09 PM com.timestored.sqldash.model.AbstractWidget configChanged
INFO: Widget 0 configChanged
Jun 02, 2024 9:18:15 PM com.timestored.docs.BackgroundDocumentsSaver requestSave
INFO: skipping persistOpenDocuments
Jun 02, 2024 9:18:45 PM com.timestored.docs.BackgroundDocumentsSaver requestSave
INFO: skipping persistOpenDocuments

@ryanhamilton
Copy link
Collaborator

ryanhamilton commented Jun 3, 2024

On my qStudio this compiles but then sqlite fails as it doesn't support REGEXP:
xerial/sqlite-jdbc#60
I'm not sure how to fix that REGEXP issue as it really seems like the JDBC driver should handle that?

Can you try pasting the PRQL into your specific prqlc that you use for qStudio?

@richb-hanover
Copy link
Contributor Author

Good troubleshooting tip. I'll let you know how it turns out.

@richb-hanover
Copy link
Contributor Author

Here's the output from prqlc on my computer. A couple thoughts:

  1. I installed PRQL 0.11.3 using Homebrew on my Mac.
  2. PRQL should use different facilities to customize the SQL based on the target statement at the top. So the prql target:sql.sqlite in the Non-Conf_Buildings.prql should handle SQLite's lack of a builtin regex. I did paste the code below into DB4S and it worked fine.
  3. I note that the output of the prqlc always includes a comment as the final line showing the version of the compiler. In the debugging output of the java -jar qstudio.jar command, I don't think I've seen that line in the SQL that was sent to the database.

Thanks for all this good work!

√ PRQL_Queries % which prqlc
/usr/local/bin/prqlc

√ PRQL_Queries % prqlc --version
prqlc 0.11.3

√ PRQL_Queries % prqlc < Non-conf_Buildings.prql compile
WITH table_3 AS (
  SELECT
    COUNT(*) AS "NumBldgs",
    MAX(("BL_Gross_Floor_Area" * 1.0 / "BL_Stories")) AS "BiggestBldgFprt",
    COALESCE(
      SUM(("BL_Gross_Floor_Area" * 1.0 / "BL_Stories")),
      0
    ) AS "TotalBldgFprt",
    COALESCE(SUM("BL_Gross_Floor_Area"), 0) AS "TotalGFA",
    "BL_PID"
  FROM
    buildings
  GROUP BY
    "BL_PID"
),
table_2 AS (
  SELECT
    table_3."NumBldgs",
    table_3."BiggestBldgFprt",
    table_3."TotalBldgFprt",
    table_3."TotalGFA",
    sd."SD_Lot_Size",
    sd."SD_Street_Address",
    sd."SD_PID",
    sd."SD_Zoning_District"
  FROM
    table_3
    JOIN "ScrapedData" AS sd ON table_3."BL_PID" = sd."SD_PID"
  WHERE
    sd."SD_Version" = 14
    AND sd."SD_Lot_Size" <> 0
    AND NOT sd."SD_Description" REGEXP 'CONDO'
    AND table_3."BiggestBldgFprt" IS NOT NULL
),
table_1 AS (
  SELECT
    table_2."NumBldgs",
    table_2."BiggestBldgFprt",
    table_2."TotalBldgFprt",
    table_2."TotalGFA",
    table_2."SD_Lot_Size",
    table_2."SD_Street_Address",
    table_2."SD_PID",
    CASE
      WHEN table_2."SD_Street_Address" REGEXP 'ORFORD ' THEN 1
      WHEN table_2."SD_Street_Address" REGEXP 'DARTMOUTH COLLEGE' THEN 1
      WHEN table_2."SD_Street_Address" REGEXP 'DORCHESTER' THEN 1
      WHEN table_2."SD_Street_Address" REGEXP 'EAST THETFORD' THEN 1
      WHEN table_2."SD_Street_Address" REGEXP 'NORTH THETFORD' THEN 1
      ELSE 0
    END AS _expr_2,
    CASE
      WHEN table_2."SD_Zoning_District" = 'ES' THEN 'RD'
      WHEN table_2."SD_Zoning_District" = 'R' THEN 'RD'
      WHEN table_2."SD_Zoning_District" = 'SFR' THEN 'RD'
      WHEN table_2."SD_Zoning_District" = 'URD' THEN 'RD'
      WHEN table_2."SD_Zoning_District" = 'LCD' THEN 'LCD'
      WHEN table_2."SD_Zoning_District" = 'CD' THEN 'LCD'
      WHEN table_2."SD_Zoning_District" = 'LDC' THEN 'LCD'
      WHEN table_2."SD_Zoning_District" = 'SD' THEN 'RD'
      WHEN table_2."SD_Zoning_District" = '' THEN NULL
      ELSE table_2."SD_Zoning_District"
    END AS _expr_3,
    czd."CZD_Actual",
    table_2."SD_Zoning_District"
  FROM
    table_2
    LEFT JOIN "CorrectedZoningDistrict" AS czd ON table_2."SD_PID" = czd."CZD_PID"
),
table_0 AS (
  SELECT
    "NumBldgs",
    "BiggestBldgFprt",
    "TotalBldgFprt",
    "TotalGFA",
    CASE
      WHEN COALESCE(_expr_3, "CZD_Actual") = 'RD'
      AND _expr_2 = 1 THEN 'Rural-State Road'
      WHEN COALESCE(_expr_3, "CZD_Actual") = 'RD'
      AND _expr_2 = 0 THEN 'Rural-Town Road'
      WHEN COALESCE(_expr_3, "CZD_Actual") = 'BD' THEN 'Commercial'
      WHEN COALESCE(_expr_3, "CZD_Actual") = 'LCD' THEN 'LymeCommon/Ctr'
      WHEN COALESCE(_expr_3, "CZD_Actual") = 'ELD' THEN 'EastLyme'
      WHEN COALESCE(_expr_3, "CZD_Actual") = 'SKIWAY' THEN 'Skiway'
      WHEN COALESCE(_expr_3, "CZD_Actual") = 'MFD' THEN 'MtnForest'
      ELSE COALESCE(_expr_3, "CZD_Actual")
    END AS _expr_0,
    "SD_Lot_Size",
    "SD_Street_Address",
    "SD_PID",
    COALESCE(_expr_3, "CZD_Actual") AS _expr_1,
    _expr_2
  FROM
    table_1
)
SELECT
  "SD_PID" AS "PID",
  "SD_Street_Address" AS "Street Address",
  _expr_0 AS "ZoningDistr",
  "SD_Lot_Size" AS "LotSize",
  "NumBldgs",
  CASE
    WHEN _expr_0 = 'LymeCommon/Ctr' THEN 6.0
    WHEN _expr_0 = 'Commercial' THEN 10.0
    WHEN _expr_0 = 'Rural-State Road' THEN 2.0
    WHEN _expr_0 = 'Rural-Town Road' THEN 2.0
    WHEN _expr_0 = 'EastLyme' THEN 1.0
    WHEN _expr_0 = 'Skiway' THEN 1.0
    WHEN _expr_0 = 'MtnForest' THEN 1.0
    ELSE '?'
  END AS "MaxAllowedFprtPct",
  "BiggestBldgFprt",
  (
    "SD_Lot_Size" * 43560 * CASE
      WHEN _expr_0 = 'LymeCommon/Ctr' THEN 6.0
      WHEN _expr_0 = 'Commercial' THEN 10.0
      WHEN _expr_0 = 'Rural-State Road' THEN 2.0
      WHEN _expr_0 = 'Rural-Town Road' THEN 2.0
      WHEN _expr_0 = 'EastLyme' THEN 1.0
      WHEN _expr_0 = 'Skiway' THEN 1.0
      WHEN _expr_0 = 'MtnForest' THEN 1.0
      ELSE '?'
    END * 1.0 / 100.0
  ) AS "MaxAllowedBldg",
  "BiggestBldgFprt" > (
    "SD_Lot_Size" * 43560 * CASE
      WHEN _expr_0 = 'LymeCommon/Ctr' THEN 6.0
      WHEN _expr_0 = 'Commercial' THEN 10.0
      WHEN _expr_0 = 'Rural-State Road' THEN 2.0
      WHEN _expr_0 = 'Rural-Town Road' THEN 2.0
      WHEN _expr_0 = 'EastLyme' THEN 1.0
      WHEN _expr_0 = 'Skiway' THEN 1.0
      WHEN _expr_0 = 'MtnForest' THEN 1.0
      ELSE '?'
    END * 1.0 / 100.0
  ) AS "BldgNonConforming",
  "TotalBldgFprt",
  CASE
    WHEN _expr_0 = 'LymeCommon/Ctr' THEN 450
    ELSE 0
  END AS "DrivewayFprt",
  "TotalBldgFprt" + CASE
    WHEN _expr_0 = 'LymeCommon/Ctr' THEN 450
    ELSE 0
  END AS "LotCoverageSF",
  (
    (
      "TotalBldgFprt" + CASE
        WHEN _expr_0 = 'LymeCommon/Ctr' THEN 450
        ELSE 0
      END
    ) * 100.0 * 1.0 / (43560 * "SD_Lot_Size")
  ) AS "LotCoveragePct",
  CASE
    WHEN _expr_0 = 'LymeCommon/Ctr' THEN 12.0
    WHEN _expr_0 = 'Commercial' THEN 20.0
    WHEN _expr_0 = 'Rural-State Road' THEN 12.0
    WHEN _expr_0 = 'Rural-Town Road' THEN 12.0
    WHEN _expr_0 = 'EastLyme' THEN 12.0
    WHEN _expr_0 = 'Skiway' THEN 2.0
    WHEN _expr_0 = 'MtnForest' THEN 2.0
    ELSE '?'
  END AS "MaxAllowedLotCoverage",
  (
    (
      "TotalBldgFprt" + CASE
        WHEN _expr_0 = 'LymeCommon/Ctr' THEN 450
        ELSE 0
      END
    ) * 100.0 * 1.0 / (43560 * "SD_Lot_Size")
  ) > CASE
    WHEN _expr_0 = 'LymeCommon/Ctr' THEN 12.0
    WHEN _expr_0 = 'Commercial' THEN 20.0
    WHEN _expr_0 = 'Rural-State Road' THEN 12.0
    WHEN _expr_0 = 'Rural-Town Road' THEN 12.0
    WHEN _expr_0 = 'EastLyme' THEN 12.0
    WHEN _expr_0 = 'Skiway' THEN 2.0
    WHEN _expr_0 = 'MtnForest' THEN 2.0
    ELSE '?'
  END AS "CoverageNonConforming",
  "TotalGFA",
  CASE
    WHEN _expr_0 = 'LymeCommon/Ctr' THEN 6000.0
    WHEN _expr_0 = 'Commercial' THEN 14000.0
    WHEN _expr_0 = 'Rural-State Road' THEN 14000.0
    WHEN _expr_0 = 'Rural-Town Road' THEN 14000.0
    WHEN _expr_0 = 'EastLyme' THEN 14000.0
    WHEN _expr_0 = 'Skiway' THEN 14000.0
    WHEN _expr_0 = 'MtnForest' THEN 14000.0
    ELSE '?'
  END AS "MaxAllowedGFA",
  "TotalGFA" > CASE
    WHEN _expr_0 = 'LymeCommon/Ctr' THEN 6000.0
    WHEN _expr_0 = 'Commercial' THEN 14000.0
    WHEN _expr_0 = 'Rural-State Road' THEN 14000.0
    WHEN _expr_0 = 'Rural-Town Road' THEN 14000.0
    WHEN _expr_0 = 'EastLyme' THEN 14000.0
    WHEN _expr_0 = 'Skiway' THEN 14000.0
    WHEN _expr_0 = 'MtnForest' THEN 14000.0
    ELSE '?'
  END AS "GFANonConforming"
FROM
  table_0
ORDER BY
  "PID"

-- Generated by PRQL compiler version:0.11.3 (https://prql-lang.org)
√ PRQL_Queries %

@richb-hanover richb-hanover changed the title Error running complex PRQL query Error running ~~complex~~ PRQL query with REGEXP Jun 4, 2024
@richb-hanover richb-hanover changed the title Error running ~~complex~~ PRQL query with REGEXP Error running PRQL query with REGEXP Jun 4, 2024
@richb-hanover
Copy link
Contributor Author

Ahah! I know more now... The query is gagging on the REGEXP call.

SQLite doesn't bundle the REGEXP facility by default. The docs at https://www.sqlite.org/lang_expr.html explain:

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)".

https://stackoverflow.com/questions/5071601/how-do-i-use-regex-in-a-sqlite-query gives many hints. Also check out https://github.com/nalgeon/sqlean/tree/main

@ryanhamilton
Copy link
Collaborator

That's frustrating. It seems either:

  1. PRQL should try to avoid REGEXP as it's non-standard or
  2. We need a JDBC sqlite version that provides it?

Either way I'm not sure there's much qStudio should do.
I have added logic to detct "prql target:" and to avoid qStudio sending a target if it's in the code. The logic / command is now:


		String target = getPrqlTarget(jdbcTypes);
		String[] commands = new String[] { "prqlc","compile", "--hide-signature-comment", "--color", "never" };
		if(target != null && !qry.contains("prql target:")) {
			commands = new String[] { "prqlc","compile", "--hide-signature-comment", "--color", "never", "--target", target };
		}

@richb-hanover
Copy link
Contributor Author

Frustrating, indeed. Another thought (although I confess that I don't really understand all the layers of this...) Googling "sqlite jdbc regexp" brings up this StackOverflow article: https://stackoverflow.com/a/28587200/1827982

Does this give you any useful info? Many thanks...

@ryanhamilton
Copy link
Collaborator

Oh I kind of see how it could be done, I'm just super reluctant to start owning that code.
qStudio tries to support 30+ databases, the only hope of doing that is by pushing most the work to them. :(
How about we raise an issue on sqlean asking for a JDBC driver?

@richb-hanover
Copy link
Contributor Author

Would that make your life straightforward? (I totally understand not owning all kinds of code...)

Is raising an issue over there something you could do? Thanks.

@ryanhamilton
Copy link
Collaborator

Raised in both places that I think it could or should be fixed. Closing here.

@richb-hanover
Copy link
Contributor Author

richb-hanover commented Jun 6, 2024

Workaround: It's not perfect, but for many situations, all you need is a LIKE %string% feature. Here's an implementation of like() that is a bit of a crock (it uses S-strings) but it works:

# implement a "like" function in lieu of a regex
let like = fld str -> s"{fld} like '%' || {str} || '%' "
from foo  
select bar
filter (like bar "stuff")

Changing all the ~= operators/comparisons to use like() allows the query to run correctly.

It would still be terrific to bundle in a SQLite version that supports REGEXP...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants