SQL Parsing bug in SqliteStudio 3.0.7

Problems, tips, hints - all about usage.

SQL Parsing bug in SqliteStudio 3.0.7

Postby vonh » Mon May 16, 2016 1:06 am

Giving the database definitions below the query shouldn't work.

Database Definition
Code: Select all
--
-- Text encoding used: windows-1252
--
PRAGMA foreign_keys = off;
BEGIN TRANSACTION;

-- Table: queries
CREATE TABLE queries (
    qry_id          INTEGER       PRIMARY KEY AUTOINCREMENT,
    qry_task_id     INTEGER,
    qry_tskg_id     INTEGER,
    qry_seq_order   INTEGER       DEFAULT (9999),
    qry_code        TEXT,
    qry_notes       TEXT,
    qry_name        VARCHAR (128),
    qry_description VARCHAR (128),
    qry_lang        VARCHAR (32)
);


-- Table: task_groups
CREATE TABLE task_groups (
    tskg_id          INTEGER                   NOT NULL,
    tskg_project_id  INTEGER,
    tskg_order       INTEGER,
    tskg_description [CHARACTER VARYING] (255),
    tskg_name        VARCHAR (64),
    tskg_notes       TEXT,
    CONSTRAINT task_groups_pk PRIMARY KEY (
        tskg_id
    ),
    CONSTRAINT task_groups_projects_fk FOREIGN KEY (
        tskg_project_id
    )
    REFERENCES projects (prj_projectid) MATCH SIMPLE
                                        ON UPDATE CASCADE
                                        ON DELETE RESTRICT
);


-- Table: tasks
CREATE TABLE tasks (
    tsk_task_id     INTEGER                   NOT NULL,
    tsk_tskg_id     INTEGER                   REFERENCES task_groups (tskg_id),
    tsk_projectid   INTEGER,
    tsk_description [CHARACTER VARYING] (255),
    tsk_name        VARCHAR (64),
    tsk_notes       TEXT,
    tsk_statusnotes TEXT,
    CONSTRAINT tasks_pk PRIMARY KEY (
        tsk_task_id
    )
);


-- Table: projects
CREATE TABLE projects (
    prj_projectid     INTEGER                   NOT NULL
                                                PRIMARY KEY AUTOINCREMENT,
    prj_clientid      INTEGER,
    prj_projectno     INTEGER,
    prj_description   [CHARACTER VARYING] (255),
    prj_name          VARCHAR (64),
    prj_startdate     DATE                      DEFAULT CURRENT_TIMESTAMP,
    prj_datecompleted DATE,
    prj_deadline      DATE,
    prj_status        [CHARACTER VARYING] (16)  DEFAULT 'NOTIFIED',
    prj_loggednotes   TEXT
);


COMMIT TRANSACTION;
PRAGMA foreign_keys = on;


Faulty SQL as the field t.tskg_id is in task_groups (t2) and not in tasks (t) the query should not execute successfully yet it does
Code: Select all
SELECT  q2.qry_id, q2.qry_name, q2.qry_description, q2.qry_code, q2.qry_lang, q2.qry_task_id, t.tsk_task_id,
      t.tsk_tskg_id, t.tsk_description, t.tsk_name, t.tskg_id,   t2.tskg_description, t2.tskg_name,
      t2.tskg_project_id, p.prj_projectid, p.prj_description, p.prj_name
    FROM  queries q2 left join tasks t on q2.qry_task_id=t.tsk_task_id left join task_groups t2 on t.tsk_tskg_id = t2.tskg_id
    left join projects p on t2.tskg_project_id=p.prj_projectid;
vonh
 
Posts: 6
Joined: Sat Feb 06, 2016 10:35 pm

Re: SQL Parsing bug in SqliteStudio 3.0.7

Postby Googie » Mon May 16, 2016 9:34 am

Indeed. Noted to be fixed.
Googie
Site Admin
 
Posts: 754
Joined: Wed Jul 27, 2011 8:04 pm

Re: SQL Parsing bug in SqliteStudio 3.0.7

Postby Googie » Sun May 29, 2016 12:30 pm

Fixed for 3.0.8.
Googie
Site Admin
 
Posts: 754
Joined: Wed Jul 27, 2011 8:04 pm


Return to Usage

Who is online

Users browsing this forum: No registered users and 1 guest