Here is a simplified test case for the purposes of illustration.
;; a view class with a one-to many join using two comparison slots
(sql:def-view-class view-class1 ()
((id1
:db-kind :key
:db-constraints :not-null
:type integer
:initarg :id1)
(id2
:db-kind :key
:db-constraints :not-null
:type integer
:initarg :id2)
(tests
:db-kind :join
:accessor vc1-tests
:db-info (:join-class view-class2
:home-key (id1 id2)
:foreign-key (id1 id2)
:set t)))
(:base-table view-class1))
;; a view class which is the target of the above join
(sql:def-view-class view-class2 ()
((id1
:db-kind :key
:db-constraints :not-null
:type integer)
(id2
:db-kind :key
:db-constraints :not-null
:type integer)
(id3
:db-kind :key
:db-constraints :not-null
:type integer
:initarg :id3))
(:base-table view-class2))
;; function to populate the database
(defun populate-database ()
(let ((vc2-item1 (make-instance 'view-class2 :id3 1))
(vc2-item2 (make-instance 'view-class2 :id3 2))
(vc2-item3 (make-instance 'view-class2 :id3 3))
(vc2-item4 (make-instance 'view-class2 :id3 4))
(vc1-item1 (make-instance 'view-class1 :id1 0 :id2 1))
(vc1-item2 (make-instance 'view-class1 :id1 1 :id2 1)))
(if (null sql:*default-database*)
(sql::connect (list nil "test" "marcusp" nil)
:database-type :postgresql))
(ignore-errors
(sql:drop-view-from-class 'view-class1)
(sql:drop-view-from-class 'view-class2))
(sql:create-view-from-class 'view-class1)
(sql:create-view-from-class 'view-class2)
(sql:add-to-relation vc1-item1 'tests vc2-item1)
(sql:add-to-relation vc1-item1 'tests vc2-item2)
(sql:add-to-relation vc1-item2 'tests vc2-item3)
(sql:add-to-relation vc1-item2 'tests vc2-item4)
(sql:store-instance vc2-item1)
(sql:store-instance vc2-item2)
(sql:store-instance vc2-item3)
(sql:store-instance vc2-item4)
(sql:store-instance vc1-item1)
(sql:store-instance vc1-item2)))
Having loading uncommonsql and the test case in cmucl-18d/x86f I
do the following:
USER> (populate-database)
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
'view_class1pk' for table 'view_class1'
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index
'view_class2pk' for table 'view_class2'
T
USER> (vc1-tests (car (sql:select 'view-class1)))
While using database /test/marcusp:
The SQL statement: "SELECT
VIEW_CLASS2.ID1,VIEW_CLASS2.ID2,VIEW_CLASS2.ID3 FROM VIEW_CLASS2 WHERE
(VIEW_CLASS2.ID1 = 0,VIEW_CLASS2.ID2 = 1)"
Caused this error: parser: parse error at or near "".
(errno is FATAL-ERROR)
Restarts:
0: [ABORT] Return to Top-Level.
Debug (type H for help)
(MAISQL-POSTGRESQL::|(PCL::FAST-METHOD DATABASE-QUERY (T
POSTGRESQL-DATABASE))|
#<unused-arg>
#<unused-arg>
"SELECT VIEW_CLASS2.ID1,VIEW_CLASS2.ID2,VIEW_CLASS2.ID3 FROM
VIEW_CLASS2 WHERE (VIEW_CLASS2.ID1 = 0,VIEW_CLASS2.ID2 = 1)"
#<MAISQL-POSTGRESQL:POSTGRESQL-DATABASE {484BC995}>)
Source: (ERROR 'MAISQL-SQL-ERROR :DATABASE DATABASE :EXPRESSION ...)
0]
It seems that the query is being turned into an sql statement in
which the WHERE clause is a list of the conditions rather than having
them connected by the AND operator; i.e,
WHERE (VIEW_CLASS2.ID1 = 0,VIEW_CLASS2.ID2 = 1)
rather than
WHERE (VIEW_CLASS2.ID1 = 0 AND VIEW_CLASS2.ID2 = 1)
I would be very grateful if anyone can help.
Marcus